0Pricing
SQL Academy · 课时

生成序列

通过递归创建行

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

什么是生成序列？

有时您需要一组表中不存在的数字、日期或其他连续值。SQL 允许您使用递归或内置函数即时创建这些值。

在本课中，您将学习如何使用 WITH RECURSIVE 生成序列——这是一种功能强大的工具，可以让查询引用自身的输出。

您的第一个递归 CTE

递归 CTE（公用表表达式）由 UNION ALL 连接的两部分组成：生成第一行的锚点部分，以及引用 CTE 自身来生成下一行的递归步骤。

当递归步骤不再返回任何行时，递归就会停止。

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

递归如何展开

将每个步骤分别想象出来会很有帮助。锚点部分为表提供初始行，然后每次递归都会添加一行，直到 WHERE 条件不再满足。

对于查询 WHERE n < 5，引擎会生成 1、2、3、4、5 这些行，然后停止，因为 5 < 5 为假。

WITH RECURSIVE steps(n, note) AS (
  SELECT 1, 'base case'
  UNION ALL
  SELECT n + 1, 'recursive step'
  FROM steps
  WHERE n < 4
)
SELECT n, note FROM steps;

生成偶数

只需在递归部分中每次增加超过 1 的值，就可以更改步长。这样会生成从 2 到 10 的所有偶数。

模式始终是：下一个值 = 当前值 + 步长。

WITH RECURSIVE evens(n) AS (
  SELECT 2
  UNION ALL
  SELECT n + 2 FROM evens WHERE n < 10
)
SELECT n FROM evens;

倒计数

递归并不局限于递增计数。将加法改为减法即可得到递减序列。请确保停止条件使用 > 而不是 <，以避免无限循环。

WITH RECURSIVE countdown(n) AS (
  SELECT 10
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown;

生成日期范围

递归 CTE 最实用的用途之一是构建日期序列。您可以从特定日期开始，每次增加一天，直到到达结束日期。

这对于填补报告中的缺失日期尤其有用——即使某天没有数据，每个日期也都会显示出来。

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

使用日期序列填补报告中的缺失日期

假设 sales 表只在有销售的日期包含行。将生成的日期序列与销售表进行 LEFT JOIN，即可获得范围内的每一天；没有销售的日期则显示为 NULL。

WITH RECURSIVE cal(d) AS (
  SELECT DATE '2024-03-01'
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM cal WHERE d < DATE '2024-03-05'
),
sales(sale_date, amount) AS (
  VALUES
    (DATE '2024-03-01', 100),
    (DATE '2024-03-03', 250),
    (DATE '2024-03-05', 180)
)
SELECT cal.d, COALESCE(sales.amount, 0) AS amount
FROM cal
LEFT JOIN sales ON cal.d = sales.sale_date
ORDER BY cal.d;

生成乘法表

递归 CTE 可以携带多个列，从而构建更丰富的输出。这里我们同时跟踪行索引和计算值。

WITH RECURSIVE mult(n, result) AS (
  SELECT 1, 1 * 7
  UNION ALL
  SELECT n + 1, (n + 1) * 7
  FROM mult
  WHERE n < 10
)
SELECT n, result AS seven_times_n FROM mult;

斐波那契数列

斐波那契数列是一种经典序列，其中每个数字都是前两个数字之和：0、1、1、2、3、5、8 ……

递归 CTE 同时跟踪当前值 a 和下一个值 b，并在每一步交换它们。

WITH RECURSIVE fib(a, b) AS (
  SELECT 0, 1
  UNION ALL
  SELECT b, a + b FROM fib WHERE a < 100
)
SELECT a AS fibonacci FROM fib;

在 PostgreSQL 中使用 generate_series

PostgreSQL 提供了一个名为 generate_series() 的内置快捷方式，无需编写递归 CTE 即可生成序列。它接受起始值、结束值和可选的步长。

这是在 PostgreSQL 中生成数字或日期范围的最简洁方式。

SELECT gs AS num
FROM generate_series(1, 10, 2) AS gs;

生成月度间隔

将 '1 month' 这一间隔传递给 generate_series()，即可构建月度日历。这非常适合创建月度报告标题，或检查哪些月份没有数据。

SELECT gs::DATE AS month_start
FROM generate_series(
  '2024-01-01'::DATE,
  '2024-06-01'::DATE,
  INTERVAL '1 month'
) AS gs;

知识检查

测试您对使用递归 CTE 生成序列的理解。

回顾：生成序列和连续值

在本课中，您学习了如何在没有源表的情况下生成数字和日期序列：

  • WITH RECURSIVE — 由锚点情况和递归步骤组成，并通过 UNION ALL 连接；当递归步骤不再返回行时停止。
  • 灵活的步长 — 加上或减去任意值，以实现递增、递减或跳过某些值。
  • 日期范围 — 加上间隔，生成每日、每月或自定义的日历序列。
  • 多列 CTE — 在迭代过程中携带额外状态，从而生成斐波那契数列等更丰富的输出。
  • generate_series() — PostgreSQL 内置功能，用于以最简洁的方式生成数字和日期序列。

生成的序列对于填补报告中的缺失数据、构建测试数据，以及任何需要完整值范围而不受数据实际内容影响的场景都至关重要。

常见问题解答

「生成序列」课时是免费的吗？

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

「生成序列」这节课中我会学到什么？

通过递归创建行 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

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

「生成序列」课时需要多长时间？

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

我能在这节 SQL Academy 课中编写并运行代码吗？

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

此课程中的所有课时

  1. 递归 CTE 的工作原理
  2. 遍历分类树
  3. 生成序列
  4. 避免无限循环
← 返回 SQL Academy