24H换单完成率数据验证计划 - 简化版

日期：2026-05-16
验证目标：通过汇总统计和订单明细，手工验证修复后的逻辑
验证数据：2026-05-14 的数据
核心需求：汇总数据+订单明细对比

验证思路

通过两个SQL来验证：

汇总统计SQL：展示该日期所有关键指标的COUNT结果，用于手工运算
订单明细SQL：展示参与计算的具体订单，用于对比验证

SQL 1：汇总统计（手工运算基础）

统计2026-05-14的各项指标：

SQL 1：汇总统计（手工运算基础）

统计2026-05-14的各项指标：

-- ===== 汇总统计：14日数据验证 =====
-- 核心修复：包含所需的CTE定义，使SQL完整可执行

WITH InterchangeUnitLabelRatesAtFirstScan AS (
    -- 步骤2.5：计算交接单的作业时标签率（基于最早扫描时间）
    SELECT 
        l.BillOfLadingNumber,
        l.MasterPackageNumber,
        COUNT(DISTINCT l.Id) AS total_requests,
        COUNT(DISTINCT CASE 
            WHEN l.Label IS NOT NULL AND l.Label != '' 
            THEN l.Id 
        END) AS total_labeled_at_any_time,
        COUNT(DISTINCT CASE 
            WHEN l.Label IS NOT NULL 
                 AND l.Label != '' 
                 AND l.LabelRetrievedAt < (
                     SELECT MIN(lsh2.CreatedAt)
                     FROM label_scan_history lsh2
                     WHERE lsh2.NeutralWaybillNumber = l.NeutralWaybillNumber
                 )
            THEN l.Id 
        END) AS labeled_at_first_scan,
        ROUND(
            COUNT(DISTINCT CASE 
                WHEN l.Label IS NOT NULL 
                     AND l.Label != '' 
                     AND l.LabelRetrievedAt < (
                         SELECT MIN(lsh3.CreatedAt)
                         FROM label_scan_history lsh3
                         WHERE lsh3.NeutralWaybillNumber = l.NeutralWaybillNumber
                     )
                THEN l.Id 
            END) * 100.0 / 
            COUNT(DISTINCT CASE 
                WHEN l.Label IS NOT NULL AND l.Label != '' 
                THEN l.Id 
            END),
            2
        ) AS label_rate_at_first_scan
    FROM label_replace_requests l
    GROUP BY l.BillOfLadingNumber, l.MasterPackageNumber
),
OverallScanStatus AS (
    -- 每个订单的扫描状态统计
    SELECT 
        s.NeutralWaybillNumber,
        MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 曾成功,
        MIN(CASE WHEN s.Result = 0 THEN s.CreatedAt ELSE NULL END) AS 首次成功时间
    FROM label_scan_history s
    GROUP BY s.NeutralWaybillNumber
)
SELECT 
    '2026-05-14' AS 统计日期,
    -- ===== 分母计算 =====
    (
        SELECT COUNT(DISTINCT l.NeutralWaybillNumber)
        FROM arrival_handover_forms a
        INNER JOIN label_replace_requests l 
            ON l.BillOfLadingNumber = a.HandoverNumber 
            OR l.MasterPackageNumber = a.HandoverNumber
        INNER JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
            ON l.BillOfLadingNumber = iulr_first.BillOfLadingNumber
            AND l.MasterPackageNumber = iulr_first.MasterPackageNumber
        WHERE DATE(a.ReceiptTime) = '2026-05-14'
            AND l.Label IS NOT NULL AND l.Label != ''
            AND iulr_first.label_rate_at_first_scan >= 80
    ) AS 分母_总应该换单数,
    -- ===== 分母的详细拆分 =====
    (
        SELECT COUNT(DISTINCT CONCAT(l.BillOfLadingNumber, '|', l.MasterPackageNumber))
        FROM arrival_handover_forms a
        INNER JOIN label_replace_requests l 
            ON l.BillOfLadingNumber = a.HandoverNumber 
            OR l.MasterPackageNumber = a.HandoverNumber
        INNER JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
            ON l.BillOfLadingNumber = iulr_first.BillOfLadingNumber
            AND l.MasterPackageNumber = iulr_first.MasterPackageNumber
        WHERE DATE(a.ReceiptTime) = '2026-05-14'
            AND l.Label IS NOT NULL AND l.Label != ''
            AND iulr_first.label_rate_at_first_scan >= 80
    ) AS 分母_交接单数,
    -- 标签率≥80% 的16点前到仓
    (
        SELECT COUNT(DISTINCT CONCAT(l.BillOfLadingNumber, '|', l.MasterPackageNumber))
        FROM arrival_handover_forms a
        INNER JOIN label_replace_requests l 
            ON l.BillOfLadingNumber = a.HandoverNumber 
            OR l.MasterPackageNumber = a.HandoverNumber
        INNER JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
            ON l.BillOfLadingNumber = iulr_first.BillOfLadingNumber
            AND l.MasterPackageNumber = iulr_first.MasterPackageNumber
        WHERE DATE(a.ReceiptTime) = '2026-05-14'
            AND l.Label IS NOT NULL AND l.Label != ''
            AND iulr_first.label_rate_at_first_scan >= 80
            AND HOUR(a.ReceiptTime) < 16
    ) AS 分母_16点前交接单数,
    -- 标签率≥80% 的16点后到仓
    (
        SELECT COUNT(DISTINCT CONCAT(l.BillOfLadingNumber, '|', l.MasterPackageNumber))
        FROM arrival_handover_forms a
        INNER JOIN label_replace_requests l 
            ON l.BillOfLadingNumber = a.HandoverNumber 
            OR l.MasterPackageNumber = a.HandoverNumber
        INNER JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
            ON l.BillOfLadingNumber = iulr_first.BillOfLadingNumber
            AND l.MasterPackageNumber = iulr_first.MasterPackageNumber
        WHERE DATE(a.ReceiptTime) = '2026-05-14'
            AND l.Label IS NOT NULL AND l.Label != ''
            AND iulr_first.label_rate_at_first_scan >= 80
            AND HOUR(a.ReceiptTime) >= 16
    ) AS 分母_16点后交接单数,
    -- ===== 分子计算 =====
    -- 16点前到仓 且已考核通过（完成时间 ≤ 次日16:00）
    (
        SELECT COUNT(DISTINCT l.NeutralWaybillNumber)
        FROM arrival_handover_forms a
        INNER JOIN label_replace_requests l 
            ON l.BillOfLadingNumber = a.HandoverNumber 
            OR l.MasterPackageNumber = a.HandoverNumber
        INNER JOIN OverallScanStatus oss ON l.NeutralWaybillNumber = oss.NeutralWaybillNumber
        INNER JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
            ON l.BillOfLadingNumber = iulr_first.BillOfLadingNumber
            AND l.MasterPackageNumber = iulr_first.MasterPackageNumber
        WHERE DATE(a.ReceiptTime) = '2026-05-14'
            AND l.Label IS NOT NULL AND l.Label != ''
            AND iulr_first.label_rate_at_first_scan >= 80
            AND HOUR(a.ReceiptTime) < 16
            AND oss.曾成功 = 1 
            AND oss.首次成功时间 IS NOT NULL
            AND oss.首次成功时间 <= CONCAT(DATE_ADD(DATE(a.ReceiptTime), INTERVAL 1 DAY), ' 16:00:00')
    ) AS 分子_16点前完成数,
    -- 16点后到仓 且已考核通过（完成时间 ≤ 次日23:59:59）
    (
        SELECT COUNT(DISTINCT l.NeutralWaybillNumber)
        FROM arrival_handover_forms a
        INNER JOIN label_replace_requests l 
            ON l.BillOfLadingNumber = a.HandoverNumber 
            OR l.MasterPackageNumber = a.HandoverNumber
        INNER JOIN OverallScanStatus oss ON l.NeutralWaybillNumber = oss.NeutralWaybillNumber
        INNER JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
            ON l.BillOfLadingNumber = iulr_first.BillOfLadingNumber
            AND l.MasterPackageNumber = iulr_first.MasterPackageNumber
        WHERE DATE(a.ReceiptTime) = '2026-05-14'
            AND l.Label IS NOT NULL AND l.Label != ''
            AND iulr_first.label_rate_at_first_scan >= 80
            AND HOUR(a.ReceiptTime) >= 16
            AND oss.曾成功 = 1 
            AND oss.首次成功时间 IS NOT NULL
            AND oss.首次成功时间 <= CONCAT(DATE_ADD(DATE(a.ReceiptTime), INTERVAL 1 DAY), ' 23:59:59')
    ) AS 分子_16点后完成数,
    -- 总分子
    (
        SELECT COUNT(DISTINCT l.NeutralWaybillNumber)
        FROM arrival_handover_forms a
        INNER JOIN label_replace_requests l 
            ON l.BillOfLadingNumber = a.HandoverNumber 
            OR l.MasterPackageNumber = a.HandoverNumber
        INNER JOIN OverallScanStatus oss ON l.NeutralWaybillNumber = oss.NeutralWaybillNumber
        INNER JOIN InterchangeUnitLabelRatesAtFirstScan iulr_first
            ON l.BillOfLadingNumber = iulr_first.BillOfLadingNumber
            AND l.MasterPackageNumber = iulr_first.MasterPackageNumber
        WHERE DATE(a.ReceiptTime) = '2026-05-14'
            AND l.Label IS NOT NULL AND l.Label != ''
            AND iulr_first.label_rate_at_first_scan >= 80
            AND oss.曾成功 = 1 
            AND oss.首次成功时间 IS NOT NULL
            AND ((HOUR(a.ReceiptTime) < 16 AND oss.首次成功时间 <= CONCAT(DATE_ADD(DATE(a.ReceiptTime), INTERVAL 1 DAY), ' 16:00:00'))
                 OR (HOUR(a.ReceiptTime) >= 16 AND oss.首次成功时间 <= CONCAT(DATE_ADD(DATE(a.ReceiptTime), INTERVAL 1 DAY), ' 23:59:59')))
    ) AS 分子_总完成数;

输出解析：

分母_总应该换单数：基于SUM(有标签包裹数) 的最终分母
分母_交接单数：参与统计的交接单总数
分母_16点前/后交接单数：按到货时间拆分的交接单数
分子_16点前完成数：16点前到仓且已完成的订单数
分子_16点后完成数：16点后到仓且已完成的订单数
分子_总完成数：总的完成订单数（分子）

手工运算：24H换单率 = 分子_总完成数 / 分母_总应该换单数 × 100%

SQL 2：订单明细（参与计算的具体订单）

展示参与计算的具体订单（分子和分母中的所有订单）：

-- ===== 订单明细：14日所有参与计算的订单 =====
-- 核心修复：包含CTE定义，使SQL完整可执行

WITH InterchangeUnitLabelRatesAtFirstScan AS (
    SELECT 
        l.BillOfLadingNumber,
        l.MasterPackageNumber,
        COUNT(DISTINCT l.Id) AS total_requests,
        COUNT(DISTINCT CASE 
            WHEN l.Label IS NOT NULL AND l.Label != '' 
            THEN l.Id 
        END) AS total_labeled_at_any_time,
        COUNT(DISTINCT CASE 
            WHEN l.Label IS NOT NULL 
                 AND l.Label != '' 
                 AND l.LabelRetrievedAt < (
                     SELECT MIN(lsh2.CreatedAt)
                     FROM label_scan_history lsh2
                     WHERE lsh2.NeutralWaybillNumber = l.NeutralWaybillNumber
                 )
            THEN l.Id 
        END) AS labeled_at_first_scan,
        ROUND(
            COUNT(DISTINCT CASE 
                WHEN l.Label IS NOT NULL 
                     AND l.Label != '' 
                     AND l.LabelRetrievedAt < (
                         SELECT MIN(lsh3.CreatedAt)
                         FROM label_scan_history lsh3
                         WHERE lsh3.NeutralWaybillNumber = l.NeutralWaybillNumber
                     )
                THEN l.Id 
            END) * 100.0 / 
            COUNT(DISTINCT CASE 
                WHEN l.Label IS NOT NULL AND l.Label != '' 
                THEN l.Id 
            END),
            2
        ) AS label_rate_at_first_scan
    FROM label_replace_requests l
    GROUP BY l.BillOfLadingNumber, l.MasterPackageNumber
),
OverallScanStatus AS (
    SELECT 
        s.NeutralWaybillNumber,
        MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 曾成功,
        MIN(CASE WHEN s.Result = 0 THEN s.CreatedAt ELSE NULL END) AS 首次成功时间
    FROM label_scan_history s
    GROUP BY s.NeutralWaybillNumber
)
SELECT 
    l.NeutralWaybillNumber AS 订单号,
    l.BillOfLadingNumber AS 交接单号,
    l.MasterPackageNumber AS 主包裹号,
    DATE(a.ReceiptTime) AS 到货日期,
    CASE WHEN HOUR(a.ReceiptTime) < 16 THEN '16点前' ELSE '16点后' END AS 到货时段,
    -- 作业时标签率
    ROUND(
        (SELECT COUNT(DISTINCT CASE 
            WHEN l2.Label IS NOT NULL 
                 AND l2.Label != '' 
                 AND l2.LabelRetrievedAt < (
                     SELECT MIN(lsh.CreatedAt)
                     FROM label_scan_history lsh
                     WHERE lsh.NeutralWaybillNumber = l2.NeutralWaybillNumber
                 )
            THEN l2.Id 
        END)
         FROM label_replace_requests l2
         WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
         AND l2.MasterPackageNumber = l.MasterPackageNumber) * 100.0 / 
        (SELECT COUNT(DISTINCT CASE 
            WHEN l2.Label IS NOT NULL AND l2.Label != '' 
            THEN l2.Id 
        END)
         FROM label_replace_requests l2
         WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
         AND l2.MasterPackageNumber = l.MasterPackageNumber),
        2
    ) AS 作业时标签率百分比,
    l.LabelRetrievedAt AS 标签推送时间,
    (SELECT MIN(lsh.CreatedAt) 
     FROM label_scan_history lsh 
     WHERE lsh.NeutralWaybillNumber = l.NeutralWaybillNumber) AS 首次扫描时间,
    -- 应该的考核时间
    CASE 
        WHEN (SELECT COUNT(DISTINCT CASE 
                WHEN l2.Label IS NOT NULL 
                     AND l2.Label != '' 
                     AND l2.LabelRetrievedAt < (
                         SELECT MIN(lsh.CreatedAt)
                         FROM label_scan_history lsh
                         WHERE lsh.NeutralWaybillNumber = l2.NeutralWaybillNumber
                     )
                THEN l2.Id 
            END)
             FROM label_replace_requests l2
             WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
             AND l2.MasterPackageNumber = l.MasterPackageNumber) * 100.0 / 
            (SELECT COUNT(DISTINCT CASE 
                WHEN l2.Label IS NOT NULL AND l2.Label != '' 
                THEN l2.Id 
            END)
             FROM label_replace_requests l2
             WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
             AND l2.MasterPackageNumber = l.MasterPackageNumber) >= 80 
        THEN
            CASE
                WHEN HOUR(a.到货时间) < 16 THEN CONCAT(DATE_ADD(DATE(a.到货时间), INTERVAL 1 DAY), ' 16:00:00')
                ELSE CONCAT(DATE_ADD(DATE(a.到货时间), INTERVAL 1 DAY), ' 23:59:59')
            END
        ELSE '无'
    END AS 应该的考核时间,
    -- 实际首次成功时间
    oss.首次成功时间,
    -- 是否在分母中
    CASE 
        WHEN (SELECT COUNT(DISTINCT CASE 
                WHEN l2.Label IS NOT NULL 
                     AND l2.Label != '' 
                     AND l2.LabelRetrievedAt < (
                         SELECT MIN(lsh.CreatedAt)
                         FROM label_scan_history lsh
                         WHERE lsh.NeutralWaybillNumber = l2.NeutralWaybillNumber
                     )
                THEN l2.Id 
            END)
             FROM label_replace_requests l2
             WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
             AND l2.MasterPackageNumber = l.MasterPackageNumber) * 100.0 / 
            (SELECT COUNT(DISTINCT CASE 
                WHEN l2.Label IS NOT NULL AND l2.Label != '' 
                THEN l2.Id 
            END)
             FROM label_replace_requests l2
             WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
             AND l2.MasterPackageNumber = l.MasterPackageNumber) >= 80 
        THEN '✓ 在分母中'
        ELSE '✗ 不在分母中'
    END AS 是否在分母中,
    -- 是否在分子中
    CASE 
        WHEN oss.曾成功 = 1 
             AND oss.首次成功时间 IS NOT NULL
             AND (SELECT COUNT(DISTINCT CASE 
                    WHEN l2.Label IS NOT NULL 
                         AND l2.Label != '' 
                         AND l2.LabelRetrievedAt < (
                             SELECT MIN(lsh.CreatedAt)
                             FROM label_scan_history lsh
                             WHERE lsh.NeutralWaybillNumber = l2.NeutralWaybillNumber
                         )
                    THEN l2.Id 
                END)
                 FROM label_replace_requests l2
                 WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
                 AND l2.MasterPackageNumber = l.MasterPackageNumber) * 100.0 / 
                (SELECT COUNT(DISTINCT CASE 
                    WHEN l2.Label IS NOT NULL AND l2.Label != '' 
                    THEN l2.Id 
                END)
                 FROM label_replace_requests l2
                 WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
                 AND l2.MasterPackageNumber = l.MasterPackageNumber) >= 80
             AND ((HOUR(a.到货时间) < 16 AND oss.首次成功时间 <= CONCAT(DATE_ADD(DATE(a.到货时间), INTERVAL 1 DAY), ' 16:00:00'))
                  OR (HOUR(a.到货时间) >= 16 AND oss.首次成功时间 <= CONCAT(DATE_ADD(DATE(a.到货时间), INTERVAL 1 DAY), ' 23:59:59')))
        THEN '✓ 在分子中'
        ELSE '✗ 不在分子中'
    END AS 是否在分子中
FROM label_replace_requests l
INNER JOIN arrival_handover_forms a ON (l.BillOfLadingNumber = a.HandoverNumber OR l.MasterPackageNumber = a.HandoverNumber)
LEFT JOIN OverallScanStatus oss ON l.NeutralWaybillNumber = oss.NeutralWaybillNumber
WHERE DATE(a.ReceiptTime) = '2026-05-14'
    AND l.Label IS NOT NULL AND l.Label != ''
ORDER BY 到货时段, 作业时标签率百分比 DESC, 订单号;

输出解析：

只展示有标签的订单
作业时标签率百分比：用于判断是否纳入24H换单率统计
应该的考核时间：根据到货时间和作业时标签率确定
是否在分母中：标签率≥80%的为"✓ 在分母中"
是否在分子中：同时满足标签率≥80%且在考核时间内完成的为"✓ 在分子中"

验证步骤

步骤1：执行SQL 1

获取汇总统计数据，得到以下关键数值：

分母_总应该换单数（分母）
分母_16点前交接单数 + 分母_16点后交接单数
分子_16点前完成数 + 分子_16点后完成数 = 分子_总完成数（分子）

步骤2：手工验证

24H换单率 = 分子_总完成数 / 分母_总应该换单数 × 100%

例如，假设结果为：

分母 = 100
分子 = 50
24H换单率 = 50 / 100 × 100% = 50%

步骤3：执行SQL 2

查看所有参与计算的订单明细，验证：

有多少订单在分母中（是否在分母中 = '✓ 在分母中'）
有多少订单在分子中（是否在分子中 = '✓ 在分子中'）
对比分子分母数据与汇总统计是否一致

步骤4：数据一致性检查

SQL 1中的分母应该 ≈ SQL 2中"在分母中"的订单COUNT
SQL 1中的分子应该 ≈ SQL 2中"在分子中"的订单COUNT

预期结果

✅ 分子 ≤ 分母（通常成立）
✅ 24H换单率是合理的百分比
✅ SQL 1和SQL 2的数据对应一致
✅ 没有出现4745%等极端异常数值

18 KiB Raw Permalink Blame History Unescape Escape

24H换单完成率数据验证计划 - 简化版

验证思路

SQL 1：汇总统计（手工运算基础）

SQL 1：汇总统计（手工运算基础）

SQL 2：订单明细（参与计算的具体订单）

验证步骤

步骤1：执行SQL 1

步骤2：手工验证

步骤3：执行SQL 2

步骤4：数据一致性检查

预期结果

18 KiB

Raw Permalink Blame History