Google Big Query计算hh:mm:ss格式时间列平均值报错求助

错误原因 #

ride_length列是字符串类型（STRING），AVG聚合函数无法直接对字符串执行数值计算，因此触发报错：No matching signature for aggregate function AVG for argument types: STRING。

解决方案 #

需要先把字符串格式的时长转成可计算的数值（秒数），求平均后再转回hh:mm:ss格式，以下是两种可行的SQL写法：

写法一：拆分时分秒计算总秒数 #

SELECT
  FORMAT_TIMESTAMP("%T", TIMESTAMP_SECONDS(CAST(AVG(
    EXTRACT(HOUR FROM TIME(ride_length)) * 3600 +
    EXTRACT(MINUTE FROM TIME(ride_length)) * 60 +
    EXTRACT(SECOND FROM TIME(ride_length))
  ) AS INT64))) AS average_duration
FROM `casestudy1-361603.project.DecData`

写法二：用TIME_DIFF简化秒数计算 #

SELECT
  FORMAT_TIMESTAMP("%T", TIMESTAMP_SECONDS(CAST(AVG(
    TIME_DIFF(TIME(ride_length), TIME("00:00:00"), SECOND)
  ) AS INT64))) AS average_duration
FROM `casestudy1-361603.project.DecData`

逻辑说明 #

1. 字符串转时间类型：通过TIME(ride_length)把hh:mm:ss格式的字符串转换为BigQuery原生TIME类型。
2. 转换为秒数：要么拆分时分秒计算总秒数，要么用TIME_DIFF计算目标时间与0点的秒数差，得到可用于聚合的数值。
3. 计算平均并格式化：对秒数求平均值后转为整数，再通过TIMESTAMP_SECONDS和FORMAT_TIMESTAMP转回hh:mm:ss格式的字符串。

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