SQL 窗口函数简单总结

这几天系统复习了下 SQL 的窗口函数，补全了很多之前的知识盲点。虽然之前工作中窗口函数用的不多（没办法，之前的工作对 SQL 并没有很依赖，实际经验积累也就很鸡肋了），不过以现在的数据分析师技术栈来看，不会窗口函数都不敢说自己懂 SQL 了（再不掌握哪些可恶的窗口函数，小孩哪能手撕 offer，祖国哪有栋梁之才）

大多数网站对窗口函数的定义大都是可以对数据库数据进行实时分析处理的函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理）。我对窗口函数的理解是，对数据分区排序（不一定都要有），并按照每行数据的在分区内的位置以及排序的位次来进行某种计算或处理，并将结果附加在原来的数据上同时不改变行数。

窗口函数的语法微软是这样定义的,而 Oracle 是这样定义，两者大同小异，其实大多数 DBMS 都对窗口函数提供了非常接近的语法支持：

window_function (expression) OVER (
   [ PARTITION BY part_list ]
   [ ORDER BY order_list ]
   [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

窗口函数先按 part_list 对数据进行分区，再按 order_list 对每个分区内的数据进行排序，最后按 frame （如果有的话）来确定当前某行的窗口，然后用 window_function 来进行某些计算，将结果附加在当前行内。

窗口函数不会改变被检索出的行的行数，而聚合函数会对每个分区只保留一个行。从执行顺序上来看，聚合函数再窗口函数的后面：

1
2
3

+--------+   +---------+   +----------+   +--------+   +---------+   +--------+   +--------+
|  FROM  +-->|  WHERE  +-->| GROUP BY +-->+ HAVING +-->+ SELECT  +-->+ WINDOW +-->+ORDER BY|
+--------+   +---------+   +----------+   +--------+   +---------+   +--------+   +--------+

{ ROWS | RANGE } BETWEEN frame_start AND frame_end ] 需要重点标记一下。

ROWS BETWEEN 很明显是行的范围，比如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING 是指以当前行为基准前面三行和后面三行总共 7 行为窗口；而 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 则表示该分区内当前行以及当前行之前的所有行为窗口。

RANGE BETWEEN 则表示数据的选择范围，比如 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING 则假设当前行的值为 v，那窗口的范围为满足数值在 [v - 3, v + 3] 范围内的当前分区内的所有行。

大多数情况下其实不会显式地指定窗口范围，这时 DBMS 会按情况默认选择一个窗口。比如当 ORDER BY 后面没有指定窗口时，窗口默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 即分区内包括当前行在内的之前的所有行；而当 ORDER BY 从句也没有时，窗口则为 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 即当前分区内的所有行。

窗口函数可以分为聚合函数和专有窗口函数。聚合函数我们在不用窗口函数时也可以经常看到：MIN, MAX, AVG, SUM, COUNT… 专有窗口函数只会在使用窗口函数时使用，按用途可以分为取值函数：FIRST_VALUE, LAST_VALUE, LEAD, LAG… 和排序函数： RANK, DENSE_RANK, ROW_NUMBER… 不同的 DBMS 可能还会有独家的专有窗口函数。