如何用BigQuery SQL生成任意任务数的笛卡尔积组合？

我来帮你搞定这个支持任意任务数的BigQuery解决方案！先理清楚咱们的场景和现有情况：

现有表结构与数据 #

Table1（任务列表） #

Table2（任务选项及属性） #

需求回顾 #

我们需要生成所有任务选项的笛卡尔积组合，输出结果要包含唯一的运行编号（Run），并且关键是要支持任意数量的任务——之前用MS Access的透视+多表笛卡尔积的方法最多只能支持4个任务，没法扩展。

现有MS Access实现（仅支持4个任务） #

你之前用的Access方法是先透视任务选项，再手动关联4个透视表来生成笛卡尔积，代码如下：

查询#1（CTQ：透视任务选项） #

TRANSFORM First(VariableT.[Optionid]) AS [FirstOfOption id]
SELECT Table2.[Optionid]
FROM VariableT
GROUP BY Table2.[Optionid]
PIVOT Table2.[Taskid];

查询#2（生成4个任务的笛卡尔积） #

SELECT CTQ.[1], CTQ_1.[2], CTQ_2.[3], CTQ_3.[4]
FROM CTQ, CTQ AS CTQ_1, CTQ AS CTQ_2, CTQ AS CTQ_3
GROUP BY CTQ.[1], CTQ_1.[2], CTQ_2.[3], CTQ_3.[4]
HAVING (((CTQ.[1]) Is Not Null) AND ((CTQ_1.[2]) Is Not Null) AND ((CTQ_2.[3]) Is Not Null) AND ((CTQ_3.[4]) Is Not Null));

这个方法的局限性很明显：任务数变了就得修改SQL，没法自动扩展。

BigQuery可扩展解决方案（支持任意任务数） #

我给你写了一个用递归CTE实现的方案，不管任务是4个还是N个，都能自动生成所有组合，不需要修改代码。核心思路是逐步构建组合：从第一个任务的选项开始，每次把现有组合和下一个任务的所有选项做笛卡尔积，直到所有任务都处理完。

完整SQL代码 #

WITH sorted_tasks AS (
    -- 给任务按ID排序并分配序号，保证递归处理顺序稳定
    SELECT Taskid, Name,
           ROW_NUMBER() OVER (ORDER BY Taskid) AS task_order
    FROM Table1
),
recursive_combinations AS (
    -- 递归起始：第一个任务的所有选项作为初始组合
    SELECT 
        st.Taskid AS current_task_id,
        st.task_order,
        -- 初始组合：仅第一个任务的单个选项条目
        ARRAY[STRUCT(
            t2.Taskid AS Taskid,
            t2.Optionid AS Optionid,
            t2.Attribute1 AS Attribute1,
            t2.Attribute2 AS Attribute2,
            t2.Attribute3 AS Attribute3
        )] AS full_combination
    FROM sorted_tasks st
    JOIN Table2 t2 ON st.Taskid = t2.Taskid
    WHERE st.task_order = 1

    UNION ALL

    -- 递归步骤：将现有组合与下一个任务的所有选项做笛卡尔积
    SELECT
        next_task.Taskid AS current_task_id,
        next_task.task_order,
        -- 把现有组合和新任务的选项拼接起来
        rc.full_combination || STRUCT(
            t2.Taskid AS Taskid,
            t2.Optionid AS Optionid,
            t2.Attribute1 AS Attribute1,
            t2.Attribute2 AS Attribute2,
            t2.Attribute3 AS Attribute3
        ) AS full_combination
    FROM recursive_combinations rc
    JOIN sorted_tasks next_task ON rc.task_order + 1 = next_task.task_order
    JOIN Table2 t2 ON next_task.Taskid = t2.Taskid
),
final_combinations AS (
    -- 只保留包含所有任务的完整组合（过滤掉中间步骤的部分组合）
    SELECT full_combination
    FROM recursive_combinations
    WHERE task_order = (SELECT MAX(task_order) FROM sorted_tasks)
)
-- 生成运行编号，并展开组合成每行对应一个任务选项的格式
SELECT
    ROW_NUMBER() OVER (ORDER BY full_combination) AS Run,
    task_entry.Taskid,
    task_entry.Optionid,
    task_entry.Attribute1,
    task_entry.Attribute2,
    task_entry.Attribute3
FROM final_combinations,
UNNEST(full_combination) AS task_entry
ORDER BY Run, Taskid;

方案细节说明 #

1. sorted_tasks：给任务排序并分配序号，确保递归时按任务ID顺序处理，新增任务时会自动加入排序，不需要修改代码；
2. recursive_combinations：递归CTE是核心，从第一个任务的选项开始，每次递归都把已有的组合和下一个任务的所有选项做笛卡尔积，逐步构建包含更多任务的组合；
3. final_combinations：过滤出包含所有任务的完整组合，排除递归过程中生成的只包含部分任务的组合；
4. 最后一步：用ROW_NUMBER()生成唯一的Run编号，再用UNNEST把数组形式的组合展开成每行对应一个任务选项的格式，和你需要的输出结构一致。

这个方案完全支持任意数量的任务，后续新增任务时，只需要往Table1里加数据，SQL不需要任何改动就能自动生成新的笛卡尔积组合。

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