SQL如何按重量占比分摊Linehaul、Fuel等类别成本生成目标报表

计算逻辑说明 #

• 按ID独立计算分摊，不同ID数据互不干扰
• 先计算每个ID的总重量，以及每个ID下Linehaul、Fuel、Accessorial三类的总成本
• 每行的重量占比 = 当前行重量 ÷ 所属ID总重量
• 每行对应三类的分摊成本 = 所属ID对应类别的总成本 × 当前行重量占比，结果保留两位小数即可匹配预期输出

实现代码（适配SQL Server环境的测试数据） #

SELECT 
    ID,
    StopNumber,
    [Weight],
    Cost,
    Category,
    -- Linehaul分摊计算
    FORMAT(
        MAX(CASE WHEN Category = 'Linehaul' THEN Cost END) OVER (PARTITION BY ID) 
        * CAST([Weight] AS DECIMAL(18,6)) / SUM([Weight]) OVER (PARTITION BY ID)
    , 'N2') AS LineHaul,
    -- Fuel分摊计算
    FORMAT(
        MAX(CASE WHEN Category = 'Fuel' THEN Cost END) OVER (PARTITION BY ID) 
        * CAST([Weight] AS DECIMAL(18,6)) / SUM([Weight]) OVER (PARTITION BY ID)
    , 'N2') AS Fuel,
    -- Accessorial分摊计算
    FORMAT(
        MAX(CASE WHEN Category = 'Accessorial' THEN Cost END) OVER (PARTITION BY ID) 
        * CAST([Weight] AS DECIMAL(18,6)) / SUM([Weight]) OVER (PARTITION BY ID)
    , 'N2') AS Accessorial
FROM #temp
ORDER BY ID, StopNumber

代码说明 #

• 窗口函数OVER (PARTITION BY ID)实现按ID分组计算，不需要额外关联子查询，执行效率更高
• CAST([Weight] AS DECIMAL(18,6))避免整数除法导致的精度丢失
• FORMAT(..., 'N2')将结果格式化为两位小数，和预期输出的千分位格式一致，若不需要千分位可改用ROUND(xxx, 2)
• 计算结果和给出的预期输出完全匹配

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