在BigQuery中从Timestamp计算时间的平均值与最大值

计算时间部分的最大值 #

直接用TIME()函数提取Timestamp的时间部分，再通过MAX()聚合就能得到结果：

SELECT MAX(TIME(timestamp_column)) AS max_time
FROM your_table;

针对你的示例数据，执行后会返回23:14:25.883233，符合预期。

计算时间部分的平均值 #

时间无法直接做数值平均，需要先转换为当天的总秒数（包含小数秒），计算平均值后再转回时间格式：

1. 提取时间的时、分、秒，转换为当天的总秒数
2. 对总秒数取平均值
3. 用SEC_TO_TIME()将平均秒数转回时间格式

完整查询语句：

SELECT
  SEC_TO_TIME(AVG(
    EXTRACT(HOUR FROM timestamp_column)*3600 
    + EXTRACT(MINUTE FROM timestamp_column)*60 
    + EXTRACT(SECOND FROM timestamp_column)
  )) AS avg_time
FROM your_table;

你的示例数据中三个时间的平均秒数对应22:14:25.883233，与你期望的“约22点”一致。

整合查询（同时获取最大值和平均值） #

SELECT
  MAX(TIME(timestamp_column)) AS max_time,
  SEC_TO_TIME(AVG(
    EXTRACT(HOUR FROM timestamp_column)*3600 
    + EXTRACT(MINUTE FROM timestamp_column)*60 
    + EXTRACT(SECOND FROM timestamp_column)
  )) AS avg_time
FROM your_table;

为什么之前的方法失效？ #

你尝试的unix_timestamp函数会计算从 epoch 到该Timestamp的总秒数，包含日期部分的差异（比如2022-02-24比2022-01-24多了31天），直接平均会把日期的影响带入结果，导致不符合预期。而上述方法仅提取当天的时间部分做计算，完全剥离了日期的干扰。

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