计算两时间列的hhmmss格式时长差及平均时长（BigQuery）

需求 #

• 计算started_at和ended_at两列的时间差，将结果以hh:mm:ss格式存入ride_length列
• 统计所有行ride_length的平均值

示例数据 #

started_at                           ended_at                       
2020-12-18 10:06:31 UTC            2020-12-18 10:16:34 UTC
2020-12-03 07:46:32 UTC            2020-12-03 08:17:19 UTC

现有SQL问题分析 #

你提供的SQL存在两个核心问题：

1. DATE_DIFF(started_at, ended_at, SECOND)会得到负数（结束时间晚于开始时间），转成TIME格式后会出现异常值
2. 仅完成了时间差计算，未实现平均值统计，且嵌套查询层级冗余

修正后的SQL语句（适用于BigQuery） #

WITH ride_details AS (
  SELECT
    -- 计算时间差（取绝对值避免负数），转成hh:mm:ss格式
    TIME(TIMESTAMP_SECONDS(ABS(DATE_DIFF(started_at, ended_at, SECOND)))) AS ride_length,
    -- 保留秒数用于平均值计算
    ABS(DATE_DIFF(started_at, ended_at, SECOND)) AS total_seconds
  FROM avid-winter-405805.bikesharingdata.jan
),
average_calc AS (
  SELECT
    -- 将平均秒数转回hh:mm:ss格式
    TIME(TIMESTAMP_SECONDS(AVG(total_seconds))) AS avg_ride_length
  FROM ride_details
)
SELECT
  rd.ride_length,
  ac.avg_ride_length
FROM ride_details rd, average_calc ac;

逻辑说明 #

1. 用ride_details CTE处理每行数据：计算时间差的绝对值（确保为正），转成hh:mm:ss格式的ride_length，同时保存秒数用于后续平均值计算
2. 用average_calc CTE统计所有行的秒数平均值，再转成hh:mm:ss格式的平均值
3. 最后关联两个CTE，输出每行的ride_length和全局平均值

基于示例数据的实际输出 #

ride_length        avg_ride_length
00:10:03            00:20:25

内容的提问来源于stack exchange，提问作者kishan