如何在BigQuery中精确计算TIME类型列的平均时间

问题本质 #

计算TIME类型（HH:MM:SS）的平均值时，直接通过秒数转TIMESTAMP再转TIME会因浮点转整数丢失秒级精度，且BigQuery不支持将TIME直接转为INTERVAL进行计算。

方案1：基于原始datetime列计算（推荐） #

直接从start_datetime和end_datetime推导时长并计算平均，避免依赖存储的time_difference列，精度更可靠：

SELECT
  TIME(
    FLOOR(avg_seconds / 3600),
    FLOOR((avg_seconds % 3600) / 60),
    MOD(avg_seconds, 60)
  ) AS average_time_difference
FROM (
  SELECT
    AVG(DATETIME_DIFF(end_datetime, start_datetime, SECOND)) AS avg_seconds
  FROM `cleaned_ok_new`
)

逻辑说明：

1. 用DATETIME_DIFF计算每条记录的时长（单位：秒，INT64类型）
2. 对秒数求平均得到带小数的浮点值，保留完整精度
3. 手动拆分平均秒数为小时、分钟、秒，用TIME()函数构造精确的TIME类型结果

方案2：基于已存储的TIME列计算 #

如果必须使用现有的time_difference列，先将TIME转为总秒数再计算平均：

SELECT
  TIME(
    FLOOR(avg_seconds / 3600),
    FLOOR((avg_seconds % 3600) / 60),
    MOD(avg_seconds, 60)
  ) AS average_time_difference
FROM (
  SELECT
    AVG(
      EXTRACT(HOUR FROM time_difference)*3600 +
      EXTRACT(MINUTE FROM time_difference)*60 +
      EXTRACT(SECOND FROM time_difference)
    ) AS avg_seconds
  FROM `cleaned_ok_new`
)

逻辑说明：

1. 提取TIME列的时、分、秒，转换为总秒数（保留秒的小数精度）
2. 计算总秒数的平均值
3. 拆分时分秒构造TIME类型结果，规避TIME转INTERVAL的报错

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