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

SQL 改进实现细节文档

核心逻辑理解

用户需求核心梳理

当前指标体系

• 当天应该换单数 = 历史未完成换单数 + 当日新增换单数
• 实际换单数 = 当天换单完成的包裹数
• 当天换单完成率 = 实际换单数 / 当天应该换单数

新增/修改的考核时间规则

当前系统对每个包裹有一个"考核时间"，用来判断包裹是否在规定时间内完成了换单。新需求改变了考核时间的计算方式：

基于标签率的分组考核：

IF 客户标签率 >= 80% THEN
  IF 到仓时间.hour < 16 THEN
    考核时间 = 次日16:00
  ELSE
    考核时间 = 次日23:59
  END IF
ELSE
  考核时间 = 该包裹实际完成换单的时间
END IF

这意味着：

• 对于标签率高的客户（>=80%），给予固定的考核时间窗口
• 对于标签率低的客户(<80%)，只要换单完成了就算达标

新的24小时换单率计算

公式：(完成时间 <= 考核时间的包裹数) / 当天应该换单数

含义：

• 分子：通过24小时内完成考核的包裹数
• 分母：当天应该完成的所有包裹数（包括历史未完成+当日新增）

新增指标

1. 16点前到仓包裹数：当日 HOUR(到仓时间) < 16 的包裹
2. 16点后到仓包裹数：当日 HOUR(到仓时间) >= 16 的包裹

---

SQL实现方案详解

关键计算步骤

步骤A：计算客户级别标签率（新增CTE）

用于在考核时间计算中判断是否应用固定时间窗口：

CustomerLabelRates AS (
  SELECT 
    l.CustomerId,
    COUNT(DISTINCT l.Id) AS total_requests,
    COUNT(DISTINCT CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN l.Id END) AS labeled_requests,
    ROUND(
      COUNT(DISTINCT CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN l.Id END) / 
      COUNT(DISTINCT l.Id) * 100, 
      2
    ) AS label_rate_percent
  FROM label_replace_requests l
  GROUP BY l.CustomerId
)

步骤A.5：计算系统整体标签率（在最终SELECT中）

用于输出到前端展示系统全局指标。

重要：标签率的分母应该是与交接单关联的所有订单数（因为当前的统计都是基于与arrival_handover_forms关联的订单）

-- 在最终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 系统标签率

说明：这样计算的标签率与后续的换单统计保持逻辑一致，都是基于与交接单有关联的订单。

步骤B：重新计算考核时间（修改ArrivalRequests CTE）

需要合并客户标签率信息，并根据新规则计算考核时间：

-- 关键伪代码逻辑
考核时间 = CASE
  WHEN clr.label_rate_percent >= 80 THEN
    CASE
      WHEN HOUR(a.ReceiptTime) < 16 THEN 
        DATE_ADD(DATE(a.ReceiptTime), INTERVAL 1 DAY) + TIME '16:00:00'
      ELSE 
        DATE_ADD(DATE(a.ReceiptTime), INTERVAL 1 DAY) + TIME '23:59:59'
    END
  ELSE
    -- 对于标签率低的客户，需要获取实际完成时间
    -- 这个需要在后续步骤中通过JOIN获得
    oss.首次成功时间
END

步骤C：统计16点分段到仓的包裹数（修改DailyBase CTE）

DailyBase AS (
  SELECT 
    dd.日期,
    -- 当日新增换单数
    COUNT(DISTINCT CASE 
      WHEN ar.到货日期 = dd.日期 AND ar.LabelRetrievedAt IS NOT NULL 
      THEN ar.RequestId 
    END) AS 当日新增换单数,
    
    -- 新增：16点前到仓
    COUNT(DISTINCT CASE 
      WHEN ar.到货日期 = dd.日期 AND HOUR(ar.到货时间) < 16
      THEN ar.RequestId 
    END) AS 16点前到仓包裹数,
    
    -- 新增：16点后到仓
    COUNT(DISTINCT CASE 
      WHEN ar.到货日期 = dd.日期 AND HOUR(ar.到货时间) >= 16
      THEN ar.RequestId 
    END) AS 16点后到仓包裹数,
    
    -- 当天标签推送数
    COUNT(DISTINCT CASE 
      WHEN DATE(CONVERT_TZ(ar.LabelRetrievedAt, '+00:00', '-05:00')) = dd.日期 
      THEN ar.RequestId 
    END) AS 当日标签推送数
  FROM DistinctDates dd
  CROSS JOIN ArrivalRequests ar
  GROUP BY dd.日期
)

步骤D：重新计算24小时完成数（新/修改CTE）

Daily24HCompletedOrders AS (
  SELECT 
    ar.到货日期 AS 日期,
    COUNT(DISTINCT ar.NeutralWaybillNumber) AS 24H内完成数
  FROM ArrivalRequests ar
  INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
  WHERE 
    oss.曾成功 = 1 
    AND oss.首次成功时间 IS NOT NULL
    AND ar.考核时间 IS NOT NULL
    -- 关键条件：完成时间 <= 考核时间
    AND oss.首次成功时间 <= ar.考核时间
  GROUP BY ar.到货日期
)

步骤E：计算"当天应该换单数"（在最终输出中）

当天应该换单数 = 累计要换的总单数 + 当日新增换单数
-- 或在CASE中根据是否是历史日期判断

步骤F：修改最终SELECT中的24小时率计算

-- 原逻辑：
CASE 
  WHEN 当日完成数 = 0 THEN '0.00%'
  ELSE CONCAT(ROUND(24H内完成数 / 当日完成数 * 100, 2), '%')
END AS 24H换单率

-- 新逻辑：
CASE 
  WHEN 当天应该换单数 = 0 THEN '0.00%'
  ELSE CONCAT(ROUND(24H内完成数 / 当天应该换单数 * 100, 2), '%')
END AS 24H换单率

---

实现复杂点分析

1. 考核时间的二阶段计算问题

问题：对于标签率低的客户，考核时间需要是"该包裹实际完成换单的时间"，但这个时间在关联ArrivalRequests时还不可知。

解决方案：

• 在ArrivalRequests中先计算一个"参考考核时间"（对标签率>=80%的客户）
• 对于标签率<80%的客户，在后续JOIN OverallScanStatus时使用首次成功时间作为考核时间
• 在最终统计时通过CASE WHEN判断

ArrivalRequests AS (
  SELECT 
    ...,
    l.CustomerId,
    -- 先计算标签率
    clr.label_rate_percent,
    -- 基础到仓时间
    a.到货时间,
    -- 根据标签率计算参考考核时间
    CASE
      WHEN clr.label_rate_percent >= 80 THEN
        CASE
          WHEN HOUR(a.ReceiptTime) < 16 THEN 
            CONCAT(DATE_ADD(DATE(a.ReceiptTime), INTERVAL 1 DAY), ' 16:00:00')
          ELSE 
            CONCAT(DATE_ADD(DATE(a.ReceiptTime), INTERVAL 1 DAY), ' 23:59:59')
        END
      ELSE
        NULL  -- 低标签率客户，考核时间取决于完成时间
    END AS 基础考核时间
  FROM ...
  LEFT JOIN CustomerLabelRates clr ON l.CustomerId = clr.CustomerId
)

2. 时间比较精度问题

问题：首次成功时间 <= 考核时间的比较需要考虑：

• UTC和UTC-5的转换
• 时间戳的精度（秒级）

解决方案：

-- 确保都转换为UTC-5时区
WHEN CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间 THEN 1

3. 统计维度的叠加问题

问题：多个CTE都需要按日期统计，需要确保JOIN逻辑不会导致数据重复计数。

解决方案：

• 在每个COUNT中使用DISTINCT确保去重
• 使用CASE WHEN限制统计范围
• 在最终聚合时使用GROUP BY日期

---

DTO修改方案

DailyLabelStatsChineseDto 新增字段

/// <summary>
/// 系统整体标签率（%）
/// </summary>
[SugarColumn(ColumnName = "系统标签率")]
public string LabelRate { get; set; }

/// <summary>
/// 16点前到仓的包裹数
/// </summary>
[SugarColumn(ColumnName = "16点前到仓包裹数")]
public int BeforeNoonArrivedCount { get; set; }

/// <summary>
/// 16点后到仓的包裹数
/// </summary>
[SugarColumn(ColumnName = "16点后到仓包裹数")]
public int AfternoonArrivedCount { get; set; }

/// <summary>
/// 当天应该换单数（历史未完成+当日新增）
/// </summary>
[SugarColumn(ColumnName = "当天应该换单数")]
public int ShouldReplaceCount { get; set; }

C#映射代码

var dto = new DailyLabelStatsChineseDto
{
    // ... 现有字段 ...
    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,
};

---

测试验证清单

• SQL语法校验（无错误）
• 数据准确性：验证16点分段统计
• 时区转换：确认所有时间操作都基于UTC-5
• 标签率计算：确认>=80%和<80%的分组逻辑
• 考核时间逻辑：抽样验证几个包裹的考核时间是否正确
• 24小时率：对比原逻辑，确保新分母计算正确
• 当天应该换单数：验证 = 累计未完成 + 当日新增
• Excel导出：确认新字段能正确导出