在BigQuery中如何计算TIME格式数据的平均时间？

问题原因 #

AVG函数仅支持对数值类型执行聚合计算，TIME类型属于时间格式，无法直接作为AVG的入参使用。想要计算平均时间需要先把TIME类型转换为从当日0点开始的总秒数，计算平均值后再转换回TIME格式即可。

修改后的查询代码 #

WITH october_fall AS
   (SELECT
   start_station_name,
   end_station_name,
   start_station_id,
   end_station_id,
   EXTRACT (DATE FROM started_at) AS start_date,
   EXTRACT(DAYOFWEEK FROM started_at) AS start_week_date,
   EXTRACT (TIME FROM started_at) AS start_time,    
   EXTRACT (DATE FROM ended_at) AS end_date,
   EXTRACT(DAYOFWEEK FROM ended_at) AS end_week_date,    
   EXTRACT (TIME FROM ended_at) AS end_time,
   DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,
   member_casual
FROM 
   `ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis
ORDER BY 
   started_at DESC)
SELECT
   COUNT (start_week_date) AS avg_start_1,
   -- 先转成秒数计算平均，再转换回TIME格式
   SEC_TO_TIME(AVG(TIME_TO_SEC(start_time))) AS avg_start_time_1,
   member_casual
FROM 
   october_fall
WHERE 
   start_week_date = 1
GROUP BY
   member_casual

逻辑说明 #

• TIME_TO_SEC函数可以将TIME类型的值转换为从当日0点开始计算的总秒数，属于数值类型可以直接交给AVG计算平均值
• 得到平均秒数后，再通过SEC_TO_TIME函数把总秒数转换回标准的TIME格式，就能得到需要的平均时间结果

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