您可以使用窗口函数对指定开窗列的数据灵活地进行分析处理工作。本文为您提供支持的窗口函数的命令格式、参数说明及示例,指导您使用窗口函数完成开发。
支持的窗口函数如下。
函数 | 功能 |
函数 | 功能 |
对窗口中的数据求平均值。 | |
用户随机抽样。返回True表示该行数据被抽中。 | |
计算窗口中的记录数。 | |
计算累计分布。 | |
计算排名。排名是连续的。 | |
取当前行所对应窗口的第一条数据的值。 | |
取当前行往前(朝分区头部方向)第N行数据的值。 | |
取当前行所对应窗口的最后一条数据的值。 | |
取当前行往后(朝分区尾部方向)第N行数据的值。 | |
计算窗口中的最大值。 | |
计算窗口中的中位数。 | |
计算窗口中的最小值。 | |
将数据顺序切分成N等份,返回数据所在等份的编号(从1到N)。 | |
取当前行所对应窗口的第N条数据的值。 | |
计算排名。输出百分比格式。 | |
计算排名。排名可能不连续。 | |
计算行号。从1开始递增。 | |
计算总体标准差。是STDDEV_POP的别名。 | |
计算样本标准差。 | |
对窗口中的数据求和。 |
窗口函数的使用限制如下:
窗口函数只能出现在select
语句中。
窗口函数中不能嵌套使用窗口函数和聚合函数。
窗口函数不能和同级别的聚合函数一起使用。
窗口函数的语法声明如下。
<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>
function_name:内建窗口函数。
expression:函数格式,具体格式以实际函数语法为准。
windowing_definition:窗口定义。详细语法格式请参见windowing_definition。
window_name:窗口名称。您可以使用window
关键字自定义窗口,为windowing_definition定义名称。自定义语句(named_window_def)如下:
window <window_name> as (<window_definition>)
自定义语句在SQL中的位置如下:
select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
windowing_definition的语法声明如下。
--partition_clause:
[partition by <expression> [, ...]]
--orderby_clause:
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]
在SELECT语句中加入窗口函数,计算窗口函数的结果时,数据会按照窗口定义中的partition by
和order by
语句进行分区和排序。如果没有partition by
语句,则仅有一个分区,包含全部数据。如果没有order by
语句,则分区内的数据会按照任意顺序排布,最终生成一个数据流。之后对于每一行数据(当前行),会按照窗口定义中的frame_clause
从数据流中截取一段数据,构成当前行的窗口。窗口函数会根据窗口中包含的数据,计算得到窗口函数针对当前行对应的输出结果。
partition by <expression> [, ...]:可选。指定分区。分区列的值相同的行被视为在同一个窗口内。详细格式请参见表操作。
order by <expression> [asc|desc][nulls {first|last}] [, ...]:可选。指定数据在一个窗口内如何排序。
当遇到相同的order by
值时,排序结果不稳定。为减少随机性,应当尽可能保持order by
值的唯一性。
frame_clause:可选。用于确定数据边界,更多frame_clause信息,请参见frame_clause。
frame_clause的语法声明如下。
--格式一。
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
--格式二。
{ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]
frame_clause是一个闭区间,用于确定数据边界,包含frame_start和frame_end位置的数据行。
ROWS|RANGE|GROUPS:必填。frame_clause的类型,各类型的frame_start和frame_end实现规则不相同。其中:
ROWS类型:通过数据行数确定数据边界。
RANGE类型:通过比较order by
列值的大小关系来确定数据边界。一般在窗口定义中会指定order by
,未指定order by
时,一个分区中的所有数据行具有相同的order by
列值。NULL与NULL被认为是相等的。
GROUPS:一个分区中所有具有相同order by
列值的数据组成一个GROUP。未指定order by
时,分区中的所有数据组成一个GROUP。NULL与NULL被认为是相等的。
frame_start、frame_end:表示窗口的起始和终止边界。frame_start必填。frame_end可选,省略时默认值为CURRENT ROW。
frame_start确定的位置必须在frame_end确定的位置的前面,或者等于frame_end的位置,即frame_start相比frame_end更靠近分区头部。分区头部是指数据按窗口定义中的order by
语句排序之后第1行数据的位置。ROWS、RANGE、GROUPS类型对应的取值范围及实现逻辑如下。
frame_clause类型 | frame_start/frame_end取值 | 说明 |
frame_clause类型 | frame_start/frame_end取值 | 说明 |
ROWS、RANGE、GROUPS | UNBOUNDED PRECEDING | 表示分区的第一行,从1开始计数。 |
UNBOUNDED FOLLOWING | 表示分区的最后一行。 | |
ROWS | CURRENT ROW | 指当前行的位置。每一行数据都会对应一个窗口函数的结果值,当前行是指在给哪一行数据计算窗口函数的结果。 |
offset PRECEDING | 指从当前行位置,向分区头部位置移动 | |
offset FOLLOWING | 指从当前行位置,向分区尾部移动 | |
RANGE | CURRENT ROW |
|
offset PRECEDING | frame_start和frame_end的位置与
日期类型数据的
| |
offset FOLLOWING | frame_start和frame_end的位置与
| |
GROUPS | CURRENT ROW |
|
offset PRECEDING |
frame_start不能设置为UNBOUNDED FOLLOWING,frame_end不能设置为UNBOUNED PRECEDING。 | |
offset FOLLOWING |
frame_start不能设置为UNBOUNDED FOLLOWING,frame_end不能设置为UNBOUNED PRECEDING。 |
frame_exclusion:可选。用于从窗口中剔除一部分数据。取值范围如下:
EXCLUDE NO OTHERS:表示不剔除任何数据。
EXCLUDE CURRENT ROW:表示剔除当前行。
EXCLUDE GROUP:表示剔除整个GROUP,即分区中与当前行具有相同order by
值的所有数据。
EXCLUDE TIES:表示剔除整个GROUP,但保留当前行。
默认frame_clause
未显示设置frame_clause时,默认的frame_clause如下,与大部分SQL系统相同。
RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
窗口边界示例
假设表tbl结构为pid: bigint, oid: bigint, rid: bigint
,表中包含如下数据:
+------------+------------+------------+
| pid | oid | rid |
+------------+------------+------------+
| 1 | NULL | 1 |
| 1 | NULL | 2 |
| 1 | 1 | 3 |
| 1 | 1 | 4 |
| 1 | 2 | 5 |
| 1 | 4 | 6 |
| 1 | 7 | 7 |
| 1 | 11 | 8 |
| 2 | NULL | 9 |
| 2 | NULL | 10 |
+------------+------------+------------+
您可以将如下SQL语句中的...
替换为窗口定义语句windowing_definition来展示每一条数据所对应的窗口包含的数据列表:
Window列为NULL时,不包含任何数据。
ROW类型窗口
窗口定义1
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | [1] |
| 1 | NULL | 2 | [1, 2] |
| 1 | 1 | 3 | [1, 2, 3] |
| 1 | 1 | 4 | [1, 2, 3, 4] |
| 1 | 2 | 5 | [1, 2, 3, 4, 5] |
| 1 | 4 | 6 | [1, 2, 3, 4, 5, 6] |
| 1 | 7 | 7 | [1, 2, 3, 4, 5, 6, 7] |
| 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2 | NULL | 9 | [9] |
| 2 | NULL | 10 | [9, 10] |
+------------+------------+------------+--------+
窗口定义2
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | NULL | 2 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | 1 | 3 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | 1 | 4 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | 2 | 5 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | 4 | 6 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | 7 | 7 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2 | NULL | 9 | [9, 10] |
| 2 | NULL | 10 | [9, 10] |
+------------+------------+------------+--------+
窗口定义3
partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | [2, 3, 4] |
| 1 | NULL | 2 | [3, 4, 5] |
| 1 | 1 | 3 | [4, 5, 6] |
| 1 | 1 | 4 | [5, 6, 7] |
| 1 | 2 | 5 | [6, 7, 8] |
| 1 | 4 | 6 | [7, 8] |
| 1 | 7 | 7 | [8] |
| 1 | 11 | 8 | NULL |
| 2 | NULL | 9 | [10] |
| 2 | NULL | 10 | NULL |
+------------+------------+------------+--------+
窗口定义4
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | NULL |
| 1 | NULL | 2 | [1] |
| 1 | 1 | 3 | [1, 2] |
| 1 | 1 | 4 | [1, 2, 3] |
| 1 | 2 | 5 | [1, 2, 3, 4] |
| 1 | 4 | 6 | [1, 2, 3, 4, 5] |
| 1 | 7 | 7 | [1, 2, 3, 4, 5, 6] |
| 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7] |
| 2 | NULL | 9 | NULL |
| 2 | NULL | 10 | [9] |
+------------+------------+------------+--------+
窗口定义5
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | NULL |
| 1 | NULL | 2 | NULL |
| 1 | 1 | 3 | [1, 2] |
| 1 | 1 | 4 | [1, 2] |
| 1 | 2 | 5 | [1, 2, 3, 4] |
| 1 | 4 | 6 | [1, 2, 3, 4, 5] |
| 1 | 7 | 7 | [1, 2, 3, 4, 5, 6] |
| 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7] |
| 2 | NULL | 9 | NULL |
| 2 | NULL | 10 | NULL |
+------------+------------+------------+--------+
窗口定义6
partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | [1] |
| 1 | NULL | 2 | [2] |
| 1 | 1 | 3 | [1, 2, 3] |
| 1 | 1 | 4 | [1, 2, 4] |
| 1 | 2 | 5 | [1, 2, 3, 4, 5] |
| 1 | 4 | 6 | [1, 2, 3, 4, 5, 6] |
| 1 | 7 | 7 | [1, 2, 3, 4, 5, 6, 7] |
| 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2 | NULL | 9 | [9] |
| 2 | NULL | 10 | [10] |
+------------+------------+------------+--------+
对比本示例与前一个示例中rid
为2、4、10的window
结果,可以观察到EXCLUDE CURRENT ROW与EXCLUDE GROUP的差异,即对于EXCLUDE GROUP,同一个分区中(pid
相等),与当前行为相同oid
的数据都被剔除了。
RANGE类型窗口
窗口定义1
partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | [1, 2] |
| 1 | NULL | 2 | [1, 2] |
| 1 | 1 | 3 | [1, 2, 3, 4] |
| 1 | 1 | 4 | [1, 2, 3, 4] |
| 1 | 2 | 5 | [1, 2, 3, 4, 5] |
| 1 | 4 | 6 | [1, 2, 3, 4, 5, 6] |
| 1 | 7 | 7 | [1, 2, 3, 4, 5, 6, 7] |
| 1 | 11 | 8 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2 | NULL | 9 | [9, 10] |
| 2 | NULL | 10 | [9, 10] |
+------------+------------+------------+--------+
CURRENT ROW作为frame_end时,取与当前行具有相同order by
值oid
的最后一条数据,因此rid
为1的记录的window
结果为[1, 2]。
窗口定义2
partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | NULL | 2 | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1 | 1 | 3 | [3, 4, 5, 6, 7, 8] |
| 1 | 1 | 4 | [3, 4, 5, 6, 7, 8] |
| 1 | 2 | 5 | [5, 6, 7, 8] |
| 1 | 4 | 6 | [6, 7, 8] |
| 1 | 7 | 7 | [7, 8] |
| 1 | 11 | 8 | [8] |
| 2 | NULL | 9 | [9, 10] |
| 2 | NULL | 10 | [9, 10] |
+------------+------------+------------+--------+
窗口定义3
partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | [1, 2] |
| 1 | NULL | 2 | [1, 2] |
| 1 | 1 | 3 | NULL |
| 1 | 1 | 4 | NULL |
| 1 | 2 | 5 | [3, 4] |
| 1 | 4 | 6 | [3, 4, 5] |
| 1 | 7 | 7 | [6] |
| 1 | 11 | 8 | NULL |
| 2 | NULL | 9 | [9, 10] |
| 2 | NULL | 10 | [9, 10] |
+------------+------------+------------+--------+
order by
值oid
为NULL的行,对于offset {PRECEDING|FOLLOWING}
,只要offset
不为UNBOUNDED,则作为frame_start,指向分区中第一条order by
值为NULL的数据;作为frame_end,指向最后一条order by
值为NULL的数据。
GROUPS类型窗口
窗口定义如下:
partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
--SQL语句如下。
select pid,
oid,
rid,
collect_list(rid) over(partition by pid order by
oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;
返回结果如下:
+------------+------------+------------+--------+
| pid | oid | rid | window |
+------------+------------+------------+--------+
| 1 | NULL | 1 | [1, 2] |
| 1 | NULL | 2 | [1, 2] |
| 1 | 1 | 3 | [1, 2, 3, 4] |
| 1 | 1 | 4 | [1, 2, 3, 4] |
| 1 | 2 | 5 | [1, 2, 3, 4, 5] |
| 1 | 4 | 6 | [3, 4, 5, 6] |
| 1 | 7 | 7 | [5, 6, 7] |
| 1 | 11 | 8 | [6, 7, 8] |
| 2 | NULL | 9 | [9, 10] |
| 2 | NULL | 10 | [9, 10] |
+------------+------------+------------+--------+
为便于理解各函数的使用方法,本文为您提供源数据,基于源数据提供函数相关示例。创建表emp,并添加数据,命令示例如下:
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;
emp.txt中的数据如下:
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
命令格式
double avg([distinct] double <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal avg([distinct] decimal <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
命令说明
返回窗口中expr的平均值。
参数说明
expr:必填。计算返回结果的表达式。DOUBLE类型或DECIMAL类型。
当输入值为STRING、BIGINT类型时,会隐式转换为DOUBLE类型后参与运算,输入其他数据类型返回报错。
当输入值为NULL时,该行不参与计算。
当指定distinct关键字时,表示取唯一值的平均值。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
expr为DECIMAL类型时,返回DECIMAL类型。其他情况下返回DOUBLE类型。expr的值都为NULL时,返回结果为NULL。
示例
示例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)从开始行到与当前行相同值所在行(相同sal值的平均值相同)的累计平均值。命令示例如下:
--执行如下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 |
+------------+------------+------------+
命令格式
boolean cluster_sample(bigint <N>) OVER ([partition_clause])
boolean cluster_sample(bigint <N>, bigint <M>) OVER ([partition_clause])
命令说明
cluster_sample(bigint <N>)
:表示随机抽取N条数据。
cluster_sample(bigint <N>, bigint <M>)
:表示按比例(M/N)随机抽取。即抽取partition_row_count×M / N
条数据。partition_row_count
指分区中的数据行数。
参数说明
N:必填。BIGINT类型常量。N为NULL时,返回值为NULL。
M:必填。BIGINT类型常量。M为NULL时,返回值为NULL。
partition_clause:可选。详情请参见windowing_definition。
返回值说明
返回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 |
+------------+------------+
命令格式
bigint count(*) over ([partition_clause] [orderby_clause] [frame_clause])
bigint count([distinct] <expr>[,...]) over ([partition_clause] [orderby_clause] [frame_clause])
命令说明
count(*)
:返回总行数。
count([distinct] <expr>[,...])
:计算行数时会忽略expr值为NULL的行,如果有多个expr,则任意expr值为NULL都被忽略。此外如果指定了distinct关键字,则计算去重之后的数据行数,任意expr值为NULL的行同样会被忽略。
参数说明
expr:必填。待计算计数值的列。可以为任意类型。当值为NULL时,该行不参与计算。当指定DISTINCT关键字时,表示取唯一值的计数值。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回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:指定薪水(sal)为开窗列,并排序,返回当前窗口(相同sal)从开始行至最后一行的累计计数值。命令示例如下:
--执行如下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 |
+------------+------------+
命令格式
double cume_dist() over([partition_clause] [orderby_clause])
命令说明
求累计分布,相当于求分区中大于等于当前行的数据在分区中的占比。大小关系由orderby_clause判定。
参数说明
partition_clause及orderby_clause:详情请参见windowing_definition。
返回值说明
返回DOUBLE类型。具体的返回值等于row_number_of_last_peer / partition_row_count
,其中:row_number_of_last_peer
指当前行所属GROUP的最后一行数据的ROW_NUMBER窗口函数返回值,partition_row_count
为该行数据所属分区的数据行数。
示例
将所有职工根据部门(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% |
+------------+------------+------------+------------+
命令格式
bigint dense_rank() over ([partition_clause] [orderby_clause])
命令说明
计算当前行在分区中按照orderby_clause排序后所处的排名。从1开始计数。分区中具有相同order by
值的行的排名相等。每当order by
值发生变化时,排名加1。
参数说明
partition_clause及orderby_clause:详情请参见windowing_definition。
返回值说明
返回BIGINT类型。未指定orderby_clause时,返回结果全为1。
示例
将所有职工根据部门(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 |
+------------+------------+------------+------------+
命令格式
first_value(<expr>[, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])
命令说明
返回表达式expr在窗口的第一条数据上进行运算的结果。
参数说明
expr:必填。待计算返回结果的表达式。
ignore_nulls:可选。BOOLEAN类型。表示是否忽略NULL值。默认值为False。当参数的值为True时,返回窗口中第一条非NULL的expr值。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回值类型同expr类型。
示例
将所有职工根据部门分组,返回每组中的第一行数据。命令示例如下:
不指定order by:
select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;
返回结果如下:
+------------+------------+------------+-------------+
| deptno | ename | sal | first_value |
+------------+------------+------------+-------------+
| 10 | TEBAGE | 1300 | 1300 | --当前窗口的开始行。
| 10 | CLARK | 2450 | 1300 |
| 10 | KING | 5000 | 1300 |
| 10 | MILLER | 1300 | 1300 |
| 10 | JACCKA | 5000 | 1300 |
| 10 | WELAN | 2450 | 1300 |
| 20 | FORD | 3000 | 3000 | --当前窗口的开始行。
| 20 | SCOTT | 3000 | 3000 |
| 20 | SMITH | 800 | 3000 |
| 20 | ADAMS | 1100 | 3000 |
| 20 | JONES | 2975 | 3000 |
| 30 | TURNER | 1500 | 1500 | --当前窗口的开始行。
| 30 | JAMES | 950 | 1500 |
| 30 | ALLEN | 1600 | 1500 |
| 30 | WARD | 1250 | 1500 |
| 30 | MARTIN | 1250 | 1500 |
| 30 | BLAKE | 2850 | 1500 |
+------------+------------+------------+-------------+
指定order by:
select deptno, ename, sal, first_value(sal) over (partition by deptno order by sal desc) as first_value from emp;
返回结果如下:
+------------+------------+------------+-------------+
| deptno | ename | sal | first_value |
+------------+------------+------------+-------------+
| 10 | JACCKA | 5000 | 5000 | --当前窗口的开始行。
| 10 | KING | 5000 | 5000 |
| 10 | CLARK | 2450 | 5000 |
| 10 | WELAN | 2450 | 5000 |
| 10 | TEBAGE | 1300 | 5000 |
| 10 | MILLER | 1300 | 5000 |
| 20 | SCOTT | 3000 | 3000 | --当前窗口的开始行。
| 20 | FORD | 3000 | 3000 |
| 20 | JONES | 2975 | 3000 |
| 20 | ADAMS | 1100 | 3000 |
| 20 | SMITH | 800 | 3000 |
| 30 | BLAKE | 2850 | 2850 | --当前窗口的开始行。
| 30 | ALLEN | 1600 | 2850 |
| 30 | TURNER | 1500 | 2850 |
| 30 | MARTIN | 1250 | 2850 |
| 30 | WARD | 1250 | 2850 |
| 30 | JAMES | 950 | 2850 |
+------------+------------+------------+-------------+
命令格式
lag(<expr>[,bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)
命令说明
返回当前行往前(朝分区头部方向)第offset行数据对应的表达式expr的值。表达式expr可以是列、列运算或者函数运算等。
参数说明
expr:必填。待计算返回结果的表达式。
offset:可选。偏移量,BIGINT类型常量,取值大于等于0。值为0时表示当前行,为1时表示前一行,以此类推。默认值为1。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。
default:可选。当offset指定的范围越界时的缺省值,常量,默认值为NULL。需要与expr对应的数据类型相同。如果expr非常量,则基于当前行进行求值。
partition_clause及orderby_clause:详情请参见windowing_definition。
返回值说明
返回值类型同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 |
+------------+------------+------------+------------+
命令格式
last_value(<expr>[, <ignore_nulls>]) over([partition_clause] [orderby_clause] [frame_clause])
命令说明
返回表达式expr在窗口的最后一条数据上进行运算的结果。
参数说明
expr:必填。待计算返回结果的表达式。
ignore_nulls:可选。BOOLEAN类型。表示是否忽略NULL值。默认值为False。当参数的值为True时,返回窗口中最后一条非NULL的expr值。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回值类型同expr类型。
示例
将所有职工根据部门分组,返回每组中的最后一行数据。命令示例如下:
不指定order by,当前窗口为第一行到最后一行的范围,返回当前窗口的最后一行的值。
select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;
返回结果如下:
+------------+------------+------------+-------------+
| deptno | ename | sal | last_value |
+------------+------------+------------+-------------+
| 10 | TEBAGE | 1300 | 2450 |
| 10 | CLARK | 2450 | 2450 |
| 10 | KING | 5000 | 2450 |
| 10 | MILLER | 1300 | 2450 |
| 10 | JACCKA | 5000 | 2450 |
| 10 | WELAN | 2450 | 2450 | --当前窗口的最后一行。
| 20 | FORD | 3000 | 2975 |
| 20 | SCOTT | 3000 | 2975 |
| 20 | SMITH | 800 | 2975 |
| 20 | ADAMS | 1100 | 2975 |
| 20 | JONES | 2975 | 2975 | --当前窗口的最后一行。
| 30 | TURNER | 1500 | 2850 |
| 30 | JAMES | 950 | 2850 |
| 30 | ALLEN | 1600 | 2850 |
| 30 | WARD | 1250 | 2850 |
| 30 | MARTIN | 1250 | 2850 |
| 30 | BLAKE | 2850 | 2850 | --当前窗口的最后一行。
+------------+------------+------------+-------------+
指定order by,当前窗口为第一行到当前行的范围。返回当前窗口的当前行的值。
select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;
返回结果如下:
+------------+------------+------------+-------------+
| deptno | ename | sal | last_value |
+------------+------------+------------+-------------+
| 10 | JACCKA | 5000 | 5000 | --当前窗口的当前行。
| 10 | KING | 5000 | 5000 | --当前窗口的当前行。
| 10 | CLARK | 2450 | 2450 | --当前窗口的当前行。
| 10 | WELAN | 2450 | 2450 | --当前窗口的当前行。
| 10 | TEBAGE | 1300 | 1300 | --当前窗口的当前行。
| 10 | MILLER | 1300 | 1300 | --当前窗口的当前行。
| 20 | SCOTT | 3000 | 3000 | --当前窗口的当前行。
| 20 | FORD | 3000 | 3000 | --当前窗口的当前行。
| 20 | JONES | 2975 | 2975 | --当前窗口的当前行。
| 20 | ADAMS | 1100 | 1100 | --当前窗口的当前行。
| 20 | SMITH | 800 | 800 | --当前窗口的当前行。
| 30 | BLAKE | 2850 | 2850 | --当前窗口的当前行。
| 30 | ALLEN | 1600 | 1600 | --当前窗口的当前行。
| 30 | TURNER | 1500 | 1500 | --当前窗口的当前行。
| 30 | MARTIN | 1250 | 1250 | --当前窗口的当前行。
| 30 | WARD | 1250 | 1250 | --当前窗口的当前行。
| 30 | JAMES | 950 | 950 | --当前窗口的当前行。
+------------+------------+------------+-------------+
命令格式
lead(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)
命令说明
返回当前行往后(朝分区尾部方向)第offset行数据对应的表达式expr的值。表达式expr可以是列、列运算或者函数运算等。
参数说明
expr:必填。待计算返回结果的表达式。
offset:可选。偏移量,BIGINT类型常量,取值大于等于0。值为0时表示当前行,为1时表示后一行,以此类推。默认值为1。输入值为STRING类型、DOUBLE类型则隐式转换为BIGINT类型后进行运算。
default:可选。当offset指定的范围越界时的缺省值,常量,默认值为NULL。需要与expr对应的数据类型相同。如果expr非常量,则基于当前行进行求值。
partition_clause及orderby_clause:详情请参见windowing_definition。
返回值说明
返回值类型同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 |
+------------+------------+------------+------------+
命令格式
max(<expr>) over([partition_clause] [orderby_clause] [frame_clause])
命令说明
返回窗口中expr的最大值。
参数说明
expr:必填。用于计算最大值的表达式。除BOOLEAN外的任意类型,当值为NULL时,该行不参与计算。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回值的类型同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 |
+------------+------------+------------+
命令格式
median(<expr>) over ([partition_clause] [orderby_clause] [frame_clause])
命令说明
计算窗口中expr的中位数。
参数说明
expr:必填。待计算中位数的表达式。DOUBLE类型或DECIMAL类型。最多支持输入255个数字,至少要输入1个数字。
当输入值为STRING类型或BIGINT类型,会隐式转换为DOUBLE类型后参与运算,输入为其他数据类型则返回报错。
当输入值为NULL时,返回NULL。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回DOUBLE类型或DECIMAL类型。所有expr为NULL时,返回结果为NULL。
示例
指定部门(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 |
+------------+------------+------------+
命令格式
min(<expr>) over([partition_clause] [orderby_clause] [frame_clause])
命令说明
返回窗口中expr的最小值。
参数说明
expr:必填。用于计算最小值的表达式。除BOOLEAN外的任意类型,当值为NULL时,该行不参与计算。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回值类型同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 |
+------------+------------+------------+
命令格式
bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])
命令说明
用于将分区中的数据按照顺序切分成N等份,并返回数据所在等份的编号。如果分区中的数据不能被均匀地切分成N等份时,最前面的等份(编号较小的)会优先多分配1条数据。
参数说明
N:必填。切片数量。BIGINT类型。
partition_clause及orderby_clause:详情请参见windowing_definition。
返回值说明
返回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 |
+------------+------------+------------+------------+
命令格式
nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])
命令说明
返回表达式expr在窗口的第N条数据进行运算的结果。
参数说明
expr:必填。待计算返回结果的表达式。
number:必填。BIGINT类型。大于等于1的整数。值为1时与FIRST_VALUE等价。
ignore_nulls:可选。BOOLEAN类型。表示是否忽略NULL值。默认值为False。当参数的值为True时,返回窗口中第N条非NULL的expr值。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回值类型同expr类型。
示例
将所有职工根据部门分组,返回每组中的第6行数据。命令示例如下:
不指定order by,当前窗口为第一行到最后一行的范围,返回当前窗口第6行的值。
select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;
返回结果如下:
+------------+------------+------------+------------+
| deptno | ename | sal | nth_value |
+------------+------------+------------+------------+
| 10 | TEBAGE | 1300 | 2450 |
| 10 | CLARK | 2450 | 2450 |
| 10 | KING | 5000 | 2450 |
| 10 | MILLER | 1300 | 2450 |
| 10 | JACCKA | 5000 | 2450 |
| 10 | WELAN | 2450 | 2450 | --当前窗口的第6行。
| 20 | FORD | 3000 | NULL |
| 20 | SCOTT | 3000 | NULL |
| 20 | SMITH | 800 | NULL |
| 20 | ADAMS | 1100 | NULL |
| 20 | JONES | 2975 | NULL | --当前窗口的没有第6行,返回NULL。
| 30 | TURNER | 1500 | 2850 |
| 30 | JAMES | 950 | 2850 |
| 30 | ALLEN | 1600 | 2850 |
| 30 | WARD | 1250 | 2850 |
| 30 | MARTIN | 1250 | 2850 |
| 30 | BLAKE | 2850 | 2850 | --当前窗口的第6行。
+------------+------------+------------+------------+
指定order by,当前窗口为第一行到当前行的范围,返回当前窗口第6行的值。
select deptno, ename, sal, nth_value(sal,6) over (partition by deptno order by sal) as nth_value from emp;
返回结果如下:
+------------+------------+------------+------------+
| deptno | ename | sal | nth_value |
+------------+------------+------------+------------+
| 10 | TEBAGE | 1300 | NULL |
| 10 | MILLER | 1300 | NULL | --当前窗口只有2行,第6行超过了窗口长度。
| 10 | CLARK | 2450 | NULL |
| 10 | WELAN | 2450 | NULL |
| 10 | KING | 5000 | 5000 |
| 10 | JACCKA | 5000 | 5000 |
| 20 | SMITH | 800 | NULL |
| 20 | ADAMS | 1100 | NULL |
| 20 | JONES | 2975 | NULL |
| 20 | SCOTT | 3000 | NULL |
| 20 | FORD | 3000 | NULL |
| 30 | JAMES | 950 | NULL |
| 30 | MARTIN | 1250 | NULL |
| 30 | WARD | 1250 | NULL |
| 30 | TURNER | 1500 | NULL |
| 30 | ALLEN | 1600 | NULL |
| 30 | BLAKE | 2850 | 2850 |
+------------+------------+------------+------------+
命令格式
double percent_rank() over([partition_clause] [orderby_clause])
命令说明
计算当前行在分区中按照orderby_clause排序后的百分比排名。
参数说明
partition_clause及orderby_clause:详情请参见windowing_definition。
返回值说明
返回DOUBLE类型,值域为[0.0, 1.0]。具体的返回值等于“(rank - 1) / (partition_row_count - 1)”
,其中:rank
为该行数据的RANK窗口函数的返回结果,partition_row_count
为该行数据所属分区的数据行数。当分区中只有一行数据时,输出结果为0.0。
示例
计算员工薪水在组内的百分比排名。命令示例如下:
select deptno, ename, sal, percent_rank() 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 |
+------------+------------+------------+------------+
命令格式
bigint rank() over ([partition_clause] [orderby_clause])
命令说明
计算当前行在分区中按照orderby_clause排序后所处的排名。从1开始计数。
参数说明
partition_clause及orderby_clause:详情请参见windowing_definition。
返回值说明
返回BIGINT类型。返回值可能重复、且不连续。具体的返回值为该行数据所在GROUP的第一条数据的ROW_NUMBER()
值。未指定orderby_clause时,返回结果全为1。
示例
将所有职工根据部门(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 |
+------------+------------+------------+------------+
命令格式
row_number() over([partition_clause] [orderby_clause])
命令说明
计算当前行在分区中的行号,从1开始递增。
参数说明
请参见windowing_definition。不允许包含frame_clause。
返回值说明
返回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 |
+------------+------------+------------+------------+
命令格式
double stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
命令说明
计算总体标准差,STDDEV_POP函数的别名。
参数说明
expr:必填。待计算总体标准差的表达式。DOUBLE类型或DECIMAL类型。
当输入值为STRING类型或BIGINT类型时,会隐式转换为DOUBLE类型后参与运算,其他类型返回报错。
当输入值为NULL时,该行不参与计算。
当指定distinct关键字时,表示计算唯一值的总体标准差。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回值类型同expr类型。所有expr为NULL时,返回结果为NULL。
示例
示例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:指定部门(deptno)为开窗列,计算薪水(sal)总体标准差,并排序,返回当前窗口(相同deptno)从开始行到与当前行相同值所在行(相同sal值的总体标准差相同)的累计总体标准差。命令示例如下:
--执行如下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 |
+------------+------------+------------+
命令格式
double stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])
decimal stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])
命令说明
计算样本标准差。
参数说明
expr:必填。待计算样本标准差的表达式。DOUBLE类型或DECIMAL类型。
当输入值为STRING类型或BIGINT类型时,会隐式转换为DOUBLE类型后参与运算,其他类型返回报错。
当输入值为NULL时,该行不参与计算。
当指定distinct关键字时,表示计算唯一值的样本标准差。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
返回值类型同expr类型。所有expr为NULL时,返回结果为NULL。窗口仅包含1条expr值非NULL的数据时,结果为0。
示例
示例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 |
+------------+------------+------------+
命令格式
sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
命令说明
返回窗口中expr之和。
参数说明
expr:必填。待计算汇总值的列。DOUBLE类型、DECIMAL类型或BIGINT类型。
当输入值为STRING类型时,会隐式转换为DOUBLE类型后参与运算,其他类型返回报错。
当输入值为NULL时,该行不参与计算。
当指定distinct关键字时,表示计算唯一值的汇总值。
partition_clause、orderby_clause及frame_clause:详情请参见windowing_definition。
返回值说明
输入值为BIGINT类型时,返回BIGINT类型。
输入值为DECIMAL类型时,返回DECIMAL类型。
输入值为DOUBLE类型或STRING类型时,返回DOUBLE类型。
输入值都为NULL时,返回NULL。
示例
示例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:指定部门(deptno)为开窗列,计算薪水(sal)汇总值,并排序,返回当前窗口(相同deptno)从开始行到与当前行相同值所在行(相同sal值的汇总值相同)的累计汇总值。命令示例如下:
--执行如下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 |
+------------+------------+------------+