LabelChange-server/daily-label-stats.sql

-- 每日标签统计 SQL 查询
-- 本文件包含用于获取每日标签统计数据的 SQL 查询语句

-- 1. 查询换单失败未完结数（所有有扫描记录但未能成功换单的数）
SELECT COUNT(DISTINCT l.NeutralWaybillNumber) AS UnfinishedFailureCount
FROM label_replace_requests l
INNER JOIN label_scan_history s ON l.NeutralWaybillNumber = s.NeutralWaybillNumber
WHERE l.Label IS NOT NULL 
  AND l.Label != ''
  AND NOT EXISTS (
      SELECT 1 
      FROM label_scan_history s2 
      WHERE s2.NeutralWaybillNumber = l.NeutralWaybillNumber 
      AND s2.Result = 0
  );

-- 2. 按日期统计当日扫描数
SELECT 
    DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date,
    COUNT(*) AS DailyScanCount
FROM label_scan_history s
INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
WHERE l.Label IS NOT NULL 
  AND l.Label != ''
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
ORDER BY Date;

-- 3. 按日期统计当日标签推送数（LabelRetrievedAt）
SELECT 
    DATE(CONVERT_TZ(l.LabelRetrievedAt, '+00:00', '-05:00')) AS Date,
    COUNT(*) AS DailyLabelPushCount
FROM label_replace_requests l
WHERE l.Label IS NOT NULL 
  AND l.Label != ''
  AND l.LabelRetrievedAt IS NOT NULL
GROUP BY DATE(CONVERT_TZ(l.LabelRetrievedAt, '+00:00', '-05:00'))
ORDER BY Date;

-- 4. 按日期统计当日换单成功数（Result=0）
SELECT 
    DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date,
    COUNT(*) AS DailySuccessCount
FROM label_scan_history s
INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
WHERE l.Label IS NOT NULL 
  AND l.Label != ''
  AND s.Result = 0
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
ORDER BY Date;

-- 5. 按日期统计当日STOP数（Result=0且描述包含“成功返回STOP标签”）
SELECT 
    DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date,
    COUNT(*) AS DailyStopCount
FROM label_scan_history s
INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
WHERE l.Label IS NOT NULL 
  AND l.Label != ''
  AND s.Result = 0
  AND s.Description LIKE '%成功返回STOP标签%'
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
ORDER BY Date;

-- 6. 按日期统计当日换单失败数（Result!=0）
SELECT 
    DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date,
    COUNT(*) AS DailyFailureCount
FROM label_scan_history s
INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
WHERE l.Label IS NOT NULL 
  AND l.Label != ''
  AND s.Result != 0
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
ORDER BY Date;

-- 7. 综合查询（获取所有统计数据）
WITH UnfinishedFailure AS (
    SELECT COUNT(DISTINCT l.NeutralWaybillNumber) AS Count
    FROM label_replace_requests l
    INNER JOIN label_scan_history s ON l.NeutralWaybillNumber = s.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL 
      AND l.Label != ''
      AND NOT EXISTS (
          SELECT 1 
          FROM label_scan_history s2 
          WHERE s2.NeutralWaybillNumber = l.NeutralWaybillNumber 
          AND s2.Result = 0
      )
),
AllDates AS (
    SELECT DISTINCT DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != ''
    UNION
    SELECT DISTINCT DATE(CONVERT_TZ(l.LabelRetrievedAt, '+00:00', '-05:00')) AS Date
    FROM label_replace_requests l
    WHERE l.Label IS NOT NULL AND l.Label != '' AND l.LabelRetrievedAt IS NOT NULL
)
SELECT 
    ad.Date,
    (SELECT Count FROM UnfinishedFailure) AS UnfinishedFailureCount,
    COALESCE(scan.DailyScanCount, 0) AS DailyScanCount,
    COALESCE(push.DailyLabelPushCount, 0) AS DailyLabelPushCount,
    COALESCE(success.DailySuccessCount, 0) AS DailySuccessCount,
    COALESCE(stop.DailyStopCount, 0) AS DailyStopCount,
    COALESCE(failure.DailyFailureCount, 0) AS DailyFailureCount,
    UTC_TIMESTAMP() - INTERVAL 5 HOUR AS DataFetchTime
FROM AllDates ad
LEFT JOIN (
    SELECT 
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date,
        COUNT(*) AS DailyScanCount
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != ''
    GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
) scan ON ad.Date = scan.Date
LEFT JOIN (
    SELECT 
        DATE(CONVERT_TZ(l.LabelRetrievedAt, '+00:00', '-05:00')) AS Date,
        COUNT(*) AS DailyLabelPushCount
    FROM label_replace_requests l
    WHERE l.Label IS NOT NULL AND l.Label != '' AND l.LabelRetrievedAt IS NOT NULL
    GROUP BY DATE(CONVERT_TZ(l.LabelRetrievedAt, '+00:00', '-05:00'))
) push ON ad.Date = push.Date
LEFT JOIN (
    SELECT 
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date,
        COUNT(*) AS DailySuccessCount
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != '' AND s.Result = 0
    GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
) success ON ad.Date = success.Date
LEFT JOIN (
    SELECT 
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date,
        COUNT(*) AS DailyStopCount
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != '' AND s.Result = 0 AND s.Description LIKE '%成功返回STOP标签%'
    GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
) stop ON ad.Date = stop.Date
LEFT JOIN (
    SELECT 
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS Date,
        COUNT(*) AS DailyFailureCount
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != '' AND s.Result != 0
    GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
) failure ON ad.Date = failure.Date
ORDER BY ad.Date;