56 lines
2.6 KiB
SQL
56 lines
2.6 KiB
SQL
-- 每日统计（UTC-5时间）- 按客户维度
|
||
SELECT
|
||
CustomerCode,
|
||
CustomerName,
|
||
day,
|
||
COUNT(DISTINCT lr_id) AS 下单数,
|
||
SUM(CASE WHEN ReplaceStatus = 'N' THEN 1 ELSE 0 END) AS 订单取消次数,
|
||
(SELECT COUNT(*)
|
||
FROM label_scan_history lsh
|
||
JOIN label_replace_requests lrr2 ON lsh.NeutralWaybillNumber = lrr2.NeutralWaybillNumber
|
||
WHERE DATE(DATE_ADD(lsh.createdat, INTERVAL -5 HOUR)) = outer_query.day
|
||
AND lrr2.CustomerId = outer_query.CustomerId) AS 换单扫描次数,
|
||
(SELECT COUNT(*)
|
||
FROM label_scan_history lsh
|
||
JOIN label_replace_requests lrr2 ON lsh.NeutralWaybillNumber = lrr2.NeutralWaybillNumber
|
||
WHERE DATE(DATE_ADD(lsh.createdat, INTERVAL -5 HOUR)) = outer_query.day
|
||
AND lrr2.CustomerId = outer_query.CustomerId
|
||
AND lsh.Result = 0) AS 换单扫描成功次数,
|
||
(SELECT COUNT(*)
|
||
FROM label_scan_history lsh
|
||
JOIN label_replace_requests lrr2 ON lsh.NeutralWaybillNumber = lrr2.NeutralWaybillNumber
|
||
WHERE DATE(DATE_ADD(lsh.createdat, INTERVAL -5 HOUR)) = outer_query.day
|
||
AND lrr2.CustomerId = outer_query.CustomerId
|
||
AND lsh.Result != 0) AS 换单扫描失败次数,
|
||
(SELECT COUNT(DISTINCT btw.id)
|
||
FROM bag_tags bt
|
||
JOIN bag_tag_waybills btw ON bt.TagNumber = btw.TagNumber
|
||
JOIN label_replace_requests lrr2 ON btw.FinalMileTrackingNumber = lrr2.FinalMileTrackingNumber
|
||
WHERE DATE(DATE_ADD(btw.createdat, INTERVAL -5 HOUR)) = outer_query.day
|
||
AND lrr2.CustomerId = outer_query.CustomerId) AS 集包成功次数,
|
||
(SELECT COUNT(*)
|
||
FROM order_logs ol
|
||
JOIN label_replace_requests lrr2 ON ol.FinalMileTrackingNumber = lrr2.FinalMileTrackingNumber
|
||
WHERE DATE(DATE_ADD(ol.createdat, INTERVAL -5 HOUR)) = outer_query.day
|
||
AND lrr2.CustomerId = outer_query.CustomerId
|
||
AND ol.OperationType = '集包') AS '集包扫描次数',
|
||
(SELECT COUNT(*)
|
||
FROM order_logs ol
|
||
JOIN label_replace_requests lrr2 ON ol.NeutralWaybillNumber = lrr2.NeutralWaybillNumber
|
||
WHERE DATE(DATE_ADD(ol.createdat, INTERVAL -5 HOUR)) = outer_query.day
|
||
AND lrr2.CustomerId = outer_query.CustomerId
|
||
AND ol.OperationResult = '失败') AS 订单异常次数
|
||
FROM (
|
||
SELECT
|
||
c.Id AS CustomerId,
|
||
c.CustomerCode,
|
||
c.CustomerName,
|
||
lr.Id AS lr_id,
|
||
lr.ReplaceStatus,
|
||
DATE(DATE_ADD(lr.createdat, INTERVAL -5 HOUR)) AS day
|
||
FROM customers c
|
||
JOIN label_replace_requests lr ON c.Id = lr.CustomerId
|
||
) outer_query
|
||
GROUP BY CustomerId, CustomerCode, CustomerName, day
|
||
ORDER BY CustomerName, day;
|