244 lines
9.2 KiB
Markdown
244 lines
9.2 KiB
Markdown
# 冻结标签率设计（Frozen Label Rate Design）
|
||
|
||
## 核心概念
|
||
|
||
### 1. 为什么需要"冻结"标签率？
|
||
|
||
**问题背景**：
|
||
- 标签率是**动态变化**的值，在交接单生命周期中不断变化
|
||
- 简单地用"最终标签率"来判断考核时间会导致逻辑矛盾
|
||
- 需要一种**时间点的快照**来确定考核时间
|
||
|
||
### 2. 冻结标签率的定义
|
||
|
||
**冻结标签率** = 以**最早的扫描时间**为分割线的标签率快照
|
||
|
||
公式：
|
||
```
|
||
冻结标签率 = (最早扫描时间 > 标签推送时间的包裹数) / 总包裹数 × 100%
|
||
```
|
||
|
||
**含义**：
|
||
- **最早扫描时间 > 标签推送时间**的包裹是在**标签推送后才开始作业**的
|
||
- 这部分包裹代表标签已经可用，在高标签率情况下进行的作业
|
||
- **最早扫描时间 ≤ 标签推送时间**的包裹是**标签推送早于或等于作业开始时间**的
|
||
- 这部分包裹代表在低标签率情况下进行的作业
|
||
|
||
---
|
||
|
||
## 设计逻辑
|
||
|
||
### 第一步：确定作业开始时间
|
||
|
||
对于每个交接单（BillOfLadingNumber + MasterPackageNumber）：
|
||
|
||
```sql
|
||
earliest_scan_time = MIN(扫描时间 for all 包裹 in this 交接单)
|
||
```
|
||
|
||
**含义**：以最早的扫描时间作为"开始作业"的时刻
|
||
|
||
### 第二步：计算冻结标签率
|
||
|
||
计算每个交接单的冻结标签率：
|
||
|
||
```
|
||
冻结标签率 = COUNT(最早扫描时间 > 标签推送时间的包裹) / 总包裹数 × 100%
|
||
```
|
||
|
||
**含义**：
|
||
- `最早扫描时间 > 标签推送时间` = 标签在作业开始前就已推送，属于高标签率
|
||
- `最早扫描时间 ≤ 标签推送时间` = 标签在作业开始时或之后推送，属于低标签率
|
||
|
||
### 第三步：根据冻结标签率确定整个交接单的考核规则
|
||
|
||
**关键规则**：根据冻结标签率的整体水平，**整个交接单中的所有包裹都按相同规则处理**
|
||
|
||
| 冻结标签率 | 考核规则 | 说明 |
|
||
|---------|--------|------|
|
||
| ≥ 80% | 到仓时间16点前后分段 | 整个交接单的100%包裹都按此规则处理 |
|
||
| < 80% | 完成即达标（NULL） | 整个交接单的100%包裹都按此规则处理 |
|
||
|
||
**重要说明**：
|
||
- **不是分别处理高低标签率的包裹**，而是以整体冻结标签率为分界
|
||
- 冻结标签率 ≥ 80% → 所有包裹按高标签率规则处理
|
||
- 冻结标签率 < 80% → 所有包裹按低标签率规则处理（完成即达标）
|
||
|
||
---
|
||
|
||
## SQL实现
|
||
|
||
### 1. InterchangeUnitLabelRates CTE (改进版)
|
||
|
||
计算冻结标签率，基于最早扫描时间与标签推送时间的比较：
|
||
|
||
```sql
|
||
InterchangeUnitLabelRates AS (
|
||
SELECT
|
||
l.BillOfLadingNumber,
|
||
l.MasterPackageNumber,
|
||
COUNT(DISTINCT l.Id) AS total_requests,
|
||
-- 冻结标签率：最早扫描时间 > 标签推送时间 的包裹数
|
||
-- 表示在标签推送后才开始作业的包裹（高标签率）
|
||
COUNT(DISTINCT CASE
|
||
WHEN l.Label IS NOT NULL AND l.Label != ''
|
||
AND (
|
||
SELECT MIN(ls.CreatedAt)
|
||
FROM label_scan_history ls
|
||
WHERE ls.NeutralWaybillNumber = l.NeutralWaybillNumber
|
||
) > l.LabelRetrievedAt
|
||
THEN l.Id
|
||
END) AS labeled_requests,
|
||
ROUND(
|
||
COUNT(DISTINCT CASE
|
||
WHEN l.Label IS NOT NULL AND l.Label != ''
|
||
AND (
|
||
SELECT MIN(ls.CreatedAt)
|
||
FROM label_scan_history ls
|
||
WHERE ls.NeutralWaybillNumber = l.NeutralWaybillNumber
|
||
) > l.LabelRetrievedAt
|
||
THEN l.Id
|
||
END) * 100.0 / COUNT(DISTINCT l.Id),
|
||
2
|
||
) AS label_rate_percent
|
||
FROM label_replace_requests l
|
||
GROUP BY l.BillOfLadingNumber, l.MasterPackageNumber
|
||
)
|
||
```
|
||
|
||
### 2. DailyHighLabelRateShould CTE
|
||
|
||
统计标签率维度中"高标签率"的应该换单数：
|
||
|
||
```sql
|
||
DailyHighLabelRateShould AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(lrr.LabelRetrievedAt, '+00:00', '-05:00')) AS 日期,
|
||
COUNT(DISTINCT lrr.NeutralWaybillNumber) AS 高标签率应该换单数
|
||
FROM label_replace_requests lrr
|
||
INNER JOIN (
|
||
-- 为每个交接单找出最早的扫描时间（作业开始时间）
|
||
SELECT
|
||
lrr2.BillOfLadingNumber,
|
||
lrr2.MasterPackageNumber,
|
||
MIN(ls.CreatedAt) AS earliest_scan_time
|
||
FROM label_replace_requests lrr2
|
||
INNER JOIN label_scan_history ls ON lrr2.NeutralWaybillNumber = ls.NeutralWaybillNumber
|
||
GROUP BY lrr2.BillOfLadingNumber, lrr2.MasterPackageNumber
|
||
) earliest_times ON lrr.BillOfLadingNumber = earliest_times.BillOfLadingNumber
|
||
AND lrr.MasterPackageNumber = earliest_times.MasterPackageNumber
|
||
WHERE
|
||
-- 最早扫描时间 > 标签推送时间 = 在标签推送后才开始作业（高标签率）
|
||
earliest_times.earliest_scan_time > lrr.LabelRetrievedAt
|
||
GROUP BY DATE(CONVERT_TZ(lrr.LabelRetrievedAt, '+00:00', '-05:00'))
|
||
)
|
||
```
|
||
|
||
### 3. DailyLowLabelRateShould CTE
|
||
|
||
统计标签率维度中"低标签率"的应该换单数：
|
||
|
||
```sql
|
||
DailyLowLabelRateShould AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(lrr.LabelRetrievedAt, '+00:00', '-05:00')) AS 日期,
|
||
COUNT(DISTINCT lrr.NeutralWaybillNumber) AS 低标签率应该换单数
|
||
FROM label_replace_requests lrr
|
||
LEFT JOIN (
|
||
-- 为每个交接单找出最早的扫描时间（作业开始时间）
|
||
SELECT
|
||
lrr2.BillOfLadingNumber,
|
||
lrr2.MasterPackageNumber,
|
||
MIN(ls.CreatedAt) AS earliest_scan_time
|
||
FROM label_replace_requests lrr2
|
||
INNER JOIN label_scan_history ls ON lrr2.NeutralWaybillNumber = ls.NeutralWaybillNumber
|
||
GROUP BY lrr2.BillOfLadingNumber, lrr2.MasterPackageNumber
|
||
) earliest_times ON lrr.BillOfLadingNumber = earliest_times.BillOfLadingNumber
|
||
AND lrr.MasterPackageNumber = earliest_times.MasterPackageNumber
|
||
WHERE
|
||
-- 最早扫描时间 <= 标签推送时间 OR 没有扫描记录（无earliest_scan_time）
|
||
-- 表示标签推送早于或等于作业开始时间（低标签率）
|
||
(earliest_times.earliest_scan_time IS NULL OR
|
||
earliest_times.earliest_scan_time <= lrr.LabelRetrievedAt)
|
||
GROUP BY DATE(CONVERT_TZ(lrr.LabelRetrievedAt, '+00:00', '-05:00'))
|
||
)
|
||
```
|
||
|
||
---
|
||
|
||
## 验证场景
|
||
|
||
### 场景：100个订单在同一交接单中
|
||
|
||
**初始状态**：
|
||
- 最早扫描时间 (earliest_scan_time) = 2026-05-16 10:00:00
|
||
- 总订单数 = 100
|
||
|
||
**标签推送时间分布**：
|
||
- 推送于 09:00-10:00（30个订单）→ 扫描时间 > 标签推送时间，属于高标签率
|
||
- 推送于 10:00-11:00（50个订单）→ 扫描时间 = 标签推送时间，属于低标签率
|
||
- 推送于 11:00-12:00（20个订单）→ 扫描时间 < 标签推送时间，属于低标签率
|
||
|
||
**冻结标签率计算**：
|
||
```
|
||
冻结标签率 = (最早扫描时间 > 标签推送时间的包裹数) / 100
|
||
= (30个订单，推送于09:00-10:00) / 100
|
||
= 30%
|
||
```
|
||
|
||
**考核规则应用**（整体规则，不是逐个）：
|
||
```
|
||
因为 冻结标签率 = 30% < 80%
|
||
→ 整个交接单的100个包裹都按照"完成即达标"规则处理
|
||
→ 无需考虑到仓时间16点前后的分段
|
||
```
|
||
|
||
**关键点**：
|
||
- 虽然30个包裹属于"高标签率"范畴，70个属于"低标签率"范畴
|
||
- 但因为整体冻结标签率 < 80%，**所有100个包裹都执行低标签率规则**
|
||
- 高标签率应该换单数 = 30（用于统计，不影响考核规则）
|
||
- 低标签率应该换单数 = 70（用于统计）
|
||
|
||
---
|
||
|
||
## 关键优势
|
||
|
||
1. **逻辑清晰**：只需比较两个时间点
|
||
2. **准确反映**：体现现场实际的标签供应情况
|
||
3. **消除矛盾**：避免"成功数<考核通过数"的数学矛盾
|
||
4. **自动分类**：每个包裹根据其标签推送时间自动归类
|
||
5. **性能友好**：避免复杂的历史快照计算
|
||
|
||
---
|
||
|
||
## 实施步骤
|
||
|
||
✅ 已完成：
|
||
1. 修改 `InterchangeUnitLabelRates` CTE 为冻结标签率计算
|
||
2. 新增 `DailyHighLabelRateShould` CTE
|
||
3. 新增 `DailyLowLabelRateShould` CTE
|
||
4. 修改最终SELECT中的24小时换单率计算（分母为高标签率应该换单数）
|
||
|
||
📋 待测试：
|
||
1. 在测试环境中验证冻结标签率的计算准确性
|
||
2. 验证考核时间的正确性
|
||
3. 验证各项统计指标的合理性
|
||
4. 对比"冻结标签率"与"最终标签率"的差异
|
||
|
||
---
|
||
|
||
## 相关字段映射
|
||
|
||
| 业务概念 | 数据库字段 | 说明 |
|
||
|---------|----------|------|
|
||
| 标签推送时间 | `label_replace_requests.LabelRetrievedAt` | 标签被推送/获取的时刻 |
|
||
| 最早扫描时间 | `label_scan_history.CreatedAt` | 交接单中最早的包裹扫描时刻 |
|
||
| 冻结标签率 | `InterchangeUnitLabelRates.label_rate_percent` | 根据最早扫描时间计算的标签率快照 |
|
||
| 高标签率应该换单数 | `DailyHighLabelRateShould.高标签率应该换单数` | 冻结标签率 ≥ 80% 的交接单中的全部包裹数 |
|
||
| 低标签率应该换单数 | `DailyLowLabelRateShould.低标签率应该换单数` | 冻结标签率 < 80% 的交接单中的全部包裹数 |
|
||
|
||
**特别说明**：
|
||
- `高标签率应该换单数`：统计所有冻结标签率≥80%的交接单中的所有包裹，这些包裹将按照"到仓时间16点前后分段"规则处理
|
||
- `低标签率应该换单数`：统计所有冻结标签率<80%的交接单中的所有包裹，这些包裹将按照"完成即达标"规则处理
|
||
|