利用MySQL窗口函数实现数据分区与滑动窗口计算-mysql教程-PHP中文网

利用MySQL窗口函数实现数据分区与滑动窗口计算

夢幻星辰

发布： 2025-09-10 10:45:01

原创

935人浏览过

MySQL窗口函数可在分区数据上执行排名、累计、滑动窗口等计算，如用SUM() OVER(PARTITION BY product_id ORDER BY sale_date ROWS 6 PRECEDING)计算7日滚动销售额。

利用mysql窗口函数实现数据分区与滑动窗口计算

MySQL窗口函数，简单来说，就是在查询结果集上定义一个窗口，然后在这个窗口内进行计算。它让你能轻松实现数据分区、排名、累计计算等复杂操作，而无需编写繁琐的存储过程或多次查询。

数据分区和滑动窗口计算是窗口函数最常见的应用场景。数据分区是将数据集按照某个或多个字段进行分组，然后在每个分区内进行计算。滑动窗口计算则是在一个固定的窗口大小内，随着数据行的移动，动态地计算窗口内的值。

解决方案

以下将通过实际示例，展示如何利用MySQL窗口函数实现数据分区与滑动窗口计算。

假设我们有一个

sales

登录后复制

表，包含以下字段：

```
sale_date
```
登录后复制
(DATE): 销售日期
```
product_id
```
登录后复制
(INT): 产品ID
```
sales_amount
```
登录后复制
(DECIMAL): 销售额

现在，我们需要计算每个产品每日的销售额，以及过去7天的销售额总和（滑动窗口）。

SELECT
    sale_date,
    product_id,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sale_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7_day_sales
FROM
    sales;

登录后复制

这个SQL查询做了以下几件事：

```
PARTITION BY product_id
```
登录后复制
: 将数据按照
```
product_id
```
登录后复制
进行分区，这意味着每个产品都会有一个独立的窗口。
```
ORDER BY sale_date ASC
```
登录后复制
: 在每个分区内，按照销售日期升序排序。这是滑动窗口计算的基础，确保数据按照时间顺序进行计算。
```
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
```
登录后复制
: 定义滑动窗口的大小。
```
6 PRECEDING
```
登录后复制
表示当前行之前的6行，
```
CURRENT ROW
```
登录后复制
表示当前行。因此，这个窗口包含了当前行以及之前的6行，总共7行。
```
SUM(sales_amount) OVER (...)
```
登录后复制
: 计算窗口内
```
sales_amount
```
登录后复制
的总和。

这个查询会返回每个产品的每日销售额，以及过去7天的销售额总和。例如，如果某个产品的销售记录如下：

sale_date	product_id	sales_amount
2023-01-01	1	100
2023-01-02	1	120
2023-01-03	1	150
2023-01-04	1	130
2023-01-05	1	160
2023-01-06	1	140
2023-01-07	1	170
2023-01-08	1	180

那么查询结果的

rolling_7_day_sales

登录后复制

列将会是：

sale_date	product_id	sales_amount	rolling_7_day_sales
2023-01-01	1	100	100
2023-01-02	1	120	220
2023-01-03	1	150	370
2023-01-04	1	130	500
2023-01-05	1	160	660
2023-01-06	1	140	800
2023-01-07	1	170	970
2023-01-08	1	180	1050

注意，前6天的

rolling_7_day_sales

登录后复制

值会逐渐增加，因为窗口内的行数不足7行。

SpeakingPass-打造你的专属雅思口语语料

使用chatGPT帮你快速备考雅思口语，提升分数

查看详情

MySQL窗口函数还有哪些其他的应用场景？

除了滑动窗口计算，窗口函数还可以用于以下场景：

排名： 计算每个分区内的排名，例如，计算每个产品的销售额排名。可以使用
```
RANK()
```
登录后复制
,
```
DENSE_RANK()
```
登录后复制
,
```
ROW_NUMBER()
```
登录后复制
等函数。
累计计算： 计算每个分区内的累计值，例如，计算每个产品的累计销售额。可以使用
```
SUM()
```
登录后复制
,
```
AVG()
```
登录后复制
,
```
MIN()
```
登录后复制
,
```
MAX()
```
登录后复制
等函数。
比较： 比较当前行与分区内的其他行，例如，计算每个产品的销售额与上一个销售日的销售额的差值。可以使用
```
LAG()
```
登录后复制
,
```
LEAD()
```
登录后复制
等函数。
分组统计： 在不使用
```
GROUP BY
```
登录后复制
的情况下，对分区内的数据进行统计。

例如，要计算每个产品销售额的排名，可以使用以下SQL查询：

SELECT
    sale_date,
    product_id,
    sales_amount,
    RANK() OVER (PARTITION BY product_id ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

登录后复制

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

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

索引： 确保用于分区和排序的字段都有索引。这可以大大提高查询速度。
数据类型： 尽量使用较小的数据类型。例如，如果
```
product_id
```
登录后复制
的取值范围不大，可以使用
```
INT
```
登录后复制
而不是
```
BIGINT
```
登录后复制
。
避免不必要的计算： 只计算需要的字段。避免在窗口函数中进行复杂的计算，可以将计算结果存储在临时表中。
合理选择窗口大小： 窗口大小会影响计算量。选择合适的窗口大小，避免过大的窗口导致性能下降。
MySQL版本： 确保使用较新的MySQL版本。新版本通常会对窗口函数进行优化。