252 lines
8.7 KiB
Markdown
252 lines
8.7 KiB
Markdown
# 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和时间转换可能影响性能，需监控
|
||
|