校招算法笔面试 | SQL笔试面试编程题-统计快递运输时长

题目

题目链接

这道题目要求我们统计每种快递类型的平均运输时长，我们要做的事情如下：

1. 确定总体问题

我们需要计算每种快递类型的平均运输时长（单位：小时，保留1位小数），并按时长从小到大排序。

2. 分析关键问题

• 连接表：将express_tb和exp_action_tb表连接起来，以便获取每个快递的运输时间。
• 计算运输时长：计算每个快递的运输时长。
• 计算平均运输时长：对每种快递类型的运输时长进行平均计算。
• 格式化输出：将结果保留一位小数。
• 排序输出：按平均运输时长从小到大排序。

3. 解决每个关键问题的代码及讲解

步骤1：连接表

我们使用JOIN将express_tb和exp_action_tb表连接起来：

from
    express_tb e
    join exp_action_tb a on e.exp_number = a.exp_number

• JOIN exp_action_tb a ON e.exp_number = a.exp_number：通过快递单号连接两个表，以便获取每个快递的运输时间。

步骤2：计算运输时长

我们使用TIMESTAMPDIFF函数计算每个快递的运输时长（以分钟为单位），然后转换为小时：

timestampdiff(minute, out_time, in_time) / 60

• TIMESTAMPDIFF(MINUTE, out_time, in_time) / 60：计算运输时长并转换为小时。
• 注意：因为需要四舍五入，直接使用hour保留一小时会出错，要使用minute计算时长然后除以六十之后再四舍五入

步骤3：计算平均运输时长

我们使用AVG函数对每种快递类型的运输时长进行平均计算，并使用ROUND函数保留一位小数：

round(avg(timestampdiff(minute, out_time, in_time) / 60), 1) as time

• AVG(...)：计算每种快递类型的平均运输时长。
• ROUND(..., 1)：将结果保留一位小数。

步骤4：排序输出

我们使用ORDER BY按平均运输时长从小到大排序输出结果：

order by
    time asc

完整代码

select
    e.exp_type,
    round(avg(timestampdiff(minute, out_time, in_time) / 60), 1) as time
from
    express_tb e
    join exp_action_tb a on e.exp_number = a.exp_number
group by
    e.exp_type
order by
    time asc;