MySQL窗口函数入门到精通：实现复杂数据分析与排名

窗口函数可在不改变原始数据行数的情况下进行排名、累计求和、移动平均等分析。其语法为function_name() OVER (PARTITION BY col ORDER BY col)，支持RANK()、ROW_NUMBER()、SUM() OVER()等函数，适用于MySQL 8.0+。与GROUP BY不同，窗口函数保留每行数据并增加计算列，常用于Top N、同比环比、移动平均等场景，配合索引和合理窗口设计可提升性能。

MySQL窗口函数，简单来说，就是让你在查询结果的“窗口”内进行计算，而不用像GROUP BY那样把数据聚合起来。它既能保留原始数据的完整性，又能进行灵活的分析，简直是数据分析的利器！

窗口函数让你在不改变原始数据行的情况下，进行诸如排名、累计求和、移动平均等操作。

解决方案

窗口函数的基本语法是：

function_name() OVER (PARTITION BY column1 ORDER BY column2)

• function_name()

  登录后复制
  ：你要使用的窗口函数，比如

  RANK()

  登录后复制
  、

  SUM()

  登录后复制
  、

  AVG()

  登录后复制
  等等。

• OVER()

  登录后复制
  ：定义窗口的范围。

• PARTITION BY column1

  登录后复制
  ：将数据按照

  column1

  登录后复制
  进行分组，每个分组就是一个窗口。如果没有

  PARTITION BY

  登录后复制
  ，则整个结果集就是一个窗口。

• ORDER BY column2

  登录后复制
  ：在每个窗口内，按照

  column2

  登录后复制
  进行排序。

几个常用的窗口函数：

• ROW_NUMBER()

  登录后复制
  ：为每个窗口内的行分配一个唯一的序号，从1开始。

• RANK()

  登录后复制
  ：为每个窗口内的行分配排名，相同的值排名相同，但会跳过后续排名。

• DENSE_RANK()

  登录后复制
  ：与

  RANK()

  登录后复制
  类似，但相同的值排名相同，不会跳过后续排名。

• NTILE(n)

  登录后复制
  ：将每个窗口内的行分成

  n

  登录后复制
  组，并为每行分配一个组号。

• SUM() OVER()

  登录后复制
  ：计算窗口内的累计和。

• AVG() OVER()

  登录后复制
  ：计算窗口内的平均值。

• LAG(column, n, default)

  登录后复制
  ：返回当前行之前

  n

  登录后复制
  行的

  column

  登录后复制
  值，如果没有前

  n

  登录后复制
  行，则返回

  default

  登录后复制
  。

• LEAD(column, n, default)

  登录后复制
  ：返回当前行之后

  n

  登录后复制
  行的

  column

  登录后复制
  值，如果没有后

  n

  登录后复制
  行，则返回

  default

  登录后复制
  。

举个例子：

假设我们有一个

sales

date

region

amount

CREATE TABLE sales (
    date DATE,
    region VARCHAR(20),
    amount DECIMAL(10, 2)
);

INSERT INTO sales (date, region, amount) VALUES
('2023-01-01', 'North', 100.00),
('2023-01-01', 'South', 150.00),
('2023-01-02', 'North', 120.00),
('2023-01-02', 'South', 180.00),
('2023-01-03', 'North', 110.00),
('2023-01-03', 'South', 200.00);

1. 计算每个区域的销售额排名：

SELECT
    date,
    region,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS sales_rank
FROM
    sales;

这个查询会按照

region

amount

2. 计算每个区域的累计销售额：

SELECT
    date,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY date) AS cumulative_sales
FROM
    sales;

这个查询会按照

region

date

3. 计算每个区域的移动平均销售额（过去三天）：

SELECT
    date,
    region,
    amount,
    AVG(amount) OVER (PARTITION BY region ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM
    sales;

这个查询会按照

region

date

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

腾讯智影-AI数字人

基于AI数字人能力，实现7*24小时AI数字人直播带货，低成本实现直播业务快速增增，全天智能在线直播

73

查看详情

MySQL 8.0 之后才开始支持窗口函数，如果你的MySQL版本低于8.0，需要升级才能使用。

MySQL窗口函数有哪些常见的应用场景？

窗口函数在数据分析中应用广泛，可以解决很多复杂的排名、统计和比较问题。

• 排名问题： 比如计算每个产品的销售额排名、每个用户的活跃度排名等等。
• 累计统计： 比如计算每天的累计销售额、每个月的累计用户增长等等。
• 同比/环比分析： 比如计算今年的销售额与去年同期的增长率、本月的销售额与上月的增长率等等。
• 移动平均： 比如计算过去7天的平均活跃用户数、过去3个月的平均销售额等等。
• Top N 问题： 比如找出每个地区销售额最高的 Top 3 产品。

窗口函数能做到的，很多情况下使用子查询或者临时表也能实现，但窗口函数通常更简洁、更高效。

如何优化MySQL窗口函数的性能？

窗口函数虽然强大，但如果使用不当，也可能导致性能问题。

• 索引优化： 确保

  PARTITION BY

  登录后复制
  和

  ORDER BY

  登录后复制
  子句中使用的列都有索引。
• 避免不必要的排序： 如果不需要排序，可以省略

  ORDER BY

  登录后复制
  子句。
• 控制窗口大小： 窗口太大可能会导致性能问题，尽量缩小窗口范围。
• 选择合适的窗口函数： 不同的窗口函数性能可能不同，根据实际需求选择最合适的函数。
• 避免在窗口函数中使用复杂的表达式： 复杂的表达式会降低性能，尽量将表达式提前计算好。
• MySQL版本： 使用较新版本的MySQL，通常会对窗口函数进行优化。

窗口函数和GROUP BY的区别是什么？

GROUP BY

• GROUP BY

  登录后复制
  会将数据按照指定的列进行分组，然后对每个分组进行聚合计算，最终只返回每个分组的一行结果。
• 窗口函数则是在指定的窗口内进行计算，不会改变原始数据的行数，而是为每一行添加额外的计算结果。

简单来说，

GROUP BY

什么时候应该使用窗口函数，什么时候应该使用GROUP BY？

• 如果你需要对数据进行分组聚合，并且只需要每个分组的一行结果，那么应该使用

  GROUP BY

  登录后复制
  。
• 如果你需要在不改变原始数据行数的情况下，进行排名、累计统计、同比/环比分析等操作，那么应该使用窗口函数。

总的来说，选择哪个取决于你的具体需求。 窗口函数在需要保留原始数据的详细信息，并同时进行聚合计算时，优势非常明显。

以上就是MySQL窗口函数入门到精通：实现复杂数据分析与排名的详细内容，更多请关注php中文网其它相关文章！