SQL： 窗口滑动（Sliding Window）

目录

什么是“窗口”？

什么是“滑动”？

🔍 滑动窗口的核心： 

 🕒 什么是时间窗口？（Time Window）

时间窗口的基本结构

时间窗口的三种常见形式 

📊 什么是行窗口？（Row-Based Window） 

行窗口的结构

常见用途与写法

ROWS vs RANGE

---

什么是“窗口”？

在数据分析中，“窗口”是一个“范围”或“区间”的意思。比如你每天记录一次体重，今天是 2024-06-01，那么：

• 如果你说“最近 3 天的平均体重”，那这个“最近 3 天”就是一个窗口。

• 它包含了：2024-05-30、2024-05-31、2024-06-01

什么是“滑动”？

滑动（sliding），就是：这个时间范围是不断往前“滑动”的，不是固定不变的。

举个例子，现在有如下每天的体重记录：

日期	体重（kg）
6月1日	60
6月2日	61
6月3日	62
6月4日	63
6月5日	64

你要计算「每一天的过去 3 天平均体重」：

第一步：6月3日的窗口 = 6月1日 ~ 6月3日

• 数据：60 + 61 + 62

• 平均：61

第二步：6月4日的窗口 = 6月2日 ~ 6月4日

• 数据：61 + 62 + 63

• 平均：62

第三步：6月5日的窗口 = 6月3日 ~ 6月5日

• 数据：62 + 63 + 64

• 平均：63

当前日期	窗口起始	窗口结束	平均体重
6月3日	6月1日	6月3日	61
6月4日	6月2日	6月4日	62
6月5日	6月3日	6月5日	63

 用一个大小为 3 天的窗口，在时间线上每天滑动一步，每次都计算窗口内的总和或平均值。

🔍 滑动窗口的核心： 

窗口滑动 = 一种时间上的移动聚合

固定窗口大小，每次时间前进一天，就“滑动”一次。

你可以想象：一个“透明的框”，只能装 3 天数据，这个框不断往右移动，每次移动就重新统计框里的数据。

---

 窗口滑动可以分为以下两大类：

窗口类型	滑动单位	常见关键词	举例说明
时间窗口	时间（天、小时）	date, interval	最近 7 天平均销售额
行窗口	行数（记录数）	ROWS BETWEEN	当前行及前 6 行的平均销售额

 🕒 什么是时间窗口？（Time Window）

时间窗口是一种以“时间”为单位的滑动分析方法。

比如说：

我想知道每一天的“过去 7 天”销售额总和或平均值。

举例：

• 今天是 2025-06-01

• 时间窗口就是：2025-05-26 到 2025-06-01

• 明天到了 2025-06-02，窗口就变成：2025-05-27 到 2025-06-02

这就是一个“固定宽度、滑动前进”的时间分析窗口。

时间窗口的基本结构

<窗口函数> OVER ([PARTITION BY 列名1, 列名2, ...]ORDER BY 时间列 [ASC|DESC]RANGE BETWEEN 间隔 PRECEDING AND 间隔 FOLLOWING
)

RANGE BETWEEN：基于 ORDER BY 列的值范围（而非行数），常见选项：

• INTERVAL 'n' DAY/HOUR/SECOND PRECEDING：向前 n 个时间单位。

• CURRENT ROW：当前行时间点。

• INTERVAL 'n' DAY/HOUR/SECOND FOLLOWING：向后 n 个时间单位。

• UNBOUNDED PRECEDING/FOLLOWING：分区开头/结尾。

关键字	含义	示例
UNBOUNDED PRECEDING	从最开头行开始	整组累加直到当前行
n PRECEDING	当前行向前数第 n 行	过去 n 行
CURRENT ROW	当前行本身	仅此行 or 截止此行
n FOLLOWING	当前行之后第 n 行	未来 n 行
UNBOUNDED FOLLOWING	一直到最后一行	当前行到结尾

示例：

SELECTorder_date,revenue,SUM(revenue) OVER (PARTITION BY customer_idORDER BY order_dateRANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS last_7_days_revenue
FROM orders;

时间窗口的实现方式

在 SQL 中，时间窗口不直接写成 RANGE BETWEEN，一般通过如下方式实现：

✅ 方法一：子查询 + BETWEEN 做“滑动聚合”

SELECTA.visited_on,(SELECT SUM(B.amount)FROM Customer BWHERE B.visited_on BETWEEN A.visited_on - INTERVAL 6 DAY AND A.visited_on) AS sum_amount
FROM Customer A

每一行 A 都会向前回顾 7 天内的数据 B，进行统计。

• 外层查询：遍历 Customer 表（别名 A），获取每行的 visited_on。

• 子查询：针对每行 A.visited_on，从 Customer 表（别名 B）筛选 visited_on 在前6天（含当天）范围内的记录，计算 amount 的总和。

• BETWEEN：定义时间窗口，从 A.visited_on - INTERVAL '6' DAY 到 A.visited_on。

✅ 方法二：自连接（JOIN）+ 时间范围 

SELECTA.visited_on,SUM(B.amount) AS amount
FROM Customer A
JOIN Customer BON B.visited_on BETWEEN A.visited_on - INTERVAL 6 DAY AND A.visited_on
GROUP BY A.visited_on

 让每一行 A 和前 6 天的 B 匹配，形成一个窗口，然后聚合。

• 自连接：主表 Customer（别名 A）与自身（别名 B）连接，ON 条件限定 B.visited_on 在 A.visited_on 前6天（含当天）的范围内。

• 时间范围：用 BETWEEN 指定从 A.visited_on - INTERVAL '6' DAY 到 A.visited_on。

• GROUP BY：按 A.visited_on 分组，计算 B.amount 的总和。

时间窗口的三种常见形式 

① 滑动窗口（Sliding Window）——「有重叠」

• 窗口大小固定（比如 7 天）

• 每天都往前滑动一步

• 常用于计算“过去 7 天的平均值”之类的场景

🧠 比如：

当前日期	窗口范围	含义
6月7日	6月1日~6月7日	计算这 7 天总消费
6月8日	6月2日~6月8日	继续往前滑一格

② 滚动窗口（Tumbling Window）——「无重叠」

• 窗口之间完全不重叠

• 一段时间为一个完整窗口，下一段重新开始

🧠 比如：

窗口编号	窗口时间范围
W1	6月1日 00:00 ~ 6月1日 05:00
W2	6月1日 05:00 ~ 6月1日 10:00
W3	6月1日 10:00 ~ 6月1日 15:00

常用于按小时、每天、每 5 分钟等做不重叠汇总统计。

SELECTDATE_TRUNC('day', visited_on) AS window_start,SUM(amount) AS tumbling_sum
FROM Customer
GROUP BY DATE_TRUNC('day', visited_on);

• DATE_TRUNC：将 visited_on 截断到指定时间单位（如 'day'、'week'），划分非重叠窗口。

• GROUP BY：按截断后的时间分组，计算每段的聚合（如 SUM）。

• 替代实现（固定时间间隔）：

SELECTFLOOR(DATEDIFF('day', '2020-01-01', visited_on) / 7) AS window_id,SUM(amount) AS tumbling_sum
FROM Customer
GROUP BY FLOOR(DATEDIFF('day', '2020-01-01', visited_on) / 7);

 使用 DATEDIFF 计算天数并划分固定7天窗口

③ 会话窗口（Session Window）——「不固定长度」

• 用户一段时间内连续操作归为一组

• 中间空闲超过某阈值就开启新窗口

 🧠 比如：用户 A 从 9:00 ~ 9:20 连续浏览页面，中间没有断开，属于一个“会话窗口”；
如果他 9:40 又回来访问，就开启另一个窗口。

这种常用于用户行为分析，比如“每次访问网站的行为路径”。 

SELECTvisited_on,SUM(amount) OVER (PARTITION BY session_id) AS session_sum
FROM (SELECTvisited_on,amount,SUM(CASE WHEN DATEDIFF('hour', prev_visited_on, visited_on) > 1 THEN 1 ELSE 0 END) OVER (ORDER BY visited_on) AS session_idFROM (SELECTvisited_on,amount,LAG(visited_on) OVER (ORDER BY visited_on) AS prev_visited_onFROM Customer) t
) t2;

• LAG：获取前一行 visited_on，计算与当前行的时间差。

• CASE WHEN：若时间差超过阈值（如1小时），标记新会话。

• SUM OVER：累计会话标记生成 session_id，按 session_id 分组计算聚合。

• PARTITION BY session_id：按会话分组聚合。

---

📊 什么是行窗口？（Row-Based Window） 

行窗口（Row Window）是以“数据行的数量”为基础定义的一个分析范围，而不是时间。

🧠 举个直觉例子：

假设你有一个按时间排序的销售数据表，你想计算“当前行以及前两行”的平均销售额。

这时不管这几行是哪几天的数据，只要是“当前行之前的 2 行 + 当前行”，就会参与计算。

 这种滑动就是**“按行”滑动，而不是按时间段”滑动**，所以叫行窗口（Row Window）。

行窗口的结构

在 SQL 中，行窗口常使用 窗口函数（Window Function）+ OVER 子句 实现。

常见语法：

<窗口函数> OVER ([PARTITION BY 列名1, 列名2, ...]ORDER BY 列名 [ASC|DESC]ROWS BETWEEN 起始点 AND 结束点
)SELECTemployee_id,salary,SUM(salary) OVER (PARTITION BY department_idORDER BY salaryROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_sum
FROM employees;

计算每个部门内，按薪资排序的当前行、前一行、后一行的薪资总和。 

• 窗口函数：如 SUM()、AVG()、ROW_NUMBER()、RANK() 等。

• PARTITION BY：可选，按指定列分组，类似 GROUP BY，但保留原始行。

• ORDER BY：定义窗口内行的排序方式，影响累积计算。

• ROWS BETWEEN：定义窗口范围，常见选项：

  • UNBOUNDED PRECEDING：从分区开头。

  • n PRECEDING：前 n 行。

  • CURRENT ROW：当前行。

  • n FOLLOWING：后 n 行。

  • UNBOUNDED FOLLOWING：到分区结尾。

常见用途与写法

✅ ① 计算“前 N 行”的累计或平均

SELECTvisited_on,amount,AVG(amount) OVER (ORDER BY visited_onROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3_days
FROM Customer;

实现“每行向前看 2 行 + 当前行”的平均销售额。

✅ ② 计算“排名”、“累计和”、“同比增长” 

SUM(amount) OVER (ORDER BY visited_onROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

 从第一行开始，累计到当前行的总销售额（running total）

ROWS vs RANGE

比较点	ROWS	RANGE
控制的是	行数（位置）	时间范围（值的范围）
示例	前两行：ROWS BETWEEN 2 PRECEDING	前 7 天：RANGE BETWEEN INTERVAL 7 DAY
支持度	MySQL 完整支持	MySQL 基本不支持 INTERVAL 用于 RANGE