如何用BigQuery/SQL计算递归树形结构中的单位价格

问题背景 #

我正在处理一个包含两个关键列的数据库：

• recipe：列出所有配方及子配方；
• name_article：存储与各配方或子配方关联的物料（原料）。

需要计算这些物料的单位价格：当name_article对应的price_unit_ht_2为NULL时，需通过匹配recipe列中与该name_article相同的值，汇总其关联物料的价格来计算。由于配方可能包含子配方，形成递归结构，必须处理递归以计算所有层级的正确单位价格。

示例数据 #

预期输出 #

解决方案：使用递归CTE处理层级计算 #

在BigQuery中，可以通过**递归公共表达式（CTE）**从最底层的基础物料（已有明确价格的物料）开始，逐层向上计算子配方和配方的单位价格。

步骤说明 #

1. 基础层：筛选出所有price_unit_ht_2不为NULL的物料，这些是计算的起点。
2. 递归层：对于每个未计算价格的配方/子配方，关联其下一级的物料，汇总quantity * 单位价格的总和，作为当前配方的单位价格。
3. 迭代计算：重复递归过程，直到所有层级的价格都被计算完成。

完整SQL代码 #

WITH RECURSIVE recipe_prices AS (
    -- 基础层：已有明确价格的物料
    SELECT
        name_article AS article,
        price_unit_ht_2 AS unit_price,
        0 AS depth
    FROM
        `your-project.your-dataset.your-table`
    WHERE
        price_unit_ht_2 IS NOT NULL

    UNION ALL

    -- 递归层：计算上层配方的单位价格
    SELECT
        r.recipe AS article,
        SUM(r.quantity * rp.unit_price) AS unit_price,
        rp.depth + 1 AS depth
    FROM
        `your-project.your-dataset.your-table` r
    JOIN
        recipe_prices rp ON r.name_article = rp.article
    WHERE
        r.price_unit_ht_2 IS NULL
        -- 确保只计算未被处理过的配方
        AND NOT EXISTS (
            SELECT 1 FROM recipe_prices rp2 WHERE rp2.article = r.recipe
        )
    GROUP BY
        r.recipe, rp.depth + 1
)
-- 关联原表，输出最终结果
SELECT
    t.recipe,
    t.name_article,
    t.id_establishment,
    ROUND(t.quantity, 2) AS quantity,
    ROUND(COALESCE(t.price_unit_ht_2, rp.unit_price), 4) AS price_unit_ht_2
FROM
    `your-project.your-dataset.your-table` t
LEFT JOIN
    recipe_prices rp ON t.name_article = rp.article
ORDER BY
    t.recipe, t.name_article;

代码解释 #

• 递归CTE recipe_prices：
  • 基础层收集所有有明确价格的物料，标记深度为0。
  • 递归层中，每次关联当前已计算出价格的物料到对应的配方，通过SUM(quantity * unit_price)计算配方的单位价格，同时深度加1，确保从底层到上层逐步计算。
  • NOT EXISTS条件避免重复计算同一配方，保证每个配方只被处理一次。
• 最终查询：将原表与递归计算出的价格关联，用COALESCE优先使用原表已有的价格，没有则使用递归计算的结果，并对数值进行格式化。

注意事项 #

• 替换代码中的your-project.your-dataset.your-table为实际的BigQuery表路径。
• 如果存在循环引用（例如配方A包含配方B，配方B又包含配方A），需要添加额外的逻辑检测并处理，避免无限递归。
• 可以根据实际需求调整ROUND函数的小数位数。

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