运营指标逻辑全量对齐修复计划

问题背景

当前最新运营监控.sql的统计结果和运营指标验证明细查询.sql的明细数据不一致，5月1日「当天应该换单数」明细统计为9593，汇总统计仅为8537，核心问题为联表条件过滤了部分有效订单，同时需要按照最新的指标定义全量对齐所有指标的计算逻辑。

修复目标

完全对齐用户给出的所有指标统计规则
5月1日「当天应该换单数」统计结果等于9593，与明细完全一致
所有指标计算逻辑在汇总SQL和明细SQL中保持统一
正确处理时区转换：所有时间除到仓时间外，统计时均转换为UTC-5，日期维度统一使用UTC-5自然日
累计要换的总单数按RequestId去重，无重复统计

修复步骤

步骤1：读取当前SQL文件确认现有逻辑

读取以下两个核心SQL文件，梳理当前的联表逻辑、指标计算方式和存在的问题：

d:\EPproject\LabelReplaceServer\最新运营监控.sql
d:\EPproject\LabelReplaceServer\运营指标验证明细查询.sql

步骤2：补全AllDates日期维度

确保日期维度包含所有需要用到的日期，避免联表时丢失有效数据：

AllDates AS (
    SELECT ReceiptDate AS 日期 FROM OrderFullInfo -- 到仓日期（UTC-5）
    UNION
    SELECT LabelRetrievedDate_UTC5 AS 日期 FROM OrderFullInfo WHERE LabelRetrievedDate_UTC5 IS NOT NULL -- 标签推送日期（UTC-5）
    UNION
    SELECT FirstSuccessDate_UTC5 AS 日期 FROM OrderFullInfo WHERE FirstSuccessDate_UTC5 IS NOT NULL -- 标签率达标日期（UTC-5）
    UNION
    SELECT DATE(CONVERT_TZ(AssessmentBaseTime, '+00:00', '-05:00')) AS 日期 FROM OrderFullInfo WHERE AssessmentBaseTime IS NOT NULL -- 考核基准日期（转UTC-5）
    UNION
    SELECT DATE(CONVERT_TZ(AssessmentTime, '+00:00', '-05:00')) AS 日期 FROM OrderFullInfo WHERE AssessmentTime IS NOT NULL -- 考核截止日期（转UTC-5）
    UNION
    SELECT DATE(CONVERT_TZ(ScanTime, '+00:00', '-05:00')) AS 日期 FROM ScanHistory WHERE ScanTime IS NOT NULL -- 扫描日期（转UTC-5）
    UNION
    SELECT DATE(CONVERT_TZ(LabelPushTime, '+00:00', '-05:00')) AS 日期 FROM LabelPushHistory WHERE LabelPushTime IS NOT NULL -- 标签推送日期（转UTC-5）
    UNION
    SELECT DATE(CONVERT_TZ(ReplaceSuccessTime, '+00:00', '-05:00')) AS 日期 FROM ReplaceHistory WHERE ReplaceSuccessTime IS NOT NULL -- 换单完成日期（转UTC-5）
)

步骤3：优化主查询联表逻辑

移除所有多余的过滤条件，确保所有有考核时间的订单都能被正确关联：

保持AllDates LEFT JOIN OrderFullInfo的关联方式
移除所有在JOIN条件和WHERE条件中多余的LabelRate >= 0.8判断（有考核时间的订单默认已满足标签率≥80%）
移除对FirstScanTime_UTC5非空的判断，兼容无扫描记录的订单

步骤4：全量更新所有指标计算逻辑（严格对齐用户定义）

指标定义对照表

指标名称	计算逻辑	说明
日期	自然日时间（UTC-5）	-
当天新增换单数	到仓时间是当天的有标签订单总数	到仓时间本身为UTC-5，无需转换
累计要换的总单数	有标签并且（当日没有换单成功记录或者换单完成时间大于今天）的不重复订单总数	按RequestId去重，换单完成时间转换为UTC-5判断
当天应该换单数	考核基准日期是当天或者考核截止日期是当天的订单总数	考核基准时间、考核截止时间均转换为UTC-5判断
当日换单完成数	换单完成记录时间是当天的包裹数，不包含STOP数	换单完成时间转换为UTC-5判断
当日换单失败数	换单非完成记录时间是当天的包裹数	换单记录时间转换为UTC-5判断
当日STOP数	换单完成时间是当天并且描述中含有STOP的包裹数	换单完成时间转换为UTC-5判断
24小时换单成功数	首次换单完成时间在考核基准时间与考核时间之间的包裹数	所有时间均转换为UTC-5后判断时间范围
当日标签推送数	标签推送时间是当天的包裹数	标签推送时间转换为UTC-5判断
当日扫描数	扫描时间是当天的扫描历史记录总数	扫描时间转换为UTC-5判断
当天换单完成率	当日换单完成数 / (当天应该换单数 + 累计要换的总单数)	-
24小时换单率	24小时换单成功数 / 当天应该换单数	-
数据拉取时间	当前时间（UTC-5）	-

步骤5：调整字段输出顺序

按照用户要求的顺序排列输出字段：

日期
当天新增换单数
累计要换的总单数
当天应该换单数
当日换单完成数
当日换单失败数
当日STOP数
24小时换单成功数
当日标签推送数
当日扫描数
当天换单完成率
24小时换单率
数据拉取时间（UTC_5）

步骤6：同步更新明细查询SQL

确保运营指标验证明细查询.sql的逻辑和汇总SQL完全对齐：

同步更新所有指标的计算逻辑
保持字段命名一致
保留所有明细字段的输出，方便用户手动核对

步骤7：验证统计结果

执行汇总SQL查询2026-05-01的「当天应该换单数」，确认结果等于9593，与明细统计结果完全一致。

验收标准

5月1日「当天应该换单数」= 9593
所有指标逻辑完全符合用户给出的定义
汇总SQL和明细SQL统计结果完全一致
SQL可正常运行无语法错误
所有非到仓时间的统计均已转换为UTC-5时区
累计要换的总单数已按RequestId去重，无重复统计

5.6 KiB Raw Permalink Blame History Unescape Escape