LabelChange-server/customer-daily-stats.sql

-- 客户维度每日标签统计查询（不关联订单表）
-- 设计逻辑：
-- 1. 基于 label_scan_history、customers 两表关联
-- 2. 统计维度：客户简称、日期、扫描数、换单成功数、换单失败数、STOP数等

WITH 
-- 步骤1：获取客户信息
CustomerInfo AS (
    SELECT 
        c.id AS CustomerId,
        c.CustomerCode,
        c.CustomerName
    FROM customers c
    WHERE c.Status = 'Y'
),

-- 步骤2：关联扫描记录与客户信息
ScanWithCustomer AS (
    SELECT 
        s.NeutralWaybillNumber,
        s.CustomerId AS ScanCustomerId,
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 扫描日期,
        s.Result,
        s.Description,
        s.CreatedAt AS 扫描时间,
        c.CustomerCode,
        c.CustomerName
    FROM label_scan_history s
    LEFT JOIN CustomerInfo c ON s.CustomerId = c.CustomerId
),

-- 步骤3：获取每个中性面单号的首次成功时间和日期
NeutralWaybillSuccessInfo AS (
    SELECT 
        s.NeutralWaybillNumber,
        MIN(CASE WHEN s.Result = 0 THEN s.CreatedAt ELSE NULL END) AS 首次成功时间,
        MIN(CASE WHEN s.Result = 0 THEN DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) ELSE NULL END) AS 首次成功日期
    FROM label_scan_history s
    GROUP BY s.NeutralWaybillNumber
),

-- 步骤4：每日扫描数统计（去重）- 有客户简称
DailyScanCount AS (
    SELECT 
        CustomerCode,
        扫描日期,
        COUNT(DISTINCT NeutralWaybillNumber) AS 扫描数
    FROM (
        -- 每个中性面单号每天取最新一条记录
        SELECT 
            CustomerCode,
            NeutralWaybillNumber,
            扫描日期,
            ROW_NUMBER() OVER (PARTITION BY NeutralWaybillNumber, 扫描日期 ORDER BY 扫描时间 DESC) AS rn
        FROM ScanWithCustomer
        WHERE CustomerCode IS NOT NULL
    ) AS t
    WHERE rn = 1
    GROUP BY CustomerCode, 扫描日期
),

-- 步骤5：每日换单成功数统计（去重）- 有客户简称，去除STOP数
DailySuccessCount AS (
    SELECT 
        CustomerCode,
        扫描日期,
        COUNT(DISTINCT NeutralWaybillNumber) AS 换单成功数
    FROM (
        SELECT 
            CustomerCode,
            NeutralWaybillNumber,
            扫描日期,
            MAX(CASE WHEN Result = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY NeutralWaybillNumber, 扫描日期) AS has_success,
            MAX(CASE WHEN Result = 0 AND Description LIKE '%STOP%' THEN 1 ELSE 0 END) OVER (PARTITION BY NeutralWaybillNumber, 扫描日期) AS has_stop,
            ROW_NUMBER() OVER (PARTITION BY NeutralWaybillNumber, 扫描日期 ORDER BY 扫描时间 DESC) AS rn
        FROM ScanWithCustomer
        WHERE CustomerCode IS NOT NULL
    ) AS t
    WHERE rn = 1 AND has_success = 1 AND has_stop = 0
    GROUP BY CustomerCode, 扫描日期
),

-- 步骤6：每日换单失败数统计（去重）- 有客户简称，除去换单成功记录
DailyFailedCount AS (
    SELECT 
        CustomerCode,
        扫描日期,
        COUNT(DISTINCT NeutralWaybillNumber) AS 换单失败数
    FROM (
        SELECT 
            CustomerCode,
            NeutralWaybillNumber,
            扫描日期,
            MAX(CASE WHEN Result = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY NeutralWaybillNumber, 扫描日期) AS has_success,
            MAX(CASE WHEN Result != 0 THEN 1 ELSE 0 END) OVER (PARTITION BY NeutralWaybillNumber, 扫描日期) AS has_failure,
            ROW_NUMBER() OVER (PARTITION BY NeutralWaybillNumber, 扫描日期 ORDER BY 扫描时间 DESC) AS rn
        FROM ScanWithCustomer
        WHERE CustomerCode IS NOT NULL
    ) AS t
    WHERE rn = 1 AND has_success = 0
    GROUP BY CustomerCode, 扫描日期
),

-- 步骤7：每日STOP数统计（去重）- 有客户简称
DailyStopCount AS (
    SELECT 
        CustomerCode,
        扫描日期,
        COUNT(DISTINCT NeutralWaybillNumber) AS STOP数
    FROM (
        SELECT 
            CustomerCode,
            NeutralWaybillNumber,
            扫描日期,
            MAX(CASE WHEN Result = 0 AND Description LIKE '%STOP%' THEN 1 ELSE 0 END) OVER (PARTITION BY NeutralWaybillNumber, 扫描日期) AS has_stop,
            ROW_NUMBER() OVER (PARTITION BY NeutralWaybillNumber, 扫描日期 ORDER BY 扫描时间 DESC) AS rn
        FROM ScanWithCustomer
        WHERE CustomerCode IS NOT NULL
    ) AS t
    WHERE rn = 1 AND has_stop = 1
    GROUP BY CustomerCode, 扫描日期
),

-- 步骤8：总无人认领数统计 - CustomerId为0的扫描记录（去重）
DailyUnclaimedCount AS (
    SELECT 
        扫描日期,
        COUNT(DISTINCT NeutralWaybillNumber) AS 总无人认领数
    FROM (
        SELECT 
            NeutralWaybillNumber,
            扫描日期,
            ROW_NUMBER() OVER (PARTITION BY NeutralWaybillNumber, 扫描日期 ORDER BY 扫描时间 DESC) AS rn
        FROM ScanWithCustomer
        WHERE ScanCustomerId = 0
    ) AS t
    WHERE rn = 1
    GROUP BY 扫描日期
),

-- 步骤9：总扫描次数统计（不去重）
DailyTotalScanCount AS (
    SELECT 
        扫描日期,
        COUNT(*) AS 总扫描次数
    FROM ScanWithCustomer
    GROUP BY 扫描日期
),

-- 步骤10：收集所有日期
AllDates AS (
    SELECT DISTINCT 扫描日期 FROM DailyScanCount
    UNION
    SELECT DISTINCT 扫描日期 FROM DailySuccessCount
    UNION
    SELECT DISTINCT 扫描日期 FROM DailyFailedCount
    UNION
    SELECT DISTINCT 扫描日期 FROM DailyStopCount
    UNION
    SELECT DISTINCT 扫描日期 FROM DailyUnclaimedCount
    UNION
    SELECT DISTINCT 扫描日期 FROM DailyTotalScanCount
),

-- 步骤11：收集所有客户简称
AllCustomers AS (
    SELECT DISTINCT CustomerCode FROM DailyScanCount
    UNION
    SELECT DISTINCT CustomerCode FROM DailySuccessCount
    UNION
    SELECT DISTINCT CustomerCode FROM DailyFailedCount
    UNION
    SELECT DISTINCT CustomerCode FROM DailyStopCount
)

-- 最终查询结果
SELECT 
    ad.扫描日期 AS 日期,
    ac.CustomerCode AS 客户简称,
    COALESCE(dsc.扫描数, 0) AS 扫描数,
    COALESCE(dsuc.换单成功数, 0) AS 换单成功数,
    COALESCE(dfac.换单失败数, 0) AS 换单失败数,
    COALESCE(dstc.STOP数, 0) AS STOP数,
    -- 换单成功率
    CASE 
        WHEN COALESCE(dsc.扫描数, 0) = 0 THEN '0.00%'
        ELSE CONCAT(ROUND(COALESCE(dsuc.换单成功数, 0) / COALESCE(dsc.扫描数, 0) * 100, 2), '%')
    END AS 换单成功率,
    COALESCE(duc.总无人认领数, 0) AS 总无人认领数,
    COALESCE(dtsc.总扫描次数, 0) AS 总扫描次数,
    (UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间（UTC_5）
FROM AllDates ad
CROSS JOIN AllCustomers ac
LEFT JOIN DailyScanCount dsc ON ad.扫描日期 = dsc.扫描日期 AND ac.CustomerCode = dsc.CustomerCode
LEFT JOIN DailySuccessCount dsuc ON ad.扫描日期 = dsuc.扫描日期 AND ac.CustomerCode = dsuc.CustomerCode
LEFT JOIN DailyFailedCount dfac ON ad.扫描日期 = dfac.扫描日期 AND ac.CustomerCode = dfac.CustomerCode
LEFT JOIN DailyStopCount dstc ON ad.扫描日期 = dstc.扫描日期 AND ac.CustomerCode = dstc.CustomerCode
LEFT JOIN DailyUnclaimedCount duc ON ad.扫描日期 = duc.扫描日期
LEFT JOIN DailyTotalScanCount dtsc ON ad.扫描日期 = dtsc.扫描日期
WHERE COALESCE(dsc.扫描数, 0) > 0
ORDER BY ad.扫描日期 DESC, ac.CustomerCode;