求每个商品近 30 天的累计 GMV（rolling window）。

考察要点

这道题面试官想看你是否精通 SQL 窗口函数（Window Functions），特别是用于时间序列分析的 滑动窗口（Sliding Window） 计算。考察的核心点在于：

1. 窗口函数的语义理解：能否准确使用 OVER()子句，包括 PARTITION BY 进行分组和 ORDER BY 进行排序。
2. 窗口框架（Window Frame）的定义：是否知道 ROWS 和 RANGE 的区别，并能为“近30天”这个业务需求选择最合适的框架定义。
3. SQL 性能意识：是否能写出在大数据量下依然高效的 SQL，并理解其背后的索引原理。

核心概念

本题的核心是使用窗口函数计算移动聚合值。

1. 窗口函数 (Window Function) 窗口函数对与当前行相关的行集（即“窗口”）进行计算。与 GROUP BY 聚合函数不同，它在计算后不合并行，而是为结果集中的每一行都返回一个计算值。其基本语法是：

   sql

   ⧉⛶

    1<function_name>() OVER (
    2    [PARTITION BY <...>]
    3    [ORDER BY <...>]
    4    [<frame_clause>]
    5)

2. 分区 PARTITION BY vs 分组 GROUP BY

   • GROUP BY product_id: 会将每个 product_id 的所有行聚合成一行。
   • PARTITION BY product_id: 会按 product_id 将数据分成多个逻辑分区，但保留所有原始行。窗口函数在每个分区内独立计算。

3. 窗口框架 (Window Frame) 这是本题的关键。它定义了在当前行的分区内，用于计算的行的具体范围。对于“近30天”的需求，我们关注 PRECEDING（之前的行）。

   • ROWS BETWEEN <N> PRECEDING AND CURRENT ROW: 基于物理行数定义窗口。ROWS BETWEEN 29 PRECEDING AND CURRENT ROW 指的是当前行及按 ORDER BY 排序的前29行，共30行。
   • RANGE BETWEEN <value> PRECEDING AND CURRENT ROW: 基于逻辑值定义窗口。RANGE BETWEEN INTERVAL '29' DAY PRECEDING AND CURRENT ROW 指的是当前行的 order_date 以及从该日期往前推29天范围内的所有行。

   ROWS vs RANGE 的关键区别： 假设某商品在某几天没有销售记录。

   product_id	order_date	daily_gmv	ROWS (2 preceding)	RANGE (2 days preceding)
   A	2023-01-01	100	100	100
   A	2023-01-02	150	250 (100+150)	250 (100+150)
   A	2023-01-05	200	350 (150+200)	200 (只有01-05当天)

   • 对于 2023-01-05 这一行：
     • ROWS BETWEEN 2 PRECEDING: 会找到前2行记录（01-02和01-01），计算 100+150+200。这不符合“近3天”的业务语义，因为它跨越了5天。
     • RANGE BETWEEN INTERVAL '2' DAY PRECEDING: 会找到日期在 [2023-01-03, 2023-01-05] 范围内的所有行。在这个例子中，只有 2023-01-05 这一天的记录，所以结果是 200。这才是业务上正确的“时间窗口”。

   因此，对于“近 N 天”这类需求，RANGE 模式是更准确的选择。

多种解法

假设我们有订单表 sales：

 1CREATE TABLE sales (
 2    order_id INT,
 3    product_id INT,
 4    order_date DATE,
 5    gmv DECIMAL(10, 2)
 6);
 7-- 假设 gmv = price * quantity 已计算好

解法 1：使用窗口函数（推荐，最优解）

这是最现代、最高效、最优雅的解法。

• 标注版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server。❌ MySQL 5.7 及更早版本不支持。
• SQL 代码：
  sql

  ⧉⛶

   1-- 首先，按天聚合每个商品的GMV，防止一天内有多笔订单导致窗口函数计算错误
   2WITH daily_gmv AS (
   3    SELECT
   4        product_id,
   5        order_date,
   6        SUM(gmv) AS total_daily_gmv
   7    FROM sales
   8    GROUP BY product_id, order_date
   9)
  10SELECT
  11    product_id,
  12    order_date,
  13    total_daily_gmv,
  14    SUM(total_daily_gmv) OVER (
  15        PARTITION BY product_id
  16        ORDER BY order_date
  17        RANGE BETWEEN INTERVAL '29' DAY PRECEDING AND CURRENT ROW
  18    ) AS rolling_gmv_30d
  19FROM daily_gmv
  20ORDER BY product_id, order_date;

• 时间/空间复杂度：时间复杂度主要由排序决定，为 $O(N \log N)$，其中 N 是 daily_gmv 的行数。如果利用索引，可以优化到近 $O(N)$。空间复杂度为 $O(P)$，P 为分区数，用于维护窗口状态。
• 索引依赖：在 sales 表上建立 (product_id, order_date) 的复合索引至关重要。这能极大地加速 GROUP BY 和后续窗口函数的 PARTITION BY / ORDER BY 操作，避免全表扫描和昂贵的 filesort。

解法 2：使用自连接（Self-Join）或相关子查询

这是一种兼容老版本数据库的经典解法，但性能通常较差。

• 标注版本：✅ 兼容所有主流 SQL 数据库，包括 MySQL 5.7。
• SQL 代码 (Self-Join)：
  sql

  ⧉⛶

   1-- 同样先按天聚合
   2WITH daily_gmv AS (
   3    SELECT
   4        product_id,
   5        order_date,
   6        SUM(gmv) AS total_daily_gmv
   7    FROM sales
   8    GROUP BY product_id, order_date
   9)
  10SELECT
  11    t1.product_id,
  12    t1.order_date,
  13    SUM(t2.total_daily_gmv) AS rolling_gmv_30d
  14FROM
  15    daily_gmv t1
  16JOIN
  17    daily_gmv t2 ON t1.product_id = t2.product_id
  18                 AND t2.order_date BETWEEN t1.order_date - INTERVAL '29' DAY AND t1.order_date
  19GROUP BY
  20    t1.product_id, t1.order_date
  21ORDER BY
  22    t1.product_id, t1.order_date;

• 时间/空间复杂度：在没有索引的情况下，复杂度接近 $O(N^2)$。即使有 (product_id, order_date) 索引，对于 daily_gmv 中的每一行 t1，都需要在 t2 中进行一次范围查找，性能远不如窗口函数。复杂度可近似为 $O(N \cdot K)$，其中 K 是窗口内的平均天数（这里是30）。
• 索引依赖：daily_gmv 作为中间结果，无法直接使用 sales 表的索引。但在支持物化 CTE 的数据库中，或如果将 daily_gmv 存为临时表并加索引， (product_id, order_date) 索引将是性能的关键。

何时使用：当必须兼容不支持窗口函数的老旧数据库系统时，这是唯一的选择。在现代数据库中，应始终首选窗口函数。

常见低分回答

1. 误用 GROUP BY：直接写 GROUP BY product_id, DATE_TRUNC('month', order_date)，这只能计算月度总GMV，而不是滑动的30天累计值。
2. 混淆 ROWS 和 RANGE：使用 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW。如核心概念中所述，这在日期不连续时会产生错误结果，因为它计算的是最近30条销售记录，而不是最近30天的销售记录。
3. 忘记 PARTITION BY：SUM(gmv) OVER (ORDER BY order_date ...) 会计算全站的总累计GMV，而不是按商品分别计算。
4. 相关子查询性能灾难：写出类似下面的相关子查询，并且没有在 (product_id, order_date) 上建立索引，导致对主表的每一行都进行一次全表扫描。
   sql

   ⧉⛶

    1-- 性能极差的写法
    2SELECT
    3    s1.product_id,
    4    s1.order_date,
    5    (SELECT SUM(s2.gmv)
    6     FROM sales s2
    7     WHERE s2.product_id = s1.product_id
    8       AND s2.order_date BETWEEN s1.order_date - INTERVAL '29' DAY AND s1.order_date
    9    ) AS rolling_gmv_30d
   10FROM sales s1;

5. 在 WHERE 子句中使用函数：在子查询或JOIN条件中对日期列使用 DATE_FORMAT(order_date, ...) 等函数，这会导致索引失效，退化为全表扫描。应始终保持列的“裸露”，将函数应用在常量或变量上。

工程实践与优化

• 数据预聚合：在真实生产环境中，订单表（sales）通常是亿级别的。直接在原始表上运行此查询会非常慢。最佳实践是创建一个每日聚合表 daily_product_gmv，通过离线任务（如每日凌晨的 Spark/Hive 任务）计算好每个商品每天的总GMV。我们的查询就直接跑在这个小得多的聚合表上。
• 执行计划分析 (EXPLAIN)：
  • 对于窗口函数解法，通过 EXPLAIN 查看执行计划。关键是看 Extra 列。理想情况下，不应出现 Using filesort。如果 (product_id, order_date) 上有索引，数据库可以直接利用索引的有序性来完成 PARTITION BY 和 ORDER BY，效率极高。
  • 对于自连接解法，EXPLAIN 会显示两个表的连接。要确保 t2 的访问类型（type）是 range 或 ref，而不是 ALL（全表扫描），这依赖于 (product_id, order_date) 上的索引。
• 物化视图/汇总表：对于需要频繁查询的BI报表，可以将最终的滚动GMV结果存储在一个专门的汇总表或物化视图中。前端应用直接查询此结果表，实现秒级响应，避免了每次查询都重复计算。
• 避免大事务：如果这个计算是某个更新流程的一部分，确保它在一个合理的事务大小内完成，避免长时间持有锁。

常见追问与延伸题目

1. 追问：如果数据量增长100倍，达到百亿级别，这条SQL还能跑吗？怎么优化？

   • 回答要点1 (架构层面)：在线查询（Ad-hoc Query）绝对不可行。必须转向离线计算 + 在线服务的架构。使用大数据处理框架（如 Spark、Flink、Hive）在数据仓库中进行批量计算。每天凌晨定时任务启动，计算所有商品的最新滚动GMV，并将结果写入一个轻量的KV存储（如 Redis）或分析型数据库（如 ClickHouse, Doris）中。线上应用直接从这些服务中读取预计算好的结果。
   • 回答要点2 (算法层面)：在离线计算中，可以进一步优化。由于是滑动窗口，今天的计算结果和昨天的结果有大量重叠。可以采用增量计算：今日30天GMV = 昨日30天GMV - 31天前的GMV + 今日GMV。这种方式避免了对整个30天窗口的重复求和，计算复杂度从 $O(W)$（W为窗口大小）降为 $O(1)$，极大地提升了大规模数据处理的效率。这在流处理（Stream Processing）场景中是标准做法。

2. 追问：如果索引建错了，比如建成了 (order_date, product_id)，会有什么影响？

   • 回答要点：索引的顺序非常重要，这被称为最左前缀原则。
     • 对于 PARTITION BY product_id ORDER BY order_date，理想的索引是 (product_id, order_date)。数据库可以先按 product_id 定位到分区，然后在分区内部，数据已经按 order_date 有序，无需再次排序。
     • 如果索引是 (order_date, product_id)，数据库可以利用它来满足 ORDER BY order_date，但无法高效地按 product_id 分区。它需要读取所有按日期排序的数据，然后在内存中进行 "Hash Partition" 或再次排序来形成分区。这会导致额外的CPU和内存开销，性能远不如前者。在 EXPLAIN 中可能会看到 Using temporary; Using filesort。

3. 追问：如果把数据库换成 ClickHouse 这种OLAP数据库，实现上有什么不同？

   • 回答要点1 (SQL语法)：ClickHouse 也支持标准的窗口函数语法，所以解法1的SQL基本可以通用。但ClickHouse有更丰富的函数集，可能会有更高效的特定函数。
   • 回答要点2 (性能与原理)：根本区别在于底层引擎。ClickHouse是列式存储数据库，对于聚合查询性能极高。它会只读取 product_id, order_date, gmv 这三列，而不是像InnoDB那样读取整行，I/O效率大幅提升。其向量化执行引擎也能并行处理数据块，使得聚合和排序操作非常快。即使没有索引，其暴力扫描的性能也远超行存数据库。
   • 回答要点3 (特定函数)：ClickHouse 提供 runningAccumulate、windowFunnel 等专为用户行为和时间序列分析设计的函数，可能提供更简洁或高效的实现方式。例如，sumXOver 系列函数可以实现类似的功能。在面试中，能提到列存、向量化执行这些OLAP数据库的核心特性，会非常加分。