testbook

窗口函数

sum, max, min, count, avg, first_value, last_value, lag, lead, row_number, rank, dense_rank, cume_dist, percent_rank

窗口函数是SQL中一类特别的函数。和聚合函数相似，窗口函数的输入也是多行记录。不同的是，聚合函数的作用于由GROUP BY子句聚合的组，而窗口函数则作用于一个窗口，这里，窗口是由OVER子句定义的多行记录。聚合函数对其所作用的每一组记录输出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函数，如sum, max, min, avg,count等也可以当作窗口函数使用。

OVER子句

语法

SELECT window_function(args)
    OVER([PARTITION_BY_clause] [ORDER_BY_clause] [WINDOW_clause])

PARTITION_BY_clause: PARTITION BY column_name, column_name, ...
ORDER_BY_clause: ORDER BY column_name, column_name, ...
WINDOW_clause: ROWS|RANGE BETWEEN (CURRENT ROW | (UNBOUNDED | [num]) PRECEDING) AND (CURRENT ROW|( UNBOUNDED | [num]) FOLLOWING)

说明

OVER子句中的三个选项：PARTITION BY子句，ORDER BY子句和WINDOW子句都为可选项，也可以组合使用。OVER子句为空则表示窗口为整张表。
PARTITION BY子句中可以用一个或多个键分区。和GROUP BY子句很像，PARTITION BY将表按分区键分区，每个分区是一个窗口，窗口函数作用于各个分区。
ORDER BY子句决定窗口函数求值的顺序。ORDER BY子句也可以用一个或多个键排序。排序可以靠ASC或者DESC决定升序或者降序。当使用ORDER BY子句时，窗口可以由WINDOW子句指定。如果不指定，默认窗口等同于ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是窗口从表或者分区（如果OVER子句中用PARTITION BY分区）的开头开始到当前行结束。
WINDOW子句让用户通过指定一个行区间来定义窗口。
- CURRENT ROW代表当前行。
- num PRECEDING定义窗口的下限：窗口从当前行向前数num行处开始；UNBOUNDED PRECEDING代表窗口没有下限。
- num FOLLOWING定义窗口的上限：窗口从当前行向后数num行处结束；UNBOUNDED FOLLOWING代表窗口没有上限。
- ROWS BETWEEN...和RANGE BETWEEN...的区别：
  ROWS定义的是物理行数的窗口，窗口宽度由行数计算。RANGE BETWEEN定义的是ORDER BY排序后的逻辑行数的窗口，窗口宽度由排名计算，只要值相同的不同列属于同一窗口。

举例
WINDOW子句:

ROWS BETWEEN CURRENT ROW AND CURRENT ROW  窗口只包含当前行
ROWS BETWEEN 3 PRECEDING AND 5 FOLLOWING   窗口从当前行向前数3行开始，到当前行向后数5行结束
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 窗口从表或分区的开头开始，到当前行结束
ROWS BETWEEN CURRENT AND UNBOUNDED FOLLOWING 窗口从当前行开始，到表或分区的结尾结束
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
窗口从表或分区的开头开始，到表或分区的结尾结束

ROWS BETWEEN和RANGE BETWEEN的区别

[$host]  transwarp> SELECT * FROM row_range;
A
A
B
C
D

[$host] transwarp> SELECT col1, count(col1) OVER (ORDER BY col1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM row_range;
A       1
A       2
B       3
C       4
D       5
[$host] transwarp> select col1, count(col1) over (order by col1 range between unbounded preceding and current row) from row_range;
A       2
A       2
B       3
C       4
D       5

ORDER BY, PARTITION BY在OVER子句中的使用：

[$host] transwarp> SELECT name, count(name) OVER (ORDER BY name) FROM user_info;
华微    1
宁新瑶  2
李平    3
李韩瑶  4
潘营泽  5
祝韩恒  6
管淑艳  7
邱坤    8
马从筠  9
魏向卉  10

[$host] transwarp> SELECT name, count(name) OVER (PARTITION BY name) FROM user_info;
宁新瑶  1
李平    1
李韩瑶  1
潘营泽  1
管淑艳  1
邱坤    1
魏向卉  1
华微    1
祝韩恒  1
马从筠  1

[$host] transwarp> SELECT name, reg_date,acc_level, count(name) OVER (PARTITION BY acc_level ORDER BY reg_date) FROM user_info;
华微    20080214        B       1
宁新瑶  20081031        D       1
李韩瑶  20110916        D       2
魏向卉  20091202        A       1
马从筠  20110101        A       2
邱坤    20121024        A       3
祝韩恒  20100101        C       1
潘营泽  20110430        C       2
管淑艳  20141003        C       3
李平    20130702        E       1

[$host] transwarp> SELECT name, acc_level, count(acc_level) OVER (PARTITION by acc_level) FROM user_info;
华微    B       1
李韩瑶  D       2
宁新瑶  D       2
邱坤    A       3
马从筠  A       3
魏向卉  A       3
潘营泽  C       3
管淑艳  C       3
祝韩恒  C       3
李平    E       1

下面我们列举所有Inceptor支持的窗口函数，包含：

first_value, last_value, lead, lag, count, max, min, avg, sum, row_number, rank, dense_rank, percent_rank, cume_dist

函数名	描述
first_value(expression)	返回一个有序集合中的第一个值。

说明
first_value的参数可以是列，输出结果为一列的子查询或者其他结果为一列的表达式。
举例
下例查询user_info表中最早注册的用户：

[$host] transwarp> SELECT name, reg_date, first_value(name) OVER(ORDER BY reg_date) FROM user_info;
华微    20080214        华微
宁新瑶  20081031        华微
魏向卉  20091202        华微
祝韩恒  20100101        华微
马从筠  20110101        华微
潘营泽  20110430        华微
李韩瑶  20110916        华微
邱坤    20121024        华微
李平    20130702        华微
管淑艳  20141003        华微

函数名	描述
last_value(expression)	返回一个有序集合中的最后一个值。

说明
last_value的参数可以是列，输出结果为一列的子查询或者其他结果为一列的表达式。
举例
下例查询user_info表中最晚注册的用户：

[$host] transwarp> SELECT name, reg_date, last_value(name) OVER(ORDER BY reg_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM user_info;
华微    20080214        管淑艳
宁新瑶  20081031        管淑艳
魏向卉  20091202        管淑艳
祝韩恒  20100101        管淑艳
马从筠  20110101        管淑艳
潘营泽  20110430        管淑艳
李韩瑶  20110916        管淑艳
邱坤    20121024        管淑艳
李平    20130702        管淑艳
管淑艳  20141003        管淑艳

注意，这里我们在ORDER子句中加了WINDOW子句，指定窗口为从当前行开始到表结尾结束。这是因为如果不指定窗口，默认窗口为从表开头开始，到当前行结束。ORDER BY的默认顺序为升序：reg_date以从小到大排列。所以在不指定窗口的情况下，第n行的last_value(name)值将是前n个注册的用户中最后一个注册的用户，也就是第n个注册的用户；last_value(name)的结果会和按reg_date升序排列一模一样：

[$host] transwarp> SELECT name, reg_date, last_value(name) OVER(ORDER BY reg_date) FROM user_info;
华微    20080214        华微
宁新瑶  20081031        宁新瑶
魏向卉  20091202        魏向卉
祝韩恒  20100101        祝韩恒
马从筠  20110101        马从筠
潘营泽  20110430        潘营泽
李韩瑶  20110916        李韩瑶
邱坤    20121024        邱坤
李平    20130702        李平
管淑艳  20141003        管淑艳

函数名	描述
lead(expression, n)	返回从当前行开始向后的第n行记录

说明
n为可选项，不选默认值为1，就是返回当前行后一行的记录。当没有记录时，返回NULL。。
举例

[$host] transwarp> transwarp> SELECT name, lead(name) OVER(ORDER BY reg_date) FROM user_info;
华微    宁新瑶
宁新瑶  魏向卉
魏向卉  祝韩恒
祝韩恒  马从筠
马从筠  潘营泽
潘营泽  李韩瑶
李韩瑶  邱坤
邱坤    李平
李平    管淑艳
管淑艳  NULL

[$host]  transwarp> SELECT name, lead(name,2) OVER(ORDER BY reg_date) FROM user_info;
华微    魏向卉
宁新瑶  祝韩恒
魏向卉  马从筠
祝韩恒  潘营泽
马从筠  李韩瑶
潘营泽  邱坤
李韩瑶  李平
邱坤    管淑艳
李平    NULL
管淑艳  NULL

函数名	描述
lag(expression, n)	返回从当前行开始向前的第n行记录

说明
n为可选项，不选默认值为1，就是返回当前行前一行的记录。当没有记录时，返回NULL。必须和OVER子句中的ORDER BY子句合用。
举例

[$host] transwarp> SELECT name, lag(name) OVER(ORDER BY reg_date) FROM user_info;
华微    NULL
宁新瑶  华微
魏向卉  宁新瑶
祝韩恒  魏向卉
马从筠  祝韩恒
潘营泽  马从筠
李韩瑶  潘营泽
邱坤    李韩瑶
李平    邱坤
管淑艳  李平
[$host]  transwarp> SELECT name, lag(name,2) OVER(ORDER BY reg_date) FROM user_info;
华微    NULL
宁新瑶  NULL
魏向卉  华微
祝韩恒  宁新瑶
马从筠  魏向卉
潘营泽  祝韩恒
李韩瑶  马从筠
邱坤    潘营泽
李平    李韩瑶
管淑艳  邱坤

函数名	描述
row_number()	返回窗口中行的序号。

说明
表或分区中的第一行序号为1，之后序号逐行递增。在同一窗口中，行的序号是唯一的。
举例

[$host] transwarp>  SELECT name, row_number() OVER(ORDER BY reg_date) FROM user_info;
华微    1
宁新瑶  2
魏向卉  3
祝韩恒  4
马从筠  5
潘营泽  6
李韩瑶  7
邱坤    8
李平    9
管淑艳  10

[$host] transwarp> SELECT name, acc_level,row_number() OVER(PARTITION BY acc_level ORDER BY reg_date) FROM user_info;
华微    B       1
宁新瑶  D       1
李韩瑶  D       2
魏向卉  A       1
马从筠  A       2
邱坤    A       3
祝韩恒  C       1
潘营泽  C       2
管淑艳  C       3
李平    E       1

函数名	描述
rank()	返回窗口中行的排名

说明
rank()对排名并列的行返回相同值，在并列排名之后的记录会空出并列所占名次。
举例

[$host] transwarp> SELECT name, acc_level, rank() OVER(ORDER BY acc_level) FROM user_info;
马从筠  A       1
魏向卉  A       1
邱坤    A       1
华微    B       4
祝韩恒  C       5
潘营泽  C       5
管淑艳  C       5
宁新瑶  D       8
李韩瑶  D       8
李平    E       10

函数名	描述
dense_rank()	返回窗口中行的排名

说明
dense_rank()对排名并列的行返回相同值，在并列排名之后的记录不会空出并列所占名次。
举例

[$host] transwarp> SELECT name, acc_level, dense_rank() OVER(ORDER BY acc_level) FROM user_info;
邱坤    A       1
马从筠  A       1
魏向卉  A       1
华微    B       2
潘营泽  C       3
管淑艳  C       3
祝韩恒  C       3
李韩瑶  D       4
宁新瑶  D       4
李平    E       5

函数名	描述
cume_dist()	返回当前行在窗口中的累积分布函数

说明
返回值为当前行在所在窗口中的百分排名，和percent_rank很像。假设按升序排列，那么cume_dist计算的是“窗口中当前行之前，包括当前行和与当前行排名相同的行数”除以“窗口中的总行数”。
举例

[$host] transwarp> SELECT name, cume_dist() OVER(ORDER BY acc_level) FROM user_info;
邱坤    0.3
马从筠  0.3
魏向卉  0.3
华微    0.4
潘营泽  0.7
管淑艳  0.7
祝韩恒  0.7
李韩瑶  0.9
宁新瑶  0.9
李平    1.0

函数名	描述
percent_rank()	返回当前行在窗口中的百分比排名

说明
假设按升序排列，那么percent_rank()计算的是“窗口中当前行之前，不包括当前行和与当前行排名相同的行数”除以“窗口中的总行数”。
举例

[$host] transwarp> SELECT name, cume_dist() OVER(ORDER BY acc_level) FROM user_info;
邱坤    0.3
马从筠  0.3
魏向卉  0.3
华微    0.4
潘营泽  0.7
管淑艳  0.7
祝韩恒  0.7
李韩瑶  0.9
宁新瑶  0.9
李平    1.0