MySQL 用得上的新特性(三) — Window Functions-一一网

0x00 前言

千呼万唤始出来，最最重要的功能，往往要压轴登场。

白话解释之前，我想先表明自己的立场。对于 Window Function 网上有两种翻译：窗口函数 vs 开窗函数。两者实则一致，我个人倾向第一种，文中后统一使用窗口函数一词。

0x01 白话解释

SQL 执行到 SELECT 阶段，提供操作多行数据的能力。

0x02 举例说明

经典问题 Top K
目前已知雇员表 employee (name varchar(32), department_id int, salary int）, 求每个部门 Top 3 工资的所有员工。

常规解法：

-- 解法大致如此。单纯举例，先假设工资不重复、名字不重复。
select * 
from employee e1
where 3 >= (
	select count(1)
	from employee e2 
	where e1.department_id = e2.department_id 
		and e1.salary < e2.salary
)
复制代码

窗口函数解法

select * -- 结果中甚至还有在部门中相对的排名 rk
from (
	select name, salary, department_id,
	 RANK() over(partition by department_id order by salary desc) rk -- 窗口函数使用场景
	from employee 
) employee_with_rank
where rk <= 3
复制代码

再次回到这个例子，需要跨过好几节，但请耐心看完，我已经很精简了。
或者可以直接跳转到举例解释

0x03 先从 SQL 执行顺序说起

直接说 2 个结论

ORDER BY 和窗口函数无法一起使用
SQL 执行过程
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT (Window Function)
ORDER BY clause
LIMIT clause

0x04 解释者再 ?

对于上述的流程，诸君应是熟稔于心，不再赘述；这里针对包含窗口函数的 SELECT 过程展开。

通常而言，SELECT 都是从一行记录中，选取需要的字段；可以是 plain field 如 field1，或是简单运算field1 + field2, 抑或是执行函数 round(field1 / field2, 2) as percent 等等，不一而足。这些数据都是从一行中获取。

如果 SELECT 要从多行中获取数据，是否可行呢？答案就是 窗口函数！

Important

首先，窗口函数操作的多行，都必须是和本行有关联，如何关联下一节语法会提到。
其次，对多行数据并不能任意操作，仅提供部分功能，这些功能就是 窗口函数!

0x05 解释者叁 ?

基于上一节的概括，我们进入本节的 MySQL 官方文档定义。

主要定义
[1] window_function([expr]) over_clause

[2] 其中 over_clause:
    {OVER (window_spec) | OVER window_name}
    
[3] 其中 window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]
    
[4] 其中 partition_clause:
    PARTITION BY expr [, expr] ...
    
[5] 其中 order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
    
[6] 其中 frame_clause:
    frame_units frame_extent

[7] 其中 frame_units:
    {ROWS | RANGE}
    
[8] 其中 frame_extent:
    {frame_start | frame_between}

[9] 其中 frame_between:
    BETWEEN frame_start AND frame_end

[10] 其中 frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}
复制代码

这么长的定义，散落在 2 个链接里，我给拼凑到了一起。

解释

承接上一节的 Important:

首先，窗口函数操作的多行，都必须是和本行有关联。
- 相关联的多行，用 over_clause 来具体定义。
- 这些关联的行，称为一个 window。
- 每次可以不使用完整的 window，只截取 window 中部分连续的行，这部分连续的行，称为 frame。
其次，对多行数据并不能任意操作，仅提供部分功能。
- 窗口函数支持所有的 GROUP BY 聚集函数
- 窗口函数提供的额外函数：
  - 获取 frame 内行号的函数 row_number()
  - 获取 frame 内第 n 行的某个字段 nth_value(expr)
  - 获取 frame 内前一行的某个字段 lag(expr)
  - ……
- expr 可以是运算，比如 ifnull(field1, field2)
- 上述 SQL 定义[1]：window_function([expr])中，expr 是可选的，因为有些窗口函数没有参数，比如 row_number()

再回顾上述长长的 SQL 定义

定义整体的范式：窗口函数 + 作用的 window

[1] window_function([expr]) over_clause
复制代码

定义 window: 使用 over(window_spec) 来定义：

根据 partition_clause 关联多行
根据 order 来排序多行
根据 frame 来限定使用行数

[2] 其中 over_clause:
    {OVER (window_spec) | OVER window_name}
    
[3] 其中 window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]
    
[4] 其中 partition_clause:
    PARTITION BY expr [, expr] ...
    
[5] 其中 order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
复制代码

定义 frame: 从哪一行开始，到哪一行结束。默认第一行开始，当前行结束。

[6] 其中 frame_clause:
    frame_units frame_extent

[7] 其中 frame_units:
    {ROWS | RANGE}
    
[8] 其中 frame_extent:
    {frame_start | frame_between}

[9] 其中 frame_between:
    BETWEEN frame_start AND frame_end

[10] 其中 frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}
复制代码

0x06 举例解释

select * 
from (
	select name, salary, department_id,
	 RANK() over(partition by department_id order by salary desc) rk 
	from employee 
) employee_with_rank
where rk <= 3
复制代码

Important

定义关联行的窗口：

over(partition by department_id order by salary desc)
- 根据 department_id 切分为多个 window，每个 window 中包含这个部分所有的用户信息
- 根据 salary 在 window 内降序排列
- frame 为空，默认为第一行到当前行。本例子即：department_id 相同的 employee 中，salary 降序最高的，直到当前薪资的行。
应用窗口函数

RANK()
- 根据 salary 排序后的排名。薪资相同，排名会有跳跃，例如 1, 2, 2, 4……