如何将年度后台（BO）成本分摊至其他销售渠道并生成结果表？

现有年度销售渠道成本表（表名t），其中CH='bo'代表后台渠道成本：

YYYY . CH   COST
=====+====+======
2022 . c1 .  20
2022 . c2 .  30
2022 . bo .  50
2023 . c1 .  25
2023 . c2 .  40
2023 . bo .  80

另有后台成本分摊系数表（表名d），定义了各渠道需分摊的后台成本比例：

CH . BO_PERC
===+========
c1 .   0.2    -- 年度后台成本的20%追加至c1渠道
c2 .   0.4    -- 年度后台成本的40%追加至c2渠道

需要将每年的后台成本按系数分摊至对应渠道，最终生成如下结果表：

YYYY . CH . COST
=====+====+=====
2022 . c1 .  30     -- 20 + 50*0.2
2022 . c2 .  50     -- 30 + 50*0.4
2023 . c1 .  41     -- 25 + 80*0.2
2023 . c2 .  72     -- 40 + 80*0.4

以下是几种通用的SQL实现方式，可根据使用的数据库类型选择：

方法1：关联子查询获取年度后台成本 #

SELECT
    t.YYYY,
    t.CH,
    t.COST + (SELECT COST FROM t AS bo WHERE bo.YYYY = t.YYYY AND bo.CH = 'bo') * d.BO_PERC AS COST
FROM t
JOIN d ON t.CH = d.CH
WHERE t.CH != 'bo'
ORDER BY t.YYYY, t.CH;

逻辑说明 #

• 筛选出非后台渠道的记录（t.CH != 'bo'）
• 通过子查询匹配当前年度的后台成本
• 关联分摊系数表，计算渠道原有成本与分摊后台成本的总和

方法2：用CTE提取年度后台成本（适用于支持窗口函数的数据库） #

如果使用MySQL 8.0+、PostgreSQL、SQL Server等支持CTE的数据库，可采用更高效的写法：

WITH yearly_bo_cost AS (
    SELECT
        YYYY,
        COST AS bo_cost
    FROM t
    WHERE CH = 'bo'
)
SELECT
    t.YYYY,
    t.CH,
    t.COST + ybc.bo_cost * d.BO_PERC AS COST
FROM t
JOIN d ON t.CH = d.CH
JOIN yearly_bo_cost ybc ON t.YYYY = ybc.YYYY
WHERE t.CH != 'bo'
ORDER BY t.YYYY, t.CH;

逻辑说明 #

• 先用CTE预提取各年度的后台成本，避免重复查询
• 将渠道表、分摊表与年度后台成本表关联，直接计算最终成本

方法3：自关联获取年度后台成本 #

SELECT
    t.YYYY,
    t.CH,
    t.COST + bo.COST * d.BO_PERC AS COST
FROM t
JOIN d ON t.CH = d.CH
JOIN t AS bo ON t.YYYY = bo.YYYY AND bo.CH = 'bo'
WHERE t.CH != 'bo'
ORDER BY t.YYYY, t.CH;

逻辑说明 #

• 将成本表自关联，关联条件为同年度且关联表为后台渠道
• 结合分摊系数表直接计算最终成本，写法简洁直观

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