大数据-玩转数据-MaxCompute窗口函数

说明

在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;
1

二、RANK

计算排名。排名可能不连续。

select deptno, ename, sal, rank() over (partition by deptno order by sal desc) as nums from emp;
1

三、DENSE_RANK

计算排名。排名是连续的。

select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) as nums from emp;
1

四、PERCENT_RANK

计算排名。输出百分比格式。

select deptno, ename, sal, percent_rank(sal) over (partition by deptno order by sal desc) as sal_new from emp;
1

五、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;
1

六、NTILE

将数据顺序切分成N等份，返回数据所在等份的编号（从1到N）。

select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;
1

七、LAG

取当前行往前（朝分区头部方向）第N行数据的值。

select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;
1

八、LEAD

取当前行往后（朝分区尾部方向）第N行数据的值。

select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;
1

九、FIRST_VALUE

取当前行所对应窗口的第一条数据的值。

select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;
1

十、LAST_VALUE

取当前行所对应窗口的最后一条数据的值。

select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;
1

十一、NTH_VALUE

取当前行所对应窗口的第N条数据的值。

select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;
1

十二、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;
1
2
3
4
5
6

十三、COUNT

计算窗口中的记录数。

select sal, count(sal) over (partition by sal) as count from emp;  
1

--开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select sal, count(sal) over (partition by sal order by sal) as count from emp; 
1
2
3
4

十四、MIN

计算窗口中的最小值。

select deptno, sal, min(sal) over (partition by deptno) from emp;
1

十五、MAX

计算窗口中的最大值。

select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;
1

十六、AVG

对窗口中的数据求平均值。

select deptno, sal, avg(sal) over (partition by deptno) from emp;
1

--关闭Hive兼容模式。
set odps.sql.hive.compatible=false;
--执行如下SQL命令。
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;
1
2
3
4

十七、SUM

对窗口中的数据求和。

select deptno, sal, sum(sal) over (partition by deptno) from emp;
1

--开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;
1
2
3
4

十八、MEDIAN

计算窗口中的中位数。

select deptno, sal, median(sal) over (partition by deptno) from emp;
1

十九、STDDEV

计算总体标准差。是STDDEV_POP的别名。

select deptno, sal, stddev(sal) over (partition by deptno) from emp;
1

--开启Hive兼容模式。
set odps.sql.hive.compatible=true;
--执行如下SQL命令。
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;
1
2
3
4

二十、STDDEV_SAMP

计算样本标准差。

select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;
1

二十一、使用限制

窗口函数的使用限制如下：

窗口函数只能出现在select语句中。
窗口函数中不能嵌套使用窗口函数和聚合函数。
窗口函数不能和同级别的聚合函数一起使用。
参考：https://help.aliyun.com/document_detail/48969.html