LabelChange-server/.trae/documents/sql_logic_error_fix.md

SQL逻辑错误修复方案

问题诊断

用户发现的逻辑问题：

当日换单成功数 < 16点前考核通过数 + 16点后考核通过数

这违反了基本的数学关系：考核通过的包裹数 ≤ 成功的包裹数

---

根本原因

当前SQL的日期维度混乱

CTE	日期维度	计算逻辑	问题
DailySuccessCount	扫描日期	按扫描成功时的日期	❌ 不同维度
DailyBeforeNoonPassed	到货日期	按订单到货时的日期	❌ 不同维度
DailyAfternoonPassed	到货日期	按订单到货时的日期	❌ 不同维度

导致的结果

示例：
订单A: 到货日期=05-15, 首次成功日期=05-16, 到货时间=15:30(16点前)

当前统计：
- 05-15 的"16点前考核通过数"包含订单A ❌（错误！订单A 05-15还未成功）
- 05-16 的"当日换单成功数"包含订单A ✓
- 结果：05-15 的考核通过数可能 > 成功数（矛盾！）

---

正确的逻辑

所有指标都应该按"首次成功日期"分组

当日换单成功数 = COUNT(DISTINCT 首次成功日期=当天的包裹)

16点前考核通过 = COUNT(DISTINCT 
    首次成功日期=当天 
    AND 满足考核时间 
    AND 到货时间<16点 的包裹)

16点后考核通过 = COUNT(DISTINCT
    首次成功日期=当天 
    AND 满足考核时间 
    AND 到货时间≥16点 的包裹)

数学关系

当日换单成功数 = 16点前成功数 + 16点后成功数
当日考核通过数 = 16点前考核通过 + 16点后考核通过
考核通过数 ≤ 成功数 ✓

---

修复步骤

步骤1：修改DailySuccessCount

当前（错误）：

DailySuccessCount AS (
    SELECT 
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,  ← 扫描日期
        ...

改为（正确）：

DailySuccessCount AS (
    SELECT 
        DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期,  ← 首次成功日期
        COUNT(DISTINCT oss.NeutralWaybillNumber) AS 当日换单成功数
    FROM OverallScanStatus oss
    INNER JOIN ArrivalRequests ar ON oss.NeutralWaybillNumber = ar.NeutralWaybillNumber
    WHERE oss.曾成功 = 1 AND oss.首次成功时间 IS NOT NULL
    GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
)

步骤2：修改DailyBeforeNoonPassed

当前（错误）：

DailyBeforeNoonPassed AS (
    SELECT 
        ar.到货日期 AS 日期,  ← 到货日期（错！）
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点前考核通过包裹数
    FROM ArrivalRequests ar
    ...
    GROUP BY ar.到货日期
)

改为（正确）：

DailyBeforeNoonPassed AS (
    SELECT 
        DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期,  ← 首次成功日期
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点前考核通过包裹数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        -- 16点前到仓
        AND HOUR(ar.到货时间) < 16
        -- 满足考核时间
        AND (
            (ar.考核时间 IS NOT NULL AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间)
            OR (ar.考核时间 IS NULL)
        )
    GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
)

步骤3：修改DailyAfternoonPassed

当前（错误）：

DailyAfternoonPassed AS (
    SELECT 
        ar.到货日期 AS 日期,  ← 到货日期（错！）
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点后考核通过包裹数
    FROM ArrivalRequests ar
    ...
    GROUP BY ar.到货日期
)

改为（正确）：

DailyAfternoonPassed AS (
    SELECT 
        DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期,  ← 首次成功日期
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点后考核通过包裹数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        -- 16点后到仓
        AND HOUR(ar.到货时间) >= 16
        -- 满足考核时间
        AND (
            (ar.考核时间 IS NOT NULL AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间)
            OR (ar.考核时间 IS NULL)
        )
    GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
)

---

修改影响分析

直接影响的CTE

• ✏️ DailySuccessCount - 修改日期维度
• ✏️ DailyBeforeNoonPassed - 修改日期维度 + JOIN逻辑
• ✏️ DailyAfternoonPassed - 修改日期维度 + JOIN逻辑

间接受影响的CTE

• DailyStatsWithPrev - 需要验证是否需要调整
• 最终SELECT - 可能需要调整来源

可以删除的CTE

• ❌ Daily24HCompletedOrders - 现在已被DailySuccessCount覆盖
• ❌ DailyCompletedOrders - 现在已被DailySuccessCount覆盖（如果只关心成功包裹）

---

验证修改后的逻辑

修改后应该满足：

∀日期d:
  当日换单成功数(d) 
    = 16点前首次成功数(d) + 16点后首次成功数(d)
    
  16点前考核通过(d) ≤ 16点前首次成功数(d)
  16点后考核通过(d) ≤ 16点后首次成功数(d)
  
  当日换单成功数(d) ≥ 当日考核通过数(d)

---

报表对比

修改前（错误）

日期      当日成功数  16点前考核  16点后考核  检查
05-16     50        60         20        ❌ 60+20 > 50 (矛盾！)

修改后（正确）

日期      当日成功数  16点前成功  16点前考核  16点后成功  16点后考核  检查
05-16     80        50         45        30         20        ✅ 80=50+30, 65≤80

---

总结

核心修改原则：

所有关于"成功"和"考核通过"的统计，都必须基于首次成功日期，而不是到货日期或扫描日期。

这样才能保证：考核通过数 ≤ 成功数 的基本逻辑关系。