MySQL中常用的窗口函数有哪些 MySQL窗口函数详解与实战案例
mysql从8.0版本开始支持窗口函数，它能在不改变结果集行数的情况下进行计算，使数据分析更高效；1. 窗口函数通过over()定义计算范围，支持partition by分组和order by排序；2. 常见函数包括rank()、dense_rank()、row_number()等排序函数，sum()、*g()等聚合函数，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()
,*G()
,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 |
| D*id | 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 |
| D*id | 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 |
| D*id | 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 |
| D*id | IT | 8000 | 1 | 1 | 1 |
| Carol | IT | 7000 | 2 | 2 | 2 |
可以看到，
RANK()函数跳过了排名 2，而
DENSE_RANK()函数没有跳过。
ROW_NUMBER()函数则为每一行都生成了唯一的排名。
聚合函数作为窗口函数的应用场景？
SUM(),
*G(),
MIN(),
MAX(),
COUNT()这些聚合函数也可以作为窗口函数使用。 它们可以在窗口范围内进行聚合计算，并将结果返回到每一行。
动态WEB网站中的PHP和MySQL：直观的QuickPro指南第2版
动态WEB网站中的PHP和MySQL详细反映实际程序的需求，仔细地探讨外部数据的验证(例如信用卡卡号的格式)、用户登录以及如何使用模板建立网页的标准外观。动态WEB网站中的PHP和MySQL的内容不仅仅是这些。书中还提到如何串联J*aScript与PHP让用户操作时更快、更方便。还有正确处理用户输入错误的方法，让网站看起来更专业。另外还引入大量来自PEAR*函数库的强大功能，对常用的、强大的包
525
查看详情
例如，要计算每个部门的累计工资：
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 |
| D*id | 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 |
| D*id | 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 |
| D*id | IT | 8000 | 3 |
NTILE(3) OVER (ORDER BY salary)将员工按照工资排序，并分成 3 个等级。
窗口函数性能优化建议
窗口函数虽然强大，但使用不当也会影响性能。以下是一些性能优化建议：
-
尽量使用索引：
PARTITION BY
和ORDER BY
子句中使用的列应该有索引，以提高查询效率。 - 避免在窗口函数中使用复杂的表达式： 复杂的表达式会增加计算量，影响性能。
- 合理选择窗口范围： 窗口范围过大或过小都会影响性能。应该根据实际需求选择合适的窗口范围。
- 避免过度使用窗口函数： 过度使用窗口函数会增加查询复杂度，影响性能。
总之，窗口函数是MySQL中非常强大的数据分析工具。掌握窗口函数的使用，可以让你更轻松地完成各种复杂的数据分析任务。但同时也要注意性能优化，避免过度使用。
以上就是MySQL中常用的窗口函数有哪些 MySQL窗口函数详解与实战案例的详细内容，更多请关注其它相关文章！
热门内容推荐
快捷栏目导航
