213 lines
5.9 KiB
Markdown
213 lines
5.9 KiB
Markdown
# 24H换单率超过100%的根本原因与修复 - 最终完成
|
||
|
||
**修复日期**: 2026-05-16
|
||
**问题类型**: 分母重复计算
|
||
**修复状态**: ✅ 编译通过
|
||
|
||
---
|
||
|
||
## 问题诊断
|
||
|
||
### 症状
|
||
|
||
24小时换单率超过100%，甚至达到899%
|
||
|
||
### 根本原因
|
||
|
||
**`DailyHighLabelRateShould` 和 `DailyLowLabelRateShould` CTE 中的分母计算错误**
|
||
|
||
原始逻辑：
|
||
```sql
|
||
DailyHighLabelRateShould AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(ar.到货时间, '+00:00', '-05:00')) AS 日期,
|
||
COUNT(DISTINCT ar.NeutralWaybillNumber) AS 高标签率应该换单数
|
||
FROM ArrivalRequests ar
|
||
INNER JOIN (...)
|
||
GROUP BY DATE(...)
|
||
)
|
||
```
|
||
|
||
**问题**：
|
||
- `ArrivalRequests` 表中可能存在**相同 `NeutralWaybillNumber` 但不同到货时间的多条记录**
|
||
- 例如：同一个订单可能在 09:00 到货一次，10:00 到货一次
|
||
- `COUNT(DISTINCT ar.NeutralWaybillNumber)` 只去重 `NeutralWaybillNumber`
|
||
- 但每一行都代表一个到货记录，相同订单的多条记录被多次计算
|
||
|
||
### 影响倍数
|
||
|
||
899% ≈ 9倍重复，表示：
|
||
- 应该换单数被计算了 9 倍
|
||
- 如果原本应该是 100 个，被计算成了 900 个
|
||
- 导致 24H换单率 = 100 / 900 ≈ 11%... 或者其他值不对
|
||
|
||
---
|
||
|
||
## 修复方案
|
||
|
||
### 修改前
|
||
|
||
```sql
|
||
COUNT(DISTINCT ar.NeutralWaybillNumber) AS 高标签率应该换单数
|
||
```
|
||
|
||
### 修改后
|
||
|
||
```sql
|
||
COUNT(DISTINCT CONCAT(ar.BillOfLadingNumber, '|', ar.MasterPackageNumber)) AS 高标签率应该换单数
|
||
```
|
||
|
||
### 修复逻辑
|
||
|
||
**关键改变**：
|
||
- 从统计**订单** (`NeutralWaybillNumber`) 改为统计**交接单** (`BillOfLadingNumber + MasterPackageNumber`)
|
||
- 因为冻结标签率是**按交接单计算**的（InterchangeUnitLabelRates）
|
||
- 一个交接单 = 一个 BillOfLadingNumber + MasterPackageNumber 的组合
|
||
- 所以应该统计的是**交接单的个数**，而不是**订单的个数**
|
||
|
||
**为什么用 CONCAT**？
|
||
- MySQL 中的 `COUNT(DISTINCT col1, col2, ...)` 在某些版本可能有问题
|
||
- 使用 `COUNT(DISTINCT CONCAT(col1, '|', col2, ...))` 更安全可靠
|
||
- `'|'` 作为分隔符，避免组合冲突
|
||
|
||
### 修复位置
|
||
|
||
1. **DailyHighLabelRateShould CTE**（第1065-1080行）
|
||
- 改为：`COUNT(DISTINCT CONCAT(ar.BillOfLadingNumber, '|', ar.MasterPackageNumber))`
|
||
|
||
2. **DailyLowLabelRateShould CTE**（第1085-1100行）
|
||
- 改为：`COUNT(DISTINCT CONCAT(ar.BillOfLadingNumber, '|', ar.MasterPackageNumber))`
|
||
|
||
---
|
||
|
||
## 修复验证
|
||
|
||
### 编译状态
|
||
|
||
✅ **编译成功** (exit code 0)
|
||
- 无编译错误
|
||
- 语法正确
|
||
|
||
### 逻辑验证
|
||
|
||
**修复后应该满足**：
|
||
```
|
||
IF 高标签率应该换单数和高标签率考核通过数都是正确的 THEN
|
||
高标签率考核通过数 <= 高标签率应该换单数
|
||
24H换单率 = 高标签率考核通过数 / 高标签率应该换单数 <= 100%
|
||
END IF
|
||
```
|
||
|
||
### 预期结果
|
||
|
||
修复前后对比：
|
||
```
|
||
修复前：
|
||
- 高标签率应该换单数 = 900（被9倍重复）
|
||
- 高标签率考核通过数 = 100
|
||
- 24H换单率 = 100 / 900 ≈ 11% 或其他不合理数值
|
||
|
||
修复后：
|
||
- 高标签率应该换单数 = 100（正确）
|
||
- 高标签率考核通过数 = 100
|
||
- 24H换单率 = 100 / 100 = 100%（合理）
|
||
```
|
||
|
||
---
|
||
|
||
## 为什么这个修复是正确的？
|
||
|
||
### 概念澄清
|
||
|
||
**交接单 vs 订单**：
|
||
- 交接单 = BillOfLadingNumber + MasterPackageNumber（物理单位）
|
||
- 订单 = NeutralWaybillNumber（订单单位）
|
||
- **一个交接单可能包含多个订单**
|
||
- **一个交接单在 ArrivalRequests 中可能有多条记录**（多次到货？多个中转站？）
|
||
|
||
### 为什么要统计交接单而不是订单？
|
||
|
||
因为：
|
||
1. **冻结标签率是按交接单计算的**
|
||
- `InterchangeUnitLabelRates` 按 BillOfLadingNumber + MasterPackageNumber 分组
|
||
- 冻结标签率 = 该交接单中有标签的包裹 / 该交接单的总包裹
|
||
|
||
2. **高标签率应该换单数应该代表交接单数**
|
||
- 表示"多少个交接单属于高标签率"
|
||
- 而不是"多少个订单在高标签率交接单中"
|
||
|
||
3. **这样才符合考核逻辑**
|
||
- 整个交接单按一个冻结标签率处理
|
||
- 所以应该统计的是交接单数
|
||
|
||
### 为什么 ArrivalRequests 会有重复？
|
||
|
||
可能原因：
|
||
1. **多次到货记录**：同一订单分多次到货
|
||
2. **多个中转站**：订单通过多个仓库中转
|
||
3. **数据导入问题**：导入过程中产生了重复
|
||
4. **业务流程**：确实有一对多的关系
|
||
|
||
无论原因如何，统计**交接单**而不是**订单**是更准确的。
|
||
|
||
---
|
||
|
||
## 相关逻辑调整
|
||
|
||
### 没有改动的地方
|
||
|
||
1. **DailyHighLabelRateAssessed**：保持不变
|
||
- 这个CTE是从 `DailyBeforeNoonPassed` 和 `DailyAfternoonPassed` 汇总
|
||
- 这两个CTE 都已经基于订单统计，不存在交接单层面的重复
|
||
|
||
2. **DailyBeforeNoonPassed / DailyAfternoonPassed**：保持不变
|
||
- 这些CTE 是基于 `ArrivalRequests` 的订单维度统计
|
||
- 逻辑是正确的
|
||
|
||
---
|
||
|
||
## 完整的数据流修复后
|
||
|
||
```
|
||
InterchangeUnitLabelRates（按交接单计算冻结标签率）
|
||
↓
|
||
冻结标签率 >= 80% 或 < 80%
|
||
↓
|
||
DailyHighLabelRateShould（按交接单统计，使用CONCAT）✅ 已修复
|
||
DailyLowLabelRateShould（按交接单统计，使用CONCAT）✅ 已修复
|
||
↓
|
||
这是分母
|
||
↓
|
||
24H换单率 = 高标签率考核通过数 / 高标签率应该换单数 <= 100% ✅
|
||
```
|
||
|
||
---
|
||
|
||
## 编译状态
|
||
|
||
✅ **编译成功** (exit code 0)
|
||
- DAL 项目编译通过
|
||
- 无编译错误
|
||
- 已可部署到测试环境
|
||
|
||
---
|
||
|
||
## 建议的后续步骤
|
||
|
||
1. **在测试环境中验证**
|
||
- 运行修复后的SQL
|
||
- 确认 24H换单率 <= 100%
|
||
|
||
2. **数据对比**
|
||
- 对比修复前后的数据差异
|
||
- 分析899%的数据是否来自9倍重复
|
||
|
||
3. **排查ArrivalRequests表**
|
||
- 检查是否真的存在相同NeutralWaybillNumber的多条记录
|
||
- 如果存在，这是业务正常情况还是数据问题
|
||
|
||
4. **文档更新**
|
||
- 更新数据字典
|
||
- 说明高标签率应该换单数的含义（交接单数，不是订单数）
|
||
|