如何在BigQuery中用DATETIME_DIFF计算分组平均出行时间？

正确SQL查询语句 #

SELECT 
  member_casual,
  AVG(
    DATETIME_DIFF(ended_at, started_at, MINUTE) +
    CASE WHEN ended_at < started_at THEN 1440 ELSE 0 END
  ) AS average_trip_duration_minutes
FROM dataset
GROUP BY member_casual;

关键修改说明 #

• 平均值计算：将单条行程的时长计算逻辑整体包裹在AVG()函数中，实现分组后的均值统计
• 时间差顺序修正：DATETIME_DIFF参数改为ended_at, started_at，确保正常时段（未跨午夜）得到正的分钟差值
• 跨午夜修正逻辑：将CASE语句的增量从24改为1440（24×60分钟），因为DATETIME_DIFF的单位是分钟，跨午夜时需要补足一整天的分钟数
• 分组逻辑简化：仅保留member_casual作为GROUP BY字段，确保结果按用户类型分组，返回两行统计数据
• 列别名优化：添加average_trip_duration_minutes别名，让结果列含义更清晰

原查询问题分析 #

原查询在GROUP BY中包含了started_at和ended_at，导致按每个单独行程的时间对分组，无法得到用户组的平均时长；同时时间差的计算顺序和跨午夜的增量单位错误，会导致时长计算偏差。

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