DuckDB中调整time_bucket时间区间起始实现自定义重采样

在DuckDB中执行时间桶聚合时，默认的time_bucket函数会生成从整点/整5分开始的区间（如13:00-13:04、13:05-13:09），但需要将区间调整为13:01-13:05、13:06-13:10这样的自定义偏移区间，得到对应的max(num)结果。

数据样本 #

原始表t的数据如下：

原SQL与结果 #

执行以下SQL：

select time_bucket(INTERVAL '5 min', time) as time, max(num) from t group by time_bucket(INTERVAL '5 min', time);

得到结果：

期望结果 #

需要调整区间后得到：

解决方案 #

DuckDB的time_bucket函数支持第三个参数偏移量，可用来调整时间桶的起始位置。我们需要将区间偏移1分钟，让每个桶的覆盖范围变为[N+1, N+5]分钟，同时为了让结果显示区间的结束时间（如13:05对应13:01-13:05的区间），可以在分桶后加上桶的间隔。

具体SQL如下：

select 
  time_bucket(INTERVAL '5 min', time, INTERVAL '1 min') + INTERVAL '5 min' as time,
  max(num)
from t
-- 过滤掉超出目标区间的数据（如13:11不属于13:06-13:10）
where time <= '2023-05-21 13:10:00'
group by time_bucket(INTERVAL '5 min', time, INTERVAL '1 min')
order by time;

说明 #

1. 偏移量参数：time_bucket(INTERVAL '5 min', time, INTERVAL '1 min')会将时间桶的起始位置偏移1分钟，这样第一个桶覆盖13:01-13:05，第二个桶覆盖13:06-13:10。
2. 调整显示时间：给分桶结果加上INTERVAL '5 min'，让结果显示区间的结束时间，和期望结果一致。
3. 过滤数据：添加where time <= '2023-05-21 13:10:00'可以排除13:11的数据，因为它不属于目标区间。

执行上述SQL后，就能得到符合要求的结果。

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