SQL三表LEFT JOIN做订单状态统计时聚合计数结果异常排查

• 计数膨胀的核心诱因是多表连续关联产生的笛卡尔积：原SQL对order_logs做两次自关联后再关联order_items，当一个订单匹配多条符合条件的日志、或者一个已配送订单下存在多条upsell类型商品时，关联结果会生成多行重复的订单记录，普通COUNT统计会把这些重复行全部计入，导致所有指标数值虚高。
• 关联逻辑存在冗余和设计缺陷：两次关联order_logs时重复添加log_1.user_id = users.id判断，且将不同状态的订单日志拆成两个别名做关联，本身就会让同一订单的不同状态事件产生乘法匹配，再叠加商品项的多行数据，重复计数问题会被进一步放大。
• 统计逻辑粒度错误：业务需求是统计符合条件的订单数量，原SQL直接统计关联后的结果行数，没有做订单去重，只要关联产生重复行，计数必然不准。

从根源避免多表关联产生的行膨胀问题：拆分不同统计维度的聚合逻辑，在关联前先完成单维度的去重计数，最后再和用户表合并结果，修正后的SQL如下：

WITH user_confirmed_stats AS (
    -- 单独统计每个用户的已确认订单数
    SELECT
        user_id,
        COUNT(DISTINCT order_id) AS confirmed
    FROM order_logs
    WHERE event = 'OrderConfirmed'
    GROUP BY user_id
),
user_delivered_stats AS (
    -- 单独统计每个用户的已配送订单数、带upsell加购的已配送订单数
    SELECT
        l.user_id,
        COUNT(DISTINCT l.order_id) AS delivered,
        COUNT(DISTINCT CASE WHEN i.id IS NOT NULL THEN l.order_id END) AS upsells
    FROM order_logs l
    LEFT JOIN order_items i
        ON l.order_id = i.order_id
        AND i.pricing_schema = 'upsell'
    WHERE l.event = 'OrderDelivered'
    GROUP BY l.user_id
)
SELECT
    u.id,
    u.name,
    COALESCE(c.confirmed, 0) AS confirmed,
    COALESCE(d.delivered, 0) AS delivered,
    COALESCE(d.upsells, 0) AS upsells
FROM users u
LEFT JOIN user_confirmed_stats c ON u.id = c.user_id
LEFT JOIN user_delivered_stats d ON u.id = d.user_id;

写法说明 #

• 拆分两个CTE分别处理已确认、已配送两类指标的统计，避免同一张日志表反复自关联产生笛卡尔积
• 所有订单计数统一用COUNT(DISTINCT order_id)，即使关联后出现同订单的多行重复数据，也只会统计一次，不会重复计数
• 用COALESCE处理用户无对应状态订单时的空值，保证无数据时返回0而非null
• upsell指标通过判断关联到的加购商品项是否存在，统计符合条件的去重订单数，不会因为单个订单有多个加购项重复计数

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