MySQL 窗口函数提供了一种灵活的方式来处理 SQL 查询中的数据，它们允许你在不需要对数据进行分组的情况下对行集进行分析。窗口函数最常用于分析性操作，比如计算排名、累计和、移动平均值等。MySQL 从版本 8.0 开始支持窗口函数。以下是窗口函数的几个关键概念和常见用法：
窗口函数的基本语法
窗口函数的基本语法结构如下：
sql
代码解读
复制代码
function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])
- function_name: 窗口函数名，如
ROW_NUMBER(),RANK(),DENSE_RANK(),SUM(),AVG(), 等。 - PARTITION BY: 可选，用于定义窗口的分区，类似于
GROUP BY。 - ORDER BY: 用于定义窗口内的行顺序。
- frame_clause: 可选，定义窗口框架，可以是
ROWS或RANGE。
常见窗口函数
- ROW_NUMBER()
- 为每个分区的每一行分配唯一的行号。
sql
- 代码解读
- 复制代码
SELECT name, department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
- RANK()
- 为每个分区的每一行分配排名，排名有重复，且后面排名会有跳跃。
sql
- 代码解读
- 复制代码
SELECT name, department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
- DENSE_RANK()
- 类似于
RANK()，但排名不跳跃。
sql
- 代码解读
- 复制代码
SELECT name, department,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
- NTILE(n)
- 将分区中的行均匀分布到
n个桶中，并为每一行分配桶号。
sql
- 代码解读
- 复制代码
SELECT name, department,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
- SUM(), AVG(), MIN(), MAX()
- 在窗口内计算聚合值。
sql
- 代码解读
- 复制代码
SELECT name, department, salary,
SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;
窗口框架
窗口框架用来定义在计算聚合值时哪些行应该被包含在窗口内。框架可以用 ROWS 或 RANGE 来定义。
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从分区开始到当前行。
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：从前一行到后一行。
示例：
sql
代码解读
复制代码
SELECT name, salary,
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_sum
FROM employees;
应用场景
窗口函数在以下场景中非常有用：
- 计算每个部门内员工的工资排名。
- 求某段时间内的累计销售额。
- 计算移动平均值或累计和。
- 分析和报告需要复杂数据操作的情况。
通过使用窗口函数，开发者可以在查询中轻松实现复杂的分析和报告功能，而不需要进行额外的数据处理。窗口函数提供了一种简洁、优雅的解决方案，可以提高 SQL 查询的表达能力和执行效率。
作者：stormsha
链接：https://juejin.cn/post/7438605834374823955
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
