SQL 实战编程题（GitHub 高频题） · Top N / 排名 / 分位数

求每天的活跃用户数 7 日移动平均。

Calculate the 7-day moving average of daily active users.

答案语言

考察要点

这道题面试官想看你是否懂 窗口函数（Window Functions） 的高级用法，特别是 滑动窗口（Sliding Window） 的定义和应用。同时，考察你对复杂 SQL 查询的拆解能力（如使用 CTE）以及对时间序列数据处理的理解。能否给出兼容老版本数据库的 自连接（Self-Join） 解法，是区分优秀候选人和普通候选人的关键点。

核心概念

本题的核心是计算“移动平均”，这需要先理解几个关联概念。

假设我们有一张用户登录表 user_logins：

sql

 1CREATE TABLE user_logins (
 2    user_id INT,
 3    login_date DATE
 4);
 5-- 插入一些示例数据
 6INSERT INTO user_logins VALUES
 7(1, '2023-10-01'), (2, '2023-10-01'), (1, '2023-10-01'),
 8(1, '2023-10-02'), (3, '2023-10-02'),
 9(2, '2023-10-03'),
10-- 10-04 无人登录
11(1, '2023-10-05'), (2, '2023-10-05'), (3, '2023-10-05'), (4, '2023-10-05'),
12(1, '2023-10-06'), (5, '2023-10-06'),
13(1, '2023-10-07'), (2, '2023-10-07'), (6, '2023-10-07'),
14(1, '2023-10-08'),
15(2, '2023-10-09'), (3, '2023-10-09'),
16(1, '2023-10-10'), (4, '2023-10-10'), (5, '2023-10-10');

日活跃用户数 (DAU - Daily Active Users) DAU 指每天的独立登录用户数。计算方式为对 user_id 去重后按天计数。
sql
```
 1SELECT
 2    login_date,
 3    COUNT(DISTINCT user_id) AS dau
 4FROM user_logins
 5GROUP BY login_date;
```
login_date dau
2023-10-01 2
2023-10-02 2
2023-10-03 1
2023-10-05 4
... ...
公共表表达式 (CTE - Common Table Expression) 使用 WITH 关键字定义的临时结果集，可以使复杂的多步查询逻辑更清晰、可读性更高。在本题中，我们可以先用一个 CTE 计算出每日的 DAU，然后在后续查询中直接使用这个结果。
窗口函数 (Window Function) 窗口函数对一组与当前行相关的表行（这个组被称为“窗口”或“Frame”）执行计算。与 GROUP BY 不同，它在计算后不合并行，而是将结果附加到每一行上。
- 语法: FUNCTION() OVER ( [PARTITION BY ...] ORDER BY ... [FRAME_CLAUSE] )
- ORDER BY login_date: 定义了行的顺序，这是计算滑动窗口的基础。
- FRAME_CLAUSE: 定义了窗口的具体范围。对于“过去7天”，我们使用 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。
图示：ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 对于 2023-10-07 这一天，窗口包含从 2023-10-01 到 2023-10-07 的所有行（注意：如果某天没有数据，如 10-04，它就不会出现在 DAU 结果集中，ROWS 会自动跳过不存在的行，只计算物理存在的、排好序的前6行）。
text
```
 1-- DAU 数据 (已按日期排序)
 2...
 3[ '2023-10-01', 2 ]  <-- 6 PRECEDING
 4[ '2023-10-02', 2 ]  <-- 5 PRECEDING
 5[ '2023-10-03', 1 ]  <-- 4 PRECEDING
 6[ '2023-10-05', 4 ]  <-- 3 PRECEDING  (10-04 不存在)
 7[ '2023-10-06', 2 ]  <-- 2 PRECEDING
 8[ '2023-10-07', 3 ]  <-- 1 PRECEDING
 9[ '2023-10-08', 1 ]  <-- CURRENT ROW  <== 计算这一行的7日移动平均，窗口是 [10-02...10-08]
10...
```

login_date	dau
2023-10-01	2
2023-10-02	2
2023-10-03	1
2023-10-05	4
...	...

多种解法

解法 1：窗口函数（推荐，性能和可读性最佳）

思路：先用 CTE 计算出每日 DAU，然后使用 AVG() 窗口函数，并定义一个7天的滑动窗口框架。
版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 及以下版本不支持。

sql

 1WITH daily_active_users AS (
 2    -- 步骤1: 计算每日独立活跃用户数 (DAU)
 3    SELECT
 4        login_date,
 5        COUNT(DISTINCT user_id) AS dau
 6    FROM
 7        user_logins
 8    GROUP BY
 9        login_date
10)
11-- 步骤2: 在DAU结果集上使用窗口函数计算7日移动平均
12SELECT
13    login_date,
14    dau,
15    AVG(dau) OVER (
16        ORDER BY login_date
17        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
18    ) AS moving_avg_7d
19FROM
20    daily_active_users
21ORDER BY
22    login_date;

复杂度：
- 时间复杂度： $O(N \log N)$ 。其中 $N$ 是 user_logins 表的行数。主要开销在于 GROUP BY 和 ORDER BY 的排序。如果数据已按 login_date 物理有序，窗口函数计算本身是 $O(D)$ ，其中 $D$ 是独立天数。
- 空间复杂度： $O(D)$ ，需要存储每日 DAU 的中间结果。
索引依赖：在 user_logins 表上建立 (login_date, user_id) 的复合索引，可以极大地加速 GROUP BY 操作，甚至可能实现覆盖索引，避免回表。

解法 2：自连接（兼容性好，性能稍逊）

思路：同样先用 CTE 计算 DAU。然后将 DAU 表自连接，对于主表的每一天 d1，连接上从 d1 当天及往前6天内的所有记录 d2。最后按 d1 的日期分组，计算 d2 的 DAU 平均值。
版本：✅ 所有主流 SQL 数据库，包括 MySQL 5.7。

sql

 1WITH daily_active_users AS (
 2    -- 步骤1: 计算每日独立活跃用户数 (DAU)
 3    SELECT
 4        login_date,
 5        COUNT(DISTINCT user_id) AS dau
 6    FROM
 7        user_logins
 8    GROUP BY
 9        login_date
10)
11-- 步骤2: 自连接计算移动平均
12SELECT
13    d1.login_date,
14    d1.dau,
15    AVG(d2.dau) AS moving_avg_7d
16FROM
17    daily_active_users d1
18JOIN
19    daily_active_users d2
20ON
21    d2.login_date BETWEEN DATE_SUB(d1.login_date, INTERVAL 6 DAY) AND d1.login_date
22GROUP BY
23    d1.login_date, d1.dau
24ORDER BY
25    d1.login_date;

复杂度：
- 时间复杂度：近似 $O(D^2)$ ，其中 $D$ 是独立天数。对于 daily_active_users 表中的每一行，都需要与另外约7行进行连接和计算。虽然连接条件有索引时会优化，但总体计算量大于窗口函数。
- 空间复杂度： $O(D)$ ，存储 DAU 中间结果。
索引依赖：该方法性能严重依赖于对 daily_active_users 这个中间结果集的 login_date 列的快速访问。数据库通常会为 CTE 物化后的结果创建隐式索引或使用哈希表来加速连接。

常见低分回答

忘记去重 COUNT(user_id)：计算的是每日总登录次数，而非独立用户数，完全曲解了“活跃用户”的定义。
窗口框架定义错误：
- ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING：这计算的是前7天的平均值，不包含当天，与题目要求不符。
- PARTITION BY YEAR(login_date)：错误地按年分区，导致移动平均只在当年内计算，跨年时会断开重算。本题不需要 PARTITION BY。
使用 RANGE 而非 ROWS 且未处理日期不连续：RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW 在语义上更精确，但如果某天没有数据（如 2023-10-04），ROWS 只会看物理上存在的前6行，而 RANGE 会在逻辑时间窗口内查找。在 DAU 数据不连续时，两者结果可能不同。不理解其差异就混用是低分表现。通常 ROWS 更符合“最近N次记录”的直觉。

使用效率极低的关联子查询：对每一行都执行一次子查询来计算平均值，这是典型的

O(D^2)

写法，性能极差。

sql

 1-- 反例：性能极差
 2SELECT
 3  login_date,
 4  dau,
 5  (SELECT AVG(dau)
 6   FROM daily_active_users d2
 7   WHERE d2.login_date BETWEEN DATE_SUB(d1.login_date, INTERVAL 6 DAY) AND d1.login_date
 8  ) AS moving_avg_7d
 9FROM daily_active_users d1;

工程实践与优化

数据预计算：在生产环境中，user_logins 表通常是亿级别的。直接对原始表进行 GROUP BY 和窗口计算会非常慢，且给线上数据库带来巨大压力。最佳实践是数据仓库分层思想：
1. 创建一个每日任务（如使用 Airflow、Azkaban 调度 Spark/SQL 脚本）。
2. 每天凌晨计算前一天的 DAU，并存入一个专门的日指标汇总表 dm_daily_metrics(report_date, dau, wau, ...)。
3. 前端报表或分析师查询时，直接从此汇总表查询。该表数据量很小（一年仅365行），计算移动平均会非常快。
处理数据空洞（Missing Dates）：如果某天完全没有用户登录，DAU 表里就会缺少这一天，导致移动平均计算不准确（例如，窗口大小不足7天）。
- 解决方案：创建一个日历表 dim_calendar(date)，包含所有连续的日期。在计算时，用日历表 LEFT JOIN DAU 表，对不存在的 DAU 使用 COALESCE(dau, 0) 填充为0。这保证了时间窗口的连续性和准确性。
  sql
```
 1-- 伪代码
 2SELECT
 3    c.date,
 4    COALESCE(d.dau, 0) AS dau,
 5    AVG(COALESCE(d.dau, 0)) OVER (ORDER BY c.date ROWS ...)
 6FROM dim_calendar c
 7LEFT JOIN daily_active_users d ON c.date = d.login_date
 8WHERE c.date BETWEEN 'start_date' AND 'end_date';
```
执行计划分析：
- 对于解法1，EXPLAIN 结果中应关注 WindowAgg 节点。检查其前置的 Sort 操作是否是性能瓶颈。
- 对于解法2，关注 JOIN 的类型。理想情况是 Nested Loop 且内表 d2 的 login_date 上有索引。如果出现 Using temporary; Using filesort，说明 GROUP BY 或 JOIN 的性能很差，需要检查索引。

常见追问与延伸题目

追问：如果数据量增长100倍，达到百亿级别，这条SQL性能怎样？怎么优化？
- 回答要点1 (批处理)：在线直接查询原始日志表是不可接受的。必须采用预计算/预聚合的方案。我会搭建离线数仓，通过每日的 ETL/ELT 任务（例如 Spark 或 Flink 批处理），将百亿级的原始日志聚合到一张非常小的日活表（daily_active_users）中。后续所有关于日指标的查询都基于这张聚合表，响应时间可以控制在毫秒级。
- 回答要点2 (流式计算)：如果需要近实时的移动平均（例如，分钟级更新），我会引入流式计算引擎（如 Flink、Spark Streaming）。通过设置滚动或滑动时间窗口，在数据流上直接增量计算和更新DAU及移动平均值，并将结果写入 Redis 或 ClickHouse 等高性能存储中，供实时看板查询。
追问：如果发现这条SQL在MySQL 8.0上很慢，你如何定位和修复？
- 回答要点1 (定位)：我会使用 EXPLAIN ANALYZE (MySQL 8.0+) 命令。它不仅显示执行计划，还会显示每个节点的实际执行时间、循环次数和行数。我会重点关注：
  - COUNT(DISTINCT ...) 步骤的 actual time，看是否是 DAU 计算的瓶颈。
  - WindowAgg 节点的 actual time，看窗口函数计算是否耗时。
  - 检查是否存在 Using temporary; Using filesort，这通常是缺少合适索引的信号。
- 回答要点2 (修复)：
  - 索引优化：确保 user_logins 表上有 (login_date, user_id) 的复合索引。这对于 GROUP BY login_date 和 COUNT(DISTINCT user_id) 是最高效的。
  - 资源调整：如果索引已最优，可能是内存不足导致排序和聚合操作溢出到磁盘。我会检查并适当调大 sort_buffer_size, tmp_table_size 和 windowing_use_high_precision 等系统变量。
  - SQL改写：如果问题出在窗口函数本身，可以尝试将逻辑拆分，或在极端情况下，用应用层代码或其他数据库组件辅助计算。但通常优化索引和内存是首选。
追问：如果换成 ClickHouse 这种 OLAP 数据库，答案有什么不同？
- 回答要点1 (函数差异)：ClickHouse 提供了极其丰富的数组和窗口函数。计算移动平均有更直接的函数，如 avgMoving()。SQL 会更简洁：SELECT login_date, dau, avgMoving(dau, 7) OVER (ORDER BY login_date) FROM ...。此外，ClickHouse 的 uniqCombined 函数在计算 COUNT(DISTINCT) 时性能远超传统数据库。
- 回答要点2 (性能和架构)：ClickHouse 是列式存储，对于这种聚合查询性能极高。它会按 login_date（如果设为排序键）进行物理排序，ORDER BY 和窗口计算几乎没有成本。因此，即使在百亿级数据上，只要表结构设计合理（如使用 MergeTree 引擎并以日期为排序键），直接查询原始表也可能在秒级完成，不一定需要预计算。但最佳实践仍然是分层建模。