您可以在MaxCompute SQL中使用窗口函数,灵活地分析处理工作。
- 窗口函数只能出现在
select
子句中。 - 窗口函数中不能嵌套使用窗口函数和聚合函数。
- 窗口函数不能和同级别的聚合函数一起使用。
- 在一个MaxCompute SQL语句中,最多可以使用5个窗口函数。
窗口函数语法说明
window_func() over (partition by [col1,col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] windowing_clause)
partition by
部分用来指定开窗的列。- 分区列的值相同的行被视为在同一个窗口内。同一窗口内最多包含1亿行数据(建议不超过500万行),否则运行时报错。该限制适用于所有内建窗口函数。
说明 例如
group by
UID,同1个UID对应的记录视为同一个窗口,即1个UID对应记录数最多包含1亿行数据(建议不超过500万行)。 order by
用于指定数据在一个窗口内如何排序。说明 当遇到相同的order by
值的时候,排序结果不稳定。为了减少随机性,应当尽可能保持order by
值的唯一性。windowing_clause
部分可以用rows
指定开窗方式,有以下两种方式:rows between x preceding|following and y preceding|following
表示窗口范围是从前或后x行到前或后y行。rows x preceding|following
窗口范围是从前或后第x行到当前行。
说明- x、y必须为大于等于0的整数常量,限定范围0~10000,值为0时表示当前行。您必须指定
order by
才可以用rows
方式指定窗口范围。 - 并非所有的窗口函数都可以使用
rows
指定开窗方式。支持这种用法的窗口函数有AVG、COUNT、MAX、MIN、STDDEV和SUM。
COUNT
- 命令格式
Bigint count([distinct] expr) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
- 命令说明
该函数用于计算计数值。
- 参数说明
expr
:任意类型,当值为NULL时,该行不参与计算。当指定distinct
关键字时,表示取唯一值的计数值。partition by [col1, col2…]
:指定开窗口的列。order by col1 [asc|desc], col2[asc|desc]
:不指定order by
时,返回当前窗口内expr
的计数值,指定order by
时返回结果以指定的顺序排序,并且值为当前窗口内从开始行到当前行的累计计数值。
说明 当指定distinct
关键字时,不能使用order by
。 - 返回值说明
返回BIGINT类型。
- 示例
假设存在表
test_src
,表中存在BIGINT类型的列user_id
。select user_id,count(user_id) over (partition by user_id) as count from test_src; +---------+------------+ | user_id | count | +---------+------------+ | 1 | 3 | | 1 | 3 | | 1 | 3 | | 2 | 1 | | 3 | 1 | +---------+------------+ --不指定order by时,返回当前窗口内user_id的计数值。 select user_id,count(user_id) over (partition by user_id order by user_id) as count from test_src; +---------+------------+ | user_id | count | +---------+------------+ | 1 | 1 | --窗口为起始点。 | 1 | 2 | --到当前行共计两条记录,返回2。 | 1 | 3 | | 2 | 1 | | 3 | 1 | +---------+------------+ --指定order by时,返回当前窗口内从开始行到当前行的累计计数值。
order by
的值重复,非Hive兼容和Hive兼容的处理方式不同:
- 当非Hive兼容时,返回值为各自行的COUNT。
set odps.sql.hive.compatible=false; select user_id, price, count(price) over (partition by user_id order by price) as count from test_src; +------------+------------+------------+ | user_id | price | count | +------------+------------+------------+ | 1 | 4.5 | 1 | -- 窗口起始行 | 1 | 5.5 | 2 | -- 第2行的count是2 | 1 | 5.5 | 3 | -- 第3行的count是3 | 1 | 6.5 | 4 | | 2 | NULL | 0 | | 2 | 3.0 | 1 | | 3 | NULL | 0 | | 3 | 4.0 | 1 | +------------+------------+------------+
- 当Hive兼容时,返回值为相同值的最后一行的COUNT。
set odps.sql.hive.compatible=true; select user_id, price, count(price) over (partition by user_id order by price) as count from test_src; +------------+------------+------------+ | user_id | price | count | +------------+------------+------------+ | 1 | 4.5 | 1 | -- 窗口起始行 | 1 | 5.5 | 3 | -- 第2行和第3行的price一致,则第2行的count为第3行的count | 1 | 5.5 | 3 | -- 第3行的count是3 | 1 | 6.5 | 4 | | 2 | NULL | 0 | | 2 | 3.0 | 1 | | 3 | NULL | 0 | | 3 | 4.0 | 1 | +------------+------------+------------+
AVG
- 命令格式
avg([distinct] expr) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
- 命令说明
该函数用于计算平均值。
- 参数说明
distinct
:当指定distinct
关键字时,表示取唯一值的平均值。expr
:DOUBLE类型、DECIMAL类型。- 当输入值为STRING、BIGINT类型时,会隐式转换到DOUBLE类型后参与运算,输入其它数据类型抛异常。
- 当输入值为NULL时,该行不参与计算。
- BOOLEAN类型不允许参与计算。
partition by [col1, col2...]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:不指定order by
时,返回当前窗口内所有值的平均值。指定order by
时,返回结果以指定的方式排序,并且返回窗口内从开始行到当前行的累计平均值。
说明 指明distinct
关键字时,不能使用order by
。 - 返回值说明
返回DOUBLE类型。
order by
的值重复,非Hive兼容和Hive兼容的处理方式不同:
- 当非Hive兼容时,返回值为各自行的AVG。
set odps.sql.hive.compatible=false; select user_id, price, avg(price) over (partition by user_id order by price) from test_src; +------------+------------+-------------------+ | user_id | price | _c2 | +------------+------------+-------------------+ | 1 | 4.5 | 4.5 | --窗口起始行 | 1 | 5.5 | 5.0 | --从第1行到第2行的AVG | 1 | 5.5 | 5.166666666666667 | --从第1行到第3行的AVG | 1 | 6.5 | 5.5 | | 2 | NULL | NULL | | 2 | 3.0 | 3.0 | | 3 | NULL | NULL | | 3 | 4.0 | 4.0 | +------------+------------+-------------------+
- 当Hive兼容时,返回值为相同值的最后一行的AVG。
set odps.sql.hive.compatible=true; select user_id, price, avg(price) over (partition by user_id order by price) from test_src; +------------+------------+-------------------+ | user_id | price | _c2 | +------------+------------+-------------------+ | 1 | 4.5 | 4.5 | --窗口起始行 | 1 | 5.5 | 5.166666666666667 | --从第1行到第3行的AVG | 1 | 5.5 | 5.166666666666667 | --从第1行到第3行的AVG | 1 | 6.5 | 5.5 | | 2 | NULL | NULL | | 2 | 3.0 | 3.0 | | 3 | NULL | NULL | | 3 | 4.0 | 4.0 | +------------+------------+-------------------+
MAX
- 命令格式
max([distinct] expr) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
- 命令说明
该函数用于计算最大值。
- 参数说明
expr
:除BOOLEAN外的任意类型。当值为NULL时,该行不参与计算。当指定distinct
关键字时,表示取唯一值的最大值(指定该参数与否对结果没有影响)。partition by [col1, col2…]
:指定开窗口的列。order by [col1[asc|desc], col2[asc|desc
:不指定order by
时,返回当前窗口内的最大值。指定order by
时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的最大值。
说明 指明distinct
关键字时,不能使用order by
。 - 返回值说明
返回值的类型同
expr
类型。
MIN
- 命令格式
min([distinct] expr) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
- 命令说明
该函数用于计算最小值。
- 参数说明
expr
:除BOOLEAN外的任意类型,当值为NULL时,该行不参与计算。当指定distinct
关键字时,表示取唯一值的最小值(指定该参数与否对结果没有影响)。partition by [col1, col2…]
:指定开窗口的列。order by [col1[asc|desc], col2[asc|desc
:不指定order by
时,返回当前窗口内的最小值。指定order by
时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的最小值。
说明 指明distinct
关键字时,不能使用order by
。 - 返回值说明
返回值类型同
expr
类型。
MEDIAN
- 命令格式
Double median(Double number1,number2...) over(partition by [col1, col2…]) Decimal median(Decimal number1,number2...) over(partition by [col1,col2…])
- 命令说明
该函数用于计算中位数最小值。
- 参数说明
- number1,number1…:DOUBLE类型或DECIMAL类型的数字。最多支持输入255个数字,至少要输入1个数字。
- 如果输入值是DOUBLE类型,会默认转成DOUBLE类型的数组参与计算。
- 当输入值为STRING类型或BIGINT类型,会隐式转换到DOUBLE类型后参与运算,输入为其他数据类型则抛异常。
- 当输入值为NULL时,返回NULL。
partition by [col1, col2…]
:指定开窗口的列。
- number1,number1…:DOUBLE类型或DECIMAL类型的数字。最多支持输入255个数字,至少要输入1个数字。
- 返回值说明
返回值类型为DOUBLE类型或DECIMAL类型。
STDDEV
- 命令格式
Double stddev([distinct] expr) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause]) Decimal stddev([distinct] expr) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
- 命令说明
该函数用于计算总体标准差。
- 参数说明
expr
:DOUBLE类型或DECIMAL类型。- 当输入值为STRING类型或BIGINT类型时,会隐式转换到DOUBLE类型后参与运算,其他类型抛异常。
- 当输入值为NULL时,该行不参与计算。
- 当指定
distinct
关键字时,表示计算唯一值的总体标准差。
说明- 当指定
distinct
关键字时,不能使用order by
。 stddev
还有一个别名函数stddev_pop
,用法和stddev
一样。
partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:不指定order by
时,返回当前窗口内的总体标准差。指定order by
时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的总体标准差。
- 返回值说明
输入值为DECIMAL类型时,返回DECIMAL类型,否则返回DOUBLE类型。
- 示例
select window, seq, stddev_pop('1\01') over (partition by window order by seq);
order by
的值重复,非Hive兼容和Hive兼容的处理方式不同:
- 当非Hive兼容时,返回值为各自行的STDDEV。
set odps.sql.hive.compatible=false; select user_id, price, stddev(price) over (partition by user_id order by price) from test_src; +------------+------------+--------------------+ | user_id | price | _c2 | +------------+------------+--------------------+ | 1 | 4.5 | 0.0 | --窗口起始行 | 1 | 5.5 | 0.5 | --从第1行到第2行的STDDEV | 1 | 5.5 | 0.4714045207910316 | --从第1行到第3行的STDDEV | 1 | 6.5 | 0.7071067811865475 | | 2 | NULL | NULL | | 2 | 3.0 | 0.0 | | 3 | NULL | NULL | | 3 | 4.0 | 0.0 | +------------+------------+--------------------+
- 当Hive兼容时,返回值为相同值的最后一行的STDDEV。
set odps.sql.hive.compatible=true; select user_id, price, stddev(price) over (partition by user_id order by price) from test_src; +------------+------------+--------------------+ | user_id | price | _c2 | +------------+------------+--------------------+ | 1 | 4.5 | 0.0 | --窗口起始行 | 1 | 5.5 | 0.4714045207910316 | --从第1行到第3行的STDDEV | 1 | 5.5 | 0.4714045207910316 | --从第1行到第3行的STDDEV | 1 | 6.5 | 0.7071067811865475 | | 2 | NULL | NULL | | 2 | 3.0 | 0.0 | | 3 | NULL | NULL | | 3 | 4.0 | 0.0 | +------------+------------+--------------------+
STDDEV_SAMP
- 命令格式
Double stddev_samp([distinct] expr) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause]) Decimal stddev_samp([distinct] expr) over((partition by [col1,col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
- 命令说明
该函数用于计算样本标准差。
- 参数说明
expr
:DOUBLE类型或DECIMAL类型。- 当输入值为STRING类型或BIGINT类型时,会隐式转换到DOUBLE类型后参与运算,其他类型抛异常。
- 当输入值为NULL时,该行不参与计算。
- 当指定
distinct
关键字时,表示计算唯一值的样本标准差。
说明 指明distinct
关键字时,不能使用order by
。partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:不指定order by
时,返回当前窗口内的样本标准差。指定order by
时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的样本标准差。
- 返回值说明
输入值为DECIMAL类型时,返回DECIMAL类型,否则返回DOUBLE类型。
SUM
- 命令格式
sum([distinct] expr) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
- 命令说明
该函数用于计算汇总值。
- 参数说明
expr
:DOUBLE类型、DECIMAL类型或BIGINT类型。- 当输入值为STRING类型时,会隐式转换到DOUBLE类型后参与运算,其他类型抛异常。
- 当输入值为NULL时,该行不参与计算。
- 当指定
distinct
关键字时,表示计算唯一值的汇总值。
partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:不指定order by
时,返回当前窗口内expr
的汇总值。指定order by
时,返回结果以指定的方式排序,并且返回当前窗口从首行至当前行的累计汇总值。
说明 指明distinct
关键字时,不能使用order by
。 - 返回值说明
- 输入值为BIGINT类型时,返回BIGINT类型。
- 输入值为DECIMAL类型时,返回DECIMAL类型。
- 输入值为DOUBLE类型或STRING类型时,返回DOUBLE类型。
order by
的值重复,非Hive兼容和Hive兼容的处理方式不同:
- 当非Hive兼容时,返回值为各自行的SUM。
set odps.sql.hive.compatible=false; select user_id, price, sum(price) over (partition by user_id order by price) from test_src; +------------+------------+------------+ | user_id | price | _c2 | +------------+------------+------------+ | 1 | 4.5 | 4.5 | --窗口起始行 | 1 | 5.5 | 10.0 | --从第1行到第2行的SUM | 1 | 5.5 | 15.5 | --从第1行到第3行的SUM | 1 | 6.5 | 22.0 | | 2 | NULL | NULL | | 2 | 3.0 | 3.0 | | 3 | NULL | NULL | | 3 | 4.0 | 4.0 | +------------+------------+------------+
- 当Hive兼容时,返回值为相同值的最后一行的SUM。
set odps.sql.hive.compatible=true; select user_id, price, sum(price) over (partition by user_id order by price) from test_src; +------------+------------+------------+ | user_id | price | _c2 | +------------+------------+------------+ | 1 | 4.5 | 4.5 | --窗口起始行 | 1 | 5.5 | 15.5 | --从第1行到第3行的SUM | 1 | 5.5 | 15.5 | --从第1行到第3行的SUM | 1 | 6.5 | 22.0 | | 2 | NULL | NULL | | 2 | 3.0 | 3.0 | | 3 | NULL | NULL | | 3 | 4.0 | 4.0 | +------------+------------+------------+
DENSE_RANK
- 命令格式
Bigint dense_rank() over(partition by [col1, col2…] order by [col1[asc|desc], col2[asc|desc]…])
- 命令说明
该函数用于计算连续排名。
col2
相同的行数据获得的排名相同。 - 参数说明
partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:指定排名依据的值。
- 返回值说明
返回BIGINT类型。
- 示例
表
emp
中的数据如下所示。| empno | ename | job | mgr | hiredate| sal| comm | deptno | 7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10 7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10 7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10
现在需要将所有职工根据部门分组,每个组内根据sal
做降序排序,获得职工自己组内的序号。--deptno(部门)作为开窗列,sal(薪水)作为结果返回时需要排序的值。 SELECT deptno, ename, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums FROM emp; --执行结果如下。 +------------+-------+------------+------------+ | deptno | ename | sal | nums | +------------+-------+------------+------------+ | 10 | JACCKA | 5000.0 | 1 | | 10 | KING | 5000.0 | 1 | | 10 | CLARK | 2450.0 | 2 | | 10 | WELAN | 2450.0 | 2 | | 10 | TEBAGE | 1300.0 | 3 | | 10 | MILLER | 1300.0 | 3 | | 20 | SCOTT | 3000.0 | 1 | | 20 | FORD | 3000.0 | 1 | | 20 | JONES | 2975.0 | 2 | | 20 | ADAMS | 1100.0 | 3 | | 20 | SMITH | 800.0 | 4 | | 30 | BLAKE | 2850.0 | 1 | | 30 | ALLEN | 1600.0 | 2 | | 30 | TURNER | 1500.0 | 3 | | 30 | MARTIN | 1250.0 | 4 | | 30 | WARD | 1250.0 | 4 | | 30 | JAMES | 950.0 | 5 | +------------+-------+------------+------------+
RANK
- 命令格式
Bigint rank() over(partition by [col1, col2…] order by [col1[asc|desc], col2[asc|desc]…])
- 命令说明
该函数用于计算排名。col2相同的行数据获得排名顺序下降。
- 参数说明
partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:指定排名依据的值。
- 返回值说明
返回BIGINT类型。
- 示例
表
emp
中的数据如下所示。| empno | ename | job | mgr | hiredate| sal| comm | deptno | 7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10 7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10 7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10
现在需要将所有职工根据部门分组,每个组内根据sal
做降序排序,获得职工自己组内的序号。--deptno(部门)作为开窗列,sal(薪水)作为结果返回时需要排序的值。 SELECT deptno,ename,sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums FROM emp; --执行结果如下。 +------------+-------+------------+------------+ | deptno | ename | sal | nums | +------------+-------+------------+------------+ | 10 | JACCKA | 5000.0 | 1 | | 10 | KING | 5000.0 | 1 | | 10 | CLARK | 2450.0 | 3 | | 10 | WELAN | 2450.0 | 3 | | 10 | TEBAGE | 1300.0 | 5 | | 10 | MILLER | 1300.0 | 5 | | 20 | SCOTT | 3000.0 | 1 | | 20 | FORD | 3000.0 | 1 | | 20 | JONES | 2975.0 | 3 | | 20 | ADAMS | 1100.0 | 4 | | 20 | SMITH | 800.0 | 5 | | 30 | BLAKE | 2850.0 | 1 | | 30 | ALLEN | 1600.0 | 2 | | 30 | TURNER | 1500.0 | 3 | | 30 | MARTIN | 1250.0 | 4 | | 30 | WARD | 1250.0 | 4 | | 30 | JAMES | 950.0 | 6 | +------------+-------+------------+------------+
LAG
- 命令格式
lag(expr,Bigint offset, default) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]])
- 命令说明
按偏移量取当前行之前第几行的值。如果当前行号为
rn
,则取行号为rn-offset
的值。 - 参数说明
expr
:任意类型。- offset:偏移量,BIGINT类型常量,取值大于0。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后执行。
- default:当offset指定的范围越界时的缺省值,常量,默认值为NULL。
partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:指定返回结果的排序方式。
- 返回值说明
返回值类型同
expr
类型。 - 示例
select seq, lag(seq+100, 1) over (partition by window order by seq) as r from sliding_window; --执行结果如下。 +------------+------------+ | seq | r | +------------+------------+ | 0 | NULL | | 1 | 100 | | 2 | 101 | | 3 | 102 | | 4 | 103 | | 5 | 104 | | 6 | 105 | | 7 | 106 | | 8 | 107 | | 9 | 108 | +------------+------------+
LEAD
- 命令格式
lead(expr,Bigint offset, default) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]])
- 命令说明
按偏移量取当前行之后第几行的值。如果当前行号为
rn
,则取行号为rn+offset
的值。 - 参数说明
expr
:任意类型。- offset:可选,偏移量,BIGINT类型常量,取值大于0。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后执行。
- default:当offset指定的范围越界时的缺省值,常量,默认值为NULL。
partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:指定返回结果的排序方式。
- 返回值说明
返回值类型同
expr
类型。 - 示例
select c_Double_a,c_String_b,c_int_a,lead(c_int_a,1) over(partition by c_Double_a order by c_String_b); select c_String_a,c_time_b,c_Double_a,lead(c_Double_a,1) over(partition by c_String_a order by c_time_b); select c_String_in_fact_num,c_String_a,c_int_a,lead(c_int_a) over(partition by c_String_in_fact_num order by c_String_a);
PERCENT_RANK
- 命令格式
percent_rank() over(partition by [col1, col2…] order by [col1[asc|desc], col2[asc|desc]…])
- 命令说明
该函数用于计算一组数据中某行的相对排名。
- 参数说明
partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:指定排名依据的值。
- 返回值说明
返回DOUBLE类型,值域为[0, 1],相对排名的计算方式为
(rank-1)/(number of rows -1)
。
ROW_NUMBER
- 命令格式
row_number() over(partition by [col1, col2…] order by [col1[asc|desc], col2[asc|desc]…])
- 命令说明
该函数用于计算行号,从1开始。
- 参数说明
partition by [col1, col2..]
:指定开窗口的列。order by col1[asc|desc], col2[asc|desc]
:指定结果返回时的排序的值。
- 返回值说明
返回BIGINT类型。
- 示例
假设表
emp
中的数据如下所示。| empno | ename | job | mgr | hiredate| sal| comm | deptno | 7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10 7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10 7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10
现在需要将所有职工根据部门分组,每个组内根据sal
做降序排序,获得职工自己组内的序号。SELECT deptno,ename,sal,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums--deptno(部门)作为开窗列,sal(薪水)作为结果返回时需要排序的值。 FROM emp; --执行结果如下。 +------------+-------+------------+------------+ | deptno | ename | sal | nums | +------------+-------+------------+------------+ | 10 | JACCKA | 5000.0 | 1 | | 10 | KING | 5000.0 | 2 | | 10 | CLARK | 2450.0 | 3 | | 10 | WELAN | 2450.0 | 4 | | 10 | TEBAGE | 1300.0 | 5 | | 10 | MILLER | 1300.0 | 6 | | 20 | SCOTT | 3000.0 | 1 | | 20 | FORD | 3000.0 | 2 | | 20 | JONES | 2975.0 | 3 | | 20 | ADAMS | 1100.0 | 4 | | 20 | SMITH | 800.0 | 5 | | 30 | BLAKE | 2850.0 | 1 | | 30 | ALLEN | 1600.0 | 2 | | 30 | TURNER | 1500.0 | 3 | | 30 | MARTIN | 1250.0 | 4 | | 30 | WARD | 1250.0 | 5 | | 30 | JAMES | 950.0 | 6 | +------------+-------+------------+------------+
CLUSTER_SAMPLE
- 命令格式
boolean cluster_sample([Bigint x, Bigint y]) over(partition by [col1, col2..])
- 命令说明
该函数用于分组抽样。
- 参数说明
- x:BIGINT类型常量,
x>=1
。如果指定参数y,x表示将一个窗口分为x份。否则,x表示在一个窗口中抽取x行记录(即有x行返回值为True)。x为NULL时,返回值为NULL。 - y:BIGINT类型常量,
y>=1
,y<=x
。表示从一个窗口分的x份中抽取y份记录(即y份记录返回值为True)。y为NULL时,返回值为NULL。 partition by [col1, col2]
:指定开窗口的列。
- x:BIGINT类型常量,
- 返回值说明
返回BOOLEAN类型。
- 示例
假设表
test_tbl
中有key
、value
两列。key
为分组字段,取值为groupa
、groupb
;value为分组字段对应的值。如下所示。+------------+--------------------+ | key | value | +------------+--------------------+ | groupa | -1.34764165478145 | | groupa | 0.740212609046718 | | groupa | 0.167537127858695 | | groupa | 0.630314566185241 | | groupa | 0.0112401388646925 | | groupa | 0.199165745875297 | | groupa | -0.320543343353587 | | groupa | -0.273930924365012 | | groupa | 0.386177958942063 | | groupa | -1.09209976687047 | | groupb | -1.10847690938643 | | groupb | -0.725703978381499 | | groupb | 1.05064697475759 | | groupb | 0.135751224393789 | | groupb | 2.13313102040396 | | groupb | -1.11828960785008 | | groupb | -0.849235511508911 | | groupb | 1.27913806620453 | | groupb | -0.330817716670401 | | groupb | -0.300156896191195 | | groupb | 2.4704244205196 | | groupb | -1.28051882084434 | +------------+--------------------+
如果您需要从每组中抽取约10%的值,可以使用以下MaxCompute SQL完成。select key, value from ( select key, value, cluster_sample(10, 1) over(partition by key) as flag from tbl ) sub where flag = true; --执行结果如下。 +-----+------------+ | key | value | +-----+------------+ | groupa | 0.167537127858695 | | groupb | 0.135751224393789 | +-----+------------+
CUME_DIST
- 命令格式
cume_dist() over(partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…]])
- 命令说明
求累计分布,相当于求分组中值大于等于当前值的行数占分组总行数的比例。
- 参数说明
比较大小的值由
order by
列指定。 - 返回值
分组中值大于等于当前值的行数占分组总行数的比例。
- 示例
假设表
emp
中的数据如下所示。| empno | ename | job | mgr | hiredate| sal| comm | deptno | 7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10 7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10 7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10
现在需要将所有职工根据部门分组,再求sal
在同一组内的前百分之几。SELECT deptno , ename , sal , concat(round(cume_dist() OVER(PARTITION BY deptno ORDER BY sal desc)*100,2),'%') as cume_dist FROM emp; --执行结果如下。 +------------+-------+------------+-----------+ | deptno | ename | sal | cume_dist | +------------+-------+------------+-----------+ | 10 | JACCKA | 5000.0 | 33.33% | | 10 | KING | 5000.0 | 33.33% | | 10 | CLARK | 2450.0 | 66.67% | | 10 | WELAN | 2450.0 | 66.67% | | 10 | TEBAGE | 1300.0 | 100.0% | | 10 | MILLER | 1300.0 | 100.0% | | 20 | SCOTT | 3000.0 | 40.0% | | 20 | FORD | 3000.0 | 40.0% | | 20 | JONES | 2975.0 | 60.0% | | 20 | ADAMS | 1100.0 | 80.0% | | 20 | SMITH | 800.0 | 100.0% | | 30 | BLAKE | 2850.0 | 16.67% | | 30 | ALLEN | 1600.0 | 33.33% | | 30 | TURNER | 1500.0 | 50.0% | | 30 | MARTIN | 1250.0 | 83.33% | | 30 | WARD | 1250.0 | 83.33% | | 30 | JAMES | 950.0 | 100.0% | +------------+-------+------------+-----------+
NTILE
- 命令格式
BIGINT ntile(BIGINT n) over(partition by [col1, col2…] [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause]))
- 命令说明
用于将分组数据按照顺序切分成
n
片,并返回当前切片值。如果切片不均匀,默认增加第一个切片的分布。 - 参数说明
n
:BIGINT数据类型。 - 返回值说明
返回BIGINT类型。
- 示例
假设表
emp
中的数据如下所示。| empno | ename | job | mgr | hiredate| sal| comm | deptno | 7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10 7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10 7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10
现在需要将所有职工根据部门按sal
高到低切分为3组,并获得职工自己组内的序号。select deptno,ename,sal,NTILE(3) OVER(PARTITION BY deptno ORDER BY sal desc) AS nt3 from emp; --执行结果如下。 +------------+-------+------------+------------+ | deptno | ename | sal | nt3 | +------------+-------+------------+------------+ | 10 | JACCKA | 5000.0 | 1 | | 10 | KING | 5000.0 | 1 | | 10 | WELAN | 2450.0 | 2 | | 10 | CLARK | 2450.0 | 2 | | 10 | TEBAGE | 1300.0 | 3 | | 10 | MILLER | 1300.0 | 3 | | 20 | SCOTT | 3000.0 | 1 | | 20 | FORD | 3000.0 | 1 | | 20 | JONES | 2975.0 | 2 | | 20 | ADAMS | 1100.0 | 2 | | 20 | SMITH | 800.0 | 3 | | 30 | BLAKE | 2850.0 | 1 | | 30 | ALLEN | 1600.0 | 1 | | 30 | TURNER | 1500.0 | 2 | | 30 | MARTIN | 1250.0 | 2 | | 30 | WARD | 1250.0 | 3 | | 30 | JAMES | 950.0 | 3 | +------------+-------+------------+------------+
在文档使用中是否遇到以下问题
更多建议
匿名提交