窗口函数
普通的聚集函数将多行数据聚集为一行结果，而窗口函数可以跨行计算，并将计算结果填充到每一行中。
窗口函数基于查询结果集的子集（即“窗口”）进行计算，实现了与聚集函数类似的聚集功能，因此聚集函数也可以作为窗口函数使用。 窗口函数可以扫描所有的行的同时，将原始数据和聚集计算结果一并输出。
注意事项
- 列存表目前只支持窗口函数rank(expression)、row_number(expression)以及聚集函数的sum，count，avg，min和max；行存表没有限制。
- 单个查询中可以包含一个或多个窗口函数表达式。
- 窗口函数仅能出现在输出列中。如果需要使用窗口函数的值进行条件过滤，需要将窗口函数嵌套在子查询中，在外层使用窗口函数表达式的别名进行条件过滤。例如：
SELECT classid, id, score FROM(SELECT *, avg(score) OVER(PARTITION BY classid) as avg_score FROM score) WHERE score >= avg_score;
- 窗口函数所在查询块中支持使用GROUP BY表达式进行分组去重，但要求窗口函数中的PARTITION BY子句中必须是GROUP BY表达式的子集，以保证窗口函数在GROUP BY列去重后的结果上进行窗口运算，同时ORDER BY子句的表达式也需要是GROUP BY表达式的子集，或聚集运算的聚集函数。例如：
SELECT classid,rank() OVER(PARTITION BY classid ORDER BY sum(score)) as avg_score FROM score GROUP BY classid, id;
语法格式
窗口函数通过OVER语句来定义窗口。OVER语句用于对数据进行分组（PARTITION BY）和组内排序（ORDER BY），从而确定窗口函数的计算范围。
1 2 3 4 | function_name ([expression [, expression ... ]]) OVER ( window_definition ) function_name ([expression [, expression ... ]]) OVER window_name function_name ( * ) OVER ( window_definition ) function_name ( * ) OVER window_name |
其中window_definition子句option为：
1 2 3 4 | [ existing_window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ] |
PARTITION BY选项将具有相同PARTITION BY表达式值的行划分为一组。
ORDER BY选项用于控制窗口函数处理行的顺序。ORDER BY后面必须跟字段名，若ORDER BY后面跟数字，该数字会被按照常量处理，对目标列没有起到排序的作用。
frame_clause子句option为：
1 2 | [ RANGE | ROWS ] frame_start [ RANGE | ROWS ] BETWEEN frame_start AND frame_end |
当需要指定一个窗口对分组内所有行结果进行计算时，我们需要指定窗口区间开始的行和结束的行。窗口区间支持RANGE、ROWS两种模式，ROWS以物理单位（行）指定窗口，RANGE将窗口指定为逻辑偏移量。
RANGE、ROWS中可以使用BETWEEN frame_start AND frame_end指定边界可取值。如果仅指定frame_start，则frame_end默认为CURRENT ROW。
frame_start和frame_end取值为：
- CURRENT ROW，当前行。
- N PRECEDING，当前行向前第n行。
- UNBOUNDED PRECEDING，当前PARTITION的第1行。
- N FOLLOWING，当前行向后第n行。
- UNBOUNDED FOLLOWING，当前PARTITION的最后1行。
需要注意，frame_start不能为UNBOUNDED FOLLOWING，frame_end不能为UNBOUNDED PRECEDING，并且frame_end选项不能比上面取值中出现的frame_start选项早。例如RANGE BETWEEN CURRENT ROW AND N PRECEDING是不被允许的。
RANK()
描述：为一个组内值进行排序，相同的值会获得相同排序序号，且后续排序会跳过并列的序号（例如：1，2，3，3，5），因此排序可能不连续。
返回值类型：BIGINT
示例：
示例表score的各字段分别表示学生id、所在班级id以及考试成绩。使用RANK函数对学生成绩进行排序：
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE score(id int,classid int,score int); INSERT INTO score VALUES(1,1,95),(2,2,95),(3,2,85),(4,1,70),(5,2,88),(6,1,70); SELECT id, classid, score,RANK() OVER(ORDER BY score DESC) FROM score; id | classid | score | rank ----+---------+-------+------ 1 | 1 | 95 | 1 2 | 2 | 95 | 1 6 | 1 | 70 | 5 4 | 1 | 70 | 5 5 | 2 | 88 | 3 3 | 2 | 85 | 4 (6 rows) |
ROW_NUMBER()
描述：为各组内值生成连续排序序号，相同的值其序号也不相同，即序号唯一。
返回值类型：BIGINT
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id, classid, score,ROW_NUMBER() OVER(ORDER BY score DESC) FROM score ORDER BY score DESC; id | classid | score | row_number ----+---------+-------+------------ 1 | 1 | 95 | 1 2 | 2 | 95 | 2 5 | 2 | 88 | 3 3 | 2 | 85 | 4 6 | 1 | 70 | 5 4 | 1 | 70 | 6 (6 rows) |
DENSE_RANK()
描述：DENSE_RANK函数为各组内值生成连续排序序号，其中相同的值具有相同序号，相同值只占用一个编号。
返回值类型：BIGINT
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id, classid, score,DENSE_RANK() OVER(ORDER BY score DESC) FROM score; id | classid | score | dense_rank ----+---------+-------+------------ 1 | 1 | 95 | 1 2 | 2 | 95 | 1 5 | 2 | 88 | 2 3 | 2 | 85 | 3 6 | 1 | 70 | 4 4 | 1 | 70 | 4 (6 rows) |
PERCENT_RANK()
描述：PERCENT_RANK函数为各组内对应值生成相对序号，即根据公式 (rank - 1) / (total rows - 1)计算所得的值。其中rank为该值依据RANK函数所生成的对应序号，totalrows为该分组内的总元素个数。
返回值类型：DOUBLE PRECISION
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id, classid, score,PERCENT_RANK() OVER(ORDER BY score DESC) FROM score; id | classid | score | percent_rank ----+---------+-------+-------------- 1 | 1 | 95 | 0 2 | 2 | 95 | 0 3 | 2 | 85 | .6 4 | 1 | 70 | .8 5 | 2 | 88 | .4 6 | 1 | 70 | .8 (6 rows) |
CUME_DIST()
描述：CUME_DIST函数为各组内对应值生成累积分布序号。即根据公式(小于等于当前值的数据行数)/(该分组总行数totalrows)计算所得的相对序号。
返回值类型：DOUBLE PRECISION
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id,classid,score,CUME_DIST() OVER(ORDER BY score DESC) FROM score; id | classid | score | cume_dist ----+---------+-------+------------------ 1 | 1 | 95 | .333333333333333 2 | 2 | 95 | .333333333333333 5 | 2 | 88 | .5 3 | 2 | 85 | .666666666666667 4 | 1 | 70 | 1 6 | 1 | 70 | 1 (6 rows) |
NTILE(num_buckets integer)
描述：NTILE函数根据num_buckets integer将有序的数据集合平均分配到num_buckets所指定数量的桶中，并将桶号分配给每一行。分配时应尽量做到平均分配。
返回值类型：INTEGER
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id,classid,score,NTILE(3) OVER(ORDER BY score DESC) FROM score; id | classid | score | ntile ----+---------+-------+------- 1 | 1 | 95 | 1 2 | 2 | 95 | 1 5 | 2 | 88 | 2 3 | 2 | 85 | 2 4 | 1 | 70 | 3 6 | 1 | 70 | 3 (6 rows) |
LAG(value any [, offset integer [, default any ]])
描述：LAG函数为各组内对应值生成滞后值。即当前值对应的行数往前偏移offset位后所得行的value值作为序号。若经过偏移后行数不存在，则对应结果取为default值。若无指定，在默认情况下，offset取为1，default值取为NULL。
返回值类型：与参数数据类型相同。
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id,classid,score,LAG(id,3) OVER(ORDER BY score DESC) FROM score; id | classid | score | lag ----+---------+-------+----- 1 | 1 | 95 | 2 | 2 | 95 | 5 | 2 | 88 | 3 | 2 | 85 | 1 4 | 1 | 70 | 2 6 | 1 | 70 | 5 (6 rows) |
LEAD(value any [, offset integer [, default any ]])
描述：LEAD函数为各组内对应值生成提前值。即当前值对应的行数向后偏移offset位后所得行的value值作为序号。若经过向后偏移后行数超过当前组内的总行数，则对应结果取为default值。若无指定，在默认情况下，offset取为1，default值取为NULL。
返回值类型：与参数数据类型相同。
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id,classid,score,LEAD(id,3) OVER(ORDER BY score DESC) FROM score; id | classid | score | lead ----+---------+-------+------ 1 | 1 | 95 | 3 2 | 2 | 95 | 4 5 | 2 | 88 | 6 3 | 2 | 85 | 4 | 1 | 70 | 6 | 1 | 70 | (6 rows) |
FIRST_VALUE(value any)
描述：取各组内的第一个数据值作为返回结果。
返回值类型：与参数数据类型相同。
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id,classid,score,FIRST_VALUE(id) OVER(ORDER BY score DESC) FROM score; id | classid | score | first_value ----+---------+-------+------------- 1 | 1 | 95 | 1 2 | 2 | 95 | 1 5 | 2 | 88 | 1 3 | 2 | 85 | 1 4 | 1 | 70 | 1 6 | 1 | 70 | 1 (6 rows) |
LAST_VALUE(value any)
描述：LAST_VALUE函数取各组内的最后一个值作为返回结果。
返回值类型：与参数数据类型相同。
LAST_VALUE函数支持IGNORE NULLS语法，该语法返回非NULL窗口中的最后一个值，如果所有值都为NULL，则返回NULL，具体格式为：
1 | LAST_VALUE (expression) [IGNORE NULLS] OVER (window_definition) |
当前IGNORE NULLS仅支持ROWS between CURRENT ROW and UNBOUNDED FOLLOWING和ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW两种窗口区间。
示例：
1 2 3 4 5 6 7 8 9 10 | SELECT id,classid,score,LAST_VALUE(id) OVER(ORDER BY score DESC) FROM score; id | classid | score | last_value ----+---------+-------+------------ 1 | 1 | 95 | 2 2 | 2 | 95 | 2 5 | 2 | 88 | 5 3 | 2 | 85 | 3 4 | 1 | 70 | 6 6 | 1 | 70 | 6 (6 rows) |
NTH_VALUE(value any, nth integer)
描述：返回该组内的第n行作为结果。若该行不存在，则默认返回NULL。
返回值类型：与参数数据类型相同。
示例：返回按成绩降序排序后的结果集中第3行的id值。
1 2 3 4 5 6 7 8 9 10 | SELECT id,classid,score,NTH_VALUE(id,3) OVER(ORDER BY score DESC) FROM score; id | classid | score | nth_value ----+---------+-------+----------- 1 | 1 | 95 | 2 | 2 | 95 | 5 | 2 | 88 | 5 3 | 2 | 85 | 5 4 | 1 | 70 | 5 6 | 1 | 70 | 5 (6 rows) |
