758 lines
28 KiB
Markdown
758 lines
28 KiB
Markdown
# 考核时间设计合理性分析（修订版）
|
||
|
||
## 核心概念澄清
|
||
|
||
### 用户业务规则说明
|
||
|
||
1. **标签率在考核时的状态**：
|
||
- 标签率本身确实是动态的，在实时数据中持续变化
|
||
- **但在对交接单中的包裹进行作业时，该时刻的标签率就成为考核计算的"最终值"**
|
||
- 即：开始对某个交接单进行考核操作时，此时的标签率被视为该交接单的考核基准标签率
|
||
|
||
2. **标签率跨越80%阈值的处理**：
|
||
- 如果在考核期间标签率从<80%跨越到≥80%，需要特殊处理
|
||
- 依据：**单个订单的标签推送时间**（在 `label_replace_requests.sql` 中有该字段）
|
||
- 逻辑：根据标签推送时间判断哪些包裹是在标签率还未达到80%时进行的作业
|
||
- 对于跨越阈值的包裹：
|
||
- 推送时间在标签率<80%期间 → 按照低标签率逻辑（完成即达标）
|
||
- 推送时间在标签率≥80%期间 → 按照高标签率逻辑（使用到仓时间的16点分段）
|
||
|
||
3. **单个订单维度的差异**：
|
||
- 同一交接单中的不同订单，**可以根据各自的标签推送时间而有不同的考核时间**
|
||
- 低于80%的订单：以换单完成时间作为考核时间 = 完成即达标
|
||
- 80%及以上的订单：以到仓时间的16点前/后作为区分
|
||
|
||
---
|
||
|
||
## 问题陈述（修订）
|
||
|
||
在现有的报表统计逻辑中存在的问题：
|
||
|
||
### 问题1：标签率跨越阈值时的处理缺失
|
||
|
||
当交接单的标签率从<80%动态上升到≥80%时，**不能简单地用当前时刻的标签率来判断所有包裹的考核时间**。
|
||
|
||
应该区分：
|
||
- **A类包裹**：标签推送时间在标签率<80%期间 → 考核时间 = NULL（完成即达标）
|
||
- **B类包裹**：标签推送时间在标签率≥80%期间 → 考核时间 = 根据到仓时间的16点分段
|
||
|
||
### 问题2：当前SQL中对单个订单差异的忽视
|
||
|
||
当前的SQL在计算 `InterchangeUnitLabelRates` 时：
|
||
```sql
|
||
InterchangeUnitLabelRates AS (
|
||
SELECT
|
||
l.BillOfLadingNumber,
|
||
l.MasterPackageNumber,
|
||
ROUND(
|
||
COUNT(DISTINCT CASE WHEN l.Label IS NOT NULL ...) * 100.0 /
|
||
COUNT(DISTINCT l.Id), 2
|
||
) AS label_rate_percent
|
||
FROM label_replace_requests l
|
||
GROUP BY l.BillOfLadingNumber, l.MasterPackageNumber
|
||
)
|
||
```
|
||
|
||
这是按交接单汇总的整体标签率，忽视了**单个订单在不同时间被标签化**的情况。
|
||
|
||
---
|
||
|
||
## 解决方案设计
|
||
|
||
### 核心设计思路（简化版）
|
||
|
||
**关键洞察**：
|
||
- 交接单中**最早的包裹完成时间** = 何时开始作业
|
||
- 交接单中**标签率达到80%的时间** = 何时达到高标签率
|
||
- 这两个时间的比较关系决定了包裹的考核规则
|
||
|
||
```
|
||
对于交接单中的每个包裹：
|
||
|
||
步骤1：确定两个关键时间点
|
||
├─ 最早完成时间(earliest_success) = MIN(该交接单内所有包裹首次成功时间)
|
||
├─ 标签率达80%时间(label_rate_80_time) = 该交接单标签率首次达到80%的时间
|
||
└─ 标签推送时间(label_pushed_at) = 该订单标签被推送的时间
|
||
|
||
步骤2：比较标签推送时间和标签率达80%时间
|
||
├─ IF label_pushed_at < label_rate_80_time:
|
||
│ ├─ 说明该订单的标签是在标签率<80%时推送的
|
||
│ ├─ 归类：低标签率订单
|
||
│ └─ 考核规则：NULL（完成即达标）
|
||
└─ ELSE (label_pushed_at >= label_rate_80_time):
|
||
├─ 说明该订单的标签是在标签率≥80%时推送的
|
||
├─ 归类：高标签率订单
|
||
└─ 考核规则：根据到仓时间的16点分段
|
||
|
||
步骤3：高标签率订单再按到仓时间分段
|
||
├─ IF 到仓时间.hour < 16:
|
||
│ └─ 考核时间 = 次日 16:00:00
|
||
└─ ELSE:
|
||
└─ 考核时间 = 次日 23:59:59
|
||
```
|
||
|
||
### 验证逻辑合理性
|
||
|
||
```
|
||
为什么这个设计有效：
|
||
|
||
1. 最早完成时间代表"开始作业时刻"
|
||
└─ 交接单内的包裹从此刻开始被处理
|
||
|
||
2. 标签率达80%时间是"达到高承诺的临界点"
|
||
└─ 在此之前推送的标签属于低标签率期间
|
||
└─ 在此之后推送的标签属于高标签率期间
|
||
|
||
3. 标签推送时间是判断标准
|
||
└─ 不需要计算"此时的标签率"
|
||
└─ 只需要比较时间大小关系
|
||
└─ 逻辑清晰，性能高效
|
||
|
||
4. 自动满足数学关系
|
||
└─ 当日换单成功数 = 最早完成时间在该日期的包裹
|
||
└─ 当日考核通过数 ≤ 当日换单成功数
|
||
└─ 恒成立！
|
||
```
|
||
|
||
### 具体逻辑实现
|
||
|
||
**步骤1：为每个交接单找出两个关键时间点**
|
||
|
||
```sql
|
||
WITH InterchangeUnitKeyTimes AS (
|
||
SELECT
|
||
iu.BillOfLadingNumber,
|
||
iu.MasterPackageNumber,
|
||
-- 该交接单中最早的包裹完成时间
|
||
MIN(oss.首次成功时间) AS earliest_success_time,
|
||
-- 该交接单标签率首次达到80%的时间
|
||
(SELECT MIN(label_pushed_at)
|
||
FROM label_replace_requests l2
|
||
WHERE l2.BillOfLadingNumber = iu.BillOfLadingNumber
|
||
AND l2.MasterPackageNumber = iu.MasterPackageNumber
|
||
AND (SELECT
|
||
COUNT(DISTINCT CASE WHEN Label IS NOT NULL THEN Id END) * 100.0 /
|
||
COUNT(DISTINCT Id)
|
||
FROM label_replace_requests l3
|
||
WHERE l3.BillOfLadingNumber = iu.BillOfLadingNumber
|
||
AND l3.MasterPackageNumber = iu.MasterPackageNumber
|
||
AND l3.label_pushed_at <= l2.label_pushed_at) >= 80
|
||
) AS label_rate_80_time
|
||
FROM interchange_units iu
|
||
LEFT JOIN OverallScanStatus oss ON iu.BillOfLadingNumber = oss.BillOfLadingNumber
|
||
GROUP BY iu.BillOfLadingNumber, iu.MasterPackageNumber
|
||
)
|
||
|
||
-- 结果示例：
|
||
-- BillOfLadingNumber | MasterPackageNumber | earliest_success_time | label_rate_80_time
|
||
-- BOL001 | MP001 | 2026-05-16 10:30:00 | 2026-05-15 17:45:00
|
||
-- 说明：这个交接单最早在10:30完成，标签率在17:45达到80%
|
||
```
|
||
|
||
**步骤2：为每个订单确定标签率归类和考核时间**
|
||
|
||
```sql
|
||
WITH SubscriptionAssessmentTime AS (
|
||
SELECT
|
||
l.Id AS subscription_id,
|
||
l.NeutralWaybillNumber,
|
||
l.BillOfLadingNumber,
|
||
l.MasterPackageNumber,
|
||
l.label_pushed_at,
|
||
ar.到货时间,
|
||
ar.到货日期,
|
||
iut.label_rate_80_time,
|
||
|
||
-- 判断该订单的标签率归类
|
||
CASE
|
||
WHEN iut.label_rate_80_time IS NULL THEN
|
||
-- 交接单标签率始终<80%
|
||
'低标签率'
|
||
WHEN l.label_pushed_at < iut.label_rate_80_time THEN
|
||
-- 该订单推送时标签率<80%
|
||
'低标签率'
|
||
ELSE
|
||
-- 该订单推送时标签率≥80%
|
||
'高标签率'
|
||
END AS label_rate_category,
|
||
|
||
-- 根据标签率归类确定考核时间
|
||
CASE
|
||
WHEN iut.label_rate_80_time IS NULL THEN
|
||
-- 标签率始终<80%
|
||
NULL -- 完成即达标
|
||
WHEN l.label_pushed_at < iut.label_rate_80_time THEN
|
||
-- 该订单标签推送时标签率<80%
|
||
NULL -- 完成即达标
|
||
ELSE
|
||
-- 该订单标签推送时标签率≥80%，按到仓时间的16点分段
|
||
CASE
|
||
WHEN HOUR(ar.到货时间) < 16
|
||
THEN CONCAT(DATE_ADD(DATE(ar.到货时间), INTERVAL 1 DAY), ' 16:00:00')
|
||
ELSE CONCAT(DATE_ADD(DATE(ar.到货时间), INTERVAL 1 DAY), ' 23:59:59')
|
||
END
|
||
END AS assessment_time,
|
||
|
||
-- 记录判断依据（便于审计）
|
||
CASE
|
||
WHEN iut.label_rate_80_time IS NULL THEN 'never_reached_80'
|
||
WHEN l.label_pushed_at < iut.label_rate_80_time THEN 'pushed_before_80'
|
||
ELSE 'pushed_after_80'
|
||
END AS classification_reason
|
||
|
||
FROM label_replace_requests l
|
||
INNER JOIN ArrivalRequests ar ON l.NeutralWaybillNumber = ar.NeutralWaybillNumber
|
||
LEFT JOIN InterchangeUnitKeyTimes iut ON l.BillOfLadingNumber = iut.BillOfLadingNumber
|
||
AND l.MasterPackageNumber = iut.MasterPackageNumber
|
||
)
|
||
|
||
-- 结果示例：
|
||
-- subscription_id | label_rate_category | assessment_time | classification_reason
|
||
-- 1001 | 低标签率 | NULL | pushed_before_80
|
||
-- 1002 | 高标签率 | 2026-05-16 16:00:00 | pushed_after_80
|
||
-- 1003 | 低标签率 | NULL | never_reached_80
|
||
```
|
||
|
||
**步骤3：用于报表统计的最终SELECT**
|
||
|
||
```sql
|
||
-- 对于DailyHighLabelRateShould和DailyLowLabelRateShould的统计
|
||
SELECT
|
||
DATE(CONVERT_TZ(l.label_pushed_at, '+00:00', '-05:00')) AS 日期,
|
||
CASE
|
||
WHEN (SELECT MIN(label_pushed_at)
|
||
FROM label_replace_requests l2
|
||
WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
|
||
AND l2.MasterPackageNumber = l.MasterPackageNumber
|
||
AND (SELECT
|
||
COUNT(DISTINCT CASE WHEN Label IS NOT NULL THEN Id END) * 100.0 /
|
||
COUNT(DISTINCT Id)
|
||
FROM label_replace_requests l3
|
||
WHERE l3.BillOfLadingNumber = l.BillOfLadingNumber
|
||
AND l3.MasterPackageNumber = l.MasterPackageNumber
|
||
AND l3.label_pushed_at <= l2.label_pushed_at) >= 80) IS NULL
|
||
OR l.label_pushed_at <
|
||
(SELECT MIN(label_pushed_at)
|
||
FROM label_replace_requests l2
|
||
WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
|
||
AND l2.MasterPackageNumber = l.MasterPackageNumber
|
||
AND (SELECT
|
||
COUNT(DISTINCT CASE WHEN Label IS NOT NULL THEN Id END) * 100.0 /
|
||
COUNT(DISTINCT Id)
|
||
FROM label_replace_requests l3
|
||
WHERE l3.BillOfLadingNumber = l.BillOfLadingNumber
|
||
AND l3.MasterPackageNumber = l.MasterPackageNumber
|
||
AND l3.label_pushed_at <= l2.label_pushed_at) >= 80)
|
||
THEN '低标签率'
|
||
ELSE '高标签率'
|
||
END AS label_rate_category,
|
||
COUNT(DISTINCT l.NeutralWaybillNumber) AS count
|
||
FROM label_replace_requests l
|
||
GROUP BY 日期, label_rate_category
|
||
```
|
||
|
||
---
|
||
|
||
## 数据库结构分析
|
||
|
||
### label_replace_requests 表结构
|
||
|
||
根据 `label_replace_requests.sql`，该表包含：
|
||
- `Id`: 订单ID
|
||
- `NeutralWaybillNumber`: 中性单号
|
||
- `BillOfLadingNumber`: 提单号
|
||
- `MasterPackageNumber`: 主包号
|
||
- `Label`: 标签内容
|
||
- **`LabelPushedAt` 或 `label_pushed_at`**: 标签推送时间 ← **关键字段**
|
||
- `CreatedAt`: 创建时间
|
||
- 其他字段...
|
||
|
||
**关键字段说明**：`label_pushed_at` 记录的是该订单的标签被推送到系统的时刻，这是判断"该订单在标签率多少时被标签化"的关键依据。
|
||
|
||
---
|
||
|
||
## 改进建议
|
||
|
||
### 建议1：利用两个关键时间点简化逻辑
|
||
|
||
不需要复杂的历史标签率快照，只需要：
|
||
|
||
```sql
|
||
-- 两个关键时间点
|
||
1. earliest_success_time = MIN(首次成功时间)
|
||
-- 交接单中最早的包裹何时完成
|
||
|
||
2. label_rate_80_time = 标签率首次达到80%时的最早标签推送时间
|
||
-- 标签率在何时达到80%
|
||
```
|
||
|
||
**优势**：
|
||
- 逻辑清晰：只涉及两个时间的比较
|
||
- 无需历史数据：不需要追溯历史标签率
|
||
- 自动分类：标签推送时即自动归类
|
||
- 高效可靠：基于确定的数据事实
|
||
|
||
### 建议2：在标签推送时自动计算考核时间
|
||
|
||
```sql
|
||
-- 伪代码
|
||
WHEN label IS PUSHED:
|
||
DO:
|
||
1. 获取该订单所属交接单的 label_rate_80_time
|
||
2. IF label_pushed_at < label_rate_80_time THEN
|
||
assessment_time = NULL -- 完成即达标
|
||
ELSE
|
||
assessment_time = 根据到仓时间的16点分段
|
||
3. SAVE assessment_time to database
|
||
```
|
||
|
||
**好处**：
|
||
- 考核时间一旦确定就不变（冻结值）
|
||
- 报表统计直接读取，无需动态计算
|
||
- 完全支持审计追溯
|
||
|
||
### 建议3：在报表统计中利用已保存的考核时间
|
||
|
||
```sql
|
||
-- 不是这样动态计算：
|
||
-- SELECT COUNT(*)
|
||
-- FROM label_replace_requests
|
||
-- WHERE 标签率 >= 80% ← 需要实时计算
|
||
|
||
-- 而是这样直接统计：
|
||
SELECT COUNT(*)
|
||
FROM label_replace_requests
|
||
WHERE label_rate_80_time IS NOT NULL AND label_pushed_at >= label_rate_80_time
|
||
-- ← 直接从已保存的字段读取
|
||
```
|
||
|
||
**优势**：
|
||
- 查询性能提升
|
||
- 结果稳定可复现
|
||
- 无需重复计算
|
||
|
||
---
|
||
|
||
## 时间维度梳理
|
||
|
||
为了避免混淆，明确各个时间字段的含义：
|
||
|
||
| 字段名 | 含义 | 用途 | 由谁设定 |
|
||
|--------|------|------|---------|
|
||
| `arrival_time` | 包裹到货时间 | 16点分段判断 | 到货系统 |
|
||
| `label_pushed_at` | 该订单的标签被推送时间 | 判断阈值跨越 | 标签推送系统 |
|
||
| `assessment_reference_time` | 考核计算的参考时刻 | 作为标签率快照的时间点 | 考核系统 |
|
||
| `first_success_time` | 包裹首次成功时间 | 与考核时间比较 | 扫描系统 |
|
||
| `assessment_time` | 考核时间（冻结值） | 判断是否考核通过 | 考核系统计算得出 |
|
||
|
||
---
|
||
|
||
## 考核通过总数的定义和计算
|
||
|
||
### 核心指标定义
|
||
|
||
**考核通过总数** = 满足以下条件的包裹总数：
|
||
|
||
```
|
||
满足考核条件的包裹 = A类 + B类
|
||
|
||
A类包裹：标签率≥80%且成功完成考核
|
||
├─ 条件1: 标签推送时间在标签率≥80%时期（或标签率始终≥80%）
|
||
├─ 条件2: 根据到仓时间的16点分段确定的考核时间
|
||
├─ 条件3: 首次成功时间 <= 考核时间
|
||
└─ 结论: 考核通过 ✓
|
||
|
||
B类包裹：标签率<80%且曾经成功
|
||
├─ 条件1: 标签推送时间在标签率<80%时期（或标签率始终<80%）
|
||
├─ 条件2: 考核时间 = NULL（完成即达标）
|
||
├─ 条件3: 曾经成功 = 1（已有首次成功时间）
|
||
└─ 结论: 考核通过 ✓
|
||
```
|
||
|
||
### 公式表示
|
||
|
||
```
|
||
考核通过总数 = (标签率≥80%的16点前考核通过包裹数)
|
||
+ (标签率≥80%的16点后考核通过包裹数)
|
||
+ (标签率<80%且曾经成功的包裹数)
|
||
|
||
其中：
|
||
- 16点前考核通过包裹数 = 16点前到仓 AND 标签率≥80% AND 首次成功时间≤考核时间
|
||
- 16点后考核通过包裹数 = 16点后到仓 AND 标签率≥80% AND 首次成功时间≤考核时间
|
||
- 低标签率成功包裹数 = 标签率<80% AND 曾经成功=1
|
||
```
|
||
|
||
### SQL实现示例
|
||
|
||
```sql
|
||
-- 在DailyLabelStatsChineseAsync的最终SELECT中新增
|
||
|
||
-- 步骤1：先计算按标签率分类的应该换单数
|
||
WithLabelRateClassification AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(l.label_pushed_at, '+00:00', '-05:00')) AS 日期,
|
||
l.BillOfLadingNumber,
|
||
l.MasterPackageNumber,
|
||
l.NeutralWaybillNumber,
|
||
-- 计算该订单被标签化时的标签率
|
||
(SELECT
|
||
ROUND(
|
||
COUNT(DISTINCT CASE WHEN Label IS NOT NULL AND Label != '' THEN Id END) * 100.0 /
|
||
COUNT(DISTINCT Id), 2
|
||
)
|
||
FROM label_replace_requests l2
|
||
WHERE l2.BillOfLadingNumber = l.BillOfLadingNumber
|
||
AND l2.MasterPackageNumber = l.MasterPackageNumber
|
||
AND l2.label_pushed_at <= l.label_pushed_at
|
||
) AS label_rate_at_push_time,
|
||
CASE
|
||
WHEN (SELECT ...) >= 80 THEN '高标签率'
|
||
ELSE '低标签率'
|
||
END AS label_rate_category
|
||
FROM label_replace_requests l
|
||
),
|
||
|
||
DailyHighLabelRateShould AS (
|
||
SELECT
|
||
日期,
|
||
COUNT(DISTINCT NeutralWaybillNumber) AS 高标签率应该换单数
|
||
FROM WithLabelRateClassification
|
||
WHERE label_rate_category = '高标签率'
|
||
GROUP BY 日期
|
||
),
|
||
|
||
DailyLowLabelRateShould AS (
|
||
SELECT
|
||
日期,
|
||
COUNT(DISTINCT NeutralWaybillNumber) AS 低标签率应该换单数
|
||
FROM WithLabelRateClassification
|
||
WHERE label_rate_category = '低标签率'
|
||
GROUP BY 日期
|
||
),
|
||
|
||
-- 步骤2：最终SELECT
|
||
SELECT
|
||
日期,
|
||
...
|
||
|
||
-- 新增字段：标签率维度的应该换单数
|
||
COALESCE(dhls.高标签率应该换单数, 0) AS 标签率80%及以上应该换单数,
|
||
COALESCE(dlls.低标签率应该换单数, 0) AS 标签率80%以下应该换单数,
|
||
(COALESCE(dhls.高标签率应该换单数, 0)
|
||
+ COALESCE(dlls.低标签率应该换单数, 0)) AS 当日应该换单数,
|
||
|
||
当日换单成功数,
|
||
|
||
-- 新增字段：24小时换单率（正确定义）
|
||
-- 分子：考核通过总数（所有类别的考核通过）
|
||
-- 分母：标签率≥80%的应该换单数（对客户的高承诺）
|
||
-- 含义：实际履约完成的所有订单，占应该完成的高标签率订单的比例
|
||
CASE
|
||
WHEN COALESCE(dhls.高标签率应该换单数, 0) = 0 THEN '0.00%'
|
||
ELSE CONCAT(
|
||
ROUND(
|
||
(COALESCE(dbc.16点前考核通过包裹数, 0)
|
||
+ COALESCE(dac.16点后考核通过包裹数, 0)
|
||
+ COALESCE(dllrp.低标签率考核通过包裹数, 0))
|
||
/ COALESCE(dhls.高标签率应该换单数, 1) * 100,
|
||
2
|
||
),
|
||
'%'
|
||
)
|
||
END AS 24小时换单率,
|
||
|
||
-- 新增字段：考核通过总数
|
||
(COALESCE(dbc.16点前考核通过包裹数, 0)
|
||
+ COALESCE(dac.16点后考核通过包裹数, 0)
|
||
+ COALESCE(dllrp.低标签率考核通过包裹数, 0)) AS 考核通过总数,
|
||
|
||
-- 新增字段：考核通过率（针对所有成功包裹）
|
||
CASE
|
||
WHEN COALESCE(dsc.当日换单成功数, 0) = 0 THEN '0.00%'
|
||
ELSE CONCAT(
|
||
ROUND(
|
||
(COALESCE(dbc.16点前考核通过包裹数, 0)
|
||
+ COALESCE(dac.16点后考核通过包裹数, 0)
|
||
+ COALESCE(dllrp.低标签率考核通过包裹数, 0))
|
||
/ COALESCE(dsc.当日换单成功数, 1) * 100,
|
||
2
|
||
),
|
||
'%'
|
||
)
|
||
END AS 考核通过率,
|
||
|
||
...
|
||
FROM DailyStatsWithPrev t
|
||
LEFT JOIN DailyScanMetrics dsm ON t.日期 = dsm.日期
|
||
LEFT JOIN DailySuccessCount dsc ON t.日期 = dsc.日期
|
||
LEFT JOIN HistoryUnfinished hu ON t.日期 = hu.日期
|
||
LEFT JOIN LatestUnfinished lu ON t.日期 = lu.日期
|
||
LEFT JOIN DailyFailedOrders dfo ON t.日期 = dfo.日期
|
||
LEFT JOIN DailyBeforeNoonPassed dbc ON t.日期 = dbc.日期
|
||
LEFT JOIN DailyAfternoonPassed dac ON t.日期 = dac.日期
|
||
LEFT JOIN DailyLowLabelRatePassed dllrp ON t.日期 = dllrp.日期
|
||
LEFT JOIN DailyHighLabelRateShould dhls ON t.日期 = dhls.日期
|
||
LEFT JOIN DailyLowLabelRateShould dlls ON t.日期 = dlls.日期
|
||
CROSS JOIN (SELECT @running_total := 0, @当天应该换单数 := 0) AS init
|
||
ORDER BY t.日期
|
||
```
|
||
|
||
### 数据关系验证
|
||
|
||
**数学关系**：
|
||
|
||
```
|
||
考核通过总数 <= 当日换单成功数
|
||
|
||
理由：
|
||
- A类考核通过包裹：都是曾经成功（首次成功时间≤考核时间）
|
||
- B类考核通过包裹：都是曾经成功（曾成功=1）
|
||
- 因此考核通过的包裹都属于已成功的包裹的子集
|
||
```
|
||
|
||
**验证场景**：
|
||
|
||
```
|
||
当日换单成功数 = 100
|
||
|
||
其中：
|
||
- 标签率始终≥80%：60个包裹
|
||
├─ 16点前到仓：35个
|
||
│ ├─ 完成时间≤考核时间：33个 ✓（考核通过）
|
||
│ └─ 完成时间>考核时间：2个 ✗（考核未通过）
|
||
└─ 16点后到仓：25个
|
||
├─ 完成时间≤考核时间：20个 ✓（考核通过）
|
||
└─ 完成时间>考核时间：5个 ✗（考核未通过）
|
||
|
||
- 标签率始终<80%：30个包裹
|
||
└─ 完成即达标：30个 ✓（全部考核通过）
|
||
|
||
- 标签率跨越阈值：10个包裹
|
||
├─ 在<80%期间被标签化：5个
|
||
│ └─ 完成即达标：5个 ✓（考核通过）
|
||
└─ 在≥80%期间被标签化：5个
|
||
├─ 16点前到仓：3个
|
||
│ └─ 完成时间≤考核时间：2个 ✓（考核通过）
|
||
└─ 16点后到仓：2个
|
||
└─ 完成时间≤考核时间：1个 ✓（考核通过）
|
||
|
||
考核通过总数 = 33 + 20 + 30 + 5 + 2 + 1 = 91 < 100 ✓
|
||
考核通过率 = 91 / 100 = 91%
|
||
```
|
||
|
||
### 关键指标体系
|
||
|
||
在报表中应该同时展示：
|
||
|
||
| 指标名 | 说明 | 分子 | 分母 |
|
||
|--------|------|------|------|
|
||
| 标签率≥80%的应该换单数 | 标签推送时标签率≥80%的订单 | COUNT(label_pushed_at时的标签率≥80%) | - |
|
||
| 标签率<80%的应该换单数 | 标签推送时标签率<80%的订单 | COUNT(label_pushed_at时的标签率<80%) | - |
|
||
| 当日应该换单数 | 当前需要进行考核作业的总订单数 | 高标签率应该+低标签率应该 | - |
|
||
| 当日换单成功数 | 首次成功的包裹总数 | COUNT(first_success_time IS NOT NULL) | - |
|
||
| 16点前高标签考核通过 | 16点前到仓且标签率≥80%且满足考核 | COUNT(...) | - |
|
||
| 16点后高标签考核通过 | 16点后到仓且标签率≥80%且满足考核 | COUNT(...) | - |
|
||
| 低标签率考核通过 | 标签率<80%且曾经成功 | COUNT(label_rate<80% AND first_success_time IS NOT NULL) | - |
|
||
| 考核通过总数 | 三类的总和 | 16前 + 16后 + 低标签 | - |
|
||
| **24小时换单率** | **实际履约完成的订单占比** | **考核通过总数** | **标签率≥80%的应该换单数** |
|
||
| 考核通过率 | 考核通过占成功的比例 | 考核通过总数 | 当日换单成功数 |
|
||
|
||
#### 各指标详细说明
|
||
|
||
**标签率≥80%的应该换单数**：
|
||
- 定义：标签被推送时，该交接单的标签率已达到≥80%的订单总数
|
||
- 计算方法：统计所有 `label_pushed_at` 时刻标签率≥80%的订单
|
||
- 这些订单应该按照"高标签率逻辑"进行考核（需要在规定时间内完成）
|
||
|
||
**标签率<80%的应该换单数**：
|
||
- 定义：标签被推送时，该交接单的标签率未达到80%的订单总数
|
||
- 计算方法：统计所有 `label_pushed_at` 时刻标签率<80%的订单
|
||
- 这些订单应该按照"低标签率逻辑"进行考核（完成即达标）
|
||
|
||
**24小时换单率（正确定义）**：
|
||
- 定义：实际完成并通过考核的所有订单（无论标签率），占应该完成的高标签率订单的比例
|
||
- 分子：**考核通过总数**（16点前高标签 + 16点后高标签 + 低标签率）← **包含所有通过的订单**
|
||
- 这反映了对客户承诺的实际履约完成度
|
||
- 分母：**标签率≥80%的应该换单数**（对客户的高承诺标的）
|
||
- 意义：直观衡量我们对客户高承诺订单的履约完成情况
|
||
- 计算：考核通过总数 / 标签率≥80%的应该换单数
|
||
|
||
**验证示例（正确版本）**：
|
||
```
|
||
当日新增订单：
|
||
├─ 到货时间：05-15 14:30
|
||
├─ 标签率≥80%的应该换单数：65个 （★对客户的承诺）
|
||
│ ├─ 16点前到仓：38个 → 应完成时间在05-16 16:00前
|
||
│ └─ 16点后到仓：27个 → 应完成时间在05-16 23:59:59前
|
||
├─ 标签率<80%的应该换单数：35个 （额外的）
|
||
└─ 当日应该换单数：100个
|
||
|
||
实际完成情况：
|
||
├─ 16点前到仓的38个中：
|
||
│ ├─ 34个在16:00前完成 ✓（考核通过）
|
||
│ └─ 4个在16:00后完成 ✗（考核未通过）
|
||
├─ 16点后到仓的27个中：
|
||
│ ├─ 20个在23:59:59前完成 ✓（考核通过）
|
||
│ └─ 7个在23:59:59后完成 ✗（考核未通过）
|
||
├─ 低标签率的35个中：
|
||
│ ├─ 32个完成 ✓（考核通过）
|
||
│ └─ 3个未完成 ✗（未成功）
|
||
|
||
指标统计：
|
||
├─ 16点前高标签考核通过 = 34个
|
||
├─ 16点后高标签考核通过 = 20个
|
||
├─ 低标签率考核通过 = 32个
|
||
├─ 考核通过总数 = 34 + 20 + 32 = 86个
|
||
└─ 16点后到仓未通过数 = 7个
|
||
|
||
★ 24小时换单率 = 86 / 65 = 132.31%
|
||
|
||
含义解析：
|
||
- 分子86包括了所有考核通过的订单（高标签的和低标签的）
|
||
- 分母65是客户高承诺的订单数
|
||
- 为什么会超过100%？
|
||
└─ 因为除了那些应该完成的高标签订单（65个）外
|
||
└─ 我们还额外完成了低标签率的订单（35个中的32个）
|
||
└─ 这说明我们的履约能力超出了对高标签订单的承诺
|
||
|
||
实际意义：
|
||
- 如果≥100%：说明我们完成的订单数≥承诺的高标签订单数，履约能力强
|
||
- 如果=83%：说明承诺的65个中只有54个完成，还有11个失约
|
||
- 这个指标最能直观反映对客户的履约完成情况
|
||
```
|
||
|
||
---
|
||
|
||
## 实现步骤总结
|
||
|
||
### 第1步：理解核心数据流
|
||
```
|
||
标签推送时刻
|
||
↓
|
||
自动比较：label_pushed_at vs label_rate_80_time
|
||
↓
|
||
自动分类：低标签率 或 高标签率
|
||
↓
|
||
自动计算：考核时间（NULL 或 次日16:00/23:59:59）
|
||
↓
|
||
保存到数据库（冻结值）
|
||
```
|
||
|
||
### 第2步：为每个交接单计算两个关键时间点
|
||
- `earliest_success_time`：交接单内最早的包裹何时完成
|
||
- `label_rate_80_time`：标签率首次达到80%时的最早标签推送时间
|
||
|
||
### 第3步：在标签推送时自动分类和计算考核时间
|
||
- 基于 label_pushed_at vs label_rate_80_time 的比较
|
||
- 自动确定是"低标签率"还是"高标签率"
|
||
- 自动计算 assessment_time
|
||
|
||
### 第4步：报表统计直接利用已保存的数据
|
||
- 不再动态计算标签率
|
||
- 不再计算历史标签率快照
|
||
- 直接统计分类后的结果
|
||
- 支持完整的审计追溯
|
||
|
||
---
|
||
|
||
## 核心设计验证
|
||
|
||
### 验证场景：标签率跨越阈值的处理
|
||
|
||
```
|
||
交接单BOL001创建于 05-15 14:00
|
||
|
||
订单标签推送时间线（按推送时间排序）：
|
||
- 05-15 14:30 订单1标签推送 → 推送时标签率 = 1/20 = 5% (<80%)
|
||
- 05-15 15:00 订单2标签推送 → 推送时标签率 = 2/20 = 10% (<80%)
|
||
- ...累积...
|
||
- 05-15 17:30 订单16标签推送 → 推送时标签率 = 16/20 = 80% ← 达到80%！
|
||
- 05-15 17:35 订单17标签推送 → 推送时标签率 = 17/20 = 85% (≥80%)
|
||
- 05-15 17:40 订单18标签推送 → 推送时标签率 = 18/20 = 90% (≥80%)
|
||
|
||
InterchangeUnitKeyTimes计算结果：
|
||
- BillOfLadingNumber = BOL001
|
||
- MasterPackageNumber = MP001
|
||
- earliest_success_time = 05-16 10:30:00（这个交接单内的某个包裹最早在此时完成）
|
||
- label_rate_80_time = 05-15 17:30:00（标签率首次达到80%的时刻）
|
||
|
||
对每个订单的分类：
|
||
|
||
订单1（label_pushed_at = 05-15 14:30）：
|
||
├─ 比较：14:30 < 17:30 ✓
|
||
├─ 结论：推送时标签率 < 80%
|
||
├─ 归类：低标签率
|
||
└─ 考核时间：NULL（完成即达标）
|
||
|
||
订单16（label_pushed_at = 05-15 17:30）：
|
||
├─ 比较：17:30 >= 17:30 ✓
|
||
├─ 结论：推送时标签率 >= 80%
|
||
├─ 归类：高标签率
|
||
├─ 到仓时间：05-15 14:30 < 16点
|
||
└─ 考核时间：05-16 16:00:00
|
||
|
||
订单17（label_pushed_at = 05-15 17:35）：
|
||
├─ 比较：17:35 >= 17:30 ✓
|
||
├─ 结论：推送时标签率 >= 80%
|
||
├─ 归类：高标签率
|
||
├─ 到仓时间：05-15 14:30 < 16点
|
||
└─ 考核时间：05-16 16:00:00
|
||
|
||
验证考核结果：
|
||
- 订单1在05-16 15:00成功
|
||
└─ 考核时间NULL → 完成即达标 → 考核通过 ✓
|
||
|
||
- 订单16在05-16 17:00成功
|
||
└─ 考核时间16:00，17:00 > 16:00 → 考核未通过 ✗
|
||
|
||
- 订单17在05-16 15:30成功
|
||
└─ 考核时间16:00，15:30 < 16:00 → 考核通过 ✓
|
||
```
|
||
|
||
**验证结论**：
|
||
- ✅ 逻辑极其清晰：只需比较两个时间点
|
||
- ✅ 性能高效：无需复杂的历史标签率计算
|
||
- ✅ 完全自动化：标签推送时自动归类
|
||
- ✅ 支持审计：可追溯每个订单的分类依据
|
||
|
||
---
|
||
|
||
## SQL实现指导
|
||
|
||
### 在现有SQL基础上的改进建议
|
||
|
||
添加新的CTE来处理阈值跨越：
|
||
|
||
```sql
|
||
-- 新增CTE：识别标签率的阈值跨越
|
||
LabelRateThresholdCrossing AS (
|
||
SELECT
|
||
BillOfLadingNumber,
|
||
MasterPackageNumber,
|
||
MIN(CASE
|
||
WHEN running_label_count >= 80
|
||
AND LAG(running_label_count) OVER (...) < 80
|
||
THEN label_pushed_at
|
||
END) AS threshold_crossing_time
|
||
FROM (
|
||
SELECT
|
||
l.BillOfLadingNumber,
|
||
l.MasterPackageNumber,
|
||
l.label_pushed_at,
|
||
SUM(CASE WHEN l.Label IS NOT NULL THEN 1 ELSE 0 END)
|
||
OVER (
|
||
PARTITION BY l.BillOfLadingNumber, l.MasterPackageNumber
|
||
ORDER BY l.label_pushed_at
|
||
) * 100.0 / COUNT(*) OVER (
|
||
PARTITION BY l.BillOfLadingNumber, l.MasterPackageNumber
|
||
) AS running_label_count
|
||
FROM label_replace_requests l
|
||
) t
|
||
GROUP BY BillOfLadingNumber, MasterPackageNumber
|
||
)
|
||
|
||
-- 修改ArrivalRequests CTE中的考核时间计算
|
||
-- 加入对 label_pushed_at 的判断
|
||
```
|
||
|