校招算法笔面试 | SQL笔试面试编程题-统计快递从创建订单到发出间隔时长

题目

题目链接

这道题目要求我们计算从快递单创建到快递邮出的平均间隔时间，我们要做的事情如下：

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, e.create_time, a.out_time) / 60

• TIMESTAMPDIFF(MINUTE, e.create_time, a.out_time) / 60：计算时间间隔并转换为小时。

步骤3：计算平均时间间隔

我们使用AVG函数对所有快递的时间间隔求平均值，并使用ROUND函数保留三位小数：

round(avg(timestampdiff(minute, e.create_time, a.out_time) / 60), 3) as time

• AVG(...)：计算所有快递的平均时间间隔。
• ROUND(..., 3)：将结果保留三位小数。

完整代码

select
    round(avg(timestampdiff(minute, e.create_time, a.out_time) / 60), 3) as time
from
    express_tb e
    join exp_action_tb a on e.exp_number = a.exp_number