MySQL 窗口函数介绍与使用

Note / 作者： /

概念和语法

MySQL 8.0 开始内置窗口函数，又叫开窗函数，属于分析函数的一种。用于解决一些报表统计分析需求或者 leetcode sql 题中也比较常见。窗口函数对一组查询行执行类似聚合的操作，但是和聚合函数不同，聚合函数对每个分组进行计算并为每个分组仅返回一行，而窗口函数不会将每个分组输出到一行，而是将每个分组的计算结果合并到与之关联的行中。

窗口函数语法

window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )
  • window_function(args) 是需要调用的窗口函数以及它的参数。 可以使用 MySQL 提供的窗口函数（非聚合函数，如下一小节），或使用聚合函数（例如MAX()、MIN()、AVG()、SUM()、COUNT() 等）；
  • OVER 子句可用于行进行分组或组内排序，窗口函数都依赖于 **OVER 子句， **OVER 具有三个可能元素的子句，分区定义，顺序定义和帧定义；
  • partition_defintion 分区定义，通过PARTITION BY 指定需要分区的列名，它是可选的。可以看成与 GROUP BY 一样功能的语法，默认为整个结果集为一个分区；
  • order_definition 顺序定义，通过 ORDER BY 指定排序列名，它是可选的。一般是在 PARTITION BY 之后排序，即在分区内排序，若没有 PARTITION BY ，则是对全部数据排序。
  • frame_definition 帧定义， frame_unit {<frame_start>|<frame_between>} 其中 frame_unit 帧单位可以是ROWS或RANGE ，frame_start和frame_between定义帧边界：
    • frame_start :
      • UNBOUNDED PRECEDING：frame从分区的第一行开始。
      • N PRECEDING：第一个当前行之前的物理N行。N可以是文字数字或计算结果的表达式。
      • CURRENT ROW：当前计算的行。
    • frame_between : 如BETWEEN frame_boundary_1 AND frame_boundary_2 ，frame_boundary_1和frame_boundary_2可各自含有下列之一：
      • frame_start：如前所述。
      • UNBOUNDED FOLLOWING：框架结束于分区的最后一行。
      • N FOLLOWING：当前行之后的物理N行。

未指定 frame_definition 的情况下，MySQL默认使用以下帧定义：
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

另外我们可以定义窗口并给定名称，以便在OVER子句中引用它们，语法：

WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

当需要多次复用一个窗口时，该语法可以更加方便简洁地维护 SQL 语句，例如：

SELECT
  val,
  ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
  RANK()       OVER (ORDER BY val) AS 'rank',
  DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;

=====================================================
# 等同于

SELECT
  val,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK()       OVER w AS 'rank',
  DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

MySQL 提供的窗口函数

Name 解释 语法
CUME_DIST() 返回一组值中的累积分布，即从第一行到与当前行值相同的最后一行的行数在分区内的总行数中的占比。 CUME_DIST() OVER (…)
DENSE_RANK() 返回分区中当前行的排名（从 1 开始），并且排名是连续的 DENSE_RANK() OVER (…)
FIRST_VALUE() 当前窗口中第一行的表达式值 FIRST_VALUE(expression) OVER (…)
LAG() 分区中，当前行之前第 N 行的表达式的值 LAG([,offset[, default_value]]) OVER (…)
LAST_VALUE() 当前窗口中最后一行的表达式值 LAST_VALUE (expression) OVER (…)
LEAD() 分区中，当前行之后第 N 行的表达式的值 LEAD([,offset[, default_value]]) OVER (…)
NTH_VALUE() 当前窗口中第 N 行的表达式值 NTH_VALUE(expression, N)[FROM FIRST] OVER (…)
NTILE() 将分区划分为 N 桶，为分区中的每一行分配桶号 NTILE(n) OVER (…)
PERCENT_RANK() 当前行所在的分区内的相对排名，即分区中小于当前行的百分比，也就是 (rank() – 1) / (分区总行数 – 1) PERCENT_RANK() OVER (…)
RANK() 返回分区中当前行的排名（从 1 开始），排名可能不连续 RANK() OVER (…)
ROW_NUMBER() 返回分区中当前行的序号（从 1 开始） ROW_NUMBER() OVER (…)

应用

以 leetcode 数据库题为例

178. 分数排名

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
在 SQL 中，id 是该表的主键。
该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。

查询并对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
按 score 降序返回结果表。

输入: 
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
输出: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS 'rank'
FROM Scores

180. 连续出现的数字

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
id 是一个自增列。


找出所有至少连续出现**三次**的数字。

返回的结果表中的数据可以按 任意顺序 排列。

结果格式如下面的例子所示：



示例 1:

输入：
Logs 表：
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

# 取当前行的 num 值等于前面第一行和前面第二行都相等的记录（即至少出现 3 次）
SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT num, 
                LAG(num, 1) OVER () AS l1, 
                LAG(num, 2) OVER () AS l2
    FROM Logs
) t1
WHERE t1.num = t1.l1
    AND t1.num = t1.l2

185. 部门工资前三高的所有员工

表: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
departmentId 是 Department 表中 ID 的外键（reference 列）。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。


表: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示部门ID和部门名。

示例 1:

输入: 
Employee 表:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department  表:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
输出: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

SELECT t2.name AS Department, t1.name AS Employee, t1.salary AS Salary
FROM (
    SELECT name, departmentId, salary, DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS ranking
    FROM Employee
) t1
    LEFT JOIN Department t2 ON t1.departmentId = t2.id
WHERE ranking <= 3

参考

window-functions

MySQL 窗口函数参考

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

Subscribe
Subscribe
Scroll to Top