# 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）

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

```sql
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关联的订单）

```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 系统标签率
```

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

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

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

```sql
-- 关键伪代码逻辑
考核时间 = 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）

```sql
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）

```sql
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：计算"当天应该换单数"（在最终输出中）

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

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

```sql
-- 原逻辑：
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判断

```sql
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的转换
- 时间戳的精度（秒级）

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

### 3. 统计维度的叠加问题

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

**解决方案**：
- 在每个COUNT中使用DISTINCT确保去重
- 使用CASE WHEN限制统计范围
- 在最终聚合时使用GROUP BY日期

---

## DTO修改方案

### DailyLabelStatsChineseDto 新增字段

```csharp
/// <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#映射代码

```csharp
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导出：确认新字段能正确导出