LabelChange-server/.trae/documents/view_logic_fix_plan.md

数据库视图逻辑修复计划

问题分析

当前问题表现

• 视图查询结果大多为0
• 仅 DailyShouldReplaceCount = 3592 和 CumulativeTotalReplaceCount = 3315 有数据
• 其他指标（新增数、完成数、到仓数等）全为0

根本原因

当前视图SQL存在以下关键问题：

1. 硬编码NOW()比较问题 （第17、24、34、42、49、56、62、69、76、83、91、100行）

   • 使用 CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE) 与 NOW() 进行比较
   • 每次查询都与当前时间比较，导致查询特定历史日期时大部分条件都不满足
   • 应该改为接受参数化的查询日期

2. JOIN逻辑混乱（第109-115行）

   • 对label_scan_history的JOIN使用子查询且索引方式错误
   • 对arrival_handover_forms的JOIN条件不合理：r.BillOfLadingNumber = ahf.HandoverNumber OR r.MasterPackageNumber = ahf.HandoverNumber
   • 应该支持多种匹配方式，但需要明确的业务逻辑

3. 时区转换过度 （多个CONVERT_TZ调用）

   • 重复的CONVERT_TZ导致性能下降
   • 应该在应用层处理时区转换，简化视图逻辑

4. 指标计算与业务逻辑不一致

   • DailyShouldReplaceCount 应该基于 arrival_handover_forms 表的接收时间
   • 当前逻辑：基于 label_replace_requests 的创建时间判断
   • 业务逻辑（源自MetricsCalculationService）：
     • 先获取当日到仓的交接单（按ReceiptTime）
     • 计算每个交接单的标签率（≥80%时才计为应换单）
     • 统计满足条件的订单数

修复方案

方案1：修改为参数化查询的视图（推荐）

思路：不在视图中使用 NOW()，改为在应用层传入查询日期，从而支持灵活的日期范围查询

优点：

• 支持查询任意日期的指标
• 简化SQL逻辑，提高性能
• 易于调试和验证

缺点：

• 不能直接在SQL中使用 SELECT * FROM view WHERE date = '2026-05-15'
• 需要使用存储过程或改为表函数

方案2：创建存储过程（更优）

思路：使用存储过程接受 QueryDate 参数，动态生成查询语句

优点：

• 完全灵活，支持任意日期查询
• 保持SQL优化和性能
• 易于维护和扩展

缺点：

• 需要修改应用层调用方式

方案3：直接修复当前视图（临时方案）

思路：

1. 在视图中使用 CURDATE() 代替 NOW()
2. 简化JOIN逻辑
3. 修正指标计算

缺点：

• 只能查询当前日期数据
• 不符合长期需求

选择：方案2（存储过程）

原因

1. 最符合实际业务需求
2. 应用层已有 db.SqlQueryable<dynamic>(sql) 的调用方式
3. 易于与C#应用集成

实现步骤

步骤1：创建存储过程 sp_GetDailyMetricsSummary

存储过程需要接受一个 QueryDate 参数（YYYY-MM-DD格式），返回当天的所有指标。

核心逻辑：

DELIMITER //
CREATE PROCEDURE sp_GetDailyMetricsSummary(IN p_date DATE)
BEGIN
  -- 时间范围定义（UTC-5时区）
  -- 开始时间：查询日期 00:00 (UTC-5)
  -- 结束时间：查询日期 23:59:59 (UTC-5)
  
  SELECT 
    p_date AS MetricsDate,
    
    -- 1. DailyNewReplaceCount：当天新增应换单数
    -- 来源：当日到仓的交接单中，标签率≥80%的订单数
    
    -- 2. DailyShouldReplaceCount：应该换单数（累计）
    -- 来源：所有到仓的交接单（直到今天）中，标签率≥80%的订单数
    
    -- 3. DailySuccessCount：当天完成数
    -- 来源：当日扫描成功(Result=0)的订单数
    
    -- 4. CumulativeTotalReplaceCount：累计未完成数
    -- 来源：历史到仓记录中，未扫描成功的订单数
    
    -- 5. DailyStopCount：当日冻结数
    -- 来源：当日扫描结果中包含"STOP"的订单数
    
    -- 6. DailyLabelPushCount：当日标签推送数
    -- 来源：当日新增标签的订单数(Label不为空)
    
    -- 7. DailyScanCount：当日扫描总数
    
    -- 8. BeforeNoonArrivedCount：16点前到仓数
    -- 来源：当日到仓时间(ReceiptTime) < 16:00 (UTC-5)的订单数
    
    -- 9. AfternoonArrivedCount：16点后到仓数
    
    -- 10. BeforeNoonPassedCount：16点前完成数
    -- 来源：16点前到仓的订单中，在次日16:00前扫描成功的订单数
    
    -- 11. AfternoonPassedCount：16点后完成数
    
    -- 12. DailyFailureCount：当日失败数
    
    NOW() AS DataFetchTime
  FROM (
    -- 基础数据集：当日及历史到仓记录
    SELECT 
      r.Id AS OrderId,
      r.NeutralWaybillNumber,
      r.Label,
      ahf.HandoverNumber,
      ahf.ReceiptTime,
      s.Id AS ScanId,
      s.Result,
      s.Description,
      s.CreatedAt AS ScanCreatedAt
    FROM label_replace_requests r
    LEFT JOIN arrival_handover_forms ahf ON 
      r.BillOfLadingNumber = ahf.HandoverNumber 
      OR r.MasterPackageNumber = ahf.HandoverNumber
    LEFT JOIN (
      SELECT * FROM label_scan_history 
      WHERE CAST(DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00'))) AS DATE) >= DATE_SUB(p_date, INTERVAL 90 DAY)
    ) s ON r.NeutralWaybillNumber = s.NeutralWaybillNumber
    WHERE r.Label IS NOT NULL
  ) base_data
  GROUP BY p_date;
END //
DELIMITER ;

关键业务规则（基于MetricsCalculationService）：

1. DailyShouldReplaceCount / BeforeNoonArrivedCount / AfternoonArrivedCount

   • 获取当日接收的交接单（ReceiptTime在p_date这一天UTC-5）
   • 计算每个交接单的标签率（标签订单数 / 总订单数）
   • 只统计标签率≥80%的订单
   • 按16点划分

2. DailySuccessCount / BeforeNoonPassedCount / AfternoonPassedCount

   • 对于16点前到仓的订单：统计次日16:00前扫描成功的数量
   • 对于16点后到仓的订单：统计本日16:00前扫描成功的数量
   • Result = 0 表示扫描成功

3. CumulativeTotalReplaceCount

   • 统计所有历史到仓记录（直到昨天）中，未扫描成功的订单

4. DailyFailureCount

   • DailyShouldReplaceCount - DailySuccessCount

步骤2：修改应用层调用

在 MetricsCalculationService.GetDailySummaryAsync() 中：

string sql = $"CALL sp_GetDailyMetricsSummary('{date:yyyy-MM-dd}')";
var summaryList = await db.SqlQueryable<dynamic>(sql).ToListAsync();

步骤3：验证和测试

1. 在数据库中执行 CALL sp_GetDailyMetricsSummary('2026-05-15')
2. 验证返回的各项指标是否大于0（符合实际数据）
3. 对比原始的11个异步查询结果
4. 性能测试：确保<1秒完成

风险评估

风险项	概率	影响	缓解方案
存储过程语法错误	中	中	保留原始视图作为备份，逐行测试
业务逻辑理解偏差	中	高	与用户确认每个指标的定义，对比原始结果
性能不达预期	低	中	添加必要索引，优化查询计划
时区转换错误	低	高	充分测试UTC-5转换逻辑，验证样本数据

实现时间表

1. 编写存储过程SQL - 验证语法正确
2. 在数据库创建存储过程 - 确保无错误
3. 用样本数据测试 - 对比原始结果
4. 修改应用层代码 - 调整GetDailySummaryAsync方法
5. 集成测试 - 前端调用验证
6. 性能验证 - 确保<1秒目标
7. 部署 - 发布到生产环境

备选方案

如果存储过程方案遇到困难，改为直接优化视图：

1. 移除所有 NOW() 比较
2. 在应用层计算日期范围（UTC-5）
3. 使用 BETWEEN 比较日期范围而非精确日期
4. 简化JOIN逻辑，分离查询