使用CTE与Join关联3张表计算天然气成本的SQL问题求助

需求背景 #

需要基于三张表按如下规则完成天然气运行成本的区块分摊计算，原有编写的SQL无法正常运行，优化方案如下：

分摊公式：(年度总运行成本 / 全年度天然气总产量) * 对应区块年度天然气产量

原SQL存在的问题 #

• CTE语法错误，标准SQL定义CTE必需加WITH关键字，原代码漏写
• 字段拼写错误：定义的列名filed为拼写错误，正确应为field
• 分组规则不符合语法要求：两次GROUP BY都只指定了field字段，但SELECT中返回了id等非聚合、未包含在GROUP BY中的字段，绝大多数数据库都会直接报错
• 计算逻辑不符合需求：原代码中sum(g.year_1)是按区块分组的产量总和，不是公式要求的全局年度总产量，计算结果完全不符合预期
• 存在多余分组操作：外层查询不需要再次执行GROUP BY，额外分组会导致数据统计错误

优化后SQL实现 #

如果ref_fee表存储的是和group_1的id一一对应的运行成本，可以使用窗口函数实现全局产量统计，避免非法分组问题：

WITH c AS (
    SELECT 
        g.id, 
        g.field,
        -- 按公式计算区块年度分摊成本
        (r.year_1 / SUM(g.year_1) OVER()) * g.year_1 AS share_cost1,
        (r.year_2 / SUM(g.year_2) OVER()) * g.year_2 AS share_cost2,
        (r.year_3 / SUM(g.year_3) OVER()) * g.year_3 AS share_cost3
    FROM group_1 AS g
    INNER JOIN ref_fee AS r ON r.id = g.id
)
SELECT 
    c.id, 
    c.field,
    c.share_cost1 * b.year_1 AS year_1,
    c.share_cost2 * b.year_2 AS year_2,
    c.share_cost3 * b.year_3 AS year_3
FROM c
INNER JOIN back b ON b.id = c.id;

如果ref_fee表存储的是全局唯一的总运行成本（仅1条数据），可以用更清晰的CTE拆分逻辑，性能也更优：

WITH -- 提取年度总运行成本
total_fee AS (
    SELECT 
        year_1 AS total_fee1,
        year_2 AS total_fee2,
        year_3 AS total_fee3 
    FROM ref_fee 
    LIMIT 1
),
-- 统计年度全局总产量
total_prod AS (
    SELECT 
        SUM(year_1) AS total_prod1,
        SUM(year_2) AS total_prod2,
        SUM(year_3) AS total_prod3
    FROM group_1
)
-- 按规则计算最终结果
SELECT 
    g.id,
    g.field,
    (tf.total_fee1 / tp.total_prod1) * g.year_1 * b.year_1 AS year_1,
    (tf.total_fee2 / tp.total_prod2) * g.year_2 * b.year_2 AS year_2,
    (tf.total_fee3 / tp.total_prod3) * g.year_3 * b.year_3 AS year_3
FROM group_1 g
CROSS JOIN total_fee tf
CROSS JOIN total_prod tp
INNER JOIN back b ON b.id = g.id;

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