物流情况统计SQL脚本
<p>SELECT
-- a.id,
-- a.order_number,
-- a.iccid,
a.msisdn,
a.receiver_mobile,
a.receiver_name,
a.express_company,
a.express_no,
a.express_send_time,
a.express_sign_time,
a.express_status
FROM cbn_member_sim_buy_order a
WHERE a.express_send_time > '2025-01-03 00:00:01' and a.express_send_time < '2025-01-03 23:59:59';</p>
<p>SELECT
express_company,
DATEDIFF(express_sign_time, express_send_time) AS days_to_sign,
COUNT(*) AS order_count
FROM cbn_member_sim_buy_order
WHERE express_sign_time IS NOT NULL
AND express_send_time IS NOT NULL
AND DATEDIFF(express_sign_time, express_send_time) BETWEEN 0 AND 10
GROUP BY
express_company,DATEDIFF(express_sign_time, express_send_time)
ORDER BY
days_to_sign;</p>
<p>SELECT
-- express_company AS logistics_company,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 0 THEN 1 ELSE 0 END) AS same_day,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 1 THEN 1 ELSE 0 END) AS day_1,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 2 THEN 1 ELSE 0 END) AS day_2,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 3 THEN 1 ELSE 0 END) AS day_3,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 4 THEN 1 ELSE 0 END) AS day_4,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 5 THEN 1 ELSE 0 END) AS day_5,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 6 THEN 1 ELSE 0 END) AS day_6,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 7 THEN 1 ELSE 0 END) AS day_7,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 8 THEN 1 ELSE 0 END) AS day_8,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 9 THEN 1 ELSE 0 END) AS day_9,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) = 10 THEN 1 ELSE 0 END) AS day_10,
SUM(CASE WHEN DATEDIFF(express_sign_time, express_send_time) > 10 THEN 1 ELSE 0 END) AS above_10
FROM cbn_member_sim_buy_order
WHERE express_sign_time IS NOT NULL
AND express_send_time IS NOT NULL
-- GROUP BY express_company
-- ORDER BY express_company;</p>
<p>SELECT
id,
order_number,
receiver_mobile,
receiver_name,
express_company,
express_status,
express_no,
express_send_time,
express_sign_time
FROM
cbn_member_sim_buy_order
WHERE
express_company = '顺丰' -- 物流公司名称
AND DATEDIFF(express_sign_time, express_send_time) = 0 -- 时间差（0表示当天签收）
AND express_sign_time IS NOT NULL
AND express_send_time IS NOT NULL;</p>
<p>SELECT
express_company as stat_name,
COUNT(*) AS order_quantity,
SUM(CASE WHEN express_no is not null THEN 1 ELSE 0 END) AS sent_quantity,
SUM(CASE WHEN express_status = 7 THEN 1 ELSE 0 END) AS rejection_quantity,
SUM(CASE WHEN express_status = 6 THEN 1 ELSE 0 END) AS sign_quantity,
SUM(CASE WHEN express_status = 5 THEN 1 ELSE 0 END) AS transit_quantity
FROM cbn_member_sim_buy_order
WHERE express_company = '顺丰'
and create_time >= '2024-11-01T00:00' and create_time <= '2025-01-03T23:59:59.999999999'
GROUP BY express_company
ORDER BY express_company;</p>
<p>SELECT
msisdn,
-- buy_order_number,
buy_order_receiver_mobile,
buy_order_receiver_name,
express_company,
express_status,
express_no,
express_send_time,
express_sign_time,
card_active_time
FROM cbn_sim
WHERE
express_company = '顺丰' -- 物流公司名称
AND DATEDIFF(express_sign_time, card_active_time) = 0 -- 时间差（0表示当天签收）
AND express_sign_time IS NOT NULL
-- AND express_send_time IS NOT NULL
AND card_active_time BETWEEN '2025-01-01T00:00' AND '2025-01-03T23:59:59.999999999';</p>