您可以在MaxCompute SQL中使用窗口函数对指定开窗列的数据灵活地进行分析处理工作。本文为您提供MaxCompute SQL支持的窗口函数的命令格式、参数说明及示例,指导您使用窗口函数完成开发。
MaxCompute SQL支持的窗口函数如下。
函数 | 功能 |
---|---|
COUNT | 计算计数值。 |
AVG | 计算平均值。 |
MAX | 计算最大值。 |
MIN | 计算最小值。 |
MEDIAN | 计算中位数。 |
STDDEV | 计算总体标准差。 |
STDDEV_SAMP | 计算样本标准差。 |
SUM | 计算汇总值。 |
DENSE_RANK | 计算连续排名。 |
RANK | 计算跳跃排名。 |
LAG | 按偏移量取当前行之前第几行的值。 |
LEAD | 按偏移量取当前行之后第几行的值。 |
PERCENT_RANK | 计算一组数据中某行的相对排名。 |
ROW_NUMBER | 计算行号。 |
CLUSTER_SAMPLE | 用于分组抽样。 |
CUME_DIST | 计算累计分布。 |
NTILE | 将分组数据按照顺序切片,并返回切片值。 |
使用限制
窗口函数的使用限制如下:
- 窗口函数只能出现在
select
语句中。 - 窗口函数中不能嵌套使用窗口函数和聚合函数。
- 窗口函数不能和同级别的聚合函数一起使用。
- 在一个MaxCompute SQL语句中,最多可以使用5个窗口函数。
- 同一窗口内最多包含1亿行数据,建议不超过500万行,否则运行时报错。
窗口函数语法
window_func() over (partition by <col1>[,<col2>…]
[order by <col1>[asc|desc][, <col2>[asc|desc]…]] <windowing_clause>)
- partition by <col1>[,<col2>…]:必填。指定开窗的列。
分区列的值相同的行被视为在同一个窗口内。
说明 例如partition by UID
,同1个UID对应的记录视为同一个窗口,1个UID对应记录数最多包含1亿行数据,建议不超过500万行。 - order by <col1>[asc|desc][, <col2>[asc|desc]…]:可选。指定数据在一个窗口内如何排序。
说明 当遇到相同的
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。
示例数据
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
tunnel upload emp.txt emp;
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
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>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:可选。不指定order by时,返回当前窗口内expr的计数值,指定order by时返回结果以指定的顺序排序,并且值为当前窗口内从开始行到当前行的累计计数值。
说明
- 当指定distinct关键字时,不能使用order by。
- 如果指定的order by的值重复,非Hive兼容和Hive兼容的处理方式不同,请参见该部分的示例。
- 返回值说明
返回BIGINT类型。
- 示例
- 示例1:指定薪水(sal)为开窗列,不排序,返回当前窗口(相同sal)的从开始行到最后一行的累计计数值。命令示例如下:
返回结果如下:select sal, count(sal) over (partition by sal) as count from emp;
+------------+------------+ | sal | count | +------------+------------+ | 800 | 1 | | 950 | 1 | | 1100 | 1 | | 1250 | 2 | --窗口开始行。第1行和第2行的sal一致,则第1行的count为第2行的累计计数值。 | 1250 | 2 | --当前窗口从第1行到第2行的累计计数值。 | 1300 | 2 | | 1300 | 2 | | 1500 | 1 | | 1600 | 1 | | 2450 | 2 | | 2450 | 2 | | 2850 | 1 | | 2975 | 1 | | 3000 | 2 | | 3000 | 2 | | 5000 | 2 | | 5000 | 2 | +------------+------------+
- 示例2:非Hive兼容模式下,指定薪水(sal)为开窗列,并排序,返回当前窗口(相同sal)从开始行到当前行的累计计数值。命令示例如下:
返回结果如下:--关闭Hive兼容模式。 set odps.sql.hive.compatible=false; --执行如下SQL命令。 select sal, count(sal) over (partition by sal order by sal) as count from emp;
+------------+------------+ | sal | count | +------------+------------+ | 800 | 1 | | 950 | 1 | | 1100 | 1 | | 1250 | 1 | --窗口开始行。第1行的累计计数值是1。 | 1250 | 2 | --第2行的累计计数值是2。 | 1300 | 1 | | 1300 | 2 | | 1500 | 1 | | 1600 | 1 | | 2450 | 1 | | 2450 | 2 | | 2850 | 1 | | 2975 | 1 | | 3000 | 1 | | 3000 | 2 | | 5000 | 1 | | 5000 | 2 | +------------+------------+
- 示例3:Hive兼容模式下,指定薪水(sal)为开窗列,并排序,返回当前窗口(相同sal)从开始行至最后一行的累计计数值。命令示例如下:
返回结果如下:--开启Hive兼容模式。 set odps.sql.hive.compatible=true; --执行如下SQL命令。 select sal, count(sal) over (partition by sal order by sal) as count from emp;
+------------+------------+ | sal | count | +------------+------------+ | 800 | 1 | | 950 | 1 | | 1100 | 1 | | 1250 | 2 | --窗口开始行。第1行和第2行的sal一致,则第1行的count为第2行的累计计数值。 | 1250 | 2 | --当前窗口从第1行到第2行的累计计数值。 | 1300 | 2 | | 1300 | 2 | | 1500 | 1 | | 1600 | 1 | | 2450 | 2 | | 2450 | 2 | | 2850 | 1 | | 2975 | 1 | | 3000 | 2 | | 3000 | 2 | | 5000 | 2 | | 5000 | 2 | +------------+------------+
- 示例1:指定薪水(sal)为开窗列,不排序,返回当前窗口(相同sal)的从开始行到最后一行的累计计数值。命令示例如下:
AVG
- 命令格式
avg([distinct] <expr>) over (partition by <col1>[, <col2>…] [order by <col1>[asc|desc][, <col2>[asc|desc]…]] [<windowing_clause>])
- 命令说明
计算平均值。
- 参数说明
- expr:必填。待计算平均值的列。DOUBLE类型或DECIMAL类型。当指定distinct关键字时,表示取唯一值的平均值。
- 当输入值为STRING、BIGINT类型时,会隐式转换为DOUBLE类型后参与运算,输入其它数据类型返回报错。
- 当输入值为NULL时,该行不参与计算。
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:可选。不指定order by时,返回当前窗口内expr的平均值。指定order by时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的累计平均值。
说明
- 当指定distinct关键字时,不能使用order by。
- 如果指定的order by的值重复,非Hive兼容和Hive兼容的处理方式不同,请参见该部分的示例。
- expr:必填。待计算平均值的列。DOUBLE类型或DECIMAL类型。当指定distinct关键字时,表示取唯一值的平均值。
- 返回值说明
返回DOUBLE类型。
- 示例
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)平均值,不排序,返回当前窗口(相同deptno)从开始行到最后一行的累计平均值。命令示例如下:
返回结果如下:select deptno, sal, avg(sal) over (partition by deptno) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 2916.6666666666665 | --窗口开始行,从第1行到第6行的累计平均值。 | 10 | 2450 | 2916.6666666666665 | --从第1行到第6行的累计平均值。 | 10 | 5000 | 2916.6666666666665 | --从第1行到第6行的累计平均值。 | 10 | 1300 | 2916.6666666666665 | | 10 | 5000 | 2916.6666666666665 | | 10 | 2450 | 2916.6666666666665 | | 20 | 3000 | 2175.0 | | 20 | 3000 | 2175.0 | | 20 | 800 | 2175.0 | | 20 | 1100 | 2175.0 | | 20 | 2975 | 2175.0 | | 30 | 1500 | 1566.6666666666667 | | 30 | 950 | 1566.6666666666667 | | 30 | 1600 | 1566.6666666666667 | | 30 | 1250 | 1566.6666666666667 | | 30 | 1250 | 1566.6666666666667 | | 30 | 2850 | 1566.6666666666667 | +------------+------------+------------+
- 示例2:非Hive兼容模式下,指定部门(deptno)为开窗列,计算薪水(sal)平均值,并排序,返回当前窗口(相同deptno)从开始行到当前行的累计平均值。命令示例如下:
返回结果如下:--关闭Hive兼容模式。 set odps.sql.hive.compatible=false; --执行如下SQL命令。 select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300.0 | --窗口开始行。 | 10 | 1300 | 1300.0 | --从第1行到第2行的累计平均值。 | 10 | 2450 | 1683.3333333333333 | --从第1行到第3行的累计平均值。 | 10 | 2450 | 1875.0 | --从第1行到第4行的累计平均值。 | 10 | 5000 | 2500.0 | --从第1行到第5行的累计平均值。 | 10 | 5000 | 2916.6666666666665 | --从第1行到第6行的累计平均值。 | 20 | 800 | 800.0 | | 20 | 1100 | 950.0 | | 20 | 2975 | 1625.0 | | 20 | 3000 | 1968.75 | | 20 | 3000 | 2175.0 | | 30 | 950 | 950.0 | | 30 | 1250 | 1100.0 | | 30 | 1250 | 1150.0 | | 30 | 1500 | 1237.5 | | 30 | 1600 | 1310.0 | | 30 | 2850 | 1566.6666666666667 | +------------+------------+------------+
- 示例3:Hive兼容模式下,指定部门(deptno)为开窗列,计算薪水(sal)平均值,并排序,返回当前窗口(相同deptno)从开始行到与当前行相同值所在行(相同sal值的平均值相同)的累计平均值。命令示例如下:
返回结果如下:--开启Hive兼容模式。 set odps.sql.hive.compatible=true; --执行如下SQL命令。 select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300.0 | --窗口开始行。第1行和第2行的sal一致,则第1行的平均值为第1行到第2行的累计平均值。 | 10 | 1300 | 1300.0 | --从第1行到第2行的累计平均值。 | 10 | 2450 | 1875.0 | --第3行和第4行的sal一致,则第3行的平均值为从第1行到第4行的累计平均值。 | 10 | 2450 | 1875.0 | --从第1行到第4行的累计平均值。 | 10 | 5000 | 2916.6666666666665 | | 10 | 5000 | 2916.6666666666665 | | 20 | 800 | 800.0 | | 20 | 1100 | 950.0 | | 20 | 2975 | 1625.0 | | 20 | 3000 | 2175.0 | | 20 | 3000 | 2175.0 | | 30 | 950 | 950.0 | | 30 | 1250 | 1150.0 | | 30 | 1250 | 1150.0 | | 30 | 1500 | 1237.5 | | 30 | 1600 | 1310.0 | | 30 | 2850 | 1566.6666666666667 | +------------+------------+------------+
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)平均值,不排序,返回当前窗口(相同deptno)从开始行到最后一行的累计平均值。命令示例如下:
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>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:可选。不指定order by时,返回当前窗口内expr的最大值。指定order by时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的最大值。
说明 当指定distinct关键字时,不能使用order by。 - 返回值说明
返回值的类型同expr类型。
- 示例
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)最大值,不排序,返回当前窗口(相同deptno)的最大值。执行命令如下:
返回结果如下:select deptno, sal, max(sal) over (partition by deptno) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 5000 | --窗口开始行,从第1行到第6行的最大值。 | 10 | 2450 | 5000 | --从第1行到第6行的最大值。 | 10 | 5000 | 5000 | --从第1行到第6行的最大值。 | 10 | 1300 | 5000 | | 10 | 5000 | 5000 | | 10 | 2450 | 5000 | | 20 | 3000 | 3000 | | 20 | 3000 | 3000 | | 20 | 800 | 3000 | | 20 | 1100 | 3000 | | 20 | 2975 | 3000 | | 30 | 1500 | 2850 | | 30 | 950 | 2850 | | 30 | 1600 | 2850 | | 30 | 1250 | 2850 | | 30 | 1250 | 2850 | | 30 | 2850 | 2850 | +------------+------------+------------+
- 示例2:指定部门(deptno)为开窗列,计算薪水(sal)最大值,并排序,返回当前窗口(相同deptno)从开始行到当前行的最大值。执行命令如下:
返回结果如下:select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | --窗口开始行。 | 10 | 1300 | 1300 | --从第1行到第2行的最大值。 | 10 | 2450 | 2450 | --从第1行到第3行的最大值。 | 10 | 2450 | 2450 | --从第1行到第4行的最大值。 | 10 | 5000 | 5000 | | 10 | 5000 | 5000 | | 20 | 800 | 800 | | 20 | 1100 | 1100 | | 20 | 2975 | 2975 | | 20 | 3000 | 3000 | | 20 | 3000 | 3000 | | 30 | 950 | 950 | | 30 | 1250 | 1250 | | 30 | 1250 | 1250 | | 30 | 1500 | 1500 | | 30 | 1600 | 1600 | | 30 | 2850 | 2850 | +------------+------------+------------+
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)最大值,不排序,返回当前窗口(相同deptno)的最大值。执行命令如下:
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>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:可选。不指定order by时,返回当前窗口内expr的最小值。指定order by时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的最小值。
说明 当指定distinct关键字时,不能使用order by。
- 返回值说明
返回值类型同expr类型。
- 示例
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)最小值,不排序,返回当前窗口(相同deptno)的最小值。执行命令如下:
返回结果如下:select deptno, sal, min(sal) over (partition by deptno) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | --窗口开始行,从第1行到第6行的最小值。 | 10 | 2450 | 1300 | --从第1行到第6行的最小值。 | 10 | 5000 | 1300 | --从第1行到第6行的最小值。 | 10 | 1300 | 1300 | | 10 | 5000 | 1300 | | 10 | 2450 | 1300 | | 20 | 3000 | 800 | | 20 | 3000 | 800 | | 20 | 800 | 800 | | 20 | 1100 | 800 | | 20 | 2975 | 800 | | 30 | 1500 | 950 | | 30 | 950 | 950 | | 30 | 1600 | 950 | | 30 | 1250 | 950 | | 30 | 1250 | 950 | | 30 | 2850 | 950 | +------------+------------+------------+
- 示例2:指定部门(deptno)为开窗列,计算薪水(sal)最小值,并排序,返回当前窗口(相同deptno)从开始行到当前行的最小值。执行命令如下:
返回结果如下:select deptno, sal, min(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | --窗口开始行。 | 10 | 1300 | 1300 | --从第1行到第2行的最小值。 | 10 | 2450 | 1300 | --从第1行到第3行的最小值。 | 10 | 2450 | 1300 | | 10 | 5000 | 1300 | | 10 | 5000 | 1300 | | 20 | 800 | 800 | | 20 | 1100 | 800 | | 20 | 2975 | 800 | | 20 | 3000 | 800 | | 20 | 3000 | 800 | | 30 | 950 | 950 | | 30 | 1250 | 950 | | 30 | 1250 | 950 | | 30 | 1500 | 950 | | 30 | 1600 | 950 | | 30 | 2850 | 950 | +------------+------------+------------+
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)最小值,不排序,返回当前窗口(相同deptno)的最小值。执行命令如下:
MEDIAN
- 命令格式
double median(double <expr>) over (partition by <col1>[, <col2>…]) decimal median(decimal <expr>) over (partition by <col1>[, <col2>…)
- 命令说明
计算中位数。
- 参数说明
- expr:必填。待计算中位数的列或数字。DOUBLE类型或DECIMAL类型。最多支持输入255个数字,至少要输入1个数字。
- 如果输入值是DOUBLE类型,会默认转成DOUBLE类型的数组参与计算。
- 当输入值为STRING类型或BIGINT类型,会隐式转换为DOUBLE类型后参与运算,输入为其他数据类型则返回报错。
- 当输入值为NULL时,返回NULL。
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- expr:必填。待计算中位数的列或数字。DOUBLE类型或DECIMAL类型。最多支持输入255个数字,至少要输入1个数字。
- 返回值说明
返回值类型为DOUBLE类型或DECIMAL类型。
- 示例
指定部门(deptno)为开窗列,计算薪水(sal)中位数,返回当前窗口(相同deptno)的中位数。执行命令如下:
返回结果如下:select deptno, sal, median(sal) over (partition by deptno) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 2450.0 | --窗口开始行,从第1行到第6行的中位数。 | 10 | 2450 | 2450.0 | | 10 | 5000 | 2450.0 | | 10 | 1300 | 2450.0 | | 10 | 5000 | 2450.0 | | 10 | 2450 | 2450.0 | | 20 | 3000 | 2975.0 | | 20 | 3000 | 2975.0 | | 20 | 800 | 2975.0 | | 20 | 1100 | 2975.0 | | 20 | 2975 | 2975.0 | | 30 | 1500 | 1375.0 | | 30 | 950 | 1375.0 | | 30 | 1600 | 1375.0 | | 30 | 1250 | 1375.0 | | 30 | 1250 | 1375.0 | | 30 | 2850 | 1375.0 | +------------+------------+------------+
STDDEV
- 命令格式
double stddev|stddev_pop([distinct] <expr>) over (partition by <col1>[, <col2>…] [order by <col1>[asc|desc][, <col2>[asc|desc]…]] [<windowing_clause>]) decimal stddev|stddev_pop([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关键字时,表示计算唯一值的总体标准差。
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:可选。不指定order by时,返回当前窗口内expr的总体标准差。指定order by时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的累计总体标准差。
说明
- 当指定distinct关键字时,不能使用order by。
- 如果指定的order by的值重复,非Hive兼容和Hive兼容的处理方式不同,请参见该部分的示例1和示例2。
- expr:必填。待计算总体标准差的列。DOUBLE类型或DECIMAL类型。
- 返回值说明
返回值类型同expr类型。
- 示例
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)总体标准差,不排序,返回当前窗口(相同deptno)的累计总体标准差。命令示例如下:
返回结果如下:select deptno, sal, stddev(sal) over (partition by deptno) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1546.1421524412158 | --窗口开始行,从第1行到第6行的累计总体标准差。 | 10 | 2450 | 1546.1421524412158 | --从第1行到第6行的累计总体标准差。 | 10 | 5000 | 1546.1421524412158 | | 10 | 1300 | 1546.1421524412158 | | 10 | 5000 | 1546.1421524412158 | | 10 | 2450 | 1546.1421524412158 | | 20 | 3000 | 1004.7387720198718 | | 20 | 3000 | 1004.7387720198718 | | 20 | 800 | 1004.7387720198718 | | 20 | 1100 | 1004.7387720198718 | | 20 | 2975 | 1004.7387720198718 | | 30 | 1500 | 610.1001739241042 | | 30 | 950 | 610.1001739241042 | | 30 | 1600 | 610.1001739241042 | | 30 | 1250 | 610.1001739241042 | | 30 | 1250 | 610.1001739241042 | | 30 | 2850 | 610.1001739241042 | +------------+------------+------------+
- 示例2:非Hive兼容模式下,指定部门(deptno)为开窗列,计算薪水(sal)总体标准差,并排序,返回当前窗口(相同deptno)从开始行到当前行的累计总体标准差。命令示例如下:
返回结果如下:--关闭Hive兼容模式。 set odps.sql.hive.compatible=false; --执行如下SQL命令。 select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 0.0 | --窗口开始行。 | 10 | 1300 | 0.0 | --从第1行到第2行的累计总体标准差。 | 10 | 2450 | 542.1151989096865 | --从第1行到第3行的累计总体标准差。 | 10 | 2450 | 575.0 | --从第1行到第4行的累计总体标准差。 | 10 | 5000 | 1351.6656391282572 | | 10 | 5000 | 1546.1421524412158 | | 20 | 800 | 0.0 | | 20 | 1100 | 150.0 | | 20 | 2975 | 962.4188277460079 | | 20 | 3000 | 1024.2947268730811 | | 20 | 3000 | 1004.7387720198718 | | 30 | 950 | 0.0 | | 30 | 1250 | 150.0 | | 30 | 1250 | 141.4213562373095 | | 30 | 1500 | 194.8557158514987 | | 30 | 1600 | 226.71568097509268 | | 30 | 2850 | 610.1001739241042 | +------------+------------+------------+
- 示例3:Hive兼容模式下,指定部门(deptno)为开窗列,计算薪水(sal)总体标准差,并排序,返回当前窗口(相同deptno)从开始行到与当前行相同值所在行(相同sal值的总体标准差相同)的累计总体标准差。命令示例如下:
返回结果如下:--开启Hive兼容模式。 set odps.sql.hive.compatible=true; --执行如下SQL命令。 select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 0.0 | --窗口开始行。第1行和第2行的sal一致,则第1行的总体标准差为第1行到第2行的累计总体标准差。 | 10 | 1300 | 0.0 | --从第1行到第2行的累计总体标准差。 | 10 | 2450 | 575.0 | --第3行和第4行的sal一致,则第3行的总体标准差为从第1行到第4行的累计总体标准差。 | 10 | 2450 | 575.0 | --从第1行到第4行的累计总体标准差。 | 10 | 5000 | 1546.1421524412158 | | 10 | 5000 | 1546.1421524412158 | | 20 | 800 | 0.0 | | 20 | 1100 | 150.0 | | 20 | 2975 | 962.4188277460079 | | 20 | 3000 | 1004.7387720198718 | | 20 | 3000 | 1004.7387720198718 | | 30 | 950 | 0.0 | | 30 | 1250 | 141.4213562373095 | | 30 | 1250 | 141.4213562373095 | | 30 | 1500 | 194.8557158514987 | | 30 | 1600 | 226.71568097509268 | | 30 | 2850 | 610.1001739241042 | +------------+------------+------------+
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)总体标准差,不排序,返回当前窗口(相同deptno)的累计总体标准差。命令示例如下:
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关键字时,表示计算唯一值的样本标准差。
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:可选。不指定order by时,返回当前窗口内expr的样本标准差。指定order by时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的样本标准差。
说明 当指定distinct关键字时,不能使用order by。
- expr:必填。待计算样本标准差的列。DOUBLE类型或DECIMAL类型。
- 返回值说明
返回值类型同expr类型。
- 示例
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)样本标准差,不排序,返回当前窗口(相同deptno)的累计样本标准差。命令示例如下:
返回结果如下:select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1693.7138680032904 | --窗口开始行,第1行到第6行的累计样本标准差。 | 10 | 2450 | 1693.7138680032904 | --从第1行到第6行的累计样本标准差。 | 10 | 5000 | 1693.7138680032904 | --从第1行到第6行的累计样本标准差。 | 10 | 1300 | 1693.7138680032904 | | 10 | 5000 | 1693.7138680032904 | | 10 | 2450 | 1693.7138680032904 | | 20 | 3000 | 1123.3320969330487 | | 20 | 3000 | 1123.3320969330487 | | 20 | 800 | 1123.3320969330487 | | 20 | 1100 | 1123.3320969330487 | | 20 | 2975 | 1123.3320969330487 | | 30 | 1500 | 668.331255192114 | | 30 | 950 | 668.331255192114 | | 30 | 1600 | 668.331255192114 | | 30 | 1250 | 668.331255192114 | | 30 | 1250 | 668.331255192114 | | 30 | 2850 | 668.331255192114 | +------------+------------+------------+
- 示例2:指定部门(deptno)为开窗列,计算薪水(sal)样本标准差,并排序,返回当前窗口(相同deptno)从开始行到当前行的累计样本标准差。命令示例如下:
返回结果如下:select deptno, sal, stddev_samp(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 0.0 | --窗口开始行。 | 10 | 1300 | 0.0 | --从第1行到第2行的累计样本标准差。 | 10 | 2450 | 663.9528095680697 | --从第1行到第3行的累计样本标准差。 | 10 | 2450 | 663.9528095680696 | | 10 | 5000 | 1511.2081259707413 | | 10 | 5000 | 1693.7138680032904 | | 20 | 800 | 0.0 | | 20 | 1100 | 212.13203435596427 | | 20 | 2975 | 1178.7175234126282 | | 20 | 3000 | 1182.7536725793752 | | 20 | 3000 | 1123.3320969330487 | | 30 | 950 | 0.0 | | 30 | 1250 | 212.13203435596427 | | 30 | 1250 | 173.20508075688772 | | 30 | 1500 | 225.0 | | 30 | 1600 | 253.4758371127315 | | 30 | 2850 | 668.331255192114 | +------------+------------+------------+
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)样本标准差,不排序,返回当前窗口(相同deptno)的累计样本标准差。命令示例如下:
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>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:可选。不指定order by时,返回当前窗口内expr的汇总值。指定order by时,返回结果以指定的方式排序,并且值为当前窗口内从开始行到当前行的累计汇总值。
说明
- 当指定distinct关键字时,不能使用order by。
- 如果指定的order by的值重复,非Hive兼容和Hive兼容的处理方式不同,请参见该部分的示例。
- expr:必填。待计算汇总值的列。DOUBLE类型、DECIMAL类型或BIGINT类型。
- 返回值说明
- 输入值为BIGINT类型时,返回BIGINT类型。
- 输入值为DECIMAL类型时,返回DECIMAL类型。
- 输入值为DOUBLE类型或STRING类型时,返回DOUBLE类型。
- 示例
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)汇总值,不排序,返回当前窗口(相同deptno)的累计汇总值。命令示例如下:
返回结果如下:select deptno, sal, sum(sal) over (partition by deptno) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 17500 | --窗口开始行。第1行到第6行的累计汇总值。 | 10 | 2450 | 17500 | --从第1行到第6行的累计汇总值。 | 10 | 5000 | 17500 | --从第1行到第6行的累计汇总值。 | 10 | 1300 | 17500 | | 10 | 5000 | 17500 | | 10 | 2450 | 17500 | | 20 | 3000 | 10875 | | 20 | 3000 | 10875 | | 20 | 800 | 10875 | | 20 | 1100 | 10875 | | 20 | 2975 | 10875 | | 30 | 1500 | 9400 | | 30 | 950 | 9400 | | 30 | 1600 | 9400 | | 30 | 1250 | 9400 | | 30 | 1250 | 9400 | | 30 | 2850 | 9400 | +------------+------------+------------+
- 示例2:非Hive兼容模式下,指定部门(deptno)为开窗列,计算薪水(sal)汇总值,并排序,返回当前窗口(相同deptno)从开始行到当前行的累计汇总值。命令示例如下:
返回结果如下:--关闭Hive兼容模式。 set odps.sql.hive.compatible=false; --执行如下SQL命令。 select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | --窗口开始行。 | 10 | 1300 | 2600 | --从第1行到第2行的累计汇总值。 | 10 | 2450 | 5050 | --从第1行到第3行的累计汇总值。 | 10 | 2450 | 7500 | | 10 | 5000 | 12500 | | 10 | 5000 | 17500 | | 20 | 800 | 800 | | 20 | 1100 | 1900 | | 20 | 2975 | 4875 | | 20 | 3000 | 7875 | | 20 | 3000 | 10875 | | 30 | 950 | 950 | | 30 | 1250 | 2200 | | 30 | 1250 | 3450 | | 30 | 1500 | 4950 | | 30 | 1600 | 6550 | | 30 | 2850 | 9400 | +------------+------------+------------+
- 示例3:Hive兼容模式下,指定部门(deptno)为开窗列,计算薪水(sal)汇总值,并排序,返回当前窗口(相同deptno)从开始行到与当前行相同值所在行(相同sal值的汇总值相同)的累计汇总值。命令示例如下:
返回结果如下:--开启Hive兼容模式。 set odps.sql.hive.compatible=true; --执行如下SQL命令。 select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 2600 | --窗口开始行。第1行和第2行的sal一致,则第1行的汇总值为第1行到第2行的累计汇总值。 | 10 | 1300 | 2600 | --从第1行到第2行的累计汇总值。 | 10 | 2450 | 7500 | --第3行和第4行的sal一致,则第3行的汇总值为从第1行到第4行的累计汇总值。 | 10 | 2450 | 7500 | --从第1行到第4行的累计汇总值。 | 10 | 5000 | 17500 | | 10 | 5000 | 17500 | | 20 | 800 | 800 | | 20 | 1100 | 1900 | | 20 | 2975 | 4875 | | 20 | 3000 | 10875 | | 20 | 3000 | 10875 | | 30 | 950 | 950 | | 30 | 1250 | 3450 | | 30 | 1250 | 3450 | | 30 | 1500 | 4950 | | 30 | 1600 | 6550 | | 30 | 2850 | 9400 | +------------+------------+------------+
- 示例1:指定部门(deptno)为开窗列,计算薪水(sal)汇总值,不排序,返回当前窗口(相同deptno)的累计汇总值。命令示例如下:
DENSE_RANK
- 命令格式
bigint dense_rank() over (partition by <col1>[, <col2>…] order by <col1>[asc|desc][, <col2>[asc|desc]…])
- 命令说明
计算连续排名。相同组的值相同的行数据获得的排名相同,且排名连续。例如两个第二名后为第三名。
- 参数说明
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:必填。指定排名依据的值。
- 返回值说明
返回BIGINT类型。
- 示例
将所有职工根据部门(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 | 1 | | 10 | KING | 5000 | 1 | | 10 | CLARK | 2450 | 2 | | 10 | WELAN | 2450 | 2 | | 10 | TEBAGE | 1300 | 3 | | 10 | MILLER | 1300 | 3 | | 20 | SCOTT | 3000 | 1 | | 20 | FORD | 3000 | 1 | | 20 | JONES | 2975 | 2 | | 20 | ADAMS | 1100 | 3 | | 20 | SMITH | 800 | 4 | | 30 | BLAKE | 2850 | 1 | | 30 | ALLEN | 1600 | 2 | | 30 | TURNER | 1500 | 3 | | 30 | MARTIN | 1250 | 4 | | 30 | WARD | 1250 | 4 | | 30 | JAMES | 950 | 5 | +------------+------------+------------+------------+
RANK
- 命令格式
bigint rank() over (partition by <col1>[, <col2>…] order by <col1>[asc|desc][, <col2>[asc|desc]…])
- 命令说明
计算跳跃排名。相同组的值相同的行数据获得的排名相同,且排名跳跃。例如两个第二名后为第四名。
- 参数说明
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:必填。指定排名依据的值。
- 返回值说明
返回BIGINT类型。
- 示例
将所有职工根据部门(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 | 1 | | 10 | KING | 5000 | 1 | | 10 | CLARK | 2450 | 3 | | 10 | WELAN | 2450 | 3 | | 10 | TEBAGE | 1300 | 5 | | 10 | MILLER | 1300 | 5 | | 20 | SCOTT | 3000 | 1 | | 20 | FORD | 3000 | 1 | | 20 | JONES | 2975 | 3 | | 20 | ADAMS | 1100 | 4 | | 20 | SMITH | 800 | 5 | | 30 | BLAKE | 2850 | 1 | | 30 | ALLEN | 1600 | 2 | | 30 | TURNER | 1500 | 3 | | 30 | MARTIN | 1250 | 4 | | 30 | WARD | 1250 | 4 | | 30 | JAMES | 950 | 6 | +------------+------------+------------+------------+
LAG
- 命令格式
lag(<expr>,bigint <offset>, <default>) over(partition by <col1>[, <col2>…] order by <col1>[asc|desc][, <col2>[asc|desc]…])
- 命令说明
按偏移量取当前行之前第几行的值。如果当前行号为
m
,则取行号为m-offset
的值。 - 参数说明
- expr:必填。待计算偏移量的列。
- offset:必填。偏移量,BIGINT类型常量,取值大于0。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。
- default:当offset指定的范围越界时的缺省值,常量,默认值为NULL。
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:必填。指定返回结果的排序方式。
- 返回值说明
返回值类型同expr类型。
- 示例
将所有职工根据部门(deptno)分组(作为开窗列),每位员工的薪水(sal)做偏移。命令示例如下:
返回结果如下:select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;
+------------+------------+------------+------------+ | deptno | ename | sal | sal_new | +------------+------------+------------+------------+ | 10 | TEBAGE | 1300 | NULL | | 10 | MILLER | 1300 | 1300 | | 10 | CLARK | 2450 | 1300 | | 10 | WELAN | 2450 | 2450 | | 10 | KING | 5000 | 2450 | | 10 | JACCKA | 5000 | 5000 | | 20 | SMITH | 800 | NULL | | 20 | ADAMS | 1100 | 800 | | 20 | JONES | 2975 | 1100 | | 20 | SCOTT | 3000 | 2975 | | 20 | FORD | 3000 | 3000 | | 30 | JAMES | 950 | NULL | | 30 | MARTIN | 1250 | 950 | | 30 | WARD | 1250 | 1250 | | 30 | TURNER | 1500 | 1250 | | 30 | ALLEN | 1600 | 1500 | | 30 | BLAKE | 2850 | 1600 | +------------+------------+------------+------------+
LEAD
- 命令格式
lead(<expr>, bigint <offset>, <default>) over(partition by <col1>[, <col2>…] [order by <col1>[asc|desc][, <col2>[asc|desc]…]])
- 命令说明
按偏移量取当前行之后第几行的值。如果当前行号为
m
,则取行号为m+offset
的值。 - 参数说明
- expr:必填。待计算偏移量的列。
- offset:必填。偏移量,BIGINT类型常量,取值大于0。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。
- default:当offset指定的范围越界时的缺省值,常量,默认值为NULL。
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:必填。指定返回结果的排序方式。
- 返回值说明
返回值类型同expr类型。
- 示例
将所有职工根据部门(deptno)分组(作为开窗列),每位员工的薪水(sal)做偏移。命令示例如下:
返回结果如下:select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;
+------------+------------+------------+------------+ | deptno | ename | sal | sal_new | +------------+------------+------------+------------+ | 10 | TEBAGE | 1300 | 1300 | | 10 | MILLER | 1300 | 2450 | | 10 | CLARK | 2450 | 2450 | | 10 | WELAN | 2450 | 5000 | | 10 | KING | 5000 | 5000 | | 10 | JACCKA | 5000 | NULL | | 20 | SMITH | 800 | 1100 | | 20 | ADAMS | 1100 | 2975 | | 20 | JONES | 2975 | 3000 | | 20 | SCOTT | 3000 | 3000 | | 20 | FORD | 3000 | NULL | | 30 | JAMES | 950 | 1250 | | 30 | MARTIN | 1250 | 1250 | | 30 | WARD | 1250 | 1500 | | 30 | TURNER | 1500 | 1600 | | 30 | ALLEN | 1600 | 2850 | | 30 | BLAKE | 2850 | NULL | +------------+------------+------------+------------+
PERCENT_RANK
- 命令格式
percent_rank() over(partition by <col1>[, <col2>…] order by <col1>[asc|desc][, <col2>[asc|desc]…])
- 命令说明
计算一组数据中某行的相对排名。
- 参数说明
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:必填。指定返回结果的排序方式。
- 返回值说明
返回DOUBLE类型,值域为[0, 1],相对排名的计算方式为
(rank-1)/(number of rows -1)
。 - 示例
计算员工薪水在组内的相对排名。命令示例如下:
返回结果如下:select deptno, ename, sal, percent_rank(sal) over (partition by deptno order by sal desc) as sal_new from emp;
+------------+------------+------------+------------+ | deptno | ename | sal | sal_new | +------------+------------+------------+------------+ | 10 | JACCKA | 5000 | 0.0 | | 10 | KING | 5000 | 0.0 | | 10 | CLARK | 2450 | 0.4 | | 10 | WELAN | 2450 | 0.4 | | 10 | TEBAGE | 1300 | 0.8 | | 10 | MILLER | 1300 | 0.8 | | 20 | SCOTT | 3000 | 0.0 | | 20 | FORD | 3000 | 0.0 | | 20 | JONES | 2975 | 0.5 | | 20 | ADAMS | 1100 | 0.75 | | 20 | SMITH | 800 | 1.0 | | 30 | BLAKE | 2850 | 0.0 | | 30 | ALLEN | 1600 | 0.2 | | 30 | TURNER | 1500 | 0.4 | | 30 | MARTIN | 1250 | 0.6 | | 30 | WARD | 1250 | 0.6 | | 30 | JAMES | 950 | 1.0 | +------------+------------+------------+------------+
ROW_NUMBER
- 命令格式
row_number() over(partition by <col1>[, <col2>…] order by <col1>[asc|desc][, <col2>[asc|desc]…])
- 命令说明
计算行号,从1开始。
- 参数说明
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:必填。指定返回结果的排序方式。
- 返回值说明
返回BIGINT类型。
- 示例
将所有职工根据部门(deptno)分组(作为开窗列),每个组内根据薪水(sal)做降序排序,获得职工在自己组内的序号。命令示例如下:
返回结果如下:select deptno, ename, sal, row_number() over (partition by deptno order by sal desc) as nums from emp;
+------------+------------+------------+------------+ | deptno | ename | sal | nums | +------------+------------+------------+------------+ | 10 | JACCKA | 5000 | 1 | | 10 | KING | 5000 | 2 | | 10 | CLARK | 2450 | 3 | | 10 | WELAN | 2450 | 4 | | 10 | TEBAGE | 1300 | 5 | | 10 | MILLER | 1300 | 6 | | 20 | SCOTT | 3000 | 1 | | 20 | FORD | 3000 | 2 | | 20 | JONES | 2975 | 3 | | 20 | ADAMS | 1100 | 4 | | 20 | SMITH | 800 | 5 | | 30 | BLAKE | 2850 | 1 | | 30 | ALLEN | 1600 | 2 | | 30 | TURNER | 1500 | 3 | | 30 | MARTIN | 1250 | 4 | | 30 | WARD | 1250 | 5 | | 30 | JAMES | 950 | 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类型常量,
x≥y≥1
。表示从一个窗口分的x份中抽取y份记录(即y份记录返回值为True)。y为NULL时,返回值为NULL。 - partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- x:必填。BIGINT类型常量,
- 返回值说明
返回BOOLEAN类型。
- 示例
如果您需要从每组中抽取约20%的值,命令示例如下:
返回结果如下:select deptno, sal from ( select deptno, sal, cluster_sample(5, 1) over (partition by deptno) as flag from emp ) sub where flag = true;
+------------+------------+ | deptno | sal | +------------+------------+ | 10 | 1300 | | 20 | 3000 | | 30 | 950 | +------------+------------+
CUME_DIST
- 命令格式
cume_dist() over(partition by <col1>[, <col2>…] order by <col1> [asc|desc][, <col2>[asc|desc]…]])
- 命令说明
求累计分布,相当于求分组中值大于等于当前值的行数占分组总行数的比例。
- 参数说明
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:必填。指定返回结果的排序方式。
- 返回值说明
分组中值大于等于当前值的行数占分组总行数的比例。
- 示例
将所有职工根据部门(deptno)分组(作为开窗列),计算薪水(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 | 33.33% | | 10 | KING | 5000 | 33.33% | | 10 | CLARK | 2450 | 66.67% | | 10 | WELAN | 2450 | 66.67% | | 10 | TEBAGE | 1300 | 100.0% | | 10 | MILLER | 1300 | 100.0% | | 20 | SCOTT | 3000 | 40.0% | | 20 | FORD | 3000 | 40.0% | | 20 | JONES | 2975 | 60.0% | | 20 | ADAMS | 1100 | 80.0% | | 20 | SMITH | 800 | 100.0% | | 30 | BLAKE | 2850 | 16.67% | | 30 | ALLEN | 1600 | 33.33% | | 30 | TURNER | 1500 | 50.0% | | 30 | MARTIN | 1250 | 83.33% | | 30 | WARD | 1250 | 83.33% | | 30 | JAMES | 950 | 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类型。
- partition by <col1>[, <col2>…]:至少指定1个开窗口的列。
- order by col1[asc|desc][, col2[asc|desc]…]:可选。指定返回结果的排序方式。
- 返回值说明
返回BIGINT类型。
- 示例
将所有职工根据部门按薪水(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 | 1 | | 10 | KING | 5000 | 1 | | 10 | CLARK | 2450 | 2 | | 10 | WELAN | 2450 | 2 | | 10 | TEBAGE | 1300 | 3 | | 10 | MILLER | 1300 | 3 | | 20 | SCOTT | 3000 | 1 | | 20 | FORD | 3000 | 1 | | 20 | JONES | 2975 | 2 | | 20 | ADAMS | 1100 | 2 | | 20 | SMITH | 800 | 3 | | 30 | BLAKE | 2850 | 1 | | 30 | ALLEN | 1600 | 1 | | 30 | TURNER | 1500 | 2 | | 30 | MARTIN | 1250 | 2 | | 30 | WARD | 1250 | 3 | | 30 | JAMES | 950 | 3 | +------------+------------+------------+------------+
在文档使用中是否遇到以下问题
更多建议
匿名提交