更新时间：2022-08-16 GMT+08:00
窗口函数
普通的聚集函数只能用来计算一行内的结果，或者把所有行聚集成一行结果。而窗口函数可以跨行计算，并且把结果填到每一行中。
- 通过查询筛选出的行的某些部分，窗口调用函数实现了类似于聚集函数的功能，所以聚集函数也可以作为窗口函数使用。 窗口函数可以扫描所有的行，根据窗口函数的PARTITION BY选项将查询的行分为一组。
- 列存表目前只支持窗口函数rank(expression)和row_number(expression)，以及聚集函数的sum，count，avg，min和max，而行存表没有限制。
- 窗口函数需要特殊的关键字OVER语句来指定窗口即触发一个窗口函数。OVER语句用于对数据进行分组，并对组内元素进行排序。窗口函数用于给组内的值生成序号。
- 窗口函数中的order by后面必须跟字段名，若order by后面跟数字，该数字会被按照常量处理，对目标列没有起到排序的作用。
窗口函数的语法格式
function_name ([expression [, expression ... ]]) OVER ( window_definition ) function_name ([expression [, expression ... ]]) OVER window_namefunction_name ( * ) OVER ( window_definition ) function_name ( * ) OVER window_name
其中window_definition子句option为：
[ existing_window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ frame_clause ]
frame_clause子句option为：
[ 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_end默认为CURRENT ROW。
BETWEEN frame_start AND 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 value PRECEDING是不被允许的。
窗口函数
- RANK()
描述：RANK函数为各组内值生成跳跃排序序号，其中，相同的值具有相同序号。
返回值类型：BIGINT
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SELECT d_moy, d_fy_week_seq, rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | rank -------+---------------+------ 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 2 | 8 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 3 | 15 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 4 | 22 1 | 5 | 29 1 | 5 | 29 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 (42 rows)
- ROW_NUMBER()
描述：ROW_NUMBER函数为各组内值生成连续排序序号，其中，相同的值其序号也不相同。
返回值类型：BIGINT
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SELECT d_moy, d_fy_week_seq, Row_number() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | row_number -------+---------------+------------ 1 | 1 | 1 1 | 1 | 2 1 | 1 | 3 1 | 1 | 4 1 | 1 | 5 1 | 1 | 6 1 | 1 | 7 1 | 2 | 8 1 | 2 | 9 1 | 2 | 10 1 | 2 | 11 1 | 2 | 12 1 | 2 | 13 1 | 2 | 14 1 | 3 | 15 1 | 3 | 16 1 | 3 | 17 1 | 3 | 18 1 | 3 | 19 1 | 3 | 20 1 | 3 | 21 1 | 4 | 22 1 | 4 | 23 1 | 4 | 24 1 | 4 | 25 1 | 4 | 26 1 | 4 | 27 1 | 4 | 28 1 | 5 | 29 1 | 5 | 30 2 | 5 | 1 2 | 5 | 2 2 | 5 | 3 2 | 5 | 4 2 | 5 | 5 2 | 6 | 6 2 | 6 | 7 2 | 6 | 8 2 | 6 | 9 2 | 6 | 10 2 | 6 | 11 2 | 6 | 12 (42 rows)
- DENSE_RANK()
描述：DENSE_RANK函数为各组内值生成连续排序序号，其中，相同的值具有相同序号。
返回值类型：BIGINT
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SELECT d_moy, d_fy_week_seq, dense_rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | dense_rank -------+---------------+------------ 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 5 | 5 1 | 5 | 5 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 (42 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 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SELECT d_moy, d_fy_week_seq, percent_rank() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | percent_rank -------+---------------+------------------ 1 | 1 | 0 1 | 1 | 0 1 | 1 | 0 1 | 1 | 0 1 | 1 | 0 1 | 1 | 0 1 | 1 | 0 1 | 2 | .241379310344828 1 | 2 | .241379310344828 1 | 2 | .241379310344828 1 | 2 | .241379310344828 1 | 2 | .241379310344828 1 | 2 | .241379310344828 1 | 2 | .241379310344828 1 | 3 | .482758620689655 1 | 3 | .482758620689655 1 | 3 | .482758620689655 1 | 3 | .482758620689655 1 | 3 | .482758620689655 1 | 3 | .482758620689655 1 | 3 | .482758620689655 1 | 4 | .724137931034483 1 | 4 | .724137931034483 1 | 4 | .724137931034483 1 | 4 | .724137931034483 1 | 4 | .724137931034483 1 | 4 | .724137931034483 1 | 4 | .724137931034483 1 | 5 | .96551724137931 1 | 5 | .96551724137931 2 | 5 | 0 2 | 5 | 0 2 | 5 | 0 2 | 5 | 0 2 | 5 | 0 2 | 6 | .454545454545455 2 | 6 | .454545454545455 2 | 6 | .454545454545455 2 | 6 | .454545454545455 2 | 6 | .454545454545455 2 | 6 | .454545454545455 2 | 6 | .454545454545455 (42 rows)
- CUME_DIST()
描述：CUME_DIST函数为各组内对应值生成累积分布序号。即根据公式(小于等于当前值的数据行数)/(该分组总行数totalrows)计算所得的相对序号。
返回值类型：DOUBLE PRECISION
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SELECT d_moy, d_fy_week_seq, cume_dist() OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim e_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | cume_dist -------+---------------+------------------ 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 1 | .233333333333333 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 2 | .466666666666667 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 3 | .7 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 4 | .933333333333333 1 | 5 | 1 1 | 5 | 1 2 | 5 | .416666666666667 2 | 5 | .416666666666667 2 | 5 | .416666666666667 2 | 5 | .416666666666667 2 | 5 | .416666666666667 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 2 | 6 | 1 (42 rows)
- NTILE(num_buckets integer)
描述：NTILE函数根据num_buckets integer将有序的数据集合平均分配到num_buckets所指定数量的桶中，并将桶号分配给每一行。分配时应尽量做到平均分配。
返回值类型：INTEGER
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SELECT d_moy, d_fy_week_seq, ntile(3) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | ntile -------+---------------+------- 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 2 1 | 3 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 4 | 3 1 | 5 | 3 1 | 5 | 3 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 1 2 | 5 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 3 2 | 6 | 3 2 | 6 | 3 2 | 6 | 3 (42 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 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SELECT d_moy, d_fy_week_seq, lag(d_moy,3,null) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | lag -------+---------------+----- 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 5 | 1 1 | 5 | 1 2 | 5 | 2 | 5 | 2 | 5 | 2 | 5 | 2 2 | 5 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 2 | 6 | 2 (42 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 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
SELECT d_moy, d_fy_week_seq, lead(d_fy_week_seq,2) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | lead -------+---------------+------ 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 2 1 | 1 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 2 1 | 2 | 3 1 | 2 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 3 1 | 3 | 4 1 | 3 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 4 1 | 4 | 5 1 | 4 | 5 1 | 5 | 1 | 5 | 2 | 5 | 5 2 | 5 | 5 2 | 5 | 5 2 | 5 | 6 2 | 5 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 6 2 | 6 | 2 | 6 | (42 rows)
- FIRST_VALUE(value any)
描述：FIRST_VALUE函数取各组内的第一个值作为返回结果。
返回值类型：与参数数据类型相同。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
SELECT d_moy, d_fy_week_seq, first_value(d_fy_week_seq) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 7 ORDER BY 1,2; d_moy | d_fy_week_seq | first_value -------+---------------+------------- 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 5 | 1 1 | 5 | 1 2 | 5 | 5 2 | 5 | 5 2 | 5 | 5 2 | 5 | 5 2 | 5 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 2 | 6 | 5 (42 rows)
- LAST_VALUE(value any)
描述：LAST_VALUE函数取各组内的最后一个值作为返回结果。
返回值类型：与参数数据类型相同。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
SELECT d_moy, d_fy_week_seq, last_value(d_moy) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 6 ORDER BY 1,2; d_moy | d_fy_week_seq | last_value -------+---------------+------------ 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 5 | 1 1 | 5 | 1 2 | 5 | 2 2 | 5 | 2 2 | 5 | 2 2 | 5 | 2 2 | 5 | 2 (35 rows)
- NTH_VALUE(value any, nth integer)
描述：NTH_VALUE函数返回该组内的第nth行作为结果。若该行不存在，则默认返回NULL。
返回值类型：与参数数据类型相同。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
SELECT d_moy, d_fy_week_seq, nth_value(d_fy_week_seq,6) OVER(PARTITION BY d_moy ORDER BY d_fy_week_seq) FROM tpcds.date_dim WHERE d_moy < 4 AND d_fy_week_seq < 6 ORDER BY 1,2; d_moy | d_fy_week_seq | nth_value -------+---------------+----------- 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 1 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 2 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 3 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 4 | 1 1 | 5 | 1 1 | 5 | 1 2 | 5 | 2 | 5 | 2 | 5 | 2 | 5 | 2 | 5 | (35 rows)
父主题： 函数和操作符
