如何在BigQuery中将时间字符串转秒并求平均后转回时间格式

完整SQL实现 #

直接通过CTE串联三个步骤，一次性完成转换、平均和格式还原：

WITH time_seconds_cte AS (
  SELECT
    TargetTime,
    -- 将HH:MM:SS字符串转为总秒数
    TIME_TO_SEC(TargetTime) AS total_seconds
  FROM MyTable
)
SELECT
  -- 将平均秒数转回HH:MM:SS格式
  SEC_TO_TIME(AVG(total_seconds)) AS average_target_time
FROM time_seconds_cte;

步骤拆解与验证（针对你的示例数据） #

1. 转秒操作：TIME_TO_SEC(TargetTime)可直接解析HH:MM:SS格式字符串，示例中：
   • 00:11:02 → 662秒
   • 00:02:00 → 120秒
2. 计算平均值：AVG(total_seconds)计算秒数的算术平均，示例中(662 + 120)/2 = 391秒
3. 转回时间格式：SEC_TO_TIME(391)将391秒还原为00:06:31，符合预期结果

处理无效时间格式的容错方案 #

如果TargetTime字段存在非法格式的字符串（比如25:00:00或非时间格式内容），可以用SAFE_TIME_TO_SEC避免查询报错，并过滤无效数据：

WITH time_seconds_cte AS (
  SELECT
    TargetTime,
    SAFE_TIME_TO_SEC(TargetTime) AS total_seconds
  FROM MyTable
  -- 过滤无法解析为有效时间的行
  WHERE SAFE_TIME_TO_SEC(TargetTime) IS NOT NULL
)
SELECT
  SEC_TO_TIME(AVG(total_seconds)) AS average_target_time
FROM time_seconds_cte;

常见问题说明 #

• 若平均值为小数秒（比如平均391.5秒），SEC_TO_TIME会自动处理为带毫秒的格式（如00:06:31.500），如果需要舍去小数部分，可以用FLOOR(AVG(total_seconds))或ROUND(AVG(total_seconds))先处理秒数。

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