如何将骑行数据按4小时分组以计算平均每时段完成骑行次数？

要实现按4小时为周期分组统计骑行次数，由于4小时并非SQL标准的时间截断单位，我们可以通过计算时间所属的4小时区间起始时间生成分组键，以下是两种通用的实现方式：

方法1：基于日期截断+小时数计算（适配多数SQL引擎） #

先将时间截断到天，再结合小时数推导所属4小时区间的起始点：

SELECT
  -- 生成4小时区间的起始时间
  DATE_TRUNC('day', dropoff_datetime) + INTERVAL '1 hour' * (EXTRACT(HOUR FROM dropoff_datetime)::INTEGER / 4 * 4) AS four_hour_window,
  COUNT(ride_id) AS total_rides
FROM final
-- 可选：添加时间范围筛选，比如仅查询2022年8月27日的区间
-- WHERE dropoff_datetime >= '2022-08-27 00:00:00' AND dropoff_datetime < '2022-08-28 00:00:00'
GROUP BY four_hour_window
ORDER BY four_hour_window;

逻辑说明： #

1. EXTRACT(HOUR FROM dropoff_datetime)：提取时间中的小时部分（取值0-23）
2. (小时数::INTEGER / 4 * 4)：通过整数除法将小时数映射到所属4小时区间的起始小时（例如11→8、13→12、23→20）
3. 将计算出的起始小时转为INTERVAL，加到截断到天的时间上，得到完整的4小时区间起始时间

方法2：基于时间戳Epoch值计算（全场景兼容） #

将时间转换为Epoch秒数，按4小时总秒数（4*3600）取整后再转回时间戳：

SELECT
  TIMESTAMP 'epoch' + (FLOOR(EXTRACT(EPOCH FROM dropoff_datetime) / (4 * 3600)) * 4 * 3600) * INTERVAL '1 second' AS four_hour_window,
  COUNT(ride_id) AS total_rides
FROM final
GROUP BY four_hour_window
ORDER BY four_hour_window;

逻辑说明： #

1. EXTRACT(EPOCH FROM dropoff_datetime)：将时间转换为从1970-01-01 00:00:00以来的秒数
2. FLOOR(秒数 / (4*3600))：将秒数按4小时总秒数取整，得到区间序号
3. 乘以4小时秒数后转回时间戳，即为该时间所属的4小时区间起始时间

特定区间查询示例 #

如果要单独统计2022年8月27日12:00-16:00的骑行次数，可直接添加WHERE条件：

SELECT
  '2022-08-27 12:00:00' AS four_hour_window,
  COUNT(ride_id) AS total_rides
FROM final
WHERE dropoff_datetime >= '2022-08-27 12:00:00' 
  AND dropoff_datetime < '2022-08-27 16:00:00';

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