【数据分析面试】10. 计算平均通勤时间（SQL：timestampdiff() 和datediff()区别）

题目

假设你在Uber工作。rides表包含了关于Uber用户在美国各地的行程信息。

编写一个查询，以获取纽约（NY）每位通勤者的平均通勤时间（以分钟为单位），以及纽约所有通勤者的平均通勤时间（以分钟为单位）。

示例：

输入：

rides表

列名	类型
id	INTEGER
commuter_id	INTEGER
start_dt	DATETIME
end_dt	DATETIME
city	VARCHAR

输出：

列名	类型
commuter_id	INTEGER
avg_commuter_time	FLOAT
avg_time	FLOAT

结果显示如下：

commuter_id	avg_commuter_time	avg_time
11	27	45
22	97	45
33	11	45

答案

解题思路

一般思路是，先计算纽约的平均通勤时间，然后再计算个人的，最后把结果汇总在一起。
其实，直接用窗口函数可以直接得出结果。

答案代码

下面是直接用一个窗口函数，完成对每个ID的平均值计算。

SELECT DISTINCTcommuter_id,FLOOR(AVG(TIMESTAMPDIFF(MINUTE,start_dt,end_dt)) OVER (PARTITION BY commuter_id)) avg_commuter_time,FLOOR(AVG(TIMESTAMPDIFF(MINUTE,start_dt,end_dt)) OVER ()) avg_time
FROM rides
WHERE city = 'NY'

• FLOOR(): 用于向下取整，将平均值舍入到最接近的整数。
• TIMESTAMPDIFF(): 用于计算两个时间戳之间的差值，单位为分钟。
• AVG( ) OVER (PARTITION BY commuter_id): 用于对每个commuter_id进行分组，并计算每个分组的平均值。
• AVG() OVER ():OVER ()`表示在整个数据集上进行计算

计算时间差的函数

TIMESTAMPDIFF()
TIMESTAMPDIFF(unit, start_datetime, end_datetime) 是一个用于计算两个日期时间之间差异的 MySQL 函数。它接受三个参数：时间单位、起始日期时间和结束日期时间。

在我们的答案中就用了TIMESTAMPDIFF(MINUTE, start_dt, end_dt)

TIMESTAMPDIFF` 函数的时间单位参数可以是以下之一：

• MICROSECOND: 微秒
• SECOND: 秒
• MINUTE: 分钟
• HOUR: 小时
• DAY: 天
• WEEK: 周
• MONTH: 月
• QUARTER: 季度
• YEAR: 年

DATEDIFF()
DATEDIFF(date1, date2): 这个函数返回两个日期之间的天数差异。它接受两个日期参数，并返回 date2 减去 date1 的天数差异。

TIMESTAMPDIFF() 可以用来计算date 格式吗？
TIMESTAMPDIFF() 函数通常用于计算两个日期时间之间的差异，因此它的参数通常是 DATETIME 或 TIMESTAMP 类型的数据。虽然可以接受 DATE 类型的数据作为参数，但是在处理时会将 DATE 类型的数据隐式转换为 DATETIME 类型，并将时间部分视为零值。

举个例子，如果你要计算两个日期之间的差异：
SELECT TIMESTAMPDIFF(DAY, '2022-01-01', '2022-01-05');

这将返回 4，表示 ‘2022-01-01’ 到 ‘2022-01-05’ 之间相隔 4 天。

虽然 TIMESTAMPDIFF() 可以处理 DATE 类型的参数，但是如果只是想计算日期之间的天数差异，使用 DATEDIFF() 更为简单和直观。