使用递归CTE生成套餐内子项的所有组合聚合结果

需求回顾 #

需要为每个package_id生成其下所有不同package_item_id对应的variant_id的所有可能组合，并输出以下字段：

• package_id：套餐组ID
• querystring：拼接套餐ID与组合内所有变体ID的参数字符串（格式示例：package_id=1&variant_ids=1,3）
• total_retail_price：所有子项retail_price * quantity的总和
• total_sell_price：所有子项sell_price * quantity的总和
• in_stock：组合内所有变体库存均充足（in_stock=1）则为1，否则为0

解决方案：递归CTE生成笛卡尔积组合 #

递归CTE可以逐步构建每个套餐下的变体组合，避免窗口函数无法生成全量组合的问题。以下是适配需求的SQL实现：

WITH RECURSIVE package_combinations AS (
    -- 锚点成员：获取每个package下的第一个package_item_id的所有变体，作为初始组合
    SELECT
        package_id,
        package_item_id,
        ARRAY[variant_id] AS variant_ids,
        quantity * retail_price AS retail_sum,
        quantity * sell_price AS sell_sum,
        in_stock AS stock_status
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY package_id ORDER BY package_item_id) AS rn
        FROM packages
    ) t
    WHERE rn = 1

    UNION ALL

    -- 递归成员：将现有组合与下一个package_item_id的变体进行笛卡尔积合并
    SELECT
        pc.package_id,
        p.package_item_id,
        pc.variant_ids || p.variant_id AS variant_ids,
        pc.retail_sum + (p.quantity * p.retail_price) AS retail_sum,
        pc.sell_sum + (p.quantity * p.sell_price) AS sell_sum,
        pc.stock_status & p.in_stock AS stock_status -- 按位与，只要有一个0结果就是0
    FROM package_combinations pc
    JOIN (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY package_id ORDER BY package_item_id) AS rn
        FROM packages
    ) p ON pc.package_id = p.package_id
        AND p.rn = (SELECT MAX(rn) FROM package_combinations WHERE package_id = pc.package_id) + 1
)
-- 最终筛选出每个套餐的完整组合（即包含所有package_item_id的组合）
SELECT
    package_id,
    CONCAT('package_id=', package_id, '&variant_ids=', ARRAY_TO_STRING(variant_ids, ',')) AS querystring,
    retail_sum AS total_retail_price,
    sell_sum AS total_sell_price,
    stock_status AS in_stock
FROM package_combinations pc
WHERE (SELECT COUNT(DISTINCT package_item_id) FROM packages WHERE package_id = pc.package_id) = ARRAY_LENGTH(variant_ids, 1)
ORDER BY package_id, variant_ids;

代码说明 #

1. 锚点CTE：

   • 为每个package_id筛选出第一个package_item_id的所有变体
   • 初始化组合数组variant_ids，以及对应的价格总和和库存状态

2. 递归CTE：

   • 将现有组合与当前套餐的下一个package_item_id的所有变体进行笛卡尔积关联
   • 拼接变体ID数组，累加价格总和，用按位与运算判断库存状态（只要有一个变体库存不足，结果就为0）

3. 最终查询：

   • 筛选出包含当前套餐所有package_item_id的完整组合（数组长度等于套餐子项数量）
   • 将变体ID数组转为逗号分隔的字符串，拼接成要求的querystring格式

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