jennisonzhang/LabelChange-server
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
LabelChange-server/.trae/documents/daily_stats_sql_analysis_plan.md
Im-Jenisson b2a9b7d3c2 上传源代码版本
2026-06-01 16:30:29 +08:00

18 KiB
Raw Permalink Blame History

日级报表SQL分析与优化计划

问题陈述

用户反馈：执行现有的日级报表SQL后，结果未达到预期效果。初步判断问题在于日期处理，应该以自然日为主体去统计和联合所有指标，而不是以到仓时间为主体。

SQL位置: d:\EPproject\LabelReplaceServer\src\DAL\Repositories\LabelReplaceRepository.cs#L723-1113 (GetDailyLabelStatsChineseAsync() 方法)

当前SQL的架构分析

核心概念梳理

1. 三种关键时间维度

时间维度 来源 用途 问题
到货日期 (ar.到货日期) arrival_handover_forms.ReceiptTime 识别到仓时间，用于16点分段统计 ✗ 作为统计主体，导致同一自然日的订单被分散
标签推送日期 (LabelRetrievedAt) label_replace_requests.LabelRetrievedAt 标识何时推送了标签 ✗ 与到货日期可能不同，混淆统计维度
扫描日期 (DailyScanStatus.日期) label_scan_history.CreatedAt (UTC-5转换) 扫描发生日期 ✓ 已正确转换为UTC-5自然日
完成日期 (首次成功日期) label_scan_history 首次成功时间 首次完成的日期 ✓ 已正确转换为UTC-5自然日

2. 当前SQL的日期使用方式

DailyBase (第833-859行)
  ↓
  使用 DistinctDates (从多个来源汇总的日期)
    ├─ 到货日期 (ar.到货日期)
    ├─ 扫描日期 (DailyScanStatus.日期)
    └─ 标签推送日期 (LabelRetrievedAt)
  ↓
  CROSS JOIN ArrivalRequests
  GROUP BY dd.日期

问题分析:

  • DailyBase 使用 CROSS JOIN，导致对每个 DistinctDates 的日期，都会与所有 ArrivalRequests 重复计算
  • 当日期来自多个来源时（到货、扫描、推送），统计维度混乱
  • 16点前/后统计基于到货时间，但归纳到不同日期，导致数据关联不清

用户判断的正确性评估

✅ 用户的判断基本正确

用户认为应该以自然日为主体进行统计，这个判断是合理的原因：

  1. 业务逻辑清晰: 报表应该按**自然日（UTC-5）**展示每天的统计数据
  2. 数据一致性: 所有指标（新增、完成、失败、16点分段）都应该在同一个自然日维度下聚合
  3. 避免维度混淆: 不应该混合到货日期、扫描日期、推送日期作为统计主体
  4. 用户需求: 用户最终需要的是"每天的报表"，而不是"按到货时间的报表"

✅ 需要改进的具体方面

问题1: DailyBase 的 CROSS JOIN 逻辑错误

位置: 第833-859行

FROM DistinctDates dd
CROSS JOIN ArrivalRequests ar
GROUP BY dd.日期

问题:

  • CROSS JOIN 会生成每个日期与每个到货请求的笛卡尔积
  • 对每个日期重复计算所有订单的统计，导致结果重复或错误
  • 应该改为：过滤到货日期属于该自然日的订单

解决方案:

FROM DistinctDates dd
INNER JOIN ArrivalRequests ar ON ar.到货日期 = dd.日期  ← 改为INNER JOIN
GROUP BY dd.日期

问题2: 16点前/后统计的日期混乱

位置: 第974-1013行 (DailyBeforeNoonPassed 和 DailyAfternoonPassed)

问题:

  • 这些统计基于 ar.到货日期，但到货日期可能与最终的统计日期不同
  • 同一自然日可能包含前一日和当日到货的订单，导致16点分段统计错乱

解决方案:

  • 需要明确：16点前/后统计是指到货时间在16点前/后，还是首次完成时间在16点前/后？
  • 如果是前者，应该按到货日期分组，再在每个到货日期内做16点分段
  • 如果是后者，应该按完成日期分组，不同处理逻辑

问题3: 日期维度不统一

位置: 全链路

问题:

  • 当日新增换单数：基于到货日期 (ar.到货日期)
  • 当日完成数：基于首次成功日期 (oss.首次成功日期)
  • 当日扫描数：基于扫描日期 (DailyScanStatus.日期)
  • 这三个时间来源完全不同，不是同一自然日的"自然日"概念

解决方案: 定义统计基准日 = 到货日期（UTC-5转换后） 作为所有统计的主体日期

推荐的重构方向

核心原则（已更新）

  1. 双维度日期处理：

    • 主体日期维度: 以**完成日期（首次成功日期 UTC-5）**作为最终报表的主体行
    • 辅助日期维度: 追溯到货日期用于计算16点分段考核

  2. 清晰的业务逻辑：

    • 16点前到仓的考核时间 = 到仓日的16点 ~ 次日16点
    • 16点后到仓的考核时间 = 次日16点 ~ 某个时间点（需确认）
    • 完成时间在考核时间内 = 考核通过
    • 统计时按完成日期分组

  3. JOIN关系：

    • 主表：按完成日期分组的所有已完成/失败订单
    • LEFT JOIN 到货信息：获取到货日期、到货时间以判断16点分段
    • LEFT JOIN 新增信息：统计该完成日期的新增包裹数

建议的SQL重构路径（修订版）

新架构：以完成日期为主体的报表

1. ArrivalRequests (保持不变)
   - 保存所有有标签的订单的到货信息
   
2. CompletionDates (新增：按完成日期分组)
   SELECT DISTINCT DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期
   FROM OverallScanStatus oss
   WHERE oss.曾成功 = 1
   
3. ArrivalsOnDate (到货日期统计：按到货日期分组)
   SELECT 
       到货日期,
       COUNT(DISTINCT...) AS 当日新增,
       COUNT(DISTINCT CASE WHEN HOUR(到货时间) < 16...) AS 16点前到仓,
       ...
   FROM ArrivalRequests
   GROUP BY 到货日期
   
4. CompletionsByDay (完成统计：按完成日期分组)
   SELECT 
       CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') AS 完成日期,
       ar.到货日期,
       COUNT(*) AS 当日完成数,
       COUNT(CASE WHEN 完成时间 <= 16点前考核时间...) AS 16点前考核通过,
       COUNT(CASE WHEN 完成时间 <= 16点后考核时间...) AS 16点后考核通过,
       ...
   FROM OverallScanStatus oss
   LEFT JOIN ArrivalRequests ar ON oss.NeutralWaybillNumber = ar.NeutralWaybillNumber
   WHERE oss.曾成功 = 1
   GROUP BY 完成日期, ar.到货日期
   
5. 最终报表
   SELECT 
       cd.日期,
       -- 到货相关（可能包含多个到货日期的订单）
       COALESCE(SUM(ArrivalsOnDate.当日新增), 0) AS 当日新增换单数,
       ...
       -- 完成相关（本日完成的所有订单）
       COALESCE(SUM(CompletionsByDay.当日完成数), 0) AS 当日换单成功数,
       COALESCE(SUM(CompletionsByDay.16点前考核通过), 0) AS 16点前考核通过包裹数,
       ...
   FROM CompletionDates cd
   LEFT JOIN ArrivalsOnDate ON ...
   LEFT JOIN CompletionsByDay ON cd.日期 = CompletionsByDay.完成日期
   GROUP BY cd.日期
   ORDER BY cd.日期 DESC

⚠️ 架构变化的关键点

旧架构 → 新架构的变化：

旧: 一行 = 一个到货日期的所有指标
    ├─ 当日新增 (基于到货日期)
    ├─ 当日完成 (可能来自不同到货日期)
    └─ 混乱导致数据不对应

新: 一行 = 一个完成日期的所有指标
    ├─ 当日新增 (该完成日期的新到货订单，来自不同日期)
    ├─ 当日完成 (该完成日期完成的所有订单)
    ├─ 16点前考核 (该完成日期完成的、来自16点前到仓的订单)
    └─ 16点后考核 (该完成日期完成的、来自16点后到仓的订单)
    
关键变化：新增、完成等指标可能来自不同的到货日期，这是正确的！

预期改进效果

改进前 vs 改进后

方面 改进前 改进后
统计维度 混乱（到货、扫描、推送三个时间维度混合） 统一（以自然日为主体）
JOIN逻辑 CROSS JOIN（笛卡尔积） INNER JOIN（一一对应）
数据完整性 同一订单可能在多个日期重复出现 同一订单只在到货日期出现一次
16点分段准确性 可能有日期偏差 基于同一日期内的到货时间精确划分
完成率计算 分子分母可能不匹配 分子分母来自同一维度，逻辑清晰

业务规则确认（用户反馈）

✅ 已确认的核心概念：包裹考核时间的完整定义

用户定义（官方）：

  • 标签率 = 该包裹关联的交接单内，所有有标签订单数 / 所有订单数
  • 考核时间 = 包裹的固有属性，由 标签率 + 到仓时间 + 16点结单概念 决定

考核时间的计算逻辑：

标签率 到仓时间 考核时间
≥80% 当日16点前 当日16点 ~ 次日16点
≥80% 当日16点后 当日16点 ~ 次日23:59:59
<80% 任何时间 包裹的首次完成时间 作为考核时间（实际上就是完成即达标）

完成统计的日期界定（关键！）：

  • 如果包裹在当日首次成功 → 算入当日换单成功数
  • 如果包裹在次日首次成功 → 算入次日换单成功数
  • 即：按完成时间（首次成功日期）进行统计，而不是按到货日期

考核通过的判定：

  • 包裹的首次完成时间 ≤ 该包裹的考核时间 = 考核通过
  • 按完成日期分组统计时，需要判断该包裹是否满足其考核时间

✅ 推导出的业务规则

基于上述考核时间的完整定义，推导出报表指标的统计方式：

统计指标 统计日期维度 说明 计算方式
当日新增换单数 到货日期 当天到货的包裹数 COUNT(DISTINCT ar.RequestId WHERE ar.到货日期 = 统计日期)
16点前到仓包裹数 到货日期 当天到货且到货时间<16点的包裹数 COUNT(...WHERE HOUR(ar.到货时间) < 16)
16点后到仓包裹数 到货日期 当天到货且到货时间≥16点的包裹数 COUNT(...WHERE HOUR(ar.到货时间) >= 16)
当日换单成功数 首次成功日期（UTC-5自然日） 当日首次成功的包裹数 COUNT(DISTINCT oss.NeutralWaybillNumber WHERE DATE(oss.首次成功日期) = 统计日期)
16点前考核通过包裹数 首次成功日期 首次成功时间满足"≥80%且16点前到仓的考核时间"的包裹数 COUNT(WHERE oss.首次成功时间 <= ar.考核时间 AND ar.到货时间<16点)
16点后考核通过包裹数 首次成功日期 首次成功时间满足"≥80%且16点后到仓的考核时间"的包裹数 COUNT(WHERE oss.首次成功时间 <= ar.考核时间 AND ar.到货时间≥16点)
当日换单失败 首次失败日期（UTC-5自然日） 当日首次失败且从未成功的包裹数 COUNT(...WHERE 首次失败日期 = 统计日期 AND 曾成功=0)

⚠️ 新发现：对标签率的重新理解

用户澄清：标签率是按交接单维度计算的

  • 标签率 = 该交接单内（所有有标签订单数） / （所有订单数）
  • 换句话说，同一交接单中的多个包裹可能共享同一个标签率

当前SQL的问题：

-- 第735-746行：按CustomerId计算，这是错的！
SELECT 
    l.CustomerId,
    COUNT(DISTINCT l.Id) AS total_requests,
    COUNT(DISTINCT CASE WHEN l.Label IS NOT NULL ... END) AS labeled_requests,
    ...
FROM label_replace_requests l
GROUP BY l.CustomerId  ← 错！应该按交接单分组

应该改为：

-- 应该按交接单（由BillOfLadingNumber或MasterPackageNumber标识）计算
SELECT 
    l.BillOfLadingNumber (或MasterPackageNumber),  ← 交接单标识
    COUNT(DISTINCT l.Id) AS total_requests,
    COUNT(DISTINCT CASE WHEN l.Label IS NOT NULL ... END) AS labeled_requests,
    ...
FROM label_replace_requests l
GROUP BY BillOfLadingNumber  ← 按交接单分组

⚠️ 关键业务流程梳理

1. 包裹到仓 → 获取到仓时间、关联交接单
   
2. 计算标签率
   ├─ 查找该包裹所在的交接单
   ├─ 统计交接单内所有订单数
   ├─ 统计交接单内有标签的订单数
   └─ 标签率 = 有标签数 / 总数
   
3. 计算考核时间
   ├─ IF 标签率 >= 80%:
   │  ├─ IF 到仓时间 < 16点: 考核时间 = 当日16点 ~ 次日16点
   │  └─ ELSE: 考核时间 = 当日16点 ~ 次日23:59:59
   └─ ELSE: 考核时间 = 包裹首次成功时间（完成即达标）
   
4. 完成换单
   ├─ 首次成功时间记录
   ├─ 判断：首次成功时间 <= 考核时间?
   └─ YES → 考核通过，NO → 考核未通过
   
5. 统计报表
   └─ 按首次成功日期分组，聚合所有指标

⚠️ 当前SQL的根本性问题（已全部确定）

通过用户的详细说明，确定了当前SQL存在以下根本性问题：

# 问题 位置 严重性 影响
1 标签率计算维度错误（按CustomerId而不是按交接单） 第735-746行 🔴 严重 导致所有基于标签率的考核时间计算都错误
2 统计主体日期错误（按到货日期而不是完成日期） 第832-859行 (DailyBase) 🔴 严重 导致报表维度完全错误
3 16点前/后考核通过基于到货日期而不是完成日期 第974-1013行 🔴 严重 导致考核通过数据统计错误
4 CROSS JOIN导致笛卡尔积 第857行 🟡 中等 导致数据重复或错误
5 日期来源混乱（到货、扫描、推送三个维度混合） 第809-823行 🟡 中等 导致统计维度混乱

⚠️ 标签率问题的深层影响

标签率计算错误导致的连锁问题：

错误的标签率
   ↓
错误的考核时间（第763-775行）
   ↓
错误的考核通过判定（第965-970行、第988-990行、第1009-1011行）
   ↓
错误的16点前/后考核通过数（第975-1013行）
   ↓
最终报表数据全部错误！

✅ Q1: 考核时间的完整边界定义

已确认（用户反馈）：

  • 16点前到仓（到仓时间 < 16点）→ 考核时间 = 当日16点 ~ 次日16点
  • 16点后到仓（到仓时间 ≥ 16点）→ 考核时间 = 当日16点 ~ 次日23:59:59（不是下下日16点）

✅ Q2: 已纠正

原问题: 完成/失败/STOP统计的日期应该是什么
用户回答: 应该按完成日期统计（首次成功日期），不是按到货日期

✅ Q3: 已明确

原问题: "当日新增换单数"的定义
用户回答: 当天到货并推送了标签的订单数（16点前和16点后的都算）

实施计划

阶段1: 业务确认（用户反馈）

  • 确认Q1、Q2、Q3的答案
  • 确认数据样本，看具体哪些日期的数据出现了问题

阶段2: SQL重构

  • 修正 DailyBase 的 CROSS JOIN 为 INNER JOIN
  • 统一所有统计的日期维度为到货日期（自然日）
  • 重新定义完成、失败、STOP等统计的基准日期
  • 验证16点分段统计的逻辑

阶段3: 测试验证

  • 对比修改前后的数据
  • 检查关键指标是否符合预期
  • 验证特殊场景（跨日订单、多个完成时间等）

阶段4: 优化细节

  • 性能优化（如需要）
  • 代码注释补充
  • 文档更新

总结

✅ 用户的判断是完全正确的

"应该以自然日为主体进行统计"这个判断是正确的。更准确的说法是：

应该以完成日期（首次成功日期的自然日）为报表的主体行日期。

这样每一行代表该自然日完成的所有订单的统计，而这些订单可能来自不同的到货日期。

✅ 当前SQL的5个根本性问题（全部已确定）

优先级 问题 位置 修复方向
🔴 严重 标签率计算维度错误：按CustomerId而不是按交接单 735-746 改为按BillOfLadingNumber/MasterPackageNumber分组
🔴 严重 统计主体日期错误：以到货日期而不是完成日期 832-859 改为以完成日期（首次成功日期）为报表维度
🔴 严重 16点前/后考核基于错误日期：基于到货日期而不是完成日期 974-1013 改为基于完成日期，关联到货日期判断分段
🟡 中等 CROSS JOIN导致笛卡尔积 857 改为适当的INNER JOIN或重新设计逻辑
🟡 中等 日期来源混乱 809-823 统一使用完成日期作为报表维度

📋 SQL重构的关键改动项

改造前（错误）:
  1. 标签率 ← 按CustomerId分组
  2. 考核时间 ← 基于错误的标签率
  3. DailyBase ← 按到货日期分组，使用CROSS JOIN
  4. 完成/失败统计 ← 基于完成日期（中途正确但最终混乱）
  5. 最终报表 ← 以到货日期为维度（导致整个报表错误）

改造后（正确）:
  1. 标签率 ← 按交接单（BillOfLadingNumber）分组计算
  2. 考核时间 ← 基于正确的标签率 + 到仓时间
  3. 不需要DailyBase这样的冗余CTE
  4. 以完成日期为报表维度
  5. 对每个完成日期，统计该日期内完成的包裹
     - 追溯到货日期判断16点前/后分段
     - 基于考核时间判断是否达标

🎯 最终报表输出的样式

日期(完成日期)  当日新增  16点前到仓  16点后到仓  当日完成  16点前考核通过  16点后考核通过  24H换单率
2024-05-16     100      60         40        80        50              18         87.5%
2024-05-15     95       55         40        85        52              18         92.9%
...

关键含义：

  • 每一行 = 该自然日（2024-05-16）完成的所有包裹的统计
  • 这些包裹可能来自多个到货日期（2024-05-15、2024-05-16等）
  • 16点前考核通过数 = 该完成日期完成的、来自16点前到仓的订单且满足其考核时间的包裹数

✅ 所有业务问题已确认

  • ✅ Q1: 16点前/后到仓的考核时间已确定
  • ✅ Q2: 完成/失败统计应按完成日期已确认
  • ✅ Q3: 当日新增定义已确认
  • ✅ Q4: 标签率应按交接单维度已确认

分析计划已完成，准备好进入实施阶段。