jennisonzhang/LabelChange-server

Fork 0

Files

Im-Jenisson b2a9b7d3c2 上传源代码版本

2026-06-01 16:30:29 +08:00

7.8 KiB

Raw Permalink Blame History

日级报表SQL完整重构模板

重构说明

基于用户的业务需求和分析计划，以下是完整的SQL重构模板。需要替换当前 GetDailyLabelStatsChineseAsync() 方法中的整个SQL查询。

核心改变

✅ 标签率维度 - 已完成修复（按交接单分组）
⏳ 报表维度 - 改为以完成日期为主体，而非到货日期
⏳ 16点统计 - 改为基于完成日期，关联到货日期判断分段
⏳ JOIN逻辑 - 移除CROSS JOIN，改为清晰的JOIN关系

重构后的SQL框架

WITH
-- 步骤1：获取所有到货交接单（保持不变）
ArrivalFormsWithDate AS (
    SELECT 
        a.Id,
        a.HandoverNumber,
        DATE(a.ReceiptTime) AS 到货日期,
        a.ReceiptTime AS 到货时间
    FROM arrival_handover_forms a
),

-- 步骤2：计算交接单级别的标签率（已修复）
InterchangeUnitLabelRates AS (
    -- 同前面修复的版本
    ...
),

-- 步骤3：到货请求信息（已修复，加入考核时间逻辑）
ArrivalRequests AS (
    -- 同前面修复的版本
    ...
),

-- 步骤4：首次成功日期的完成日期统计（关键CTE - 新增）
CompletionDatesWithArrivalInfo AS (
    SELECT 
        DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 完成日期,
        oss.NeutralWaybillNumber,
        oss.首次成功时间,
        ar.到货日期,
        ar.到货时间,
        ar.标签率,
        ar.考核时间,
        CASE
            WHEN ar.考核时间 IS NOT NULL AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= ar.考核时间
                THEN 1 ELSE 0
        END AS 是否考核通过,
        CASE
            WHEN ar.到货时间 < 16 THEN 1 ELSE 0
        END AS 是否16点前到仓
    FROM OverallScanStatus oss
    LEFT JOIN ArrivalRequests ar ON oss.NeutralWaybillNumber = ar.NeutralWaybillNumber
    WHERE oss.曾成功 = 1 AND oss.首次成功时间 IS NOT NULL
),

-- 步骤5：按完成日期聚合的报表数据
DailyCompletionStats AS (
    SELECT 
        cda.完成日期 AS 日期,
        COUNT(DISTINCT cda.NeutralWaybillNumber) AS 当日换单成功数,
        COUNT(DISTINCT CASE 
            WHEN cda.是否16点前到仓 = 1 AND cda.是否考核通过 = 1
            THEN cda.NeutralWaybillNumber 
        END) AS 16点前考核通过包裹数,
        COUNT(DISTINCT CASE 
            WHEN cda.是否16点前到仓 = 0 AND cda.是否考核通过 = 1
            THEN cda.NeutralWaybillNumber 
        END) AS 16点后考核通过包裹数
    FROM CompletionDatesWithArrivalInfo cda
    GROUP BY cda.完成日期
),

-- 步骤6：按到货日期聚合的到货信息统计
DailyArrivalStats AS (
    SELECT 
        ar.到货日期,
        COUNT(DISTINCT ar.RequestId) AS 当日新增换单数,
        COUNT(DISTINCT CASE 
            WHEN HOUR(ar.到货时间) < 16
            THEN ar.RequestId 
        END) AS 16点前到仓包裹数,
        COUNT(DISTINCT CASE 
            WHEN HOUR(ar.到货时间) >= 16
            THEN ar.RequestId 
        END) AS 16点后到仓包裹数
    FROM ArrivalRequests ar
    GROUP BY ar.到货日期
),

-- 步骤7：获取所有需要显示的完成日期
CompletionDates AS (
    SELECT DISTINCT DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期
    FROM OverallScanStatus oss
    WHERE oss.曾成功 = 1
),

-- 最终报表
SELECT 
    cd.日期,
    -- 完成数据（本日完成的所有包裹）
    COALESCE(dcs.当日换单成功数, 0) AS 当日换单成功数,
    COALESCE(dcs.16点前考核通过包裹数, 0) AS 16点前考核通过包裹数,
    COALESCE(dcs.16点后考核通过包裹数, 0) AS 16点后考核通过包裹数,
    -- 到货数据（该日期及之前到货的新增包裹）
    COALESCE(SUM(das.当日新增换单数) OVER (ORDER BY cd.日期 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS 累计新增包裹,
    COALESCE(das.16点前到仓包裹数, 0) AS 16点前到仓包裹数,
    COALESCE(das.16点后到仓包裹数, 0) AS 16点后到仓包裹数,
    -- 其他统计指标...
    (UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间
FROM CompletionDates cd
LEFT JOIN DailyCompletionStats dcs ON cd.日期 = dcs.日期
LEFT JOIN DailyArrivalStats das ON cd.日期 = das.到货日期
ORDER BY cd.日期 DESC;

关键修改说明

⚠️ 需要删除的CTE（已过时）

以下CTE应该被删除，因为它们基于错误的逻辑：

❌ DistinctDates - 混合了多个日期来源
❌ LatestDate - 不再需要
❌ DailyBase - 使用了CROSS JOIN，逻辑错误
❌ HistoryUnfinished - 基于错误的报表维度
❌ LatestUnfinished - 基于错误的报表维度
❌ DailyCompletedOrders - 基于到货日期而非完成日期
❌ Daily24HCompletedOrders - 基于错误的维度
❌ DailyBeforeNoonPassed - 基于错误的维度
❌ DailyAfternoonPassed - 基于错误的维度
❌ DailyStatsWithPrev - 基于错误的维度
❌ 最终的复杂子查询与变量计算 - 需要重写

⚠️ 需要保留和修改的CTE

以下CTE需要保留，但可能需要微调：

✅ ArrivalFormsWithDate - 保持不变
✅ InterchangeUnitLabelRates - 已修复
✅ ArrivalRequests - 已修复
✅ DailyScanStatus - 保持不变
✅ OverallScanStatus - 保持不变
✅ DailyScanMetrics - 保持，但使用完成日期聚合
✅ DailySuccessCount - 保持，已基于完成日期

实施步骤

第一步：删除旧的CTE（第809-1036行）

删除以下范围内的所有旧CTE定义和最终的复杂查询逻辑：

AllDates
DistinctDates
LatestDate
DailyBase
DailyScanMetrics
DailySuccessCount
HistoryUnfinished
LatestUnfinished
DailyFailedOrders
DailyCompletedOrders
Daily24HCompletedOrders
DailyBeforeNoonPassed
DailyAfternoonPassed
DailyStatsWithPrev
最终的复杂SELECT...FROM子查询

第二步：添加新的CTE

在保留的CTE之后，添加新的4个CTE（见上面的框架）：

CompletionDatesWithArrivalInfo
DailyCompletionStats
DailyArrivalStats
CompletionDates

第三步：替换最终SELECT

用新的简化版本替换原有的复杂最终SELECT查询。

报表输出对比

改进前（错误）

日期(到货日期)  当日新增  当日完成  16点前考核  16点后考核
2024-05-16     100      X        X           X
(到货日期的统计，但完成数据可能来自其他日期 - 混乱)

改进后（正确）

日期(完成日期)  当日完成  16点前考核  16点后考核  累计新增  16点前到仓  16点后到仓
2024-05-16     80       50          18         100        60          40
(完成日期的统计，完成数据准确，到货数据可能来自之前多天 - 清晰)

重点注意事项

1️⃣ 日期维度变化

报表行 = 一个完成日期（首次成功日期）
到货数据 = 该完成日期对应的到货统计（可能来自不同到货日期）
完成数据 = 该完成日期完成的所有包裹

2️⃣ 考核通过的准确判定

考核通过 = 包裹首次成功时间 <= 该包裹的考核时间

其中考核时间由以下规则确定：
- IF 标签率 >= 80% AND 到仓时间 < 16点: 考核时间 = 当日16点 ~ 次日16点
- IF 标签率 >= 80% AND 到仓时间 >= 16点: 考核时间 = 当日16点 ~ 次日23:59:59
- IF 标签率 < 80%: 首次成功时间本身就是考核时间（完成即达标）

3️⃣ 16点分段的准确含义

16点前考核通过 = 该完成日期完成的、来自16点前到仓的订单中满足其考核时间的包裹数
16点后考核通过 = 该完成日期完成的、来自16点后到仓的订单中满足其考核时间的包裹数

下一步

用户需要根据此模板，手动重构SQL代码或提供完整的新SQL供我直接替换到Repository中。

7.8 KiB Raw Permalink Blame History Unescape Escape