SQL Interview Prep · 课时

生成数字和日期序列

Q: 「生成数字和日期序列」课时是免费的吗？

是的 — 「生成数字和日期序列」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。

Q: 「生成数字和日期序列」这节课中我会学到什么？

使用递归生成序列，用于填补间隔和创建日历 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

Q: 学习 SQL Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

Q: 「生成数字和日期序列」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

Q: 我能在这节 SQL Interview Prep 课中编写并运行代码吗？

能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

使用递归生成序列，用于填补间隔和创建日历

生成数字和日期序列 是 CoddyKit 上的免费 SQL Interview Prep 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。

无层级结构的递归

递归 CTE 不仅适用于树结构。另一个主要用途是生成序列：一串数字，或某个范围内的每个日期。面试官会在问题需要填补缺口时考察这一点——也就是生成任何表中都不存在的行。

经典题目是：“显示这个月每天的销售额，包括没有销售额的日期。”除非先生成所有日期，否则您无法显示缺失的日期。

简单的数字序列

锚点成员生成第一个数字；递归成员在每次迭代中加一；递归成员中的 WHERE 负责停止递归。这样会生成从 1 到 10 的数字。

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

终止谓词

与组织架构不同，数字序列没有可以自然停止的叶节点——您可以不断递增下去。因此，必须在递归成员中添加明确的停止条件：WHERE n < 10。

当 n 达到 10 时，下一次迭代中的 WHERE 会过滤掉唯一的候选行，递归成员不返回任何内容，递归便会停止。在面试中，忘记这个保护条件是递归失控的首要原因。

参数化范围

通过由某个值或变量驱动上界，让序列更加灵活。这里会生成从 1 到 N 的序列，其中 N 由外部提供。同样的结构还可以生成从 0 开始或按步长递增的序列——只需改变锚点和增量即可。

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 2 FROM nums WHERE n + 2 <= 99
)
SELECT n FROM nums;  -- odd numbers 1,3,5,...,99

生成日期序列

将整数运算换成日期运算，就可以得到一个日历。锚点成员是开始日期；递归成员每天加一天，直到超过结束日期。

添加一天的语法因数据库方言而异——这里使用的是 PostgreSQL 风格的时间间隔写法。

WITH RECURSIVE cal AS (
    SELECT DATE '2024-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day'
    FROM cal
    WHERE d < DATE '2024-01-31'
)
SELECT d FROM cal;

使用 LEFT JOIN 填补缺口

现在将日历与真实数据结合起来。先生成每一天，再对销售表使用 LEFT JOIN，这样缺失的日期就会显示为 NULL，然后用 COALESCE 将其转换为 0。

这种两步模式——先生成骨架，再使用 LEFT JOIN 连接事实数据——是所有缺口填补方案的核心。

WITH RECURSIVE cal AS (
    SELECT DATE '2024-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day' FROM cal
    WHERE d < DATE '2024-01-07'
)
SELECT cal.d, COALESCE(SUM(s.amount), 0) AS total
FROM cal
LEFT JOIN sales s ON s.sale_date = cal.d
GROUP BY cal.d
ORDER BY cal.d;

月度和周度骨架

改变增量即可构建更粗粒度的日历。使用 INTERVAL '1 month' 生成月度骨架，或使用 INTERVAL '7 day' 生成周度骨架。当面试官要求生成包含空月份的月度报告时，这种方法很有用。

WITH RECURSIVE months AS (
    SELECT DATE '2024-01-01' AS m
    UNION ALL
    SELECT m + INTERVAL '1 month' FROM months
    WHERE m < DATE '2024-12-01'
)
SELECT m FROM months;

日期运算的方言差异

日期运算是这些查询中可移植性最差的部分。请了解以下变体：

PostgreSQL：d + INTERVAL '1 day'。
MySQL：DATE_ADD(d, INTERVAL 1 DAY)。
SQL Server：DATEADD(DAY, 1, d)。
SQLite：date(d, '+1 day')。

如果您能说明递归结构完全相同，只有日期函数会变化，这会是一个体现您了解数据库方言差异的有力回答。

递归与序列生成函数的比较

PostgreSQL 提供了内置的 generate_series()，无需递归即可生成数字或日期，而且速度更快、可读性更好：

SELECT generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day');

如果面试官所使用的数据库支持它，请优先使用它。但许多数据库引擎（MySQL、较早版本的 SQL Server）并不支持它——这正是递归 CTE 作为可移植备用方案的用武之地。

留意递归上限

生成较大的序列可能会触及数据库引擎的递归上限。SQL Server 默认将 MAXRECURSION 100 设为上限，因此生成 365 天的日历会失败，除非在查询末尾追加 OPTION (MAXRECURSION 0) 来取消限制。

PostgreSQL 没有固定上限，但带有错误谓词的失控序列可能会一直运行，直到耗尽内存。在扩大规模之前，务必确认终止谓词正确。

-- SQL Server: lift the 100-row recursion cap
-- ...recursive CTE here...
SELECT * FROM cal
OPTION (MAXRECURSION 0);

对序列执行 CROSS JOIN

生成的序列通常只是一个组成部分。拥有数字 CTE 后，可以对它使用 CROSS JOIN 来扩展或拆分行——例如，根据数量重复每条订单行，或为每位客户展开一个日期范围。

认识到递归生成的是可复用的构件，而不只是最终答案，是优秀的面试回答与机械式回答之间的区别。

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT o.order_id, nums.n AS unit
FROM orders o
JOIN nums ON nums.n <= o.quantity;

快速检查

为什么停止谓词对于数字序列和日期序列至关重要？

回顾

递归可以生成任何表中都不存在的行：

在锚点成员中生成第一个值，在递归成员中递增。
始终添加明确的终止谓词——序列没有自然终点。
构建日期或数字骨架，然后使用 LEFT JOIN 连接事实数据，并使用 COALESCE 填补缺口。
在可用时优先使用 generate_series；在 SQL Server 中注意 MAXRECURSION。

接下来：防止递归失控的安全技术。

常见问题解答