46 lines
1.3 KiB
SQL
46 lines
1.3 KiB
SQL
-- 总无人认领数统计（单独查询，用于排查）
|
||
-- 逻辑：扫描历史记录中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; |