BigQuery中如何计算月度骑行行程的平均与中位数时长？

先纠正你之前的计算错误 #

你之前用(ended_at-started_at)*1440得到错误结果的原因是：BigQuery里两个TIMESTAMP相减得到的是INTERVAL类型，直接和数字相乘时，会默认把这个INTERVAL当成「天数」来计算。比如实际时长1小时26分（约0.597天），乘以1440就会得到0.597×1440≈2064分钟，完全偏离了实际值。

正确的计算方案 #

下面分两种场景给出最优SQL写法：

场景1：用started_at和ended_at计算（推荐，避免trip_duration字段可能的误差）

直接用TIMESTAMP_DIFF函数计算两个时间戳的差值，指定单位为分钟，这样能直接得到准确的行程分钟数，再基于此计算平均和中位数：

SELECT
  -- 按年月分组
  EXTRACT(YEAR FROM started_at) AS trip_year,
  EXTRACT(MONTH FROM started_at) AS trip_month,
  -- 平均行程时长（分钟）
  AVG(TIMESTAMP_DIFF(ended_at, started_at, MINUTE)) AS avg_trip_minutes,
  -- 连续型中位数（偶数个数据时取中间两数的平均值）
  PERCENTILE_CONT(TIMESTAMP_DIFF(ended_at, started_at, MINUTE), 0.5) OVER (PARTITION BY trip_year, trip_month) AS median_trip_cont,
  -- 离散型中位数（直接返回数据集中实际存在的中间值）
  PERCENTILE_DISC(TIMESTAMP_DIFF(ended_at, started_at, MINUTE), 0.5) OVER (PARTITION BY trip_year, trip_month) AS median_trip_disc,
  -- 将平均时长转回TIME格式（方便直观查看）
  TIME_ADD(TIME(0,0,0), INTERVAL AVG(TIMESTAMP_DIFF(ended_at, started_at, MINUTE)) MINUTE) AS avg_trip_time
FROM
  `case-study-367714.case_study.yearly_data`
GROUP BY
  trip_year, trip_month
ORDER BY
  trip_year, trip_month;

场景2：直接使用已有的trip_duration字段（TIME类型）

如果要基于现有的TIME类型字段计算，需要先把TIME转成总分钟数（包含秒的小数部分），再进行统计：

SELECT
  EXTRACT(YEAR FROM started_at) AS trip_year,
  EXTRACT(MONTH FROM started_at) AS trip_month,
  -- 把TIME转成总分钟数：小时×60 + 分钟 + 秒/60
  AVG(EXTRACT(HOUR FROM trip_duration)*60 + EXTRACT(MINUTE FROM trip_duration) + EXTRACT(SECOND FROM trip_duration)/60) AS avg_trip_minutes,
  PERCENTILE_CONT(EXTRACT(HOUR FROM trip_duration)*60 + EXTRACT(MINUTE FROM trip_duration) + EXTRACT(SECOND FROM trip_duration)/60, 0.5) OVER (PARTITION BY trip_year, trip_month) AS median_trip_cont,
  PERCENTILE_DISC(EXTRACT(HOUR FROM trip_duration)*60 + EXTRACT(MINUTE FROM trip_duration) + EXTRACT(SECOND FROM trip_duration)/60, 0.5) OVER (PARTITION BY trip_year, trip_month) AS median_trip_disc,
  TIME_ADD(TIME(0,0,0), INTERVAL AVG(EXTRACT(HOUR FROM trip_duration)*60 + EXTRACT(MINUTE FROM trip_duration) + EXTRACT(SECOND FROM trip_duration)/60) MINUTE) AS avg_trip_time
FROM
  `case-study-367714.case_study.yearly_data`
GROUP BY
  trip_year, trip_month
ORDER BY
  trip_year, trip_month;

关键说明 #

• 中位数选择：PERCENTILE_CONT适合需要精确中间值的场景，PERCENTILE_DISC适合需要实际存在的行程时长的场景，根据你的分析需求选择即可。
• 格式转换：最后用TIME_ADD把分钟数转回TIME格式，方便直接查看时分秒形式的平均时长。

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