-- 总无人认领数统计（单独查询，用于排查）
-- 逻辑：扫描历史记录中CustomerId为0的，有扫描记录的订单数（去重）

WITH 
-- 步骤1：获取所有CustomerId为0的扫描记录
UnclaimedScans AS (
    SELECT 
        s.NeutralWaybillNumber,
        s.CustomerId,
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 扫描日期,
        s.CreatedAt AS 扫描时间,
        s.Result,
        s.Description
    FROM label_scan_history s
    WHERE s.CustomerId = 0
),

-- 步骤2：每个中性面单号每天取最新一条记录
DailyUnclaimedDistinct AS (
    SELECT 
        NeutralWaybillNumber,
        扫描日期,
        扫描时间,
        Result,
        Description,
        ROW_NUMBER() OVER (PARTITION BY NeutralWaybillNumber, 扫描日期 ORDER BY 扫描时间 DESC) AS rn
    FROM UnclaimedScans
),

-- 步骤3：统计每日总无人认领数（去重）
DailyUnclaimedCount AS (
    SELECT 
        扫描日期,
        COUNT(DISTINCT NeutralWaybillNumber) AS 总无人认领数
    FROM DailyUnclaimedDistinct
    WHERE rn = 1
    GROUP BY 扫描日期
)

-- 最终查询结果
SELECT 
    扫描日期 AS 日期,
    总无人认领数,
    (UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间（UTC_5）
FROM DailyUnclaimedCount
ORDER BY 扫描日期 DESC;