146 lines
5.8 KiB
SQL
146 lines
5.8 KiB
SQL
-- ==============================================================
|
||
-- 日汇总指标视图 - v_DailyMetricsSummary
|
||
-- 用途：一次查询获取所有日汇总指标，替代 11 个独立的 SQL 查询
|
||
-- 创建日期：2026-05-17
|
||
-- 实际表名：label_replace_requests, label_scan_history, arrival_handover_forms
|
||
-- ==============================================================
|
||
|
||
DROP VIEW IF EXISTS v_DailyMetricsSummary;
|
||
|
||
CREATE VIEW v_DailyMetricsSummary AS
|
||
SELECT
|
||
-- 日期信息
|
||
CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) AS MetricsDate,
|
||
|
||
-- 核心数量指标 - 当天新增换单数（有标签的订单）
|
||
COUNT(DISTINCT CASE
|
||
WHEN CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
AND r.Label IS NOT NULL
|
||
THEN r.Id
|
||
END) AS DailyNewReplaceCount,
|
||
|
||
-- 应该换单数 (新增 + 积压未完成)
|
||
COUNT(DISTINCT CASE
|
||
WHEN CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) <= CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
AND r.Label IS NOT NULL
|
||
THEN r.Id
|
||
END) AS DailyShouldReplaceCount,
|
||
|
||
-- 完成数 (成功扫描 Result=0)
|
||
COUNT(DISTINCT CASE
|
||
WHEN r.Label IS NOT NULL
|
||
AND s.Id IS NOT NULL
|
||
AND s.Result = 0
|
||
AND CAST(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
THEN r.Id
|
||
END) AS DailySuccessCount,
|
||
|
||
-- 累计未完成数 (历史数据中未完成的)
|
||
COUNT(DISTINCT CASE
|
||
WHEN r.Label IS NOT NULL
|
||
AND (s.Id IS NULL OR s.Result != 0)
|
||
AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) < CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
THEN r.Id
|
||
END) AS CumulativeTotalReplaceCount,
|
||
|
||
-- STOP 数 (ReplaceStatus = 'N')
|
||
COUNT(DISTINCT CASE
|
||
WHEN r.ReplaceStatus = 'N'
|
||
AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
THEN r.Id
|
||
END) AS DailyStopCount,
|
||
|
||
-- 标签推送数 (Label 不为空的订单)
|
||
COUNT(DISTINCT CASE
|
||
WHEN r.Label IS NOT NULL
|
||
AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
THEN r.Id
|
||
END) AS DailyLabelPushCount,
|
||
|
||
-- 扫描数 (当日扫描记录)
|
||
COUNT(DISTINCT CASE
|
||
WHEN CAST(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
THEN s.Id
|
||
END) AS DailyScanCount,
|
||
|
||
-- 16点前到仓数 (UTC-5 时区)
|
||
COUNT(DISTINCT CASE
|
||
WHEN HOUR(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00')) < 16
|
||
AND CAST(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
THEN ahf.Id
|
||
END) AS BeforeNoonArrivedCount,
|
||
|
||
-- 16点后到仓数
|
||
COUNT(DISTINCT CASE
|
||
WHEN HOUR(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00')) >= 16
|
||
AND CAST(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
THEN ahf.Id
|
||
END) AS AfternoonArrivedCount,
|
||
|
||
-- 16点前完成数
|
||
COUNT(DISTINCT CASE
|
||
WHEN HOUR(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00')) < 16
|
||
AND CAST(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
AND s.Result = 0
|
||
THEN s.Id
|
||
END) AS BeforeNoonPassedCount,
|
||
|
||
-- 16点后完成数
|
||
COUNT(DISTINCT CASE
|
||
WHEN HOUR(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00')) >= 16
|
||
AND CAST(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
AND s.Result = 0
|
||
THEN s.Id
|
||
END) AS AfternoonPassedCount,
|
||
|
||
-- 失败数 (应该完成但未完成)
|
||
COUNT(DISTINCT CASE
|
||
WHEN r.Label IS NOT NULL
|
||
AND (s.Id IS NULL OR s.Result != 0)
|
||
AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
|
||
THEN r.Id
|
||
END) AS DailyFailureCount,
|
||
|
||
-- 时间戳
|
||
NOW() AS DataFetchTime
|
||
|
||
FROM label_replace_requests r
|
||
LEFT JOIN label_scan_history s ON r.NeutralWaybillNumber = s.NeutralWaybillNumber
|
||
AND s.Id = (
|
||
SELECT Id FROM label_scan_history
|
||
WHERE NeutralWaybillNumber = r.NeutralWaybillNumber
|
||
ORDER BY CreatedAt ASC LIMIT 1
|
||
)
|
||
LEFT JOIN arrival_handover_forms ahf ON r.BillOfLadingNumber = ahf.HandoverNumber
|
||
OR r.MasterPackageNumber = ahf.HandoverNumber
|
||
|
||
WHERE r.Label IS NOT NULL
|
||
AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
|
||
|
||
GROUP BY CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE);
|
||
|
||
-- ==============================================================
|
||
-- 索引优化（所有表已有足够的索引，无需添加）
|
||
-- ==============================================================
|
||
|
||
-- label_replace_requests 表现有索引：
|
||
-- idx_neutral_waybill_number (唯一)，idx_created_at，idx_replace_status
|
||
-- idx_customer_id，idx_final_mile_tracking_number
|
||
-- idx_customer_id_created_at (复合)
|
||
|
||
-- label_scan_history 表现有索引：
|
||
-- IX_CustomerId，IX_NeutralWaybillNumber，IX_ReferenceNumber
|
||
-- IX_FinalMileTrackingNumber，IX_CreatedAt，IX_Result
|
||
-- idx_customer_id_neutral_waybill_number (复合)
|
||
|
||
-- arrival_handover_forms 表现有索引：
|
||
-- UQ_HandoverNumber (唯一)
|
||
|
||
-- 结论：现有索引已足以支持视图查询，无需添加新索引
|
||
|
||
-- ==============================================================
|
||
-- 使用示例
|
||
-- ==============================================================
|
||
-- SELECT * FROM v_DailyMetricsSummary WHERE MetricsDate = '2026-05-17';
|
||
|