473 lines
22 KiB
SQL
473 lines
22 KiB
SQL
DROP PROCEDURE IF EXISTS sp_GetOperationsMonitor;
|
||
|
||
DELIMITER $$
|
||
|
||
CREATE PROCEDURE sp_GetOperationsMonitor()
|
||
BEGIN
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_scan_agg;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_daily_scan;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_form_order;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_form_first_scan;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_form_stats;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_form_push_ranked;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_order_full;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_should_replace_dedup;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_label_push;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_daily_metrics;
|
||
|
||
-- ================================================================
|
||
-- 步骤1a：tmp_scan_agg - 每个运单的扫描摘要
|
||
-- 一次全量扫描 label_scan_history，消除后续所有重复扫描
|
||
-- ================================================================
|
||
CREATE TEMPORARY TABLE tmp_scan_agg (
|
||
NeutralWaybillNumber VARCHAR(100) NOT NULL,
|
||
MinScanTime DATETIME NULL,
|
||
FirstSuccessTime_UTC5 DATETIME NULL,
|
||
FirstSuccessDate_UTC5 DATE NULL,
|
||
IsSuccess TINYINT NOT NULL DEFAULT 0,
|
||
IsStopLabel TINYINT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (NeutralWaybillNumber)
|
||
);
|
||
|
||
INSERT INTO tmp_scan_agg
|
||
SELECT
|
||
NeutralWaybillNumber,
|
||
MIN(CreatedAt),
|
||
MIN(CASE WHEN Result = 0 THEN CONVERT_TZ(CreatedAt, '+00:00', '-05:00') END),
|
||
DATE(MIN(CASE WHEN Result = 0 THEN CONVERT_TZ(CreatedAt, '+00:00', '-05:00') END)),
|
||
MAX(CASE WHEN Result = 0 THEN 1 ELSE 0 END),
|
||
MAX(CASE WHEN Result = 0 AND Description LIKE '%成功返回STOP标签%' THEN 1 ELSE 0 END)
|
||
FROM label_scan_history
|
||
GROUP BY NeutralWaybillNumber;
|
||
|
||
-- ================================================================
|
||
-- 步骤1b：tmp_daily_scan - 每日扫描统计（扫描数/完成数/失败数/STOP数）
|
||
-- 与步骤1a共用同一次 label_scan_history 扫描
|
||
-- ================================================================
|
||
CREATE TEMPORARY TABLE tmp_daily_scan (
|
||
日期 DATE NOT NULL,
|
||
当日扫描数 INT NOT NULL DEFAULT 0,
|
||
当日换单完成数 INT NOT NULL DEFAULT 0,
|
||
当日换单失败数 INT NOT NULL DEFAULT 0,
|
||
当日STOP数 INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (日期)
|
||
);
|
||
|
||
INSERT INTO tmp_daily_scan (日期, 当日扫描数, 当日换单完成数, 当日STOP数)
|
||
SELECT
|
||
DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00')),
|
||
COUNT(*),
|
||
COUNT(DISTINCT CASE WHEN Result = 0 THEN NeutralWaybillNumber END),
|
||
COUNT(DISTINCT CASE WHEN Result = 0 AND Description LIKE '%成功返回STOP标签%' THEN NeutralWaybillNumber END)
|
||
FROM label_scan_history
|
||
GROUP BY DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00'));
|
||
|
||
UPDATE tmp_daily_scan ds
|
||
INNER JOIN (
|
||
SELECT
|
||
DATE(CONVERT_TZ(h.CreatedAt, '+00:00', '-05:00')) AS 日期,
|
||
COUNT(DISTINCT h.NeutralWaybillNumber) AS 当日换单失败数
|
||
FROM label_scan_history h
|
||
INNER JOIN tmp_scan_agg sa ON h.NeutralWaybillNumber = sa.NeutralWaybillNumber
|
||
WHERE h.Result != 0
|
||
AND sa.IsSuccess = 0
|
||
GROUP BY DATE(CONVERT_TZ(h.CreatedAt, '+00:00', '-05:00'))
|
||
) fail_data ON ds.日期 = fail_data.日期
|
||
SET ds.当日换单失败数 = fail_data.当日换单失败数;
|
||
|
||
-- ================================================================
|
||
-- 步骤2：tmp_form_order - 关联交接单和换单请求
|
||
-- 先对 arrival_handover_forms 两次索引查找（HandoverNumber 有唯一索引）
|
||
-- 加上步骤1新建的 idx_master_package_number / idx_bill_of_lading_number 索引
|
||
-- ================================================================
|
||
CREATE TEMPORARY TABLE tmp_form_order (
|
||
FormId INT NOT NULL,
|
||
HandoverNumber VARCHAR(100) NOT NULL,
|
||
ReceiptTime DATETIME NULL,
|
||
ReceiptDate DATE NULL,
|
||
ReceiptHour TINYINT NULL,
|
||
RequestId INT NOT NULL,
|
||
NeutralWaybillNumber VARCHAR(100) NOT NULL,
|
||
LabelRetrievedAt_UTC5 DATETIME NULL,
|
||
LabelRetrievedDate_UTC5 DATE NULL,
|
||
RequestCreatedAt DATETIME NOT NULL,
|
||
HasLabel TINYINT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (RequestId),
|
||
INDEX idx_form_id (FormId),
|
||
INDEX idx_waybill (NeutralWaybillNumber),
|
||
INDEX idx_receipt_date (ReceiptDate),
|
||
INDEX idx_label_date (LabelRetrievedDate_UTC5)
|
||
);
|
||
|
||
INSERT INTO tmp_form_order
|
||
SELECT
|
||
COALESCE(f_m.Id, f_b.Id) AS FormId,
|
||
COALESCE(f_m.HandoverNumber, f_b.HandoverNumber) AS HandoverNumber,
|
||
COALESCE(f_m.ReceiptTime, f_b.ReceiptTime) AS ReceiptTime,
|
||
DATE(COALESCE(f_m.ReceiptTime, f_b.ReceiptTime)) AS ReceiptDate,
|
||
HOUR(COALESCE(f_m.ReceiptTime, f_b.ReceiptTime)) AS ReceiptHour,
|
||
r.Id AS RequestId,
|
||
r.NeutralWaybillNumber,
|
||
CASE WHEN r.LabelRetrievedAt IS NOT NULL
|
||
THEN CONVERT_TZ(r.LabelRetrievedAt, '+00:00', '-05:00') END AS LabelRetrievedAt_UTC5,
|
||
DATE(CONVERT_TZ(r.LabelRetrievedAt, '+00:00', '-05:00')) AS LabelRetrievedDate_UTC5,
|
||
r.CreatedAt AS RequestCreatedAt,
|
||
CASE WHEN r.Label IS NOT NULL AND r.Label != '' THEN 1 ELSE 0 END AS HasLabel
|
||
FROM label_replace_requests r
|
||
LEFT JOIN arrival_handover_forms f_m ON r.MasterPackageNumber = f_m.HandoverNumber
|
||
LEFT JOIN arrival_handover_forms f_b ON r.BillOfLadingNumber = f_b.HandoverNumber
|
||
WHERE COALESCE(f_m.Id, f_b.Id) IS NOT NULL;
|
||
|
||
-- ================================================================
|
||
-- 步骤3：tmp_form_first_scan - 每个交接单的首次扫描时间
|
||
-- 直接读取 tmp_scan_agg，无需再扫 label_scan_history
|
||
-- ================================================================
|
||
CREATE TEMPORARY TABLE tmp_form_first_scan (
|
||
FormId INT NOT NULL,
|
||
FirstScanTime_UTC5 DATETIME NULL,
|
||
PRIMARY KEY (FormId)
|
||
);
|
||
|
||
INSERT INTO tmp_form_first_scan
|
||
SELECT
|
||
fo.FormId,
|
||
CONVERT_TZ(MIN(sa.MinScanTime), '+00:00', '-05:00')
|
||
FROM tmp_form_order fo
|
||
LEFT JOIN tmp_scan_agg sa ON fo.NeutralWaybillNumber = sa.NeutralWaybillNumber
|
||
GROUP BY fo.FormId;
|
||
|
||
-- ================================================================
|
||
-- 步骤4：tmp_form_stats - 每个交接单的总数、达标阈值和首次达标时间
|
||
-- ================================================================
|
||
CREATE TEMPORARY TABLE tmp_form_stats (
|
||
FormId INT NOT NULL,
|
||
TotalOrderCount INT NOT NULL DEFAULT 0,
|
||
QualifyNeedCount INT NOT NULL DEFAULT 0,
|
||
FirstQualifiedTime_UTC5 DATETIME NULL,
|
||
PRIMARY KEY (FormId)
|
||
);
|
||
|
||
INSERT INTO tmp_form_stats (FormId, TotalOrderCount, QualifyNeedCount)
|
||
SELECT
|
||
FormId,
|
||
COUNT(DISTINCT RequestId),
|
||
CEIL(COUNT(DISTINCT RequestId) * 0.8)
|
||
FROM tmp_form_order
|
||
GROUP BY FormId;
|
||
|
||
CREATE TEMPORARY TABLE tmp_form_push_ranked (
|
||
FormId INT NOT NULL,
|
||
LabelRetrievedAt_UTC5 DATETIME NULL,
|
||
PushOrder INT NOT NULL,
|
||
QualifyNeedCount INT NOT NULL,
|
||
INDEX idx_form_push (FormId, PushOrder)
|
||
);
|
||
|
||
INSERT INTO tmp_form_push_ranked
|
||
SELECT
|
||
fo.FormId,
|
||
fo.LabelRetrievedAt_UTC5,
|
||
ROW_NUMBER() OVER (PARTITION BY fo.FormId ORDER BY fo.LabelRetrievedAt_UTC5),
|
||
fs.QualifyNeedCount
|
||
FROM tmp_form_order fo
|
||
INNER JOIN tmp_form_stats fs ON fo.FormId = fs.FormId
|
||
WHERE fo.HasLabel = 1 AND fo.LabelRetrievedAt_UTC5 IS NOT NULL;
|
||
|
||
UPDATE tmp_form_stats fs
|
||
INNER JOIN (
|
||
SELECT
|
||
FormId,
|
||
MIN(LabelRetrievedAt_UTC5) AS FirstQualifiedTime_UTC5
|
||
FROM tmp_form_push_ranked
|
||
WHERE PushOrder >= QualifyNeedCount
|
||
GROUP BY FormId
|
||
) q ON fs.FormId = q.FormId
|
||
SET fs.FirstQualifiedTime_UTC5 = q.FirstQualifiedTime_UTC5;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_form_push_ranked;
|
||
|
||
-- ================================================================
|
||
-- 步骤5：tmp_order_full - 每个有标签订单的完整信息（含考核时间）
|
||
-- 预先计算 AssessmentBaseTime / AssessmentTime / AssessmentDate
|
||
-- 避免后续重复展开 GREATEST() CASE WHEN
|
||
-- ================================================================
|
||
CREATE TEMPORARY TABLE tmp_order_full (
|
||
RequestId INT NOT NULL,
|
||
FormId INT NOT NULL,
|
||
NeutralWaybillNumber VARCHAR(100) NOT NULL,
|
||
HasLabel TINYINT NOT NULL DEFAULT 0,
|
||
ReceiptDate DATE NULL,
|
||
ReceiptTime DATETIME NULL,
|
||
LabelRetrievedDate_UTC5 DATE NULL,
|
||
LabelRetrievedAt_UTC5 DATETIME NULL,
|
||
RequestCreatedDate_UTC5 DATE NULL,
|
||
AssessmentBaseTime DATETIME NULL,
|
||
AssessmentBaseDate DATE NULL,
|
||
AssessmentTime DATETIME NULL,
|
||
AssessmentDate DATE NULL,
|
||
FirstSuccessTime_UTC5 DATETIME NULL,
|
||
FirstSuccessDate_UTC5 DATE NULL,
|
||
IsSuccess TINYINT NOT NULL DEFAULT 0,
|
||
IsStopLabel TINYINT NOT NULL DEFAULT 0,
|
||
IsCompletedInAssessment TINYINT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (RequestId),
|
||
INDEX idx_receipt_date (ReceiptDate),
|
||
INDEX idx_label_date (LabelRetrievedDate_UTC5),
|
||
INDEX idx_success_date (FirstSuccessDate_UTC5),
|
||
INDEX idx_assessment_base (AssessmentBaseDate),
|
||
INDEX idx_assessment_date (AssessmentDate),
|
||
INDEX idx_created_date (RequestCreatedDate_UTC5),
|
||
INDEX idx_success_label_date (IsSuccess, LabelRetrievedDate_UTC5, FirstSuccessDate_UTC5)
|
||
);
|
||
|
||
INSERT INTO tmp_order_full
|
||
SELECT
|
||
fo.RequestId,
|
||
fo.FormId,
|
||
fo.NeutralWaybillNumber,
|
||
fo.HasLabel,
|
||
fo.ReceiptDate,
|
||
fo.ReceiptTime,
|
||
fo.LabelRetrievedDate_UTC5,
|
||
fo.LabelRetrievedAt_UTC5,
|
||
DATE(CONVERT_TZ(fo.RequestCreatedAt, '+00:00', '-05:00')) AS RequestCreatedDate_UTC5,
|
||
GREATEST(
|
||
CASE
|
||
WHEN ffs.FirstScanTime_UTC5 IS NOT NULL
|
||
AND ffs.FirstScanTime_UTC5 < fo.ReceiptTime
|
||
THEN ffs.FirstScanTime_UTC5
|
||
ELSE fo.ReceiptTime
|
||
END,
|
||
fs.FirstQualifiedTime_UTC5
|
||
) AS AssessmentBaseTime,
|
||
DATE(GREATEST(
|
||
CASE
|
||
WHEN ffs.FirstScanTime_UTC5 IS NOT NULL
|
||
AND ffs.FirstScanTime_UTC5 < fo.ReceiptTime
|
||
THEN ffs.FirstScanTime_UTC5
|
||
ELSE fo.ReceiptTime
|
||
END,
|
||
fs.FirstQualifiedTime_UTC5
|
||
)) AS AssessmentBaseDate,
|
||
CASE
|
||
WHEN fs.FirstQualifiedTime_UTC5 IS NOT NULL AND fo.HasLabel = 1
|
||
THEN CASE
|
||
WHEN HOUR(GREATEST(
|
||
CASE
|
||
WHEN ffs.FirstScanTime_UTC5 IS NOT NULL
|
||
AND ffs.FirstScanTime_UTC5 < fo.ReceiptTime
|
||
THEN ffs.FirstScanTime_UTC5
|
||
ELSE fo.ReceiptTime
|
||
END,
|
||
fs.FirstQualifiedTime_UTC5
|
||
)) < 16
|
||
THEN DATE_ADD(
|
||
DATE_ADD(DATE(GREATEST(
|
||
CASE
|
||
WHEN ffs.FirstScanTime_UTC5 IS NOT NULL
|
||
AND ffs.FirstScanTime_UTC5 < fo.ReceiptTime
|
||
THEN ffs.FirstScanTime_UTC5
|
||
ELSE fo.ReceiptTime
|
||
END,
|
||
fs.FirstQualifiedTime_UTC5
|
||
)), INTERVAL 1 DAY),
|
||
INTERVAL 16 HOUR
|
||
)
|
||
ELSE DATE_ADD(
|
||
DATE_ADD(DATE(GREATEST(
|
||
CASE
|
||
WHEN ffs.FirstScanTime_UTC5 IS NOT NULL
|
||
AND ffs.FirstScanTime_UTC5 < fo.ReceiptTime
|
||
THEN ffs.FirstScanTime_UTC5
|
||
ELSE fo.ReceiptTime
|
||
END,
|
||
fs.FirstQualifiedTime_UTC5
|
||
)), INTERVAL 1 DAY),
|
||
INTERVAL '23:59:59' HOUR_SECOND
|
||
)
|
||
END
|
||
ELSE NULL
|
||
END AS AssessmentTime,
|
||
DATE(CASE
|
||
WHEN fs.FirstQualifiedTime_UTC5 IS NOT NULL AND fo.HasLabel = 1
|
||
THEN DATE_ADD(DATE(GREATEST(
|
||
CASE
|
||
WHEN ffs.FirstScanTime_UTC5 IS NOT NULL
|
||
AND ffs.FirstScanTime_UTC5 < fo.ReceiptTime
|
||
THEN ffs.FirstScanTime_UTC5
|
||
ELSE fo.ReceiptTime
|
||
END,
|
||
fs.FirstQualifiedTime_UTC5
|
||
)), INTERVAL 1 DAY)
|
||
ELSE NULL
|
||
END) AS AssessmentDate,
|
||
sa.FirstSuccessTime_UTC5,
|
||
sa.FirstSuccessDate_UTC5,
|
||
COALESCE(sa.IsSuccess, 0) AS IsSuccess,
|
||
COALESCE(sa.IsStopLabel, 0) AS IsStopLabel,
|
||
0 AS IsCompletedInAssessment
|
||
FROM tmp_form_order fo
|
||
INNER JOIN tmp_form_stats fs ON fo.FormId = fs.FormId
|
||
LEFT JOIN tmp_form_first_scan ffs ON fo.FormId = ffs.FormId
|
||
LEFT JOIN tmp_scan_agg sa ON fo.NeutralWaybillNumber = sa.NeutralWaybillNumber
|
||
WHERE fo.HasLabel = 1;
|
||
|
||
UPDATE tmp_order_full
|
||
SET IsCompletedInAssessment = 1
|
||
WHERE AssessmentTime IS NOT NULL
|
||
AND IsSuccess = 1
|
||
AND FirstSuccessTime_UTC5 <= AssessmentTime;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_form_first_scan;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_form_stats;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_form_order;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_scan_agg;
|
||
|
||
-- ================================================================
|
||
-- 步骤6：构建 tmp_daily_metrics 的日期维度
|
||
-- 从 tmp_order_full 和 tmp_daily_scan 收集所有出现过的日期
|
||
-- ================================================================
|
||
CREATE TEMPORARY TABLE tmp_daily_metrics (
|
||
日期 DATE NOT NULL,
|
||
当天新增换单数 INT NOT NULL DEFAULT 0,
|
||
当天应该换单数 INT NOT NULL DEFAULT 0,
|
||
当日标签推送数 INT NOT NULL DEFAULT 0,
|
||
累计要换的总单数 INT NOT NULL DEFAULT 0,
|
||
`24H完成数` INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (日期)
|
||
);
|
||
|
||
INSERT IGNORE INTO tmp_daily_metrics (日期)
|
||
SELECT DISTINCT ReceiptDate FROM tmp_order_full WHERE ReceiptDate IS NOT NULL
|
||
UNION
|
||
SELECT DISTINCT LabelRetrievedDate_UTC5 FROM tmp_order_full WHERE LabelRetrievedDate_UTC5 IS NOT NULL
|
||
UNION
|
||
SELECT DISTINCT FirstSuccessDate_UTC5 FROM tmp_order_full WHERE FirstSuccessDate_UTC5 IS NOT NULL
|
||
UNION
|
||
SELECT DISTINCT AssessmentBaseDate FROM tmp_order_full WHERE AssessmentBaseDate IS NOT NULL
|
||
UNION
|
||
SELECT DISTINCT AssessmentDate FROM tmp_order_full WHERE AssessmentDate IS NOT NULL
|
||
UNION
|
||
SELECT DISTINCT 日期 FROM tmp_daily_scan;
|
||
|
||
-- ================================================================
|
||
-- 步骤7：按日期聚合各项指标（无 CROSS JOIN，均为有索引的 GROUP BY）
|
||
-- ================================================================
|
||
|
||
-- 当天新增换单数
|
||
UPDATE tmp_daily_metrics dm
|
||
INNER JOIN (
|
||
SELECT ReceiptDate AS 日期, COUNT(DISTINCT RequestId) AS cnt
|
||
FROM tmp_order_full
|
||
WHERE ReceiptDate IS NOT NULL
|
||
GROUP BY ReceiptDate
|
||
) t ON dm.日期 = t.日期
|
||
SET dm.当天新增换单数 = t.cnt;
|
||
|
||
-- 当日标签推送数
|
||
UPDATE tmp_daily_metrics dm
|
||
INNER JOIN (
|
||
SELECT LabelRetrievedDate_UTC5 AS 日期, COUNT(DISTINCT RequestId) AS cnt
|
||
FROM tmp_order_full
|
||
WHERE LabelRetrievedDate_UTC5 IS NOT NULL
|
||
GROUP BY LabelRetrievedDate_UTC5
|
||
) t ON dm.日期 = t.日期
|
||
SET dm.当日标签推送数 = t.cnt;
|
||
|
||
-- 当天应该换单数 + 24H完成数
|
||
-- 一个订单的 AssessmentBaseDate 和 AssessmentDate 可能不同（BaseDate=T，AssessmentDate=T+1）
|
||
-- 该订单在 T 和 T+1 两天都应被计入"当天应该换单数"
|
||
-- 使用 UNION 展开两个日期维度后去重统计
|
||
CREATE TEMPORARY TABLE tmp_should_replace_dedup (
|
||
日期 DATE NOT NULL,
|
||
当天应该换单数 INT NOT NULL DEFAULT 0,
|
||
`24H完成数` INT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (日期)
|
||
);
|
||
|
||
INSERT INTO tmp_should_replace_dedup
|
||
SELECT
|
||
d.日期,
|
||
COUNT(DISTINCT ofu.RequestId) AS 当天应该换单数,
|
||
COUNT(DISTINCT CASE
|
||
WHEN ofu.IsCompletedInAssessment = 1
|
||
AND ofu.FirstSuccessDate_UTC5 = d.日期
|
||
THEN ofu.RequestId END) AS `24H完成数`
|
||
FROM (
|
||
SELECT DISTINCT AssessmentBaseDate AS 日期 FROM tmp_order_full WHERE AssessmentBaseDate IS NOT NULL
|
||
UNION
|
||
SELECT DISTINCT AssessmentDate AS 日期 FROM tmp_order_full WHERE AssessmentDate IS NOT NULL
|
||
) d
|
||
INNER JOIN tmp_order_full ofu
|
||
ON ofu.AssessmentTime IS NOT NULL
|
||
AND (ofu.AssessmentBaseDate = d.日期 OR ofu.AssessmentDate = d.日期)
|
||
AND (ofu.IsSuccess = 0 OR ofu.FirstSuccessDate_UTC5 <= d.日期)
|
||
GROUP BY d.日期;
|
||
|
||
UPDATE tmp_daily_metrics dm
|
||
INNER JOIN tmp_should_replace_dedup srd ON dm.日期 = srd.日期
|
||
SET
|
||
dm.当天应该换单数 = srd.当天应该换单数,
|
||
dm.`24H完成数` = srd.`24H完成数`;
|
||
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_should_replace_dedup;
|
||
|
||
-- 累计要换的总单数
|
||
-- 每个订单的计入区间：[MAX(LabelRetrievedDate_UTC5, RequestCreatedDate_UTC5), FirstSuccessDate_UTC5-1 或无穷]
|
||
-- 对每个日期 d：count 满足 start_date <= d AND (无成功 OR success_date > d) 的订单数
|
||
-- 利用 tmp_daily_metrics × tmp_order_full JOIN，但 tmp_order_full 已建索引，
|
||
-- 数据量：日期数 × 订单数远小于原来的 CROSS JOIN（OrderFullInfo 是完整订单，现在只取有标签的）
|
||
UPDATE tmp_daily_metrics dm
|
||
INNER JOIN (
|
||
SELECT
|
||
dm2.日期,
|
||
COUNT(DISTINCT ofu.RequestId) AS cnt
|
||
FROM tmp_daily_metrics dm2
|
||
INNER JOIN tmp_order_full ofu
|
||
ON ofu.HasLabel = 1
|
||
AND ofu.LabelRetrievedDate_UTC5 <= dm2.日期
|
||
AND ofu.RequestCreatedDate_UTC5 <= dm2.日期
|
||
AND (ofu.IsSuccess = 0 OR ofu.FirstSuccessDate_UTC5 > dm2.日期)
|
||
GROUP BY dm2.日期
|
||
) t ON dm.日期 = t.日期
|
||
SET dm.累计要换的总单数 = t.cnt;
|
||
|
||
-- ================================================================
|
||
-- 最终输出
|
||
-- ================================================================
|
||
SELECT
|
||
dm.日期,
|
||
dm.当天新增换单数,
|
||
dm.累计要换的总单数,
|
||
dm.当天应该换单数,
|
||
COALESCE(ds.当日换单完成数, 0) AS 当日换单完成数,
|
||
COALESCE(ds.当日换单失败数, 0) AS 当日换单失败数,
|
||
COALESCE(ds.当日STOP数, 0) AS 当日STOP数,
|
||
dm.`24H完成数` AS 24小时换单成功数,
|
||
dm.当日标签推送数,
|
||
COALESCE(ds.当日扫描数, 0) AS 当日扫描数,
|
||
CASE
|
||
WHEN dm.当天应该换单数 = 0 THEN '0.00%'
|
||
ELSE CONCAT(ROUND(COALESCE(ds.当日换单完成数, 0) / dm.当天应该换单数 * 100, 2), '%')
|
||
END AS 当天换单完成率,
|
||
CASE
|
||
WHEN dm.当天应该换单数 = 0 THEN '0.00%'
|
||
ELSE CONCAT(ROUND(dm.`24H完成数` / dm.当天应该换单数 * 100, 2), '%')
|
||
END AS 24小时换单率,
|
||
(UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间（UTC_5）
|
||
FROM tmp_daily_metrics dm
|
||
LEFT JOIN tmp_daily_scan ds ON dm.日期 = ds.日期
|
||
ORDER BY dm.日期 DESC;
|
||
|
||
-- ================================================================
|
||
-- 清理临时表
|
||
-- ================================================================
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_daily_scan;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_order_full;
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_daily_metrics;
|
||
|
||
END$$
|
||
|
||
DELIMITER ;
|