BigQuery：如何用递归方法将表中每行重复指定次数？

现有base表数据如下：

base
+------+
| rn   |
+------+
| 1    |
| 2    |
| 3    |
+------+

需要将每行重复3次，预期输出9行：

+------+-------------+
| rn   | iteration   |
+------+-------------+
| 1    |     1       |
| 2    |     1       |
| 3    |     1       |
| 1    |     2       |
| 2    |     2       |
| 3    |     2       |
| 1    |     3       |
| 2    |     3       |
| 3    |     3       |
+------+-------------+

原递归SQL执行后生成39行而非预期的9行，代码如下：

WITH recursive test AS (
    SELECT
      *,
      1 AS iteration
    FROM `base`

    UNION ALL

    SELECT
      a.*,
      b.iteration + 1 AS iteration
    FROM `base` a
    JOIN test b ON b.iteration < 3
)
SELECT *
FROM test
ORDER BY 2,1

问题原因 #

原SQL递归段的JOIN操作触发了笛卡尔积：每次递归时，base全表行都会和test中符合条件的所有行交叉连接，随着递归次数增加，数据量呈指数级膨胀，最终生成大量冗余数据。

修正方案 #

方案一：先生成迭代序列再关联原表 #

先递归生成1-3的迭代次数序列，再与原表做交叉连接，逻辑清晰且避免冗余：

WITH recursive test AS (
    -- 初始生成第1次迭代
    SELECT 1 AS iteration
    UNION ALL
    -- 递归生成后续迭代，直到达到3次
    SELECT iteration + 1
    FROM test
    WHERE iteration < 3
)
-- 交叉连接原表与迭代序列，得到每行对应所有迭代次数的结果
SELECT b.rn, t.iteration
FROM base b
CROSS JOIN test t
ORDER BY t.iteration, b.rn;

方案二：直接递归每行的迭代次数 #

针对原表每行单独递增迭代次数，避免不必要的交叉连接：

WITH recursive test AS (
    SELECT rn, 1 AS iteration
    FROM base
    UNION ALL
    SELECT rn, iteration + 1
    FROM test
    WHERE iteration < 3
)
SELECT *
FROM test
ORDER BY iteration, rn;

两种方案都能精准生成预期的9行结果，可根据实际场景选择。

内容的提问来源于stack exchange，提问作者ebuzz168