17370845950

新闻动态

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
('2025-01-01', 'North', 100.00),
('2025-01-01', 'South', 150.00),
('2025-01-02', 'North', 120.00),
('2025-01-02', 'South', 180.00),
('2025-01-03', 'North', 110.00),
('2025-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

定义了窗口的范围，表示当前行和前两行。

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
```
。
如果你需要在不改变原始数据行数的情况下，进行排名、累计统计、同比/环比分析等操作，那么应该使用窗口函数。

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

17370845950

关于我们

服务项目

广告推广

案例欣赏