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
表，包含员工姓名、部门和工资：
AliceSales5000BobSales6000CarolIT7000DavidIT8000EveSales5500
要计算每个部门的工资排名，可以使用以下SQL：
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rankFROM employees;
结果如下：
BobSales60001EveSales55002AliceSales50003DavidIT80001CarolIT70002
可以看到，
RANK()
函数在每个部门内按照工资降序进行了排名。
排序函数：RANK(), DENSE_RANK(), ROW_NUMBER() 的区别？
这三个排序函数都用于计算排名，但它们的行为略有不同。
RANK()
：如果有并列排名，会跳过后续排名。例如，如果有两个第一名，则下一个排名是第三名。
DENSE_RANK()
：如果有并列排名，不会跳过后续排名。例如，如果有两个第一名，则下一个排名是第二名。
ROW_NUMBER()
：无论是否有并列排名，都会按照顺序生成唯一的排名。
继续上面的例子，如果我们在 Sales 部门添加一个工资为 6000 的员工 Frank：
AliceSales5000BobSales6000FrankSales6000CarolIT7000DavidIT8000EveSales5500
使用不同的排序函数：
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_numberFROM employees;
结果如下：
BobSales6000111FrankSales6000112EveSales5500323AliceSales5000434DavidIT8000111CarolIT7000222
可以看到，
RANK()
函数跳过了排名 2，而
DENSE_RANK()
函数没有跳过。
ROW_NUMBER()
函数则为每一行都生成了唯一的排名。
聚合函数作为窗口函数的应用场景？
SUM()
,
AVG()
,
MIN()
,
MAX()
,
COUNT()
这些聚合函数也可以作为窗口函数使用。 它们可以在窗口范围内进行聚合计算，并将结果返回到每一行。
动态WEB网站中的PHP和MySQL：直观的QuickPro指南第2版
动态WEB网站中的PHP和MySQL详细反映实际程序的需求，仔细地探讨外部数据的验证(例如信用卡卡号的格式)、用户登录以及如何使用模板建立网页的标准外观。动态WEB网站中的PHP和MySQL的内容不仅仅是这些。书中还提到如何串联JavaScript与PHP让用户操作时更快、更方便。还有正确处理用户输入错误的方法，让网站看起来更专业。另外还引入大量来自PEAR外挂函数库的强大功能，对常用的、强大的包
508 查看详情
例如，要计算每个部门的累计工资：
SELECT name, department, salary, SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS cumulative_salaryFROM employees;
结果如下：
AliceSales50005000EveSales550010500BobSales600016500CarolIT70007000DavidIT800015000
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_differenceFROM employees;
结果如下：
AliceSales50005000EveSales5500500BobSales6000500CarolIT70007000DavidIT80001000
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_differenceFROM 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_differenceFROM employees;
这两个SQL语句的结果相同，都是计算每个员工的工资与部门最高工资的差。
如何使用NTILE()函数进行数据分桶？
NTILE(n)
函数将窗口内的数据分成 n 桶，并为每一行分配一个桶号。
例如，要将员工按照工资分成 3 个等级：
SELECT name, department, salary, NTILE(3) OVER (ORDER BY salary) AS salary_levelFROM employees;
结果如下：
AliceSales50001EveSales55001BobSales60002CarolIT70002DavidIT80003
NTILE(3) OVER (ORDER BY salary)
将员工按照工资排序，并分成 3 个等级。
窗口函数性能优化建议
窗口函数虽然强大，但使用不当也会影响性能。以下是一些性能优化建议：
尽量使用索引：
PARTITION BY
和
ORDER BY
子句中使用的列应该有索引，以提高查询效率。避免在窗口函数中使用复杂的表达式： 复杂的表达式会增加计算量，影响性能。合理选择窗口范围： 窗口范围过大或过小都会影响性能。应该根据实际需求选择合适的窗口范围。避免过度使用窗口函数： 过度使用窗口函数会增加查询复杂度，影响性能。
总之，窗口函数是MySQL中非常强大的数据分析工具。掌握窗口函数的使用，可以让你更轻松地完成各种复杂的数据分析任务。但同时也要注意性能优化，避免过度使用。
以上就是MySQL中常用的窗口函数有哪些 MySQL窗口函数详解与实战案例的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1027733.html
微信扫一扫 
支付宝扫一扫 
