数据库：SQL窗⼝函数知识介绍
窗⼝函数（Window Function） 是 SQL2003 标准中定义的⼀项新特性，并在 SQL2011、SQL2016 中⼜加以完善，添加了若⼲处拓展。窗⼝函数不同于我们熟悉的普通函数和聚合函数，它为每⾏数据进⾏⼀次计算：输⼊多⾏（⼀个窗⼝）、返回⼀个值。在报表等分析型查询中，窗⼝函数能优雅地表达某些需求，发挥不可替代的作⽤。
本⽂⾸先介绍窗⼝函数的定义及基本语法，之后将介绍在 DBMS 和⼤数据系统中是如何实现⾼效计算窗⼝函数的，包括窗⼝函数的优化、执⾏以及并⾏执⾏。
什么是窗⼝函数？
窗⼝函数出现在 SELECT ⼦句的表达式列表中，它最显著的特点就是 OVER 关键字。语法定义如下：
window_function (expression) OVER (
[ PARTITION BY part_list ]中考试题及答案
[ ORDER BY order_list ]
[ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )
其中包括以下可选项：
PARTITION BY 表⽰将数据先按 part_list 进⾏分区
ORDER BY 表⽰将各个分区内的数据按 order_list 进⾏排序
Figure 1. 窗⼝函数的基本概念
最后⼀项表⽰ Frame 的定义，即：当前窗⼝包含哪些数据？
ROWS 选择前后⼏⾏，例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING 表⽰往前 3 ⾏到往后 3 ⾏，⼀共 7 ⾏数据（或⼩于 7 ⾏，如果碰到了边界）
RANGE 选择数据范围，例如 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING 表⽰所有值在 [c−3,c+3][c−3,c+3] 这个范围内的⾏，cc 为当前⾏的值
Figure 2. Rows 窗⼝和 Range 窗⼝
逻辑语义上说，⼀个窗⼝函数的计算“过程”如下：
1. 按窗⼝定义，将所有输⼊数据分区、再排序（如果需要的话）
2. 对每⼀⾏数据，计算它的 Frame 范围
3. 将 Frame 内的⾏集合输⼊窗⼝函数，计算结果填⼊当前⾏
举个例⼦：
chine go to publc wcSELECT dealer_id, emp_name, sales,
ROW_NUMBER() OVER (PARTITION BY dealer_id ORDER BY sales) AS rank,
AVG(sales) OVER (PARTITION BY dealer_id) AS avgsales
FROM sales
上述查询中，rank 列表⽰在当前经销商下，该雇员的销售排名；avgsales 表⽰当前经销商下所有雇
+------------+-----------------+--------+------+---------------+
| dealer_id | emp_name | sales | rank | avgsales |
+------------+-----------------+--------+------+---------------+
| 1 | Raphael Hull | 8227 | 1 | 14356 |
| 1 | Jack Salazar | 9710 | 2 | 14356 |
| 1 | Ferris Brown | 19745 | 3 | 14356 |
| 1 | Noel Meyer | 19745 | 4 | 14356 |
| 2 | Haviva Montoya | 9308 | 1 | 13924 |
| 2 | Beverly Lang | 16233 | 2 | 13924 |
| 2 | Kameko French | 16233 | 3 | 13924 |
| 3 | May Stout | 9308 | 1 | 12368 |
| 3 | Abel Kim | 12369 | 2 | 12368 |
| 3 | Ursa George | 15427 | 3 | 12368 |
+------------+-----------------+--------+------+---------------+
如果不指定 PARTITION BY，则不对数据进⾏分区；换句话说，所有数据看作同⼀个分区
如果不指定 ORDER BY，则不对各分区做排序，通常⽤于那些顺序⽆关的窗⼝函数，例如 SUM()
如果不指定 Frame ⼦句，则默认采⽤以下的 Frame 定义：曼谷奔逃
若不指定 ORDER BY，默认使⽤分区内所有⾏ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING
若指定了 ORDER BY，默认使⽤分区内第⼀⾏到当前值 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW
最后，窗⼝函数可以分为以下 3 类：
聚合（Aggregate）：AVG(), COUNT(), MIN(), MAX(), SUM()...
取值（Value）：FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()...
排序（Ranking）：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()...
受限于篇幅，本⽂不去探讨各个窗⼝函数的含义。关注公众号Java技术栈，在后台回复：⾯试，可以获取我整理的 MySQL 系列⾯试题和答案，⾮常齐全。
2012流行歌曲排行榜
注：Frame 定义并⾮所有窗⼝函数都适⽤，⽐如 ROW_NUMBER()、RANK()、LEAD() 等。这些函数总是应⽤于整个分区，⽽⾮当前 Frame。
窗⼝函数 VS. 聚合函数
从聚合这个意义上出发，似乎窗⼝函数和 Group By 聚合函数都能做到同样的事情。但是，它们之间的相似点也仅限于此了！这其中的关键区别在于：窗⼝函数仅仅只会将结果附加到当前的结果上，它不会对已有的⾏或列做任何修改。⽽ Group By 的做法完全不同：对于各个Group 它仅仅会保留⼀⾏聚合结果。
有的读者可能会问，加了窗⼝函数之后返回结果的顺序明显发⽣了变化，这不算⼀种修改吗？因为 SQL 及关系代数都是以 multi-t 为基础定义的，结果集本⾝并没有顺序可⾔，ORDER BY 仅仅是最终呈现结果的顺序。
burgundy另⼀⽅⾯，从逻辑语义上说，SELECT 语句的各个部分可以看作是按以下顺序“执⾏”的：
Figure 3. SQL 各部分的逻辑执⾏顺序
注意到窗⼝函数的求值仅仅位于 ORDER BY 之前，⽽位于 SQL 的绝⼤部分之后。这也和窗⼝函数只附加、不修改的语义是呼应的——结果集在此时已经确定好了，再依此计算窗⼝函数。
窗⼝函数的执⾏
窗⼝函数经典的执⾏⽅式分为排序和函数求值这 2 步。
Figure 4. ⼀个窗⼝函数的执⾏过程，通常分为排序和求值 2 步
embassy窗⼝定义中的 PARTITION BY 和 ORDER BY 都很容易通过排序完成。例如，对于窗⼝ PARTITION BY a, b ORDER BY c, d，我们可以对输⼊数据按 (a,b,c,d)(a,b,c,d) 或 (b,a,c,d)(b,a,c,d) 做排序，之后数据就排列成 Figure 1 中那样了。
接下来考虑：如何处理 Frame？
对于整个分区的 Frame（例如 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING），只要对整个分区计算⼀次即可，没什么好说的；
对于逐渐增长的 Frame（例如 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），可以⽤ Aggregator 维护累加的状态，这也很容易实现；
对于滑动的 Frame（例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING）相对困难⼀些。⼀种经典的做法是要求
Aggregator 不仅⽀持增加还⽀持删除（Removable），这可能⽐你想的要更复杂，例如考虑下 MAX() 的实现。
窗⼝函数的优化
对于窗⼝函数，优化器能做的优化有限。这⾥为了⾏⽂的完整性，仍然做⼀个简要的说明。
通常，我们⾸先会把窗⼝函数从 Project 中抽取出来，成为⼀个独⽴的算⼦称之为 Window。
Figure 5. 窗⼝函数的优化过程
有时候，⼀个 SELECT 语句中包含多个窗⼝函数，它们的窗⼝定义（OVER ⼦句）可能相同、也可能不同。显然，对于相同的窗⼝，完全没必要再做⼀次分区和排序，我们可以将它们合并成⼀个 Window 算⼦。
对于不同的窗⼝，最朴素地，我们可以将其全部分成不同的 Window，如上图所⽰。实际执⾏时，每个 Window 都需要先做⼀次排序，代价不⼩。
那是否可能利⽤⼀次排序计算多个窗⼝函数呢？某些情况下，这是可能的。例如本⽂例⼦中的 2 个窗⼝函数：
ps是什么... ROW_NUMBER() OVER (PARTITION BY dealer_id ORDER BY sales) AS rank,
AVG(sales) OVER (PARTITION BY dealer_id) AS avgsales ...
虽然这 2 个窗⼝并⾮完全⼀致，但是 AVG(sales) 不关⼼分区内的顺序，完全可以复⽤ ROW_NUMBER() 的窗⼝。
窗⼝函数的并⾏执⾏
现代 DBMS ⼤多⽀持并⾏执⾏。对于窗⼝函数，由于各个分区之间的计算完全不相关，我们可以很容易地将各个分区分派给不同的节点（线程），从⽽达到分区间并⾏。
但是，如果窗⼝函数只有⼀个全局分区（⽆ PARTITION BY ⼦句），或者分区数量很少、不⾜以充分并⾏时，怎么办呢？上⽂中我们提到的 Removable Aggregator 的技术显然⽆法继续使⽤了，它依赖于单个 Aggregator 的内部状态，很难有效地并⾏起来。
TUM 的这篇论⽂中提出使⽤线段树（Segment Tree）实现⾼效的分区内并⾏。线段树是⼀个 N 叉树数据结构，每个节点包含当前节点下的部分聚合结果。
下图是⼀个使⽤⼆叉线段树计算 SUM() 的例⼦。例如下图中第三⾏的 1212，表⽰叶节点 5+75+7 的聚合结果；⽽它上⽅的 2525 表⽰叶节点 5+7+3+105+7+3+10 的聚合结果。
Figure 6. 使⽤线段树计算给定范围的总和
线段树可以在 O(nlogn)O(nlog n) 时间内构造，并能在 O(logn)O(log n) 时间内查询任意区间的聚合结果。更棒的是，不仅查询可以多线程
并发互不⼲扰，⽽且线段树的构造过程也能被很好地并⾏起来。
Referencesbyaccident
1.
2.
3.
4.
5.
尝试错误IT技术分享社区
个⼈博客⽹站：
⽂章推荐程序员效率：画流程图常⽤的⼯具程序员效率：整理常⽤的在线笔记软件远程办公：常⽤的远程协助软件，你都知道吗？51单⽚机程序下载、ISP及串⼝基础
>sav
