使用time_bucket_gapfill查询结果超出限定起始时间问题求助

问题根源 #

time_bucket_gapfill 是按指定桶大小生成时间桶的，它会把查询到的第一个数据点映射到对应的完整桶起始时间——哪怕这个桶的起始时间早于你在WHERE子句里限定的起始时间。比如你设定的起始时间是1661961600000，但第一个数据点落在了前一天的桶（1661904000000）里，gapfill就会把这个桶也包含进来。

解决办法 #

1. 给time_bucket_gapfill指定start和end参数（推荐） #

直接在函数里传入你要限定的时间范围，强制它只生成这个区间内的桶：

SELECT time_bucket_gapfill(86400000, create_time, start => 1661961600000, end => 1664467200000) as "bucket", count(*)
FROM annotation
WHERE create_time > 1661961600000 AND create_time < 1664467200000
GROUP BY bucket

这种方式从根源上避免了生成范围外的桶，效率最高。

2. 在外层查询过滤bucket #

先让函数生成所有可能的桶，再在外层过滤掉不符合时间范围的结果：

SELECT bucket, count
FROM (
    SELECT time_bucket_gapfill(86400000, create_time) as "bucket", count(*) as count
    FROM annotation
    WHERE create_time > 1661961600000 AND create_time < 1664467200000
    GROUP BY bucket
) t
WHERE bucket >= 1661961600000

这种方式逻辑简单，适合不想修改原函数调用的场景。

3. 使用HAVING子句过滤 #

在GROUP BY之后用HAVING直接过滤桶的时间：

SELECT time_bucket_gapfill(86400000, create_time) as "bucket", count(*)
FROM annotation
WHERE create_time > 1661961600000 AND create_time < 1664467200000
GROUP BY bucket
HAVING bucket >= 1661961600000

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