【阿里巴巴】统计各快递种类平均运输时长
WHERE 条件JOIN 连接聚合函数GROUP BY日期函数ORDER BY面试真题
题目描述
来源：阿里巴巴 SQL 面试真题
某物流公司需要根据快递信息和运输动作记录，统计每种快递的平均运输时长。
数据表
express_tb 快递信息表
| 字段 | 类型 | 说明 |
|---|---|---|
| exp_number | VARCHAR | 快递单号，主键 |
| exp_type | VARCHAR | 快递种类 |
| out_city | VARCHAR | 快递发出城市 |
| in_city | VARCHAR | 快递邮入城市 |
| create_time | DATETIME | 快递单创建时间 |
exp_action_tb 快递运输动作表
| 字段 | 类型 | 说明 |
|---|---|---|
| exp_number | VARCHAR | 快递单号 |
| transport_type | VARCHAR | 运输类型 |
| out_time | DATETIME | 快递发出时间 |
| in_time | DATETIME | 快递到达时间，可为空 |
统计口径
- 每条运输动作的运输时长为
in_time - out_time。 - 使用秒级时间差除以
3600.0换算为小时，保留不足一小时的部分。 - 只统计
out_time和in_time均不为空的完整运输动作。 - 按快递种类计算所有完整运输动作的平均时长。
- 没有完整运输动作的快递种类不输出。
题目要求
- 输出快递种类和平均运输时长。
- 平均运输时长单位为小时，四舍五入保留 1 位小数。
- 按平均运输时长从小到大排序。
- 平均时长相同时，按快递种类升序排列。
期望输出列
| 列名 | 说明 |
|---|---|
| exp_type | 快递种类 |
| avg_transport_hours | 平均运输时长，单位小时 |
数据样例
| exp_numberPKVARCHAR(32) | exp_typeVARCHAR(50) | out_cityVARCHAR(50) | in_cityVARCHAR(50) | create_timeDATETIME |
|---|---|---|---|---|
| ALI1001 | 普通件 | 北京 | 上海 | 2022-06-01 08:00:00 |
| ALI1002 | 普通件 | 广州 | 深圳 | 2022-06-01 08:30:00 |
| ALI1003 | 生鲜 | 杭州 | 南京 | 2022-06-01 09:00:00 |
| ALI1004 | 生鲜 | 成都 | 重庆 | 2022-06-01 09:30:00 |
| ALI1005 | 易碎品 | 苏州 | 北京 | 2022-06-01 10:00:00 |
| ALI1006 | 文件 | 上海 | 天津 | 2022-06-01 10:30:00 |
输入数据显示 6 / 6 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里