LabelChange-server/.trae/specs/arrival_stats_sql/mysql_statements.md

到货信息统计 MySQL 语句

1. 基本统计语句

SELECT 
    COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown') AS `Key`,
    c.CustomerCode AS CustomerCode,
    COUNT(*) AS ArrivalOrderCount,
    SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) AS NoLabelDataCount,
    SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) AS LabeledOrderCount,
    CONCAT(ROUND((SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%') AS LabelRate,
    (SELECT COUNT(*) 
     FROM label_scan_history s 
     WHERE s.NeutralWaybillNumber = l.NeutralWaybillNumber 
     AND s.Result = 0) AS ReplaceCompletedCount,
    COUNT(*) - (SELECT COUNT(*) 
                FROM label_scan_history s 
                WHERE s.NeutralWaybillNumber = l.NeutralWaybillNumber 
                AND s.Result = 0) AS ReplacePendingCount,
    (SELECT MIN(h.ReceiptTime) 
     FROM arrival_handover_forms h 
     WHERE h.HandoverNumber = COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber)) AS ArrivalTime,
    MAX(l.BillOfLadingNumber) AS BillOfLadingNumber,
    MAX(l.MasterPackageNumber) AS MasterPackageNumber
FROM 
    label_replace_requests l
LEFT JOIN 
    customers c ON l.CustomerId = c.Id
WHERE 
    1=1
    -- 客户ID过滤
    AND (l.CustomerId = 1 OR 1 IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
GROUP BY 
    COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown')
ORDER BY 
    l.CreatedAt DESC;

2. 优化版本（使用子查询优化扫描记录统计）

WITH scan_summary AS (
    SELECT 
        NeutralWaybillNumber,
        COUNT(*) AS ReturnedLabelCount
    FROM 
        label_scan_history
    WHERE 
        Result = 0
    GROUP BY 
        NeutralWaybillNumber
),
arrival_summary AS (
    SELECT 
        HandoverNumber,
        MIN(ReceiptTime) AS MinReceiptTime
    FROM 
        arrival_handover_forms
    GROUP BY 
        HandoverNumber
)
SELECT 
    COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown') AS `Key`,
    c.CustomerCode AS CustomerCode,
    COUNT(*) AS ArrivalOrderCount,
    SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) AS NoLabelDataCount,
    SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) AS LabeledOrderCount,
    CONCAT(ROUND((SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%') AS LabelRate,
    COALESCE(SUM(s.ReturnedLabelCount), 0) AS ReplaceCompletedCount,
    COUNT(*) - COALESCE(SUM(s.ReturnedLabelCount), 0) AS ReplacePendingCount,
    COALESCE(a.MinReceiptTime, b.MinReceiptTime) AS ArrivalTime,
    MAX(l.BillOfLadingNumber) AS BillOfLadingNumber,
    MAX(l.MasterPackageNumber) AS MasterPackageNumber
FROM 
    label_replace_requests l
LEFT JOIN 
    customers c ON l.CustomerId = c.Id
LEFT JOIN 
    scan_summary s ON l.NeutralWaybillNumber = s.NeutralWaybillNumber
LEFT JOIN 
    arrival_summary a ON l.BillOfLadingNumber = a.HandoverNumber
LEFT JOIN 
    arrival_summary b ON l.MasterPackageNumber = b.HandoverNumber
WHERE 
    1=1
    -- 客户ID过滤
    AND (l.CustomerId = 1 OR 1 IS NULL)
    -- 日期范围过滤
    AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
    AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
GROUP BY 
    COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown')
ORDER BY 
    l.CreatedAt DESC;

3. 实现说明

3.1 分组逻辑

使用 COALESCE 函数实现提单号优先的分组逻辑：

• 当 BillOfLadingNumber 不为 NULL 时，使用 BillOfLadingNumber 作为分组依据
• 当 BillOfLadingNumber 为 NULL 时，使用 MasterPackageNumber 作为分组依据
• 当两者都为 NULL 时，使用 'Unknown' 作为分组依据

3.2 统计计算

• 到货订单数量：使用 COUNT(*) 统计每个分组的记录数
• 无标签数据数量：使用 SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) 统计
• 已有标签订单数：使用 SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) 统计
• 已有标签率：计算已有标签订单数占总订单数的百分比
• 换单完成数量：统计有扫描记录且结果为 0 的记录数
• 未换单完成数量：总订单数减去换单完成数量
• 到货时间：从到货交接单表中获取最早的收货时间

3.3 过滤条件

• 客户ID过滤：可根据需要修改客户ID值
• 日期范围过滤：可根据需要修改日期范围

4. 性能优化建议

1. 索引优化：

   • 在 label_replace_requests 表上添加以下索引：
     • (CustomerId, CreatedAt)
     • (BillOfLadingNumber)
     • (MasterPackageNumber)
   • 在 label_scan_history 表上添加索引：
     • (NeutralWaybillNumber, Result)
   • 在 arrival_handover_forms 表上添加索引：
     • (HandoverNumber, ReceiptTime)

2. 查询优化：

   • 使用 CTE (Common Table Expressions) 减少重复子查询
   • 避免在 GROUP BY 子句中使用复杂表达式
   • 合理使用 JOIN 替代子查询

3. 数据量控制：

   • 考虑添加分页功能，避免一次性返回大量数据
   • 对于历史数据，可以考虑归档策略

5. 使用说明

1. 参数调整：

   • 客户ID：修改 l.CustomerId = 1 中的 1 为实际客户ID
   • 日期范围：修改 '2026-01-01' 和 '2026-12-31' 为实际日期范围

2. 结果解释：

   • Key 列：表示分组依据，可能是提单号、主包号或 'Unknown'
   • 其他列：表示各统计指标

3. 注意事项：

   • Key 是 MySQL 关键字，使用反引号包围
   • 所有字符串参数都使用单引号包围
   • 日期参数使用 'YYYY-MM-DD' 格式
   • 可以根据实际需要修改示例值