在BigQuery中计算h:mm格式ride_length列的平均时长（SQL新手求助）

针对BigQuery中h:mm格式的ride_length列，你可以通过以下SQL查询计算平均时长（以分钟为单位）：

基础查询（直接返回分钟数） #

SELECT
  AVG(
    EXTRACT(HOUR FROM PARSE_TIME('%H:%M', ride_length)) * 60 +
    EXTRACT(MINUTE FROM PARSE_TIME('%H:%M', ride_length))
  ) AS average_ride_length_minutes
FROM
  `你的项目ID.你的数据集.你的表名`
WHERE
  ride_length IS NOT NULL -- 过滤空值和格式无效的记录

分步解释 #

1. 解析字符串为时间类型：PARSE_TIME('%H:%M', ride_length)把h:mm格式的字符串转换成BigQuery可识别的TIME类型。
2. 转换为总分钟数：用小时数乘以60加上分钟数，得到单条记录的总骑行分钟数。
3. 计算平均值：AVG()函数对所有有效记录的总分钟数取平均。

可选：将平均结果转回h:mm格式 #

如果需要把计算出的平均分钟数再转为h:mm格式，可以嵌套查询实现：

SELECT
  FORMAT_TIME('%H:%M', TIME_ADD(TIME(0, 0, 0), INTERVAL average_minutes MINUTE)) AS average_ride_length_hhmm,
  average_minutes AS average_ride_length_minutes
FROM (
  SELECT
    AVG(
      EXTRACT(HOUR FROM PARSE_TIME('%H:%M', ride_length)) * 60 +
      EXTRACT(MINUTE FROM PARSE_TIME('%H:%M', ride_length))
    ) AS average_minutes
  FROM
    `你的项目ID.你的数据集.你的表名`
  WHERE
    ride_length IS NOT NULL
)

注意点 #

• 务必替换你的项目ID.你的数据集.你的表名为你实际的BigQuery资源路径。
• 如果ride_length存在不符合h:mm格式的内容，PARSE_TIME会返回NULL，WHERE子句会自动排除这些无效数据，避免干扰计算结果。

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