MySQL中常用的窗口函数有哪些 MySQL窗口函数详解与实战案例

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

要计算每个部门的工资排名，可以使用以下SQL：

SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
    employees;

结果如下：

可以看到，

RANK()

排序函数：RANK(), DENSE_RANK(), ROW_NUMBER() 的区别？

这三个排序函数都用于计算排名，但它们的行为略有不同。

• RANK()

  登录后复制
  ：如果有并列排名，会跳过后续排名。例如，如果有两个第一名，则下一个排名是第三名。

• DENSE_RANK()

  登录后复制
  ：如果有并列排名，不会跳过后续排名。例如，如果有两个第一名，则下一个排名是第二名。

• ROW_NUMBER()

  登录后复制
  ：无论是否有并列排名，都会按照顺序生成唯一的排名。

继续上面的例子，如果我们在 Sales 部门添加一个工资为 6000 的员工 Frank：

使用不同的排序函数：

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;

结果如下：

可以看到，

RANK()

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_salary
FROM
    employees;

结果如下：

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;

结果如下：

LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary)

LAG()

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)

例如，要将员工按照工资分成 3 个等级：

SELECT
    name,
    department,
    salary,
    NTILE(3) OVER (ORDER BY salary) AS salary_level
FROM
    employees;

结果如下：

NTILE(3) OVER (ORDER BY salary)

窗口函数性能优化建议

窗口函数虽然强大，但使用不当也会影响性能。以下是一些性能优化建议：

• 尽量使用索引：

  PARTITION BY

  登录后复制
  和

  ORDER BY

  登录后复制
  子句中使用的列应该有索引，以提高查询效率。
• 避免在窗口函数中使用复杂的表达式： 复杂的表达式会增加计算量，影响性能。
• 合理选择窗口范围： 窗口范围过大或过小都会影响性能。应该根据实际需求选择合适的窗口范围。
• 避免过度使用窗口函数： 过度使用窗口函数会增加查询复杂度，影响性能。

总之，窗口函数是MySQL中非常强大的数据分析工具。掌握窗口函数的使用，可以让你更轻松地完成各种复杂的数据分析任务。但同时也要注意性能优化，避免过度使用。

以上就是MySQL中常用的窗口函数有哪些 MySQL窗口函数详解与实战案例的详细内容，更多请关注php中文网其它相关文章！