318 lines
9.2 KiB
Markdown
318 lines
9.2 KiB
Markdown
# 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导出：确认新字段能正确导出
|
||
|