更新时间:2022-02-22 GMT+08:00

OVER子句

功能描述

窗口函数与OVER语句一起使用。OVER语句用于对数据进行分组,并对组内元素进行排序。窗口函数用于给组内的值生成序号。

语法格式

1
2
3
4
5
SELECT window_func(args) OVER
  ([PARTITION BY col_name, col_name, ...]
   [ORDER BY col_name, col_name, ...]
   [ROWS | RANGE BETWEEN (CURRENT ROW | (UNBOUNDED |[num]) PRECEDING)
  AND (CURRENT ROW | ( UNBOUNDED | [num]) FOLLOWING)]);

关键字

  • PARTITION BY:可以用一个或多个键分区。和GROUP BY子句类似,PARTITION BY将表按分区键分区,每个分区是一个窗口,窗口函数作用于各个分区。单表分区数最多允许7000个。
  • ORDER BY:决定窗口函数求值的顺序。可以用一个或多个键排序。通过ASC或DESC决定升序或降序。窗口由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…的区别:
    • ROW为物理窗口,即根据ORDER BY子句排序后,取前N行及后N行的数据计算(与当前行的值无关,只与排序后的行号相关)。
    • RANGE为逻辑窗口,即指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内。
  • 窗口有以下多种场景,如
    • 窗口只包含当前行。
      1
      ROWS BETWEEN CURRENT ROW AND CURRENT ROW
      
    • 窗口从当前行向前数3行开始,到当前行向后数5行结束。
      1
      ROWS BETWEEN 3 PRECEDING AND 5 FOLLOWING
      
    • 窗口从表或分区的开头开始,到当前行结束。
      1
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      
    • 窗口从当前行开始,到表或分区的结尾结束。
      1
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
      
    • 窗口从表或分区的开头开始,到表或分区的结尾结束。
      1
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      

注意事项

OVER子句包括:PARTITION BY子句、ORDER BY子句和WINDOW子句,可组合使用。OVER子句为空表示窗口为整张表。

示例

上述语句窗口从表或分区的开头开始,到当前行结束,对over_test表按照id字段进行排序,并返回排序好后的id及id所对应的序号。

1
SELECT id, count(id) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM over_test;