160 lines
5.8 KiB
Markdown
160 lines
5.8 KiB
Markdown
# 到货信息统计 MySQL 语句
|
||
|
||
## 1. 基本统计语句
|
||
|
||
```sql
|
||
SELECT
|
||
COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown') AS `Key`,
|
||
c.CustomerCode AS CustomerCode,
|
||
COUNT(*) AS ArrivalOrderCount,
|
||
SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) AS NoLabelDataCount,
|
||
SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) AS LabeledOrderCount,
|
||
CONCAT(ROUND((SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%') AS LabelRate,
|
||
(SELECT COUNT(*)
|
||
FROM label_scan_history s
|
||
WHERE s.NeutralWaybillNumber = l.NeutralWaybillNumber
|
||
AND s.Result = 0) AS ReplaceCompletedCount,
|
||
COUNT(*) - (SELECT COUNT(*)
|
||
FROM label_scan_history s
|
||
WHERE s.NeutralWaybillNumber = l.NeutralWaybillNumber
|
||
AND s.Result = 0) AS ReplacePendingCount,
|
||
(SELECT MIN(h.ReceiptTime)
|
||
FROM arrival_handover_forms h
|
||
WHERE h.HandoverNumber = COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber)) AS ArrivalTime,
|
||
MAX(l.BillOfLadingNumber) AS BillOfLadingNumber,
|
||
MAX(l.MasterPackageNumber) AS MasterPackageNumber
|
||
FROM
|
||
label_replace_requests l
|
||
LEFT JOIN
|
||
customers c ON l.CustomerId = c.Id
|
||
WHERE
|
||
1=1
|
||
-- 客户ID过滤
|
||
AND (l.CustomerId = 1 OR 1 IS NULL)
|
||
-- 日期范围过滤
|
||
AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
|
||
AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
|
||
GROUP BY
|
||
COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown')
|
||
ORDER BY
|
||
l.CreatedAt DESC;
|
||
```
|
||
|
||
## 2. 优化版本（使用子查询优化扫描记录统计）
|
||
|
||
```sql
|
||
WITH scan_summary AS (
|
||
SELECT
|
||
NeutralWaybillNumber,
|
||
COUNT(*) AS ReturnedLabelCount
|
||
FROM
|
||
label_scan_history
|
||
WHERE
|
||
Result = 0
|
||
GROUP BY
|
||
NeutralWaybillNumber
|
||
),
|
||
arrival_summary AS (
|
||
SELECT
|
||
HandoverNumber,
|
||
MIN(ReceiptTime) AS MinReceiptTime
|
||
FROM
|
||
arrival_handover_forms
|
||
GROUP BY
|
||
HandoverNumber
|
||
)
|
||
SELECT
|
||
COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown') AS `Key`,
|
||
c.CustomerCode AS CustomerCode,
|
||
COUNT(*) AS ArrivalOrderCount,
|
||
SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END) AS NoLabelDataCount,
|
||
SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) AS LabeledOrderCount,
|
||
CONCAT(ROUND((SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2), '%') AS LabelRate,
|
||
COALESCE(SUM(s.ReturnedLabelCount), 0) AS ReplaceCompletedCount,
|
||
COUNT(*) - COALESCE(SUM(s.ReturnedLabelCount), 0) AS ReplacePendingCount,
|
||
COALESCE(a.MinReceiptTime, b.MinReceiptTime) AS ArrivalTime,
|
||
MAX(l.BillOfLadingNumber) AS BillOfLadingNumber,
|
||
MAX(l.MasterPackageNumber) AS MasterPackageNumber
|
||
FROM
|
||
label_replace_requests l
|
||
LEFT JOIN
|
||
customers c ON l.CustomerId = c.Id
|
||
LEFT JOIN
|
||
scan_summary s ON l.NeutralWaybillNumber = s.NeutralWaybillNumber
|
||
LEFT JOIN
|
||
arrival_summary a ON l.BillOfLadingNumber = a.HandoverNumber
|
||
LEFT JOIN
|
||
arrival_summary b ON l.MasterPackageNumber = b.HandoverNumber
|
||
WHERE
|
||
1=1
|
||
-- 客户ID过滤
|
||
AND (l.CustomerId = 1 OR 1 IS NULL)
|
||
-- 日期范围过滤
|
||
AND (l.CreatedAt >= '2026-01-01' OR '2026-01-01' IS NULL)
|
||
AND (l.CreatedAt <= '2026-12-31' OR '2026-12-31' IS NULL)
|
||
GROUP BY
|
||
COALESCE(l.BillOfLadingNumber, l.MasterPackageNumber, 'Unknown')
|
||
ORDER BY
|
||
l.CreatedAt DESC;
|
||
```
|
||
|
||
## 3. 实现说明
|
||
|
||
### 3.1 分组逻辑
|
||
|
||
使用 `COALESCE` 函数实现提单号优先的分组逻辑：
|
||
- 当 `BillOfLadingNumber` 不为 NULL 时，使用 `BillOfLadingNumber` 作为分组依据
|
||
- 当 `BillOfLadingNumber` 为 NULL 时，使用 `MasterPackageNumber` 作为分组依据
|
||
- 当两者都为 NULL 时，使用 'Unknown' 作为分组依据
|
||
|
||
### 3.2 统计计算
|
||
|
||
- **到货订单数量**：使用 `COUNT(*)` 统计每个分组的记录数
|
||
- **无标签数据数量**：使用 `SUM(CASE WHEN l.Label IS NULL OR l.Label = '' THEN 1 ELSE 0 END)` 统计
|
||
- **已有标签订单数**：使用 `SUM(CASE WHEN l.Label IS NOT NULL AND l.Label != '' THEN 1 ELSE 0 END)` 统计
|
||
- **已有标签率**：计算已有标签订单数占总订单数的百分比
|
||
- **换单完成数量**：统计有扫描记录且结果为 0 的记录数
|
||
- **未换单完成数量**：总订单数减去换单完成数量
|
||
- **到货时间**：从到货交接单表中获取最早的收货时间
|
||
|
||
### 3.3 过滤条件
|
||
|
||
- **客户ID过滤**：可根据需要修改客户ID值
|
||
- **日期范围过滤**：可根据需要修改日期范围
|
||
|
||
## 4. 性能优化建议
|
||
|
||
1. **索引优化**：
|
||
- 在 `label_replace_requests` 表上添加以下索引：
|
||
- `(CustomerId, CreatedAt)`
|
||
- `(BillOfLadingNumber)`
|
||
- `(MasterPackageNumber)`
|
||
- 在 `label_scan_history` 表上添加索引：
|
||
- `(NeutralWaybillNumber, Result)`
|
||
- 在 `arrival_handover_forms` 表上添加索引：
|
||
- `(HandoverNumber, ReceiptTime)`
|
||
|
||
2. **查询优化**：
|
||
- 使用 CTE (Common Table Expressions) 减少重复子查询
|
||
- 避免在 GROUP BY 子句中使用复杂表达式
|
||
- 合理使用 JOIN 替代子查询
|
||
|
||
3. **数据量控制**：
|
||
- 考虑添加分页功能，避免一次性返回大量数据
|
||
- 对于历史数据，可以考虑归档策略
|
||
|
||
## 5. 使用说明
|
||
|
||
1. **参数调整**：
|
||
- 客户ID：修改 `l.CustomerId = 1` 中的 1 为实际客户ID
|
||
- 日期范围：修改 `'2026-01-01'` 和 `'2026-12-31'` 为实际日期范围
|
||
|
||
2. **结果解释**：
|
||
- `Key` 列：表示分组依据，可能是提单号、主包号或 'Unknown'
|
||
- 其他列：表示各统计指标
|
||
|
||
3. **注意事项**：
|
||
- `Key` 是 MySQL 关键字，使用反引号包围
|
||
- 所有字符串参数都使用单引号包围
|
||
- 日期参数使用 'YYYY-MM-DD' 格式
|
||
- 可以根据实际需要修改示例值 |