OVER

功能描述

OVER子句与窗口函数配合使用，用于定义窗口的分区、排序和范围。窗口函数为每行数据基于其所在窗口计算一个值，而不像普通聚合函数那样将多行合并为一行。

语法格式

window_func ( [ expression [, ...] ] ) OVER
 ( [ PARTITION BY expression [, ...] ]
   [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
   [ window_frame ] )

其中 window_frame 为：

{ ROWS | RANGE } BETWEEN frame_start AND frame_end

frame_start 和 frame_end 为：

{ CURRENT ROW
   | UNBOUNDED PRECEDING
   | UNBOUNDED FOLLOWING
   | offset PRECEDING
   | offset FOLLOWING }

参数说明

参数	说明
window_func	窗口函数，包括聚合窗口函数（SUM、AVG、COUNT、MAX、MIN等）和排名窗口函数（RANK、DENSE_RANK、ROW_NUMBER、NTILE、LEAD、LAG、FIRST_VALUE、LAST_VALUE、NTH_VALUE等）。
PARTITION BY	按一个或多个表达式对数据进行分区，窗口函数在各分区内独立计算。类似于GROUP BY，但不合并行。
ORDER BY	决定窗口函数计算的顺序。可用ASC或DESC指定升序或降序。
ROWS	物理窗口，基于行号偏移定义窗口范围。
RANGE	逻辑窗口，基于ORDER BY表达式的值偏移定义窗口范围。
CURRENT ROW	当前行。
UNBOUNDED PRECEDING	窗口起点，从分区第一行开始。
UNBOUNDED FOLLOWING	窗口终点，到分区最后一行结束。
offset PRECEDING	从当前行向前偏移offset行（ROWS）或值（RANGE）。
offset FOLLOWING	从当前行向后偏移offset行（ROWS）或值（RANGE）。
NULLS FIRST	NULL值排在最前。
NULLS LAST	NULL值排在最后。

窗口范围示例

窗口范围	说明
ROWS BETWEEN CURRENT ROW AND CURRENT ROW	窗口只包含当前行
ROWS BETWEEN 3 PRECEDING AND 5 FOLLOWING	窗口从当前行向前3行到向后5行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	窗口从分区开头到当前行（默认窗口）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING	窗口从当前行到分区结尾
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	窗口覆盖整个分区

ROWS与RANGE的区别

ROWS为物理窗口，根据行号偏移计算，与当前行的值无关。
RANGE为逻辑窗口，根据ORDER BY表达式的值偏移计算，包含值在范围内的所有行。

注意事项

OVER子句包括PARTITION BY、ORDER BY和窗口范围三部分，可组合使用。
不指定ORDER BY时，窗口默认覆盖整个分区。
不指定窗口范围时，默认窗口为ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
OVER子句为空时表示窗口为整张表。

示例

累计计数：

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

分区排名：

SELECT name, class_id, score,
   RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS class_rank
   FROM student;

移动平均：

SELECT date, amount,
   AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
   FROM sales;

使用窗口别名（Spark 4.0）：

SELECT name, score,
   RANK() OVER w AS rnk,
   SUM(score) OVER w AS total
   FROM student
   WINDOW w AS (PARTITION BY class_id ORDER BY score DESC);