京东大数据面试SQL-物流时效统计
一、题目背景
这道题来自京东物流履约中心的数据分析岗面试。京东自营物流的核心竞争力之一就是"211 限时达"——上午 11 点前下单当天送达，晚上 11 点前下单次日 15 点前送达。为了监控履约质量，数据分析团队需要按仓库维度统计每个环节的平均耗时。
📝 业务流程 一个订单从下单到签收，在物流系统中会经历三个关键时间节点：
- 下单时间 — 用户在 APP 提交订单
- 出库时间 — 仓库完成拣货、打包、出库扫描
- 签收时间 — 用户签收快递
拣货耗时 = 出库 - 下单；配送耗时 = 签收 - 出库；总耗时 = 签收 - 下单。
二、题目
现有一张订单物流轨迹表 t3_jd_order_logistics，记录了每个订单从下单到签收各环节的时间节点。请统计每个仓库各环节的平均耗时（单位：小时）。
t3_jd_order_logistics 表
+-----------+---------------+----------------------+----------------------+----------------------+
| order_id | warehouse_id | order_time | outbound_time | sign_time |
+-----------+---------------+----------------------+----------------------+----------------------+
| ORD001 | WH001 | 2025-01-10 08:00:00 | 2025-01-10 10:30:00 | 2025-01-11 14:00:00 |
| ORD002 | WH001 | 2025-01-10 09:00:00 | 2025-01-10 12:00:00 | 2025-01-12 09:00:00 |
| ORD003 | WH002 | 2025-01-10 08:30:00 | 2025-01-10 09:00:00 | 2025-01-11 18:00:00 |
| ORD004 | WH001 | 2025-01-11 10:00:00 | 2025-01-11 15:00:00 | 2025-01-13 10:00:00 |
| ORD005 | WH002 | 2025-01-11 11:00:00 | NULL | NULL |
| ORD006 | WH002 | 2025-01-12 07:00:00 | 2025-01-12 09:00:00 | 2025-01-13 20:00:00 |
+-----------+---------------+----------------------+----------------------+----------------------+
6 rows selected (0.169 seconds)
期望结果：按仓库统计拣货耗时、配送耗时、总耗时（单位：小时，保留1位小数）。
⚠️ 注意 ORD005 出库时间和签收时间均为 NULL——这个订单可能还未出库（或数据缺失），不参与任何环节的统计。
三、思路分析
本题核心三步：过滤 NULL → 计算时间差 → 按仓库聚合。
- 过滤 NULL：WHERE 子句排除 outbound_time 或 sign_time 为 NULL 的记录
- 时间差计算：
(结束时间 - 开始时间) / 3600得到小时数- Spark SQL 中 timestamp 相减得到秒数（BIGINT）
- 也可以用
unix_timestamp()统一转换为 Unix 秒再相减
- 聚合：GROUP BY 仓库，AVG 求各环节平均耗时
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：观察数据中的 NULL 值
先看全表，直观感受哪些订单需要排除。
SELECT * FROM t3_jd_order_logistics;
ORD001-WH001: 下单08:00 → 出库10:30 → 签收14:00(次日) ✅ 完整
ORD002-WH001: 下单09:00 → 出库12:00 → 签收09:00(2天后) ✅ 完整
ORD003-WH002: 下单08:30 → 出库09:00 → 签收18:00(次日) ✅ 完整
ORD004-WH001: 下单10:00 → 出库15:00 → 签收10:00(2天后) ✅ 完整
ORD005-WH002: 下单11:00 → 出库NULL → 签收NULL ❌ 需排除
ORD006-WH002: 下单07:00 → 出库09:00 → 签收20:00(次日) ✅ 完整
ORD005 两个时间都是 NULL，可能还在拣货中，不能用来算平均时效。
步骤2：过滤未完成订单，计算每单各环节耗时
SELECT
order_id,
warehouse_id,
(unix_timestamp(outbound_time) - unix_timestamp(order_time)) / 3600.0 AS pick_hours,
(unix_timestamp(sign_time) - unix_timestamp(outbound_time)) / 3600.0 AS delivery_hours,
(unix_timestamp(sign_time) - unix_timestamp(order_time)) / 3600.0 AS total_hours
FROM t3_jd_order_logistics
WHERE outbound_time IS NOT NULL
AND sign_time IS NOT NULL;
执行结果：
+-----------+---------------+-------------+-----------------+--------------+
| order_id | warehouse_id | pick_hours | delivery_hours | total_hours |
+-----------+---------------+-------------+-----------------+--------------+
| ORD001 | WH001 | 2.500000 | 27.500000 | 30.000000 |
| ORD002 | WH001 | 3.000000 | 45.000000 | 48.000000 |
| ORD003 | WH002 | 0.500000 | 33.000000 | 33.500000 |
| ORD004 | WH001 | 5.000000 | 43.000000 | 48.000000 |
| ORD006 | WH002 | 2.000000 | 35.000000 | 37.000000 |
+-----------+---------------+-------------+-----------------+--------------+
5 rows selected (6.966 seconds)
步骤3：按仓库聚合求平均
SELECT
warehouse_id,
round(avg((unix_timestamp(outbound_time) - unix_timestamp(order_time)) / 3600.0) ,1) AS avg_pick_hours,
round(avg((unix_timestamp(sign_time) - unix_timestamp(outbound_time)) / 3600.0 ) ,1) AS avg_delivery_hours,
round(avg((unix_timestamp(sign_time) - unix_timestamp(order_time)) / 3600.0 ) ,1) AS avg_total_hours
FROM t3_jd_order_logistics
WHERE outbound_time IS NOT NULL
AND sign_time IS NOT NULL
group by warehouse_id
最终结果：
+---------------+-----------------+---------------------+------------------+
| warehouse_id | avg_pick_hours | avg_delivery_hours | avg_total_hours |
+---------------+-----------------+---------------------+------------------+
| WH002 | 1.3 | 34.0 | 35.3 |
| WH001 | 3.5 | 38.5 | 42.0 |
+---------------+-----------------+---------------------+------------------+
2 rows selected (8.435 seconds)(dwsql.com)
分析：
- WH002 拣货更快（1.3h vs 3.5h），可能因为自动化程度更高或订单量更少
- 两个仓库的配送耗时都在 30+ 小时，说明主要是跨城配送
- WH002 总时效优于 WH001，可以作为标杆仓库推广经验
五、常见坑点
⚠️ NULL 值处理 如果不过滤 NULL，
unix_timestamp(NULL)返回 NULL，AVG 会自动忽略 NULL——这看起来没问题，但实际上有隐患：虽然 AVG 跳过了 NULL，但如果面试官问"总共有多少订单参与了统计"，你答不上来。显式 WHERE 过滤更清晰、更安全。
⚠️ 直接用 AVG 聚合的精度问题 如果先对每行计算时间差再 AVG，浮点累加可能有精度误差。对于需要精确到秒的场景，建议：
六、举一反三
- 按日期维度统计：如果要求每天的时效趋势，只需在 SELECT 和 GROUP BY 中加入
DATE(order_time)，观察是否有周末效应 - 异常订单检测：如果某个订单的拣货耗时 > 24 小时，可能遇到了缺货或系统故障——可以用
HAVING或CASE WHEN标记异常 - 百分位数：AVG 容易被极端值拉偏，用
PERCENTILE_APPROX(total_hours, 0.5)看中位数更稳健 - SLA 达成率：物流中常用 SLA（如"48小时内送达率"），将上述 SQL 的 AVG 改成
SUM(CASE WHEN total_hours <= 48 THEN 1 ELSE 0 END) / COUNT(*)
七、知识点总结
💡 核心公式
| 考点 | 说明 |
|---|---|
| NULL 过滤 | 显式 WHERE IS NOT NULL，不依赖 AVG 自动跳过 |
| 时间差计算 | Spark SQL: CAST AS BIGINT 相减；Hive/MySQL: unix_timestamp / TIMESTAMPDIFF |
| AVG + ROUND | 聚合后格式化，保证可读性 |
| 业务解读 | 数值本身不是终点，能说出"哪个仓库好、为什么好"才是数据分析的价值 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE IF NOT EXISTS t3_jd_order_logistics (
order_id STRING,
warehouse_id STRING,
order_time STRING,
outbound_time STRING,
sign_time STRING
);
INSERT INTO t3_jd_order_logistics VALUES
('ORD001', 'WH001', '2025-01-10 08:00:00', '2025-01-10 10:30:00', '2025-01-11 14:00:00'),
('ORD002', 'WH001', '2025-01-10 09:00:00', '2025-01-10 12:00:00', '2025-01-12 09:00:00'),
('ORD003', 'WH002', '2025-01-10 08:30:00', '2025-01-10 09:00:00', '2025-01-11 18:00:00'),
('ORD004', 'WH001', '2025-01-11 10:00:00', '2025-01-11 15:00:00', '2025-01-13 10:00:00'),
('ORD005', 'WH002', '2025-01-11 11:00:00', NULL, NULL),
('ORD006', 'WH002', '2025-01-12 07:00:00', '2025-01-12 09:00:00', '2025-01-13 20:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
