mysql从8.0版本开始支持窗口函数，它能在不改变结果集行数的情况下进行计算，使数据分析更高效；1. 窗口函数通过over()定义计算范围，支持partition by分组和order by排序；2. 常见函数包括rank()、dense_rank()、row_number()等排序函数，sum()、avg()等聚合函数，lag()、lead()等偏移函数，以及first_value()、last_value()、ntile()等；3. rank()并列时跳过后续排名，dense_rank()不跳过，row_number()生成唯一序号；4. 聚合函数作为窗口函数可实现累计计算，如累计工资；5. lag()和lead()用于获取前后行数据，常用于差值分析；6. first_value()取窗口首值，last_value()需配合rows between unbounded preceding and unbounded following才能取窗口末值；7. ntile(n)将数据按序分为n桶，用于分层分析；8. 性能优化建议包括为partition by和order by列建立索引、避免复杂表达式、合理设定窗口范围并避免过度使用窗口函数；掌握这些内容可有效提升复杂查询的编写效率与执行性能。
mysql窗口函数，简单来说，就是在查询结果集上进行计算，但又不改变结果集的行数。它就像给你的数据加了一层“透视镜”，能让你看到更丰富的信息，而不用像GROUP BY那样改变数据的原始结构。
MySQL从8.0版本开始支持窗口函数，极大地增强了数据分析的能力。
MySQL窗口函数详解与实战案例
窗口函数是什么，为什么我们需要它？
想象一下，你要计算每个部门的工资排名，或者找出每个月销售额最高的几天。如果用传统的SQL，你需要各种子查询和连接，代码又长又难懂。窗口函数就像一个“神奇的盒子”，它能在你的原始数据上进行计算，然后把结果“贴”回每一行数据上。这样，你就能在同一行看到原始数据和计算结果，分析起来方便多了。
窗口函数的核心在于“窗口”的概念，它定义了计算的范围。这个范围可以是整个结果集，也可以是按照某种条件划分的子集。
MySQL常见的窗口函数有哪些？
MySQL提供了丰富的窗口函数，大致可以分为以下几类：
- 排序函数：
RANK()
,
DENSE_RANK()
,
ROW_NUMBER()
- 聚合函数：
SUM()
,
AVG()
,
MIN()
,
MAX()
,
count()
- 分布函数：
PERCENT_RANK()
,
CUME_DIST()
- 偏移函数：
LAG()
,
LEAD()
- 首尾函数：
FIRST_VALUE()
,
LAST_VALUE()
- 其他函数：
NTH_VALUE()
,
NTILE()
这些函数的功能各不相同，但它们都遵循窗口函数的通用语法：
函数名(参数) OVER (PARTITION BY 列名 ORDER BY 列名)
PARTITION BY
用于将结果集划分为多个分区，窗口函数会在每个分区内独立计算。
ORDER BY
用于指定每个分区内的排序规则。如果没有
PARTITION BY
，则整个结果集被视为一个分区。
举个例子，假设我们有一个
employees
表，包含员工姓名、部门和工资：
| name | department | salary |
|---|---|---|
| Alice | Sales | 5000 |
| Bob | Sales | 6000 |
| Carol | IT | 7000 |
| David | IT | 8000 |
| Eve | Sales | 5500 |
要计算每个部门的工资排名，可以使用以下SQL：
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM employees;
结果如下：
| name | department | salary | salary_rank |
|---|---|---|---|
| Bob | Sales | 6000 | 1 |
| Eve | Sales | 5500 | 2 |
| Alice | Sales | 5000 | 3 |
| David | IT | 8000 | 1 |
| Carol | IT | 7000 | 2 |
可以看到，
RANK()
函数在每个部门内按照工资降序进行了排名。
排序函数：RANK(), DENSE_RANK(), ROW_NUMBER() 的区别？
这三个排序函数都用于计算排名，但它们的行为略有不同。
-
RANK()
：如果有并列排名，会跳过后续排名。例如，如果有两个第一名，则下一个排名是第三名。
-
DENSE_RANK()
：如果有并列排名，不会跳过后续排名。例如，如果有两个第一名，则下一个排名是第二名。
-
ROW_NUMBER()
：无论是否有并列排名，都会按照顺序生成唯一的排名。
继续上面的例子，如果我们在 Sales 部门添加一个工资为 6000 的员工 Frank：
| name | department | salary |
|---|---|---|
| Alice | Sales | 5000 |
| Bob | Sales | 6000 |
| Frank | Sales | 6000 |
| Carol | IT | 7000 |
| David | IT | 8000 |
| Eve | Sales | 5500 |
使用不同的排序函数：
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_number FROM employees;
结果如下：
| name | department | salary | rank_rank | dense_rank | row_number |
|---|---|---|---|---|---|
| Bob | Sales | 6000 | 1 | 1 | 1 |
| Frank | Sales | 6000 | 1 | 1 | 2 |
| Eve | Sales | 5500 | 3 | 2 | 3 |
| Alice | Sales | 5000 | 4 | 3 | 4 |
| David | IT | 8000 | 1 | 1 | 1 |
| Carol | IT | 7000 | 2 | 2 | 2 |
可以看到，
RANK()
函数跳过了排名 2，而
DENSE_RANK()
函数没有跳过。
ROW_NUMBER()
函数则为每一行都生成了唯一的排名。
聚合函数作为窗口函数的应用场景？
SUM()
,
AVG()
,
MIN()
,
MAX()
,
COUNT()
这些聚合函数也可以作为窗口函数使用。 它们可以在窗口范围内进行聚合计算，并将结果返回到每一行。
例如，要计算每个部门的累计工资：
SELECT name, department, salary, SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS cumulative_salary FROM employees;
结果如下：
| name | department | salary | cumulative_salary |
|---|---|---|---|
| Alice | Sales | 5000 | 5000 |
| Eve | Sales | 5500 | 10500 |
| Bob | Sales | 6000 | 16500 |
| Carol | IT | 7000 | 7000 |
| David | IT | 8000 | 15000 |
SUM(salary) OVER (PARTITION BY department ORDER BY salary)
计算了每个部门内，按照工资排序的累计工资。
如何使用LAG() 和 LEAD() 函数？
LAG()
和
LEAD()
函数用于访问窗口内前一行或后一行的值。
-
LAG(column, n, default_value)
：返回窗口内当前行之前第 n 行的 column 值。如果之前没有 n 行，则返回 default_value。
-
LEAD(column, n, default_value)
：返回窗口内当前行之后第 n 行的 column 值。如果之后没有 n 行，则返回 default_value。
例如，要计算每个员工的工资与前一个员工的工资差：
SELECT name, department, salary, salary - LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS salary_difference FROM employees;
结果如下：
| name | department | salary | salary_difference |
|---|---|---|---|
| Alice | Sales | 5000 | 5000 |
| Eve | Sales | 5500 | 500 |
| Bob | Sales | 6000 | 500 |
| Carol | IT | 7000 | 7000 |
| David | IT | 8000 | 1000 |
LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary)
返回了每个部门内，前一个员工的工资。第一个员工的工资差为 5000，因为
LAG()
函数返回了默认值 0。
FIRST_VALUE() 和 LAST_VALUE() 函数的应用场景
FIRST_VALUE()
和
LAST_VALUE()
函数用于返回窗口内第一行和最后一行的值。
-
FIRST_VALUE(column)
：返回窗口内第一行的 column 值。
-
LAST_VALUE(column)
：返回窗口内最后一行的 column 值。
需要注意的是，
LAST_VALUE()
函数默认只返回当前行及之前行的最后一行的值。如果需要返回整个窗口的最后一行的值，需要使用
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
子句。
例如，要计算每个员工的工资与部门最高工资的差：
SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS salary_difference FROM employees;
或者使用
FIRST_VALUE()
和
LAST_VALUE()
函数：
SELECT name, department, salary, LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - salary AS salary_difference FROM employees;
这两个sql语句的结果相同，都是计算每个员工的工资与部门最高工资的差。
如何使用NTILE()函数进行数据分桶？
NTILE(n)
函数将窗口内的数据分成 n 桶，并为每一行分配一个桶号。
例如，要将员工按照工资分成 3 个等级：
SELECT name, department, salary, NTILE(3) OVER (ORDER BY salary) AS salary_level FROM employees;
结果如下：
| name | department | salary | salary_level |
|---|---|---|---|
| Alice | Sales | 5000 | 1 |
| Eve | Sales | 5500 | 1 |
| Bob | Sales | 6000 | 2 |
| Carol | IT | 7000 | 2 |
| David | IT | 8000 | 3 |
NTILE(3) OVER (ORDER BY salary)
将员工按照工资排序，并分成 3 个等级。
窗口函数性能优化建议
窗口函数虽然强大，但使用不当也会影响性能。以下是一些性能优化建议：
- 尽量使用索引：
PARTITION BY
和
ORDER BY
子句中使用的列应该有索引，以提高查询效率。
- 避免在窗口函数中使用复杂的表达式： 复杂的表达式会增加计算量，影响性能。
- 合理选择窗口范围： 窗口范围过大或过小都会影响性能。应该根据实际需求选择合适的窗口范围。
- 避免过度使用窗口函数： 过度使用窗口函数会增加查询复杂度，影响性能。
总之，窗口函数是MySQL中非常强大的数据分析工具。掌握窗口函数的使用，可以让你更轻松地完成各种复杂的数据分析任务。但同时也要注意性能优化，避免过度使用。
