361 lines
17 KiB
SQL
361 lines
17 KiB
SQL
-- 客户维度每日标签统计综合查询
|
||
-- 设计逻辑：和原运营监控SQL逻辑完全一致，新增按CustomerCode维度分组统计
|
||
WITH
|
||
-- 步骤1：获取所有到货交接单（时间已是UTC-5）
|
||
ArrivalForms AS (
|
||
SELECT
|
||
Id AS FormId,
|
||
HandoverNumber,
|
||
ReceiptTime,
|
||
DATE(ReceiptTime) AS ReceiptDate,
|
||
HOUR(ReceiptTime) AS ReceiptHour
|
||
FROM arrival_handover_forms
|
||
),
|
||
-- 步骤2：获取所有换单请求，关联客户表获取CustomerCode
|
||
LabelRequests AS (
|
||
SELECT
|
||
r.Id AS RequestId,
|
||
r.NeutralWaybillNumber,
|
||
r.BillOfLadingNumber,
|
||
r.MasterPackageNumber,
|
||
r.Label,
|
||
r.LabelRetrievedAt,
|
||
r.CreatedAt AS RequestCreatedAt,
|
||
c.CustomerCode, -- 通过customerid关联客户表获取客户代码
|
||
-- 转换为UTC-5时间
|
||
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
|
||
FROM label_replace_requests r
|
||
LEFT JOIN customers c ON r.customerid = c.Id -- 通过customerid关联客户表
|
||
),
|
||
-- 步骤3：关联到货交接单和换单请求（优先匹配大箱号，无大箱号匹配则用提单号）
|
||
FormRequestRelation AS (
|
||
SELECT
|
||
-- 优先取大箱号匹配的交接单信息，无则取提单号匹配的
|
||
COALESCE(f_m.FormId, f_b.FormId) AS FormId,
|
||
COALESCE(f_m.HandoverNumber, f_b.HandoverNumber) AS HandoverNumber,
|
||
COALESCE(f_m.ReceiptTime, f_b.ReceiptTime) AS ReceiptTime,
|
||
COALESCE(f_m.ReceiptDate, f_b.ReceiptDate) AS ReceiptDate,
|
||
COALESCE(f_m.ReceiptHour, f_b.ReceiptHour) AS ReceiptHour,
|
||
r.RequestId,
|
||
r.NeutralWaybillNumber,
|
||
r.Label,
|
||
r.LabelRetrievedAt,
|
||
r.LabelRetrievedAt_UTC5,
|
||
r.LabelRetrievedDate_UTC5,
|
||
r.RequestCreatedAt,
|
||
r.CustomerCode, -- 传递客户代码
|
||
-- 是否有标签
|
||
CASE WHEN r.Label IS NOT NULL AND r.Label != '' THEN 1 ELSE 0 END AS HasLabel
|
||
FROM LabelRequests r
|
||
-- 先匹配大箱号
|
||
LEFT JOIN ArrivalForms f_m ON r.MasterPackageNumber = f_m.HandoverNumber
|
||
-- 大箱号匹配不到再匹配提单号
|
||
LEFT JOIN ArrivalForms f_b ON r.BillOfLadingNumber = f_b.HandoverNumber
|
||
-- 只保留有匹配到交接单的订单
|
||
WHERE COALESCE(f_m.FormId, f_b.FormId) IS NOT NULL
|
||
),
|
||
-- 步骤4：获取每个交接单的首次扫描时间
|
||
FormFirstScan AS (
|
||
SELECT
|
||
fr.FormId,
|
||
MIN(s.CreatedAt) AS FirstScanTime,
|
||
CONVERT_TZ(MIN(s.CreatedAt), '+00:00', '-05:00') AS FirstScanTime_UTC5
|
||
FROM FormRequestRelation fr
|
||
LEFT JOIN label_scan_history s ON fr.NeutralWaybillNumber = s.NeutralWaybillNumber
|
||
GROUP BY fr.FormId
|
||
),
|
||
-- 步骤5：先计算每个交接单的总订单数和达标阈值
|
||
FormTotalOrderCount AS (
|
||
SELECT
|
||
FormId,
|
||
COUNT(DISTINCT RequestId) AS TotalOrderCount,
|
||
CEIL(COUNT(DISTINCT RequestId) * 0.8) AS QualifyNeedCount
|
||
FROM FormRequestRelation
|
||
GROUP BY FormId
|
||
),
|
||
-- 步骤6：计算每个交接单每个标签的推送时间及排序，匹配达标阈值
|
||
FormLabelPushTimes AS (
|
||
SELECT
|
||
fr.FormId,
|
||
fr.LabelRetrievedAt_UTC5,
|
||
-- 按推送时间排序，计算累计推送的标签数
|
||
ROW_NUMBER() OVER (PARTITION BY fr.FormId ORDER BY fr.LabelRetrievedAt_UTC5) AS PushOrder,
|
||
ftoc.QualifyNeedCount
|
||
FROM FormRequestRelation fr
|
||
INNER JOIN FormTotalOrderCount ftoc ON fr.FormId = ftoc.FormId
|
||
WHERE fr.HasLabel = 1 AND fr.LabelRetrievedAt_UTC5 IS NOT NULL
|
||
),
|
||
-- 步骤7：计算每个交接单的首次达标日期和时间
|
||
FormFirstQualifiedDate AS (
|
||
SELECT
|
||
FormId,
|
||
MIN(DATE(LabelRetrievedAt_UTC5)) AS FirstQualifiedDate,
|
||
MIN(LabelRetrievedAt_UTC5) AS FirstQualifiedTime_UTC5
|
||
FROM FormLabelPushTimes
|
||
WHERE PushOrder >= QualifyNeedCount
|
||
GROUP BY FormId
|
||
),
|
||
-- 步骤8：计算每个交接单的标签率（按当前实际情况统计，无需冻结）
|
||
FormLabelRateAndQualifiedDate AS (
|
||
SELECT
|
||
fr.FormId,
|
||
ftoc.TotalOrderCount,
|
||
-- 有标签的订单数
|
||
COUNT(DISTINCT CASE WHEN fr.HasLabel = 1 THEN fr.RequestId END) AS HasLabelOrderCount,
|
||
-- 标签率：当前有标签订单数 / 总订单数
|
||
CASE
|
||
WHEN ftoc.TotalOrderCount = 0 THEN 0
|
||
ELSE COUNT(DISTINCT CASE WHEN fr.HasLabel = 1 THEN fr.RequestId END) / ftoc.TotalOrderCount
|
||
END AS LabelRate,
|
||
fqd.FirstQualifiedDate,
|
||
fqd.FirstQualifiedTime_UTC5
|
||
FROM FormRequestRelation fr
|
||
INNER JOIN FormTotalOrderCount ftoc ON fr.FormId = ftoc.FormId
|
||
LEFT JOIN FormFirstQualifiedDate fqd ON fr.FormId = fqd.FormId
|
||
GROUP BY fr.FormId, ftoc.TotalOrderCount, fqd.FirstQualifiedDate, fqd.FirstQualifiedTime_UTC5
|
||
),
|
||
-- 步骤9：计算每个订单的考核时间（基于达标时间和到货时间较晚者）
|
||
OrderAssessment AS (
|
||
SELECT
|
||
fr.*,
|
||
flr.LabelRate,
|
||
flr.FirstQualifiedDate,
|
||
flr.FirstQualifiedTime_UTC5,
|
||
fs.FirstScanTime_UTC5,
|
||
-- 订单创建日期（UTC-5）
|
||
DATE(CONVERT_TZ(fr.RequestCreatedAt, '+00:00', '-05:00')) AS RequestCreatedDate_UTC5,
|
||
-- 考核基准时间：如果首次扫描早于到仓则用首次扫描时间，再和标签率达标时间取较晚者
|
||
GREATEST(
|
||
CASE
|
||
WHEN fs.FirstScanTime_UTC5 IS NOT NULL AND fs.FirstScanTime_UTC5 < fr.ReceiptTime
|
||
THEN fs.FirstScanTime_UTC5
|
||
ELSE fr.ReceiptTime
|
||
END,
|
||
flr.FirstQualifiedTime_UTC5
|
||
) AS AssessmentBaseTime,
|
||
-- 考核时间计算（仅标签率≥80%且有标签的订单）
|
||
CASE
|
||
WHEN flr.FirstQualifiedTime_UTC5 IS NOT NULL AND fr.HasLabel = 1 THEN
|
||
CASE
|
||
-- 考核基准时间16点前：考核截止次日16点
|
||
WHEN HOUR(GREATEST(
|
||
CASE
|
||
WHEN fs.FirstScanTime_UTC5 IS NOT NULL AND fs.FirstScanTime_UTC5 < fr.ReceiptTime
|
||
THEN fs.FirstScanTime_UTC5
|
||
ELSE fr.ReceiptTime
|
||
END,
|
||
flr.FirstQualifiedTime_UTC5
|
||
)) < 16 THEN
|
||
DATE_ADD(
|
||
DATE_ADD(DATE(GREATEST(
|
||
CASE
|
||
WHEN fs.FirstScanTime_UTC5 IS NOT NULL AND fs.FirstScanTime_UTC5 < fr.ReceiptTime
|
||
THEN fs.FirstScanTime_UTC5
|
||
ELSE fr.ReceiptTime
|
||
END,
|
||
flr.FirstQualifiedTime_UTC5
|
||
)), INTERVAL 1 DAY),
|
||
INTERVAL 16 HOUR
|
||
)
|
||
-- 考核基准时间16点后：考核截止次日23:59:59
|
||
ELSE
|
||
DATE_ADD(
|
||
DATE_ADD(DATE(GREATEST(
|
||
CASE
|
||
WHEN fs.FirstScanTime_UTC5 IS NOT NULL AND fs.FirstScanTime_UTC5 < fr.ReceiptTime
|
||
THEN fs.FirstScanTime_UTC5
|
||
ELSE fr.ReceiptTime
|
||
END,
|
||
flr.FirstQualifiedTime_UTC5
|
||
)), INTERVAL 1 DAY),
|
||
INTERVAL '23:59:59' HOUR_SECOND
|
||
)
|
||
END
|
||
ELSE NULL
|
||
END AS AssessmentTime
|
||
FROM FormRequestRelation fr
|
||
LEFT JOIN FormLabelRateAndQualifiedDate flr ON fr.FormId = flr.FormId
|
||
LEFT JOIN FormFirstScan fs ON fr.FormId = fs.FormId
|
||
WHERE fr.HasLabel = 1 -- 仅统计有标签的订单
|
||
),
|
||
-- 步骤7：获取每个订单的扫描状态
|
||
OrderScanStatus AS (
|
||
SELECT
|
||
s.NeutralWaybillNumber,
|
||
-- 首次成功时间（UTC-5）
|
||
MIN(CASE WHEN s.Result = 0 THEN CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') END) AS FirstSuccessTime_UTC5,
|
||
DATE(MIN(CASE WHEN s.Result = 0 THEN CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') END)) AS FirstSuccessDate_UTC5,
|
||
-- 是否成功
|
||
MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS IsSuccess,
|
||
-- 是否是STOP标签
|
||
MAX(CASE WHEN s.Result = 0 AND s.Description LIKE '%成功返回STOP标签%' THEN 1 ELSE 0 END) AS IsStopLabel
|
||
FROM label_scan_history s
|
||
GROUP BY s.NeutralWaybillNumber
|
||
),
|
||
-- 步骤8：合并订单信息和扫描状态
|
||
OrderFullInfo AS (
|
||
SELECT
|
||
oa.*,
|
||
oss.FirstSuccessTime_UTC5,
|
||
oss.FirstSuccessDate_UTC5,
|
||
oss.IsSuccess,
|
||
oss.IsStopLabel,
|
||
-- 是否在考核时间内完成
|
||
CASE
|
||
WHEN oa.AssessmentTime IS NOT NULL AND oss.IsSuccess = 1
|
||
AND oss.FirstSuccessTime_UTC5 <= oa.AssessmentTime
|
||
THEN 1 ELSE 0
|
||
END AS IsCompletedInAssessment
|
||
FROM OrderAssessment oa
|
||
LEFT JOIN OrderScanStatus oss ON oa.NeutralWaybillNumber = oss.NeutralWaybillNumber
|
||
),
|
||
-- 步骤9：独立统计每日+客户维度成功换单的去重订单数
|
||
CustomerDailySuccessCount AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
|
||
c.CustomerCode,
|
||
COUNT(DISTINCT s.NeutralWaybillNumber) AS 当日换单完成数
|
||
FROM label_scan_history s
|
||
INNER JOIN label_replace_requests r ON s.NeutralWaybillNumber = r.NeutralWaybillNumber
|
||
INNER JOIN customers c ON r.customerid = c.Id
|
||
WHERE s.Result = 0
|
||
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')), c.CustomerCode
|
||
),
|
||
-- 步骤10：独立统计每日+客户维度失败换单的去重订单数（当日有失败且无成功）
|
||
CustomerDailyFailCount AS (
|
||
SELECT
|
||
日期,
|
||
CustomerCode,
|
||
COUNT(DISTINCT NeutralWaybillNumber) AS 当日换单失败数
|
||
FROM (
|
||
SELECT
|
||
DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
|
||
c.CustomerCode,
|
||
s.NeutralWaybillNumber,
|
||
MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS has_success,
|
||
MAX(CASE WHEN s.Result != 0 THEN 1 ELSE 0 END) AS has_fail
|
||
FROM label_scan_history s
|
||
INNER JOIN label_replace_requests r ON s.NeutralWaybillNumber = r.NeutralWaybillNumber
|
||
INNER JOIN customers c ON r.customerid = c.Id
|
||
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')), c.CustomerCode, s.NeutralWaybillNumber
|
||
) t
|
||
WHERE has_fail = 1 AND has_success = 0
|
||
GROUP BY 日期, CustomerCode
|
||
),
|
||
-- 步骤11：独立统计每日+客户维度的扫描次数
|
||
CustomerDailyScanCount AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
|
||
c.CustomerCode,
|
||
COUNT(*) AS 当日扫描数
|
||
FROM label_scan_history s
|
||
INNER JOIN label_replace_requests r ON s.NeutralWaybillNumber = r.NeutralWaybillNumber
|
||
INNER JOIN customers c ON r.customerid = c.Id
|
||
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')), c.CustomerCode
|
||
),
|
||
-- 步骤12：独立统计每日+客户维度STOP标签的去重订单数
|
||
CustomerDailyStopCount AS (
|
||
SELECT
|
||
DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
|
||
c.CustomerCode,
|
||
COUNT(DISTINCT s.NeutralWaybillNumber) AS 当日STOP数
|
||
FROM label_scan_history s
|
||
INNER JOIN label_replace_requests r ON s.NeutralWaybillNumber = r.NeutralWaybillNumber
|
||
INNER JOIN customers c ON r.customerid = c.Id
|
||
WHERE s.Result = 0 AND s.Description LIKE '%成功返回STOP标签%'
|
||
GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')), c.CustomerCode
|
||
),
|
||
-- 步骤12：获取所有日期维度
|
||
AllDates AS (
|
||
SELECT ReceiptDate AS 日期 FROM OrderFullInfo -- 到仓日期（UTC-5）
|
||
UNION
|
||
SELECT LabelRetrievedDate_UTC5 AS 日期 FROM OrderFullInfo WHERE LabelRetrievedDate_UTC5 IS NOT NULL -- 标签推送日期（UTC-5）
|
||
UNION
|
||
SELECT FirstSuccessDate_UTC5 AS 日期 FROM OrderFullInfo WHERE FirstSuccessDate_UTC5 IS NOT NULL -- 换单成功日期（UTC-5）
|
||
UNION
|
||
SELECT DATE(AssessmentBaseTime) AS 日期 FROM OrderFullInfo WHERE AssessmentBaseTime IS NOT NULL -- 考核基准日期（UTC-5）
|
||
UNION
|
||
SELECT DATE(AssessmentTime) AS 日期 FROM OrderFullInfo WHERE AssessmentTime IS NOT NULL -- 考核截止日期（UTC-5）
|
||
UNION
|
||
SELECT DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00')) AS 日期 FROM label_scan_history WHERE CreatedAt IS NOT NULL -- 扫描日期（转UTC-5）
|
||
UNION
|
||
SELECT DATE(CONVERT_TZ(LabelRetrievedAt, '+00:00', '-05:00')) AS 日期 FROM label_replace_requests WHERE LabelRetrievedAt IS NOT NULL -- 标签推送日期（转UTC-5）
|
||
UNION
|
||
SELECT DATE(CONVERT_TZ(CreatedAt, '+00:00', '-05:00')) AS 日期 FROM label_scan_history WHERE Result = 0 AND CreatedAt IS NOT NULL -- 换单完成日期（转UTC-5）
|
||
),
|
||
-- 步骤10：去重排序日期
|
||
DistinctDates AS (
|
||
SELECT DISTINCT 日期
|
||
FROM AllDates
|
||
ORDER BY 日期
|
||
),
|
||
-- 步骤13：每日+客户维度指标汇总
|
||
DailyCustomerMetrics AS (
|
||
SELECT
|
||
dd.日期,
|
||
ofi.CustomerCode, -- 客户代码维度
|
||
-- 当天新增换单数：到仓时间是当天的有标签订单数
|
||
COUNT(DISTINCT CASE WHEN ofi.ReceiptDate = dd.日期 THEN ofi.RequestId END) AS 当天新增换单数,
|
||
-- 当天应该换单数：有考核时间、考核基准日期是当天或考核截止日期是当天，且换单完成时间小于等于统计日期的订单（有考核时间默认标签率≥80%）
|
||
COUNT(DISTINCT CASE
|
||
WHEN ofi.AssessmentTime IS NOT NULL
|
||
AND (DATE(ofi.AssessmentBaseTime) = dd.日期 OR DATE(ofi.AssessmentTime) = dd.日期)
|
||
AND (ofi.IsSuccess = 0 OR ofi.FirstSuccessDate_UTC5 <= dd.日期)
|
||
THEN ofi.RequestId
|
||
END) AS 当天应该换单数,
|
||
-- 当日标签推送数：标签推送时间是当天的订单数
|
||
COUNT(DISTINCT CASE WHEN ofi.LabelRetrievedDate_UTC5 = dd.日期 THEN ofi.RequestId END) AS 当日标签推送数,
|
||
-- 累计要换的总单数：有标签，标签推送时间<=统计日期，创建时间<=统计日期，且统计日当天未完成换单的不重复订单总数（不考虑标签率、到仓时间、考核时间）
|
||
COUNT(DISTINCT CASE
|
||
WHEN ofi.HasLabel = 1
|
||
AND ofi.LabelRetrievedDate_UTC5 <= dd.日期
|
||
AND ofi.RequestCreatedDate_UTC5 <= dd.日期
|
||
AND (ofi.IsSuccess = 0 OR ofi.FirstSuccessDate_UTC5 > dd.日期)
|
||
THEN ofi.RequestId
|
||
END) AS 累计要换的总单数,
|
||
-- 24小时换单成功数：当天应该换单数中、首次成功时间在考核基准时间与考核时间之间、且完成时间是当天的订单数
|
||
COUNT(DISTINCT CASE
|
||
WHEN ofi.AssessmentTime IS NOT NULL
|
||
AND (DATE(ofi.AssessmentBaseTime) = dd.日期 OR DATE(ofi.AssessmentTime) = dd.日期)
|
||
AND ofi.IsCompletedInAssessment = 1
|
||
AND ofi.FirstSuccessDate_UTC5 = dd.日期
|
||
THEN ofi.RequestId
|
||
END) AS 24H完成数
|
||
FROM DistinctDates dd
|
||
CROSS JOIN OrderFullInfo ofi
|
||
GROUP BY dd.日期, ofi.CustomerCode -- 按日期+客户代码分组
|
||
)
|
||
-- 最终输出
|
||
SELECT
|
||
dcm.CustomerCode, -- 客户代码作为第一列
|
||
dcm.日期,
|
||
dcm.当天新增换单数,
|
||
dcm.累计要换的总单数,
|
||
dcm.当天应该换单数,
|
||
COALESCE(cdsc.当日换单完成数, 0) AS 当日换单完成数,
|
||
COALESCE(cdfc.当日换单失败数, 0) AS 当日换单失败数,
|
||
COALESCE(cdstop.当日STOP数, 0) AS 当日STOP数,
|
||
dcm.24H完成数 AS 24小时换单成功数,
|
||
dcm.当日标签推送数,
|
||
COALESCE(cdscan.当日扫描数, 0) AS 当日扫描数,
|
||
-- 当天换单完成率：当日换单完成数 / 当天应该换单数
|
||
CASE
|
||
WHEN dcm.当天应该换单数 = 0 THEN '0.00%'
|
||
ELSE CONCAT(ROUND(COALESCE(cdsc.当日换单完成数, 0) / dcm.当天应该换单数 * 100, 2), '%')
|
||
END AS 当天换单完成率,
|
||
-- 24小时换单率：考核时间内完成数 / 当天应该考核的订单总数
|
||
CASE
|
||
WHEN dcm.当天应该换单数 = 0 THEN '0.00%'
|
||
ELSE CONCAT(ROUND(dcm.24H完成数 / dcm.当天应该换单数 * 100, 2), '%')
|
||
END AS 24小时换单率,
|
||
-- 数据拉取时间
|
||
(UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间（UTC_5）
|
||
FROM DailyCustomerMetrics dcm
|
||
LEFT JOIN CustomerDailySuccessCount cdsc ON dcm.日期 = cdsc.日期 AND dcm.CustomerCode = cdsc.CustomerCode
|
||
LEFT JOIN CustomerDailyFailCount cdfc ON dcm.日期 = cdfc.日期 AND dcm.CustomerCode = cdfc.CustomerCode
|
||
LEFT JOIN CustomerDailyScanCount cdscan ON dcm.日期 = cdscan.日期 AND dcm.CustomerCode = cdscan.CustomerCode
|
||
LEFT JOIN CustomerDailyStopCount cdstop ON dcm.日期 = cdstop.日期 AND dcm.CustomerCode = cdstop.CustomerCode
|
||
WHERE dcm.CustomerCode IS NOT NULL -- 过滤无客户代码的订单
|
||
ORDER BY dcm.日期 DESC, dcm.CustomerCode;
|