【MySQL】窗口函数详解（概念+练习+实战）

窗口函数是MySQL 8.0引入的一个强大功能，允许在SELECT语句中进行数据的复杂计算。与传统的聚合函数不同，窗口函数不会将多行数据聚合为一行，而是能在保留行的基础上，计算出某些分析结果。

本篇文章将详细介绍MySQL的窗口函数，包括其概念、常见的应用场景、示例代码、实战应用等。通过这篇文章，你将能够深入理解窗口函数并应用到日常的数据分析和查询中。

1. 什么是窗口函数？

窗口函数与聚合函数不同，聚合函数会将多行合并为一行，而窗口函数是对某些行（即窗口）进行计算，每行的计算结果仍然保留原来的行数。简单来说，窗口函数的核心在于“计算过程中不会丢失数据行”。

常见的窗口函数包括：

• ROW_NUMBER()：为结果集中的每一行返回一个唯一的编号。
• RANK()：返回排名，排名相同的记录具有相同的排名，但跳过下一名。
• DENSE_RANK()：返回排名，排名相同的记录具有相同的排名，不跳过下一名。
• NTILE()：将行分组并返回分组编号。
• LEAD() 和 LAG()：获取当前行前后若干行的值。
• SUM()、AVG()、MAX()、MIN() 等聚合函数：可在窗口中使用，进行分组计算。

窗口函数语法

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

SELECT column_name, 
       window_function() OVER (
           PARTITION BY partition_expression 
           ORDER BY order_expression
           [frame_clause]
       )
FROM table_name;

• window_function：窗口函数的名称，如ROW_NUMBER()等。
• PARTITION BY：可选，表示按某列或列组分组。
• ORDER BY：可选，表示窗口内按某列或列组排序。
• frame_clause：可选，表示定义当前窗口的范围。

2. 窗口函数的基本概念

2.1 ROW_NUMBER()

ROW_NUMBER()用于为查询结果中的每一行返回唯一的行号，行号根据ORDER BY指定的顺序递增。

SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

在上述示例中，查询的每个员工按工资降序排列，并为每个员工分配一个行号。

2.2 RANK()

RANK()返回排名，排名相同的行具有相同的排名，但排名跳过下一位。

SELECT employee_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees;

当有相同的工资时，员工的排名会相同，但是下一名的排名会跳过。

2.3 DENSE_RANK()

DENSE_RANK()类似于RANK()，但不会跳过下一名。

SELECT employee_id, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

即使员工的工资相同，DENSE_RANK()也会使排名紧密排列，不跳过下一名。

2.4 LEAD() 和 LAG()

LEAD()和LAG()用于在当前行的基础上，向前或向后获取某一行的值。

SELECT employee_id, salary,
       LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
       LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;

在上面的查询中，LAG()获取前一行的工资，而LEAD()获取后一行的工资。

2.5 NTILE()

NTILE()将结果集划分为指定数量的桶，并为每行返回桶号。

SELECT employee_id, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS bucket
FROM employees;

这个查询将员工的工资划分为4个区间，并为每个员工分配一个桶号。

3. 窗口函数实战应用

3.1 排名前N名员工

我们要找出工资最高的前5名员工，可以使用ROW_NUMBER()窗口函数：

SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees
WHERE row_num <= 5;

该查询为每个员工按工资降序排列，并限制查询结果为前5名。

3.2 按部门排名员工

在实际业务中，我们常常需要按部门为员工排名，这时可以结合PARTITION BY子句来实现：

SELECT department_id, employee_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

在上述查询中，每个部门的员工按工资排名，并给出在该部门中的排名。

3.3 累积工资计算

使用SUM()作为窗口函数，可以计算员工工资的累积和：

SELECT employee_id, salary,
       SUM(salary) OVER (ORDER BY employee_id) AS cumulative_salary
FROM employees;

这会计算员工工资的累积总和，从第一行到当前行的所有工资。

3.4 获取每位员工的前一个工资记录

SELECT employee_id, salary,
       LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary
FROM employees;

LAG()函数允许我们获取员工的前一个工资记录。

4. 窗口函数的应用场景

窗口函数广泛应用于以下场景：

• 排名和分组：可以轻松实现分组内的排名计算，如找出每个部门的前几名员工。
• 时间序列分析：在分析时间序列数据时，可以使用LEAD()和LAG()函数比较前后的数据。
• 累积和：通过使用聚合函数（如SUM()）的窗口版本，可以快速计算累积和、移动平均等。
• 分桶：使用NTILE()可以将数据均匀地分配到不同的桶中，实现简单的分布分析。

5. 总结

窗口函数是MySQL中非常强大的工具，尤其在处理复杂的查询和分析任务时，它们能显著简化SQL的编写。本文通过详细的介绍和丰富的代码示例，讲解了MySQL窗口函数的用法，帮助你掌握这项强大的技能。

通过本文的学习，你可以使用窗口函数完成各种复杂的数据分析任务，如排名、分组、累积和时间序列分析等。希望这些内容能够为你提供有价值的帮助。如果你有任何问题，欢迎讨论！

参考文档

• MySQL官方文档：窗口函数