impala开窗函数分析函数最全版（强烈建议收藏）
impala开窗函数/分析函数最全版（强烈建议收藏）
本⽂基于impala3.2版本，所有的内置开窗分析函数；
⽂章⽬录
⼀、补充概念说明
1、什么是开窗函数？
开窗函数是为⾏定义⼀个窗⼝（⼀个窗⼝⾥⾯是需要操作的⾏的集合），然后对窗⼝的⾏的组合值进⾏操作，不需要使⽤group by进⾏分区，能够在⼀⾏⾥⾯返回基础⾏的列和聚合列（简单来说就是结果多出⼀列聚合值，函数⾥⾯带有over）
2、开窗函数的分类？
⼀般分为“排名开窗函数” 和 “聚合开窗函数”
3、本章节中使⽤的表格数据如下：表名 student_score
stuid course score
1003语⽂65
1002语⽂79
1001英语79
1004英语87
1001语⽂81
1002英语87
1004语⽂97
1003英语65
⼆、排名开窗函数
1、排名开窗函数的使⽤语法
Function（args,…argn） OVER（【PARTITION BY <…>】【ORDER BY <…>】【<window_clause>】）
可以单独使⽤order by ，也可以和partition by ⼀起使⽤；window_clause是指窗⼝的范围；
2、排名开窗函数（排名函数）
序号函数名称使⽤说明
1rank（）over（）分组排序⽣成排名（重复的话序号⼀样，然后跳过重复的序号）
2row_number（）over（）分组排序⽣成排名（不区分重复）
3dense_rank（）over（）分组排序⽣成排名（重复的话序号⼀样，然后顺排）
⽰例如下：
--使⽤⽰例:
select*,
RANK()OVER(partition by course order by score desc)as r,
ROW_NUMBER()OVER(partition by course order by score desc)as rn,
DENSE_RANK()OVER(partition by course order by score desc)as dr
FROM student_score
--输出结果：（因为数据太长，这⾥只展⽰英语课的结果）
stuid  course    score    r  rn  dr
1004英语87111
1002英语87121
1001英语79332
1003英语65443
3、排名开窗函数（切⽚函数）
序号函数名称使⽤说明
1ntile（）over（）分组内将数据切⽚
⽰例如下：
--使⽤⽰例：
select*,
ntile(2)OVER(partition by course order by score desc)as nt1,--将分组内数据切成2⽚
ntile(4)OVER(partition by course order by score desc)as nt2    --将分组内数据切成4⽚
FROM student_score
--输出结果：（因为数据太长，这⾥只展⽰英语课的结果）
stuid  course    score    nt1  nt2
1004英语8711
1002英语8712
1001英语7923
1003英语6524
4、排名开窗函数（序列分析函数）
序号函数名称使⽤说明
1cume_dist（）over（）⼩于等于当前⾏值的⾏数/总⾏数2percent_rank（）over（）(当前rank值-1) / (总⾏数-1)
⽰例如下：
--使⽤⽰例：
select*,
CUME_DIST()OVER(partition by course order by score)as cd,
rank()OVER(partition by course order by score desc)as r,--分区排名当前⾏rank值
SUM(1)OVER(partition by course)as s,--分组内数据总⾏数
PERCENT_RANK()OVER(partition by course order by score desc)as pr  --=(r-1)/(s-1)
FROM student_score
--输出结果：（因为数据太长，这⾥只展⽰英语课的结果）
stuid  course    score    cd    rn  s  pr
1004英语871140
1002英语871140
1001英语790.5340.66666666667
1003英语650.25441
groupby是什么函数5、排名开窗函数（lead+lag）
序号函数名称使⽤说明
1lead（col，n，default）over（）⽤于统计窗⼝内往下第n⾏值
序号函数名称使⽤说明
2lag（col，n，default）over（）⽤于统计窗⼝内往上第n⾏值
⽰例如下：
第⼀个参数是列名，第⼆个参数是指往上/下第n⾏（可选，默认为1），第三个参数为默认值（往上往下为null的时候取默认值，否则取null）
--使⽤⽰例：
select*,
lead(stuid)over(partition by course order by score)as lead1,--stuid往上1⾏值
lag(stuid)over(partition by course order by score)as lag1,--stuid往下1⾏值
lead(stuid,2,0)over(partition by course order by score)as lead2,--stuid往上2⾏值，默认为0
lag(stuid,2,0)over(partition by course order by score)as lag2    --stuid往下2⾏值，默认为0
FROM student_score
--输出结果：（因为数据太长，这⾥只展⽰英语课的结果）
stuid  course    score    lead1    lag1    lead2    lag2
1002英语87100410010
1004英语871001100210030
1001英语791003100401002
1003英语65100101004
6、排名开窗函数（first_value+last_value）
序号函数名称使⽤说明
1first_value（col）over（）获取统计窗⼝内排名第⼀的列值
2last_value（col）over（）获取统计窗⼝内排名最后的列值
⽰例如下：
last_value默认的窗⼝范围是：rows between unbounded preceding and current row（当前⾏数据与当前⾏之前的数据的⽐较）；所以如果要去当前⾏与整个分组的数据⽐较需要加上：rows between unbounded preceding and unbounded following
--使⽤⽰例：
select*,
FIRST_VALUE(score)OVER(partition by course order by score desc)as fv,
LAST_VALUE(score)OVER(partition by course order by score desc)as lv,
LAST_VALUE(score)OVER(partition by course order by score desc
rows between unbounded preceding and unbounded following)as lv1
FROM student_score
--输出结果：（因为数据太长，这⾥只展⽰英语课的结果）
stuid  course    score    fv    lv    lv1
1002英语87878765
1004英语87878765
1001英语79877965
1003英语65876565
三、聚合开窗函数
1、聚合开窗函数的使⽤语法
Function（args,…argn） OVER（【PARTITION BY <…>】【ORDER BY <…>】【<window_clause>】）
聚合开窗函数能使⽤partition by ; order by ⼀般配合window_clause（指窗⼝的范围）⼀起使⽤；
2、聚合开窗函数
序号函数名称使⽤说明
1count（sal） over （）获取统计窗⼝内的指定列的数据量序号函数名称使⽤说明
2max（sal） over （）获取统计窗⼝内的指定列的最⼤值3min（sal） over （）获取统计窗⼝内的指定列的最⼩值4avg（sal） over （）获取统计窗⼝内的指定列的平均值5sum（sal） over （）获取统计窗⼝内的指定列的和
⽰例如下：
--使⽤⽰例：
select*,
count(score)OVER(partition by course)as c,
max(score)OVER(partition by course)as max1,
min(score)OVER(partition by course)as min1,
avg(score)OVER(partition by course)as a,
sum(score)OVER(partition by course)as s,--统计分组内所有的⾏
sum(score)OVER(partition by course order by score desc)as s1,--统计起点到当前⾏（重复的会算⼀
⾏）sum(score)OVER(partition by course order by score desc--统计起点到当前⾏
rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)as s2,
sum(score)OVER(partition by course order by score desc--统计当前⾏+往前1⾏
rows BETWEEN1PRECEDING and CURRENT row)as s3,
sum(score)OVER(partition by course order by score desc--统计当前⾏+往前⼀⾏+往后⼀⾏rows BETWEEN1PRECEDING and1FOLLOWING)as s4,
sum(score)OVER(partition by course order by score desc--统计当前⾏+往后所有⾏
rows BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING)as s5
from student_score
--输出结果：（因为数据太长，这⾥只展⽰英语课的结果）
stuid  course  score c  max1  min1  a      s    s1    s2  s3  s4    s5
1004英语874876579.53181748787174318
1002英语874876579.5318174174174253231
1001英语794876579.5318253253166231144
1003英语654876579.531831831814414465

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。