在BigQuery中转换hh:mm:ss格式ride_length为整数并计算平均值的问题

我懂你现在的困扰——想把BigQuery里ride_length字段（hh:mm:ss格式）的平均时长算出整数小时结果，比如直接得到1而不是01:00:00，之前用CAST转整数没成功对吧？这是因为TIME类型没法直接转成整数，得先把时间转换成数值型的时长间隔，再计算平均和取整。下面给你几个实用的方法：

方法一：拆分时分秒转总秒数计算（适用于TIME类型字段） #

先把每个ride_length拆成小时、分钟、秒，转换成总秒数，计算平均值后再转成小时并取整：

SELECT
  -- 可以根据需求选CAST、FLOOR或ROUND：CAST直接截断小数，ROUND四舍五入，FLOOR取整数部分
  CAST(AVG(
    EXTRACT(HOUR FROM ride_length) * 3600 +
    EXTRACT(MINUTE FROM ride_length) * 60 +
    EXTRACT(SECOND FROM ride_length)
  ) / 3600 AS INT64) AS avg_ride_hours
FROM
  your_table_name -- 替换成你的表名

方法二：用TIME_DIFF简化计算（适用于TIME类型字段） #

利用TIME_DIFF函数直接计算目标时间与0点的秒数差，再求平均转小时：

SELECT
  CAST(AVG(TIME_DIFF(ride_length, TIME '00:00:00', SECOND)) / 3600 AS INT64) AS avg_ride_hours
FROM
  your_table_name

这个写法更简洁，原理和方法一一致，只是用内置函数减少了拆分步骤。

如果ride_length是字符串类型 #

如果你的ride_length是字符串（不是TIME类型），得先把它转成TIME类型再处理：

SELECT
  CAST(AVG(
    EXTRACT(HOUR FROM PARSE_TIME('%H:%M:%S', ride_length)) * 3600 +
    EXTRACT(MINUTE FROM PARSE_TIME('%H:%M:%S', ride_length)) * 60 +
    EXTRACT(SECOND FROM PARSE_TIME('%H:%M:%S', ride_length))
  ) / 3600 AS INT64) AS avg_ride_hours
FROM
  your_table_name

这里用PARSE_TIME('%H:%M:%S', ride_length)把字符串转成TIME类型，再按前面的逻辑计算即可。

为什么之前CAST失败？ #

直接CAST TIME类型到整数是行不通的，因为BigQuery的TIME类型本质是一天内的时刻，不是时长间隔，必须先转换成秒/分钟这类数值型的时长单位，才能进行数学运算和取整。

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