# 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之前的包裹数量

**实现方案**：
在每日统计中新增计数：
```sql
COUNT(DISTINCT CASE 
    WHEN ar.到货日期 = dd.日期 AND HOUR(ar.到货时间) < 16
    THEN ar.RequestId 
END) AS 16点前到仓包裹数
```

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

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

**实现方案**：
在每日统计中新增计数：
```sql
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：分析当前代码结构
- [x] 已分析 `DailyLabelStatsChineseDto` 类结构
- [x] 已确认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时新增）：
```sql
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映射中添加新字段：
```csharp
// 注意：系统标签率是常数（不随日期变化），在每行数据中值相同
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和时间转换可能影响性能，需监控