186 lines
6.0 KiB
Markdown
186 lines
6.0 KiB
Markdown
# 视图逻辑修复方案总结
|
||
|
||
## 📌 问题回顾
|
||
|
||
用户反馈数据库视图查询结果大部分为0：
|
||
```
|
||
2026-05-15 | 0 | 3592 | 0 | 3315 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2026-05-16 20:21:40
|
||
```
|
||
|
||
仅有 `DailyShouldReplaceCount`(3592) 和 `CumulativeTotalReplaceCount`(3315) 有数据，其他指标全是0。
|
||
|
||
## 🔍 根本原因分析
|
||
|
||
### 问题1：硬编码NOW()导致的日期比较错误
|
||
**原视图SQL片段**：
|
||
```sql
|
||
CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
```
|
||
|
||
**问题**：每次查询都与当前时间比较，而不是与查询参数的日期比较。
|
||
|
||
**影响**：
|
||
- 查询2026-05-15的数据时，条件自动比较为"2026-05-15是否等于今天"
|
||
- 由于2026-05-15不等于今天的日期，所以大部分WHERE条件都返回FALSE
|
||
- 导致除了依赖其他条件的指标外，大部分指标都被过滤掉了
|
||
|
||
### 问题2：JOIN关联逻辑不合理
|
||
**原视图**使用 `OR` 条件进行关联：
|
||
```sql
|
||
r.BillOfLadingNumber = ahf.HandoverNumber OR r.MasterPackageNumber = ahf.HandoverNumber
|
||
```
|
||
|
||
虽然这个关联本身可以工作，但与硬编码的时间比较配合，导致很多联接行被意外过滤。
|
||
|
||
### 问题3：时区转换过度使用
|
||
多次重复的 `CONVERT_TZ()` 调用导致：
|
||
- SQL语句复杂性增加
|
||
- 性能下降
|
||
- 维护困难
|
||
|
||
## ✨ 修复方案
|
||
|
||
### 采用方案：存储过程（Stored Procedure）
|
||
|
||
**关键改进**：
|
||
|
||
1. **参数化日期输入**
|
||
- 从硬编码 `NOW()` 改为接受 `p_date` 参数
|
||
- 支持查询任意历史日期
|
||
|
||
2. **简化时区转换**
|
||
- 在存储过程开始时一次性计算日期范围（v_date_start, v_date_end）
|
||
- 后续查询直接使用这些变量，避免重复转换
|
||
|
||
3. **优化JOIN逻辑**
|
||
- 使用临时表 `temp_qualified_arrivals` 预先计算标签率≥80%的交接单
|
||
- 后续查询直接JOIN临时表，提高查询效率
|
||
|
||
4. **清晰的指标计算**
|
||
- 每个指标独立计算，使用SELECT...INTO语句
|
||
- 便于调试和验证
|
||
|
||
### 存储过程核心逻辑
|
||
|
||
```sql
|
||
CREATE PROCEDURE sp_GetDailyMetricsSummary(IN p_date DATE)
|
||
|
||
-- 1. 计算UTC-5时区的日期范围
|
||
SET v_date_start = CONVERT_TZ(CONCAT(DATE(p_date), ' 00:00:00'), '-05:00', '+00:00');
|
||
SET v_date_end = CONVERT_TZ(CONCAT(DATE(p_date), ' 23:59:59'), '-05:00', '+00:00');
|
||
|
||
-- 2. 创建临时表存储标签率≥80%的交接单
|
||
CREATE TEMPORARY TABLE temp_qualified_arrivals AS
|
||
SELECT ... FROM arrival_handover_forms ... HAVING labeled_orders / total_orders >= 0.8;
|
||
|
||
-- 3. 基于临时表和时间范围计算各项指标
|
||
SELECT COUNT(...) INTO v_daily_should_replace_count ...
|
||
SELECT COUNT(...) INTO v_daily_success_count ...
|
||
-- ... 其他指标 ...
|
||
|
||
-- 4. 返回所有指标结果
|
||
SELECT ... AS MetricsDate, v_daily_new_replace_count AS DailyNewReplaceCount, ...
|
||
```
|
||
|
||
## 📊 预期改进
|
||
|
||
### 性能改进
|
||
- **原方案**：11个独立异步查询，耗时15-30秒
|
||
- **新方案**：单个存储过程调用，目标<1秒
|
||
- **性能提升**：15-30倍
|
||
|
||
### 正确性改进
|
||
- **原问题**：大多数指标返回0
|
||
- **修复后**：各指标返回合理的非零值
|
||
- **根本原因**：移除了硬编码的NOW()导致的日期比较错误
|
||
|
||
### 灵活性改进
|
||
- **原方案**：视图固定查询当前日期
|
||
- **新方案**：可查询任意历史日期
|
||
- **支持**：报表、趋势分析等需要历史数据的功能
|
||
|
||
## 🔧 实现变更
|
||
|
||
### 1. 数据库变更
|
||
📄 文件：`database/migrations/002_create_sp_daily_metrics_summary.sql`
|
||
- 创建存储过程 `sp_GetDailyMetricsSummary`
|
||
- 接收参数：`p_date DATE`
|
||
- 返回：13个指标列
|
||
|
||
### 2. 应用层变更
|
||
📄 文件：`src/BLL/Services/MetricsCalculationService.cs`
|
||
- 第462行：修改 `GetDailySummaryAsync()` 方法
|
||
- 将 `SELECT * FROM v_DailyMetricsSummary WHERE MetricsDate = '{date}'`
|
||
- 改为 `CALL sp_GetDailyMetricsSummary('{date:yyyy-MM-dd}')`
|
||
|
||
### 3. 降级方案保留
|
||
- 保留原有的 `GetDailySummaryAsync_Original()` 方法
|
||
- 如果存储过程调用失败，自动降级到11个独立查询
|
||
- 确保系统可用性
|
||
|
||
## 📝 验证步骤
|
||
|
||
### 数据库层验证
|
||
1. 执行存储过程创建脚本
|
||
2. 执行 `CALL sp_GetDailyMetricsSummary('2026-05-15')`
|
||
3. 验证返回数据：
|
||
- DailyShouldReplaceCount ≈ 3592 ✅
|
||
- CumulativeTotalReplaceCount ≈ 3315 ✅
|
||
- 其他指标 > 0 ✅
|
||
|
||
### 应用层验证
|
||
1. 重新编译并运行后端服务
|
||
2. 调用 API：`GET /api/metrics/daily-dashboard?date=2026-05-15`
|
||
3. 验证JSON响应包含所有指标
|
||
|
||
### 前端验证
|
||
1. 打开仪表盘：`http://localhost:5002/metrics-dashboard-summary.html`
|
||
2. 选择日期 2026-05-15
|
||
3. 验证显示的指标正确性
|
||
|
||
### 性能验证
|
||
1. 使用浏览器开发者工具查看API响应时间
|
||
2. 应该 < 1000ms（1秒）
|
||
|
||
## 🎯 后续可选优化
|
||
|
||
1. **缓存层**：添加Redis缓存，避免频繁查询相同日期
|
||
2. **物化视图**：定期生成历史数据快照
|
||
3. **分析表**：预生成常用报表的汇总数据
|
||
4. **分区**：按日期分区arrival_handover_forms表
|
||
|
||
## 📋 关键指标定义（业务规则）
|
||
|
||
### DailyShouldReplaceCount
|
||
- 当日到仓且标签率≥80%的订单总数
|
||
- 业务含义：当天应该执行的换单操作数
|
||
|
||
### DailySuccessCount
|
||
- 当日成功扫描的订单数（Result=0）
|
||
- 业务含义：当天完成的换单操作数
|
||
|
||
### CumulativeTotalReplaceCount
|
||
- 截至前一天未成功扫描的订单数
|
||
- 业务含义：历史待处理的订单数
|
||
|
||
### BeforeNoonArrivedCount / AfternoonArrivedCount
|
||
- 按16:00时间点划分的到仓订单
|
||
- 业务含义：区分早班和晚班处理的订单
|
||
|
||
### 完成率计算
|
||
- DailyCompletionRate = DailySuccessCount / DailyShouldReplaceCount * 100%
|
||
- 指标类型：百分比，≥95%为绿色，85-95%为黄色，<85%为红色
|
||
|
||
## ✅ 完成检查清单
|
||
|
||
- [x] 分析根本原因
|
||
- [x] 设计解决方案
|
||
- [x] 编写存储过程SQL
|
||
- [x] 修改应用层代码
|
||
- [x] 准备执行说明文档
|
||
- [ ] **用户执行**：在数据库中创建存储过程
|
||
- [ ] **测试**：验证存储过程返回正确数据
|
||
- [ ] **集成**：前端调用验证
|
||
- [ ] **性能**：确认查询时间<1秒
|
||
|