BigQuery中如何将平均时长转为hh:mm:ss格式并使用ROUND函数？

问题出在直接对TIME类型做减法并取平均，这种方式会返回带微秒精度的结果，且TIME类型的平均逻辑不够直观。正确的思路是先将时长转换为数值类型（比如秒数）进行计算，再格式化回目标格式并应用ROUND函数。

方案1：时长≤24小时，格式化为hh:mm:ss #

如果平均时长不会超过24小时，可利用FORMAT_TIMESTAMP快速格式化：

SELECT
  pickup_location_id,
  FORMAT_TIMESTAMP('%T', TIMESTAMP_SECONDS(ROUND(avg_seconds))) AS avg_duration
FROM (
  SELECT
    pickup_location_id,
    AVG(TIME_DIFF(dropoff_datetime, pickup_datetime, SECOND)) AS avg_seconds
  FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2022`
  WHERE dropoff_datetime > pickup_datetime -- 过滤异常的返程数据
  GROUP BY pickup_location_id
)
ORDER BY avg_seconds DESC

方案2：支持时长>24小时，格式化为hh:mm:ss（如25:30:15） #

若存在平均时长超过24小时的场景，需手动计算时分秒并拼接：

SELECT
  pickup_location_id,
  CONCAT(
    CAST(ROUND(avg_seconds) DIV 3600 AS STRING), ':',
    FORMAT('%02d', (ROUND(avg_seconds) MOD 3600) DIV 60), ':',
    FORMAT('%02d', ROUND(avg_seconds) MOD 60)
  ) AS avg_duration
FROM (
  SELECT
    pickup_location_id,
    AVG(TIME_DIFF(dropoff_datetime, pickup_datetime, SECOND)) AS avg_seconds
  FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2022`
  WHERE dropoff_datetime > pickup_datetime -- 过滤异常的返程数据
  GROUP BY pickup_location_id
)
ORDER BY avg_seconds DESC

关键说明 #

1. 转换为秒数计算：用TIME_DIFF获取每个行程的秒数时长，再取平均值，让数值计算更可控，也能直接用ROUND函数处理精度。
2. 异常数据过滤：添加WHERE dropoff_datetime > pickup_datetime排除掉下车时间早于上车时间的异常记录，避免负时长影响结果。
3. 格式化逻辑：
   • 方案1利用TIMESTAMP_SECONDS将秒数转为时间戳，再用%T格式符直接输出hh:mm:ss；
   • 方案2通过整除和取模运算拆分小时、分钟、秒，并用FORMAT('%02d')保证分钟和秒为两位数字。

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