【MySQL】探索 MySQL 窗口函数（Window Functions）

本文主要是介绍【MySQL】探索 MySQL 窗口函数（Window Functions），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

缘分让我们相遇乱世以外
命运却要我们危难中相爱
也许未来遥远在光年之外
我愿守候未知里为你等待
我没想到为了你我能疯狂到
山崩海啸没有你根本不想逃
我的大脑为了你已经疯狂到
脉搏心跳没有你根本不重要
🎵 邓紫棋《光年之外》

在大数据分析和处理的过程中，我们经常需要对数据进行复杂的分析和计算。传统的 SQL 聚合函数（如 SUM, AVG, MAX, MIN 等）虽然强大，但它们在处理一些特定需求时显得力不从心，比如需要在保留行级别信息的同时进行聚合计算。这时候，窗口函数（Window Functions）便显得尤为重要。本文将深入探讨 MySQL 窗口函数的使用及其强大之处。

什么是窗口函数？

窗口函数允许我们在不改变行级别数据的前提下，执行复杂的聚合和分析操作。与传统的聚合函数不同，窗口函数不会对结果进行分组，它会为每一行返回一个值，并且这个值是基于某个“窗口”内的行计算得出的。

基本语法

窗口函数的基本语法如下：

window_function() OVER ([PARTITION BY partition_expression][ORDER BY sort_expression][frame_clause]
)

window_function(): 窗口函数的名称，例如 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG() 等。
PARTITION BY partition_expression: 可选项，定义窗口的分区。
ORDER BY sort_expression: 可选项，定义窗口的排序。
frame_clause: 可选项，定义窗口的范围。

常见的窗口函数

ROW_NUMBER()
ROW_NUMBER() 函数为结果集的每一行分配一个唯一的行号。

SELECTname,department,salary,ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROMemployees;

上述查询为每个部门的员工按薪资降序排列，并分配一个行号。

RANK() 和 DENSE_RANK()
RANK() 和 DENSE_RANK() 函数类似，但处理排名相同时有所不同：

RANK(): 如果有两个相同的排名，下一名的排名会跳过。
DENSE_RANK(): 如果有两个相同的排名，下一名的排名不会跳过。

SELECTname,department,salary,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROMemployees;

聚合函数作为窗口函数
常见的聚合函数如 SUM(), AVG(), MAX(), MIN() 也可以作为窗口函数使用。

SELECTdepartment,employee,salary,SUM(salary) OVER (PARTITION BY department) AS total_salary,AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROMemployees;

上述查询计算了每个部门的总薪资和平均薪资，并将结果保留在每一行。

窗口范围（Frame）
窗口函数的强大之处还在于它可以定义窗口的范围。范围可以使用 ROWS 或 RANGE 子句定义。

SELECTemployee,sale_date,sales,SUM(sales) OVER (PARTITION BY employeeORDER BY sale_dateROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_sum
FROMsales;

上述查询计算了每个员工在当前行及之前两行内的销售额的移动和。

实际案例

假设我们有一个销售数据表 sales，包含以下字段：sale_id, employee, sale_date, amount。我们希望计算每个员工的累计销售额。

CREATE TABLE sales (sale_id INT AUTO_INCREMENT PRIMARY KEY,employee VARCHAR(50),sale_date DATE,amount DECIMAL(10, 2)
);INSERT INTO sales (employee, sale_date, amount) VALUES
('Alice', '2024-01-01', 100.00),
('Alice', '2024-01-02', 200.00),
('Alice', '2024-01-03', 150.00),
('Bob', '2024-01-01', 50.00),
('Bob', '2024-01-02', 300.00),
('Bob', '2024-01-03', 200.00);SELECTemployee,sale_date,amount,SUM(amount) OVER (PARTITION BY employee ORDER BY sale_date) AS cumulative_sales
FROMsales;

查询结果如下：

+----------+------------+--------+------------------+
| employee | sale_date  | amount | cumulative_sales |
+----------+------------+--------+------------------+
| Alice    | 2024-01-01 | 100.00 |           100.00 |
| Alice    | 2024-01-02 | 200.00 |           300.00 |
| Alice    | 2024-01-03 | 150.00 |           450.00 |
| Bob      | 2024-01-01 |  50.00 |            50.00 |
| Bob      | 2024-01-02 | 300.00 |           350.00 |
| Bob      | 2024-01-03 | 200.00 |           550.00 |
+----------+------------+--------+------------------+