说明
在SELECT语句中加入窗口函数,计算窗口函数的结果时,数据会按照窗口定义中的partition by和order by语句进行分区和排序。如果没有partition by语句,则仅有一个分区,包含全部数据。如果没有order by语句,则分区内的数据会按照任意顺序排布,最终生成一个确定的数据流。之后对于每一行数据(当前行),会按照窗口定义中的frame_clause从数据流中截取一段数据,构成当前行的窗口。窗口函数会根据窗口中包含的数据,计算得到窗口函数针对当前行对应的输出结果。
一、ROW_NUMBER
计算行号,从1开始递增
select deptno, ename, sal, row_number() over (partition by deptno order by sal desc) as nums from emp;
二、RANK
计算排名。排名可能不连续。
select deptno, ename, sal, rank() over (partition by deptno order by sal desc) as nums from emp;
三、DENSE_RANK
计算排名。排名是连续的。
select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) as nums from emp;
四、PERCENT_RANK
计算排名。输出百分比格式。
select deptno, ename, sal, percent_rank(sal) over (partition by deptno order by sal desc) as sal_new from emp;
五、CUME_DIST
计算累计分布。
select deptno, ename, sal, concat(round(cume_dist() over (partition by deptno order by sal desc)*100,2),'%') as cume_dist from emp;
六、NTILE
将数据顺序切分成N等份,返回数据所在等份的编号(从1到N)。
select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;
七、LAG
取当前行往前(朝分区头部方向)第N行数据的值。
select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;
八、LEAD
取当前行往后(朝分区尾部方向)第N行数据的值。
select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;
九、FIRST_VALUE
取当前行所对应窗口的第一条数据的值。
select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;
十、LAST_VALUE
取当前行所对应窗口的最后一条数据的值。
select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;
十一、NTH_VALUE
取当前行所对应窗口的第N条数据的值。
select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;
十二、CLUSTER_SAMPLE
用户随机抽样。返回True表示该行数据被抽中。
select deptno, sal
from (
select deptno, sal, cluster_sample(5, 1) over (partition by deptno) as flag
from emp
) sub
where flag = true;
十三、COUNT
计算窗口中的记录数。
select sal, count(sal) over (partition by sal) as count from emp;
--开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select sal, count(sal) over (partition by sal order by sal) as count from emp;
十四、MIN
计算窗口中的最小值。
select deptno, sal, min(sal) over (partition by deptno) from emp;
十五、MAX
计算窗口中的最大值。
select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;
十六、AVG
对窗口中的数据求平均值。
select deptno, sal, avg(sal) over (partition by deptno) from emp;
--关闭Hive兼容模式。
set odps.sql.hive.compatible=false;
--执行如下SQL命令。
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;
十七、SUM
对窗口中的数据求和。
select deptno, sal, sum(sal) over (partition by deptno) from emp;
--开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;
十八、MEDIAN
计算窗口中的中位数。
select deptno, sal, median(sal) over (partition by deptno) from emp;
十九、STDDEV
计算总体标准差。是STDDEV_POP的别名。
select deptno, sal, stddev(sal) over (partition by deptno) from emp;
--开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;
二十、STDDEV_SAMP
计算样本标准差。
select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;
二十一、使用限制
窗口函数的使用限制如下:
窗口函数只能出现在select语句中。
窗口函数中不能嵌套使用窗口函数和聚合函数。
窗口函数不能和同级别的聚合函数一起使用。
参考:https://help.aliyun.com/document_detail/48969.html