更新时间:2024-09-02 GMT+08:00

窗口函数

普通的聚集函数只能用来计算一行内的结果,或者把所有行聚集成一行结果。而窗口函数可以跨行计算,并且把结果填到每一行中。

通过查询筛选出的行的某些部分,窗口调用函数实现了类似于聚集函数的功能,所以聚集函数也可以作为窗口函数使用。 窗口函数可以扫描所有的行,并同时将原始数据和聚集分析结果同时显示出来。

注意事项

  • 列存表目前只支持窗口函数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语句用于对数据进行分组,并对组内元素进行排序。窗口函数用于给组内的值生成序号:

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是不被允许的。

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两种窗口区间。

RANK()

描述:RANK函数为各组内值生成跳跃排序序号,其中相同的值具有相同序号,但相同值占用多个编号。

返回值类型:BIGINT

示例:

给定表score(id, classid, 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()

描述: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)

描述:FIRST_VALUE函数取各组内的第一个值作为返回结果。

返回值类型:与参数数据类型相同。

示例:

 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函数取各组内的最后一个值作为返回结果。

返回值类型:与参数数据类型相同。

示例:

 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)

描述:NTH_VALUE函数返回该组内的第nth行作为结果。若该行不存在,则默认返回NULL。

返回值类型:与参数数据类型相同。

示例:

 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)