进阶查询（窗口函数 / CTE / 递归 / 子查询） · CTE 与递归查询

如何用递归 CTE 生成日期序列、补齐某段时间的空缺？

How to use a recursive CTE to generate a date series and fill in gaps for a given period?

答案语言

考察要点

这道题面试官想看你是否掌握了 SQL 中处理序列和层次化数据的进阶能力。核心考察点是**递归公用表表达式（Recursive Common Table Expression, CTE）**的语法和应用。具体来说，面试官希望确认你理解：

递归 CTE 的结构：锚点成员（Anchor Member）和递归成员（Recursive Member）如何工作。
递归的终止条件：如何正确设置 WHERE 子句来防止无限循环。
实际应用场景：如何利用生成的数据序列（如日期）与业务表进行 LEFT JOIN，以实现数据补齐（Data Densification），这是报表和数据分析中非常常见的需求。

核心概念

公用表表达式（CTE） 是一个在 SELECT, INSERT, UPDATE, DELETE 语句上下文中定义的临时命名结果集。它以 WITH 关键字开头，可以提高复杂查询的可读性和模块化。

递归 CTE 是一种特殊的 CTE，它会引用自身。它非常适合用于处理具有层次结构或序列关系的数据。

一个递归 CTE 必须包含以下三个部分：

锚点成员（Anchor Member）：这是递归的起点，是一个不引用 CTE 自身的 SELECT 语句。它只执行一次。
UNION ALL 操作符：用于连接锚点成员和递归成员。强烈推荐使用 UNION ALL 而非 UNION，因为前者避免了昂贵的去重操作，而序列生成通常不需要去重。
递归成员（Recursive Member）：这是一个引用 CTE 自身的 SELECT 语句。它会反复执行，每次执行都以上一次的结果集作为输入，直到结果集为空。
终止条件：通常在递归成员的 WHERE 子句中定义，当条件不满足时，递归成员返回空集，递归停止。

结构与示例

基本结构

sql

 1WITH RECURSIVE cte_name (column1, column2, ...) AS (
 2    -- 1. 锚点成员：递归的初始行，只执行一次
 3    SELECT initial_value1, initial_value2, ...
 4    
 5    UNION ALL
 6    
 7    -- 2. 递归成员：引用 cte_name 自身，反复执行
 8    SELECT column1 + 1, ...
 9    FROM cte_name
10    WHERE column1 < 10 -- 3. 终止条件
11)
12-- 使用 CTE
13SELECT * FROM cte_name;

示例：生成 2023-01-01 到 2023-01-05 的日期序列

sql

 1-- DDL & Data
 2CREATE TABLE sales (
 3    sale_date DATE,
 4    amount DECIMAL(10, 2)
 5);
 6INSERT INTO sales VALUES
 7('2023-01-01', 100.00),
 8('2023-01-03', 150.00),
 9('2023-01-04', 50.00);

目标：补齐 1 月 1 日到 5 日的销售数据，没有销售的日期记为 0。

| 原始数据 (sales) | | :--------------- | :----------- | | sale_date | amount | | 2023-01-01 | 100.00 | | 2023-01-03 | 150.00 | | 2023-01-04 | 50.00 |

| 期望输出 | | :--------------- | :----------- | | report_date | total_sales | | 2023-01-01 | 100.00 | | 2023-01-02 | 0.00 | | 2023-01-03 | 150.00 | | 2023-01-04 | 50.00 | | 2023-01-05 | 0.00 |

版本差异

支持：MySQL 8.0+, PostgreSQL, SQL Server, Oracle, SQLite 3.8.3+。
不支持：MySQL 5.7 及更早版本。这是面试中非常重要的区分点。
语法差异：
- 标准 SQL 和多数数据库（如 PostgreSQL）使用 WITH RECURSIVE。
- SQL Server 和 Oracle 中，RECURSIVE 关键字是可选的，直接使用 WITH 即可。MySQL 8.0 为了兼容性也支持不写 RECURSIVE，但推荐写上以明确意图。

多种解法

假设我们有 sales 表，需要补齐 '2023-01-01' 到 '2023-01-31' 的每日销售总额。

解法 1：使用递归 `CTE`（标准现代解法）

思路：先用递归 CTE 生成一个完整的日期序列，然后将此序列作为驱动表（左表），LEFT JOIN 聚合后的销售数据表。
版本：✅ MySQL 8.0+ / PostgreSQL / SQL Server

SQL 实现：

sql

 1WITH RECURSIVE date_series (dt) AS (
 2  -- 锚点成员: 定义序列的起始日期
 3  SELECT '2023-01-01'
 4  UNION ALL
 5  -- 递归成员: 从上一天的日期生成下一天的日期
 6  SELECT dt + INTERVAL 1 DAY
 7  FROM date_series
 8  -- 终止条件: 当日期达到结束日期时停止
 9  WHERE dt < '2023-01-31'
10)
11SELECT
12  d.dt AS report_date,
13  -- 使用 COALESCE 或 IFNULL 将 NULL 替换为 0
14  COALESCE(s.daily_amount, 0) AS total_sales
15FROM
16  date_series d
17LEFT JOIN (
18  -- 预先聚合 sales 表以提高 JOIN 效率
19  SELECT
20    sale_date,
21    SUM(amount) AS daily_amount
22  FROM sales
23  WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
24  GROUP BY sale_date
25) s ON d.dt = s.sale_date
26ORDER BY
27  d.dt;

复杂度：
- 时间复杂度： $O(M + N \log N)$ 。其中 $M$ 是日期范围的天数，用于生成序列； $N$ 是 sales 表中符合日期范围的记录数，GROUP BY 操作复杂度通常为 $O(N \log N)$ （排序聚合）或 $O(N)$ （哈希聚合）。
- 空间复杂度： $O(M)$ ，用于存储生成的日期序列。
索引依赖：在 sales(sale_date) 列上建立索引至关重要，可以极大地加速子查询中的 WHERE 和 GROUP BY 操作。

解法 2：使用预先存在的日历表（工程最佳实践）

思路：在数据库中维护一个永久的日历表（dim_date），包含所有可能需要的日期及相关维度（如星期、月份、是否节假日等）。查询时直接从该表 LEFT JOIN 业务数据。
版本：✅ 所有 SQL 版本

SQL 实现：

sql

 1-- 假设已存在一个名为 calendar_table 的日历表，其中有 date_val 列
 2-- CREATE TABLE calendar_table (date_val DATE PRIMARY KEY, ...);
 3
 4SELECT
 5  c.date_val AS report_date,
 6  COALESCE(s.daily_amount, 0) AS total_sales
 7FROM
 8  calendar_table c
 9LEFT JOIN (
10  SELECT
11    sale_date,
12    SUM(amount) AS daily_amount
13  FROM sales
14  WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
15  GROUP BY sale_date
16) s ON c.date_val = s.sale_date
17WHERE
18  c.date_val BETWEEN '2023-01-01' AND '2023-01-31'
19ORDER BY
20  c.date_val;

复杂度：
- 时间复杂度： $O(M + N \log N)$ 。与解法 1 类似，但通常更快，因为日历表是物理存在的，查询优化器可以更好地利用其索引。
- 空间复杂度： $O(1)$ （不考虑日历表本身的存储）。
索引依赖：calendar_table(date_val) 应有主键或唯一索引。sales(sale_date) 同样需要索引。
适用场景：这是生产环境中的首选方案。它性能最高、查询最简洁、可复用性最强，是数据仓库建模的标准实践。

解法 3：利用数字序列表（兼容老版本 MySQL 5.7）

思路：如果不能使用递归 CTE 且没有日历表，可以动态生成一个数字序列（0, 1, 2, ...），然后通过日期函数 DATE_ADD() 将这些数字加到起始日期上，从而构造出日期序列。
版本：✅ 所有 SQL 版本（语法细节可能略有不同）

SQL 实现 (MySQL 5.7 兼容):

sql

 1-- MySQL 5.7 不支持 WITH，需使用派生表
 2SELECT
 3  generated_date AS report_date,
 4  COALESCE(s.daily_amount, 0) AS total_sales
 5FROM (
 6  -- 生成 0-999 的数字序列，足以覆盖近3年
 7  SELECT '2023-01-01' + INTERVAL n.num DAY AS generated_date
 8  FROM (
 9      SELECT n1.i + n10.i * 10 + n100.i * 100 AS num
10      FROM
11          (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n1
12      CROSS JOIN
13          (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n10
14      CROSS JOIN
15          (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n100
16  ) n
17  WHERE '2023-01-01' + INTERVAL n.num DAY <= '2023-01-31'
18) date_series
19LEFT JOIN (
20  SELECT
21    sale_date,
22    SUM(amount) AS daily_amount
23  FROM sales
24  WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
25  GROUP BY sale_date
26) s ON date_series.generated_date = s.sale_date
27ORDER BY
28  report_date;

适用场景：当数据库版本老旧（如 MySQL 5.7）且没有权限创建永久表时的权宜之计。虽然能解决问题，但代码可读性差，且有数字序列范围的限制。

常见低分回答

忘记递归终止条件：在递归成员中不写 WHERE 子句，导致无限递归，最终因超出 cte_max_recursion_depth（MySQL 默认 1000）而报错。
LEFT JOIN 的主次颠倒：FROM sales s LEFT JOIN date_series d ...。这会导致结果中只包含 sales 表中存在的日期，完全无法达到“补齐”空缺日期的目的。驱动表必须是完整的日期序列。
在 WHERE 子句中过滤 LEFT JOIN 的右表：... LEFT JOIN sales s ON ... WHERE s.amount > 0。这会隐式地将 LEFT JOIN 转换为 INNER JOIN，因为 WHERE 条件会过滤掉右表中不匹配的行（这些行的 s.amount 为 NULL）。对右表的过滤条件应放在 ON 子句或子查询中。
使用 UNION 而非 UNION ALL：SELECT ... UNION SELECT ... FROM cte_name。在每一步递归中都进行去重检查，对于生成唯一序列的场景是完全不必要的性能开销。
不处理 NULL 值：最终 SELECT 中直接写 s.daily_amount，导致报表中出现 NULL 而不是 0，这通常不符合业务需求，显得考虑不周。应使用 COALESCE(s.daily_amount, 0) 或 IFNULL(s.daily_amount, 0)。

工程实践与优化

首选日历表：在任何需要频繁按日期分析的生产系统中，都应该建立一个日历维度表。这是数据仓库建模的基础，性能和易用性远超任何动态生成方案。
注意 cte_max_recursion_depth：在 MySQL 中，如果需要生成的序列长度超过 1000，需要手动调整会话变量：SET SESSION cte_max_recursion_depth = 5000;。但这通常是一个信号，表明递归 CTE 可能不是最佳工具，应该考虑使用日历表。
执行计划分析：
- 使用 EXPLAIN 查看执行计划。对于递归 CTE，你会看到一个派生表（derived）代表 CTE 的物化结果。
- 关键在于检查 LEFT JOIN 的效率。确保对 sales.sale_date 的访问类型（type）是 range 或 ref，而不是 ALL（全表扫描）。Extra 字段中若出现 Using index 则表示覆盖索引或索引条件下推等优化被有效利用。
- 使用 EXPLAIN ANALYZE (MySQL 8.0.18+) 可以看到每一步的实际执行时间、行数，能更精确地定位性能瓶颈。
预聚合：如示例代码所示，先对 sales 表按日期进行 GROUP BY 聚合，再进行 JOIN，而不是将完整的日期序列 JOIN 到巨大的原始 sales 表上。这可以显著减少 JOIN 操作需要处理的数据量。

常见追问与延伸题目

追问：如果我需要生成一个跨度 10 年的日期序列，递归 CTE 还合适吗？为什么？
- 回答要点：不合适。
  - 性能问题：每次查询都动态生成超过 3650 行数据，会带来不必要的 CPU 和内存开销，尤其是在高并发场景下。
  - 配置限制：会轻易超过 MySQL 默认的 cte_max_recursion_depth (1000) 限制。虽然可以调大该值，但这治标不治本，且表明工具选择不当。
  - 最佳实践：对于这种大规模、静态的序列，日历表是唯一正确的选择。一次创建，多次使用，查询时只是简单的表连接，性能和可维护性都最佳。
追问：除了补齐日期，递归 CTE 还有哪些经典应用场景？
- 回答要点：递归 CTE 主要用于处理层次结构和图结构数据。
  - 组织架构查询：给定一个员工表（employee_id, manager_id），查询某位经理下的所有直接和间接下属。
  - 物料清单（BOM）展开：在制造业中，查询一个成品由哪些半成品和原材料组成，以及所有层级的组件。
  - 无限级分类查询：在电商网站中，查询某个商品分类下的所有子分类。
  - 图路径搜索：例如，在航班信息表中查找两个城市之间的所有可能转机路线。
追问：如果换成 PostgreSQL，刚才的递归 CTE 写法有什么不同？OceanBase/TiDB 呢？
- 回答要点：
  - PostgreSQL：语法完全兼容。PostgreSQL 对递归 CTE 的支持非常成熟和强大，性能也很好。写法上没有区别。
  - OceanBase：OceanBase 兼容 MySQL 语法，因此也支持 WITH RECURSIVE。其分布式架构可能会影响执行计划，但 SQL 语法层面是一致的。
  - TiDB：TiDB 也高度兼容 MySQL 协议和语法。从 TiDB v5.3.0 开始，正式支持递归 CTE。因此，在较新版本的 TiDB 中，该 SQL 可以直接运行。在不支持的版本中，则需要采用与 MySQL 5.7 类似的兼容方案（如数字表或应用层处理）。对于分布式数据库，理解其数据分片键（SHARDING KEY）对 JOIN 性能的影响也很重要。