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、排名开窗函数（排名函数）

序号	函数名称	使用说明
1	rank（）over（）	分组排序生成排名（重复的话序号一样，然后跳过重复的序号）
2	row_number（）over（）	分组排序生成排名（不区分重复）
3	dense_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	英语	      87	 1	 1	 1      
1002	英语	      87	 1	 2	 1
1001	英语	      79	 3	 3	 2
1003	英语	      65	 4	 4	 3

3、排名开窗函数（切片函数）

序号	函数名称	使用说明
1	ntile（）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	英语	      87	 1	   1	       
1002	英语	      87	 1	   2	 
1001	英语	      79	 2	   3	 
1003	英语	      65	 2	   4	 

4、排名开窗函数（序列分析函数）

序号	函数名称	使用说明
1	cume_dist（）over（）	小于等于当前行值的行数/总行数
2	percent_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	英语	      87	 1     1    4   0   
1002	英语	      87	 1     1    4   0
1001	英语	      79	 0.5   3    4   0.66666666667
1003	英语	      65	 0.25  4    4   1

5、排名开窗函数（lead+lag）

序号	函数名称	使用说明
1	lead（col，n，default）over（）	用于统计窗口内往下第n行值
2	lag（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	英语	      87	 1004             1001     0
1004	英语	      87	 1001     1002    1003     0
1001	英语	      79	 1003     1004    0        1002
1003	英语	      65	          1001    0        1004

6、排名开窗函数（first_value+last_value）

序号	函数名称	使用说明
1	first_value（col）over（）	获取统计窗口内排名第一的列值
2	last_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	英语	      87	 87    87     65
1004	英语	      87	 87    87     65
1001	英语	      79	 87    79     65
1003	英语	      65	 87    65     65

三、聚合开窗函数

1、聚合开窗函数的使用语法

Function（args,…argn） OVER（【PARTITION BY <…>】【ORDER BY <…>】【<window_clause>】）

聚合开窗函数能使用partition by ; order by 一般配合window_clause（指窗口的范围）一起使用；

2、聚合开窗函数

序号	函数名称	使用说明
1	count（sal） over （）	获取统计窗口内的指定列的数据量
2	max（sal） over （）	获取统计窗口内的指定列的最大值
3	min（sal） over （）	获取统计窗口内的指定列的最小值
4	avg（sal） over （）	获取统计窗口内的指定列的平均值
5	sum（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 BETWEEN 1 PRECEDING and  CURRENT row) as s3,
sum(score) OVER(partition by course order by score desc          --统计当前行+往前一行+往后一行
                rows BETWEEN 1 PRECEDING and  1 FOLLOWING ) 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	英语	   87	4	87	  65	79.5	318	 174	87	 87	  174	318
1002	英语	   87	4	87	  65	79.5	318	 174	174	 174  253	231
1001	英语	   79	4	87	  65	79.5	318	 253	253	 166  231	144
1003	英语	   65	4	87	  65	79.5	318	 318	318	 144  144	65