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所对应的序号。