226 lines
6.1 KiB
Markdown
226 lines
6.1 KiB
Markdown
# SQL逻辑错误修复方案
|
||
|
||
## 问题诊断
|
||
|
||
**用户发现的逻辑问题**：
|
||
```
|
||
当日换单成功数 < 16点前考核通过数 + 16点后考核通过数
|
||
```
|
||
|
||
这违反了基本的数学关系：**考核通过的包裹数 ≤ 成功的包裹数**
|
||
|
||
---
|
||
|
||
## 根本原因
|
||
|
||
### 当前SQL的日期维度混乱
|
||
|
||
| CTE | 日期维度 | 计算逻辑 | 问题 |
|
||
|-----|---------|---------|------|
|
||
| **DailySuccessCount** | 扫描日期 | 按扫描成功时的日期 | ❌ 不同维度 |
|
||
| **DailyBeforeNoonPassed** | 到货日期 | 按订单到货时的日期 | ❌ 不同维度 |
|
||
| **DailyAfternoonPassed** | 到货日期 | 按订单到货时的日期 | ❌ 不同维度 |
|
||
|
||
### 导致的结果
|
||
|
||
```
|
||
示例：
|
||
订单A: 到货日期=05-15, 首次成功日期=05-16, 到货时间=15:30(16点前)
|
||
|
||
当前统计：
|
||
- 05-15 的"16点前考核通过数"包含订单A ❌（错误！订单A 05-15还未成功）
|
||
- 05-16 的"当日换单成功数"包含订单A ✓
|
||
- 结果：05-15 的考核通过数可能 > 成功数（矛盾！）
|
||
```
|
||
|
||
---
|
||
|
||
## 正确的逻辑
|
||
|
||
### 所有指标都应该按"首次成功日期"分组
|
||
|
||
```sql
|
||
当日换单成功数 = COUNT(DISTINCT 首次成功日期=当天的包裹)
|
||
|
||
16点前考核通过 = COUNT(DISTINCT
|
||
首次成功日期=当天
|
||
AND 满足考核时间
|
||
AND 到货时间<16点 的包裹)
|
||
|
||
16点后考核通过 = COUNT(DISTINCT
|
||
首次成功日期=当天
|
||
AND 满足考核时间
|
||
AND 到货时间≥16点 的包裹)
|
||
```
|
||
|
||
### 数学关系
|
||
|
||
```
|
||
当日换单成功数 = 16点前成功数 + 16点后成功数
|
||
当日考核通过数 = 16点前考核通过 + 16点后考核通过
|
||
考核通过数 ≤ 成功数 ✓
|
||
```
|
||
|
||
---
|
||
|
||
## 修复步骤
|
||
|
||
### 步骤1：修改DailySuccessCount
|
||
|
||
**当前（错误）**：
|
||
```sql
|
||
DailySuccessCount AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期, ← 扫描日期
|
||
...
|
||
```
|
||
|
||
**改为（正确）**：
|
||
```sql
|
||
DailySuccessCount AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期, ← 首次成功日期
|
||
COUNT(DISTINCT oss.NeutralWaybillNumber) AS 当日换单成功数
|
||
FROM OverallScanStatus oss
|
||
INNER JOIN ArrivalRequests ar ON oss.NeutralWaybillNumber = ar.NeutralWaybillNumber
|
||
WHERE oss.曾成功 = 1 AND oss.首次成功时间 IS NOT NULL
|
||
GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
|
||
)
|
||
```
|
||
|
||
### 步骤2：修改DailyBeforeNoonPassed
|
||
|
||
**当前（错误）**：
|
||
```sql
|
||
DailyBeforeNoonPassed AS (
|
||
SELECT
|
||
ar.到货日期 AS 日期, ← 到货日期（错！）
|
||
COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点前考核通过包裹数
|
||
FROM ArrivalRequests ar
|
||
...
|
||
GROUP BY ar.到货日期
|
||
)
|
||
```
|
||
|
||
**改为（正确）**：
|
||
```sql
|
||
DailyBeforeNoonPassed AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期, ← 首次成功日期
|
||
COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点前考核通过包裹数
|
||
FROM ArrivalRequests ar
|
||
INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
|
||
WHERE
|
||
oss.曾成功 = 1
|
||
AND oss.首次成功时间 IS NOT NULL
|
||
-- 16点前到仓
|
||
AND HOUR(ar.到货时间) < 16
|
||
-- 满足考核时间
|
||
AND (
|
||
(ar.考核时间 IS NOT NULL AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间)
|
||
OR (ar.考核时间 IS NULL)
|
||
)
|
||
GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
|
||
)
|
||
```
|
||
|
||
### 步骤3：修改DailyAfternoonPassed
|
||
|
||
**当前（错误）**：
|
||
```sql
|
||
DailyAfternoonPassed AS (
|
||
SELECT
|
||
ar.到货日期 AS 日期, ← 到货日期（错！）
|
||
COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点后考核通过包裹数
|
||
FROM ArrivalRequests ar
|
||
...
|
||
GROUP BY ar.到货日期
|
||
)
|
||
```
|
||
|
||
**改为（正确）**：
|
||
```sql
|
||
DailyAfternoonPassed AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期, ← 首次成功日期
|
||
COUNT(DISTINCT ar.NeutralWaybillNumber) AS 16点后考核通过包裹数
|
||
FROM ArrivalRequests ar
|
||
INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
|
||
WHERE
|
||
oss.曾成功 = 1
|
||
AND oss.首次成功时间 IS NOT NULL
|
||
-- 16点后到仓
|
||
AND HOUR(ar.到货时间) >= 16
|
||
-- 满足考核时间
|
||
AND (
|
||
(ar.考核时间 IS NOT NULL AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间)
|
||
OR (ar.考核时间 IS NULL)
|
||
)
|
||
GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
|
||
)
|
||
```
|
||
|
||
---
|
||
|
||
## 修改影响分析
|
||
|
||
### 直接影响的CTE
|
||
|
||
- ✏️ `DailySuccessCount` - 修改日期维度
|
||
- ✏️ `DailyBeforeNoonPassed` - 修改日期维度 + JOIN逻辑
|
||
- ✏️ `DailyAfternoonPassed` - 修改日期维度 + JOIN逻辑
|
||
|
||
### 间接受影响的CTE
|
||
|
||
- `DailyStatsWithPrev` - 需要验证是否需要调整
|
||
- 最终SELECT - 可能需要调整来源
|
||
|
||
### 可以删除的CTE
|
||
|
||
- ❌ `Daily24HCompletedOrders` - 现在已被DailySuccessCount覆盖
|
||
- ❌ `DailyCompletedOrders` - 现在已被DailySuccessCount覆盖（如果只关心成功包裹）
|
||
|
||
---
|
||
|
||
## 验证修改后的逻辑
|
||
|
||
修改后应该满足：
|
||
|
||
```
|
||
∀日期d:
|
||
当日换单成功数(d)
|
||
= 16点前首次成功数(d) + 16点后首次成功数(d)
|
||
|
||
16点前考核通过(d) ≤ 16点前首次成功数(d)
|
||
16点后考核通过(d) ≤ 16点后首次成功数(d)
|
||
|
||
当日换单成功数(d) ≥ 当日考核通过数(d)
|
||
```
|
||
|
||
---
|
||
|
||
## 报表对比
|
||
|
||
### 修改前（错误）
|
||
```
|
||
日期 当日成功数 16点前考核 16点后考核 检查
|
||
05-16 50 60 20 ❌ 60+20 > 50 (矛盾！)
|
||
```
|
||
|
||
### 修改后（正确）
|
||
```
|
||
日期 当日成功数 16点前成功 16点前考核 16点后成功 16点后考核 检查
|
||
05-16 80 50 45 30 20 ✅ 80=50+30, 65≤80
|
||
```
|
||
|
||
---
|
||
|
||
## 总结
|
||
|
||
**核心修改原则**：
|
||
|
||
所有关于"成功"和"考核通过"的统计，都必须基于**首次成功日期**，而不是**到货日期**或**扫描日期**。
|
||
|
||
这样才能保证：**考核通过数 ≤ 成功数** 的基本逻辑关系。
|
||
|