基于4个数据源的订单状态统计：关联排除与特殊场景处理问询

统计需求 #

需统计以下4种订单状态的数量：

• in progress（进行中）
• completed（已完成）
• pending deletion（待删除）
• deleted（已删除）

数据源说明 #

共有4个数据源：

1. 可信数据源（source of truth，简称st）

2. 待删除订单列表（list of pending deletion，简称PD）

3. 手动更新订单表（manually updated table with Orders，简称MO）

4. 已删除订单列表（list of DELETED）
   该表记录已从可信数据源移除的订单：

字段说明 #

• st、PD、MO表包含ORDER ID和status字段
• st和DELETED表包含unique_order_ID字段

需处理的限制场景 #

• 已删除订单不在可信数据源中；
• 同一ORDER ID对应不同unique_order_ID时，需区分统计（旧订单删除后新建同名订单，需分别计入对应状态）

原SQL存在的问题 #

1. 字段名、表名拼写错误（如st.oders应为st.ORDER ID，MO.orders_status应为MO.status）
2. 过滤逻辑错误，错误排除了待删除订单
3. 未基于unique_order_ID区分同名订单，导致统计混淆
4. CTE定义与表名重复（pending_deletion既是表名又是CTE名称）
5. 统计deleted状态时引用错误表名in_deleted

优化后的SQL查询 #

WITH all_orders AS (
    -- 从可信数据源获取未删除的有效订单，整合状态
    SELECT
        st.`ORDER ID`,
        st.`OrderUNIQUE_ID` AS unique_id,
        CASE
            -- 优先识别PD表的待删除状态
            WHEN pd.status = 'pending deletion' THEN 'pending deletion'
            -- 取MO表中非删除的有效状态（若多条取第一条，可根据实际逻辑调整）
            ELSE COALESCE(
                (SELECT mo.status FROM MO mo WHERE mo.`ORDER ID` = st.`ORDER ID` AND mo.status != 'deleted' LIMIT 1),
                'unknown'
            )
        END AS order_status
    FROM st
    LEFT JOIN PD pd ON st.`ORDER ID` = pd.`ORDER ID`
    -- 排除已删除的唯一订单标识
    WHERE st.`OrderUNIQUE_ID` NOT IN (SELECT `ORDER_unique_ID` FROM DELETED)

    UNION ALL

    -- 从已删除列表获取已删除订单
    SELECT
        d.`ORDER ID`,
        d.`ORDER_unique_ID` AS unique_id,
        'deleted' AS order_status
    FROM DELETED d
),
status_summary AS (
    SELECT
        order_status AS name,
        COUNT(DISTINCT unique_id) AS count
    FROM all_orders
    -- 仅统计目标状态
    WHERE order_status IN ('in progress', 'completed', 'pending deletion', 'deleted')
    GROUP BY order_status
)
-- 确保四种状态全展示，无数据时显示0
SELECT
    status.name,
    COALESCE(ss.count, 0) AS count
FROM (
    SELECT 'in progress' AS name UNION ALL
    SELECT 'completed' AS name UNION ALL
    SELECT 'pending deletion' AS name UNION ALL
    SELECT 'deleted' AS name
) status
LEFT JOIN status_summary ss ON status.name = ss.name
ORDER BY count DESC;

优化说明 #

1. 基于唯一标识区分订单：用unique_order_ID作为订单唯一标识，解决同名订单（同一ORDER ID不同unique_id）的统计混淆问题
2. 整合全量订单：通过UNION ALL合并可信数据源的有效订单与已删除列表的订单，确保无遗漏
3. 状态优先级处理：优先识别PD表的待删除状态，再取MO表中的非删除状态，保证状态准确性
4. 全状态覆盖：通过基础状态列表左联统计结果，确保四种状态都能显示，即使数量为0
5. 修正逻辑与拼写错误：修复原SQL中的字段名、表名错误，调整过滤逻辑

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