如何基于delivery与sale表统计商品销交总量及销交比率

如果直接将sale和delivery表明细按商品字段关联后再聚合，会因为同一商品在两张表的明细行数不同产生笛卡尔积，导致累计数量被异常放大，最终统计结果完全失真。

核心实现逻辑 #

• 对齐统计粒度：以两张表共有的iname（商品名称）、dname（业务归属主体）作为唯一匹配维度，避免跨主体同名商品的数据串算
• 预聚合降粒度：先分别对销售、交付表按上述维度做分组求和，拿到单维度下的累计值后再做关联，从根源上避免明细join的数量膨胀问题
• 全量覆盖统计结果：用全外连接关联两张预聚合结果，保证有销售记录、有交付记录的商品都能被纳入统计范围，不会遗漏单边数据
• 鲁棒性处理：做除零保护、空值补0、浮点类型转换，避免计算过程中触发SQL报错或结果精度异常

参考实现代码 #

WITH sale_group AS (
    SELECT
        iname,
        dname,
        SUM(sqty) AS total_sale_qty
    FROM sale
    GROUP BY iname, dname
),
delivery_group AS (
    SELECT
        iname,
        dname,
        SUM(dqty) AS total_delivery_qty
    FROM delivery
    GROUP BY iname, dname
)
SELECT
    COALESCE(s.iname, d.iname) AS 统计商品,
    COALESCE(s.dname, d.dname) AS 业务归属名称,
    COALESCE(s.total_sale_qty, 0) AS 累计销售总数量,
    COALESCE(d.total_delivery_qty, 0) AS 累计交付总数量,
    CASE
        WHEN COALESCE(d.total_delivery_qty, 0) = 0 THEN NULL
        ELSE ROUND(s.total_sale_qty * 1.0 / d.total_delivery_qty, 2)
    END AS 销售交付比率
FROM sale_group s
FULL OUTER JOIN delivery_group d
    ON s.iname = d.iname
    AND s.dname = d.dname;

注意事项 #

• 如果数据库不支持全外连接（比如低版本MySQL），可以分别用左连接把销售全量关联交付、右连接把交付全量关联销售，最后用UNION ALL合并去重，实现同等的全量覆盖效果
• 比率计算时要提前做数值类型转换，避免整数相除的截断问题，比如在MySQL中直接用两个整数做除法会只保留整数部分，乘1.0转浮点数后再计算能保证精度
• 若后续需要按单据号（dnum/snum）维度叠加统计，可以在预聚合的分组字段里追加对应单据字段，不需要修改整体的关联逻辑

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