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

SQL 指标优化计划

一、当前SQL逻辑分析

现有指标计算

1. 当日新增换单数：当天到货并且推送了标签数据的订单数量
2. 累计要换的总单数：历史未完成换单数 + 当日新增换单数（通过滚动求和计算）
3. 当日标签推送数：通过标签推送时间计算的当天标签推送数量
4. 当日换单成功数：当天完成的换单包裹数（Result = 0）
5. 当天换单完成率：当日完成数/(当日新增换单数+累计要换的总单数)
6. 24H换单率：24小时内完成的包裹数/当日完成数

现有逻辑中的关键CTE

• ArrivalFormsWithDate：获取所有到货交接单基础信息
• ArrivalRequests：关联到货单与换单请求，计算考核时间（目前：取标签推送时间和到仓时间的较晚时间）
• OverallScanStatus：获取每个订单的首次成功信息
• DailyBase：每日基础统计

---

二、新需求分析与实现方案

需求1：修改包裹考核时间逻辑

原逻辑：取标签推送时间与到仓时间的较晚时间

新逻辑：根据标签率判断

• 标签率 >= 80%（对客承诺90%）

  • 到仓时间 < 16:00：考核时间截止为次日16:00
  • 到仓时间 >= 16:00：考核时间截止为次日23:59

• 标签率 < 80%（对客承诺90%）

  • 考核时间 = 包裹换单完成时间

实现方案：

1. 在 ArrivalRequests CTE 中需要：

   • 计算每个订单所属客户的标签率
   • 根据标签率和到仓时间计算新的考核时间

2. 需要新增CTE计算客户的标签率：

   CustomerLabelRate: 计算每个客户的标签率 = 有标签的订单数/总订单数
   

3. 修改 ArrivalRequests 中的考核时间计算逻辑

需求2：修改24小时换单完成率

原逻辑：24H内完成数/当日完成数

新逻辑：(包裹换单完成时间 - 包裹考核时间 <= 0 的包裹数) / 当天应该换单数

说明：

• 包裹换单完成时间 <= 考核时间 的包裹视为24小时内完成
• 分母改为"当天应该换单数"而不是"当日完成数"

实现方案：

1. 创建新CTE计算每日24小时内完成的包裹数
2. 修改分母为当天应该换单数（历史未完成数+当日新增数）

需求3：新增指标 - 16:00前到仓的包裹数量

定义：当日到仓时间在16:00之前的包裹数量

实现方案： 在每日统计中新增计数：

COUNT(DISTINCT CASE 
    WHEN ar.到货日期 = dd.日期 AND HOUR(ar.到货时间) < 16
    THEN ar.RequestId 
END) AS 16点前到仓包裹数

需求4：新增指标 - 16:00后到仓的包裹数量

定义：当日到仓时间在16:00之后的包裹数量

实现方案： 在每日统计中新增计数：

COUNT(DISTINCT CASE 
    WHEN ar.到货日期 = dd.日期 AND HOUR(ar.到货时间) >= 16
    THEN ar.RequestId 
END) AS 16点后到仓包裹数

---

二.五、实现方式评估：SQL实现 vs 代码实现

方案对比

方案A：直接在SQL中完整实现（推荐）

优点：

• 数据库层面完成所有计算，性能最优
• 减少应用层数据传输和处理
• 逻辑清晰，便于维护和调试
• 数据一致性更好

缺点：

• SQL复杂度高，维护难度大
• 调试相对困难

方案B：SQL + C#代码混合实现

优点：

• 分离关注点，部分逻辑在应用层更清晰
• 便于测试和调试

缺点：

• 性能相对较差（多次数据传输）
• 代码复杂度反而更高
• 数据一致性难以保证

最终决策：采用方案A（SQL完整实现）

理由：

1. 虽然SQL复杂，但逻辑清晰且一次性完成
2. 涉及大量的CASE WHEN计算，在数据库层完成更高效
3. 新增的标签率计算本质上是CTE级别的操作，适合SQL实现

---

三、实现步骤

步骤1：分析当前代码结构

• 已分析 DailyLabelStatsChineseDto 类结构
• 已确认SQL所在文件位置

步骤2：修改DTO类添加新字段

• 在 DailyLabelStatsChineseDto 类中添加：
  • LabelRate（标签率 %）- 用于下推到前端展示整体标签率
  • BeforeNoonArrivedCount（16:00前到仓包裹数）
  • AfternoonArrivedCount（16:00后到仓包裹数）
  • ShouldReplaceCount（当天应该换单数 = 历史未完成+当日新增）
  • Rate24HourModified（修改后的24小时换单率，分母为当天应该换单数）

步骤3：修改SQL实现新逻辑

SQL文件位置：d:\EPproject\LabelReplaceServer\src\DAL\Repositories\LabelReplaceRepository.cs (L709-1040)

3.1 新增 CustomerLabelRate CTE

• 计算每个客户的标签率 = (有标签的订单数) / (总订单数)

3.2 修改 ArrivalRequests CTE

• 添加客户标签率信息
• 根据标签率和到仓时间重新计算考核时间：

  CASE
    WHEN 标签率 >= 0.8 THEN
      CASE
        WHEN HOUR(到仓时间) < 16 THEN DATE_ADD(DATE(到仓时间), INTERVAL 1 DAY) 16:00:00
        ELSE DATE_ADD(DATE(到仓时间), INTERVAL 1 DAY) 23:59:59
      END
    ELSE
      包裹换单完成时间
  END AS 考核时间
  

3.3 修改 DailyBase CTE

• 添加16:00前和16:00后到仓包裹数的统计

3.4 新增/修改24小时完成数计算CTE

• 重新计算基于新考核时间的24小时内完成数

3.5 修改最终SELECT语句

• 添加新的指标列输出
• 修改24小时换单率的分母

步骤4：修改C#代码映射新字段

• 在 GetDailyLabelStatsChineseAsync() 方法中添加新列的映射

步骤5：验证和测试

• 检查SQL语法
• 验证数据准确性
• 测试边界情况

---

四、新增DTO字段映射与标签率计算

SQL输出新列

1. 标签率 → 系统整体的标签率（有标签的订单数/总订单数）
2. 16点前到仓包裹数 → BeforeNoonArrivedCount
3. 16点后到仓包裹数 → AfternoonArrivedCount
4. 当天应该换单数 → ShouldReplaceCount
5. 修改后的24小时换单率 → Rate24HourModified 或保持原 Rate24Hour 字段

标签率计算方式

概念澄清：

• 交接单(Handover)：到货交接单，由 HandoverNumber 标识
• 订单(Request)：换单请求记录(label_replace_requests)
• 关系：一个交接单可以关联多个订单（通过 BillOfLadingNumber 或 MasterPackageNumber 匹配）
• 总订单数：一个交接单关联的所有 label_replace_requests 的总数

客户标签率计算逻辑：

• 每个交接单所属一个客户
• 客户标签率 = 该客户下有标签的订单总数 / 该客户下的总订单数
• 系统标签率 = 全系统有标签的订单总数 / 全系统总订单数

在SQL中计算系统标签率（在最终SELECT时新增）：

CONCAT(ROUND(
  (SELECT COUNT(DISTINCT l.Id) FROM label_replace_requests l 
   INNER JOIN arrival_handover_forms a 
   ON l.BillOfLadingNumber = a.HandoverNumber OR l.MasterPackageNumber = a.HandoverNumber
   WHERE l.Label IS NOT NULL AND l.Label != '') 
  /
  (SELECT COUNT(DISTINCT l.Id) FROM label_replace_requests l 
   INNER JOIN arrival_handover_forms a 
   ON l.BillOfLadingNumber = a.HandoverNumber OR l.MasterPackageNumber = a.HandoverNumber) 
  * 100, 2
), '%') AS 系统标签率

C#映射代码

在 GetDailyLabelStatsChineseAsync() 方法的reader映射中添加新字段：

// 注意：系统标签率是常数（不随日期变化），在每行数据中值相同
LabelRate = reader["系统标签率"] as string ?? "0.00%",
BeforeNoonArrivedCount = reader["16点前到仓包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点前到仓包裹数"]) : 0,
AfternoonArrivedCount = reader["16点后到仓包裹数"] != DBNull.Value ? Convert.ToInt32(reader["16点后到仓包裹数"]) : 0,
ShouldReplaceCount = reader["当天应该换单数"] != DBNull.Value ? Convert.ToInt32(reader["当天应该换单数"]) : 0,

---

五、关键数据表结构回顾

• arrival_handover_forms: 到货交接单表

  • ReceiptTime: 到仓时间（UTC-5）
  • HandoverNumber: 交接单号

• label_replace_requests: 换单请求表

  • NeutralWaybillNumber: 中性运单号
  • Label: 标签
  • LabelRetrievedAt: 标签推送时间
  • CustomerId: 客户ID

• label_scan_history: 扫描历史表

  • CreatedAt: 扫描时间（UTC）
  • Result: 扫描结果（0=成功）

---

六、实现注意事项

1. 时区处理：确保所有时间比较统一使用UTC-5时区
2. 标签率计算：需要明确如何定义"总订单数"（所有订单还是特定条件的订单）
3. 考核时间精确性：新逻辑涉及时间戳的精确比较，需谨慎处理
4. 向后兼容性：可能需要在Excel导出功能中也添加新列
5. 性能考虑：大量的CASE WHEN和时间转换可能影响性能，需监控