ClickHouse：如何从9:15开始按小时聚合时间序列数据

现有ClickHouse表结构如下：

time_stamp     DateTime(Asia/Kolkata)
value          Int32

需求是生成从9:15到23:15的时间序列，每个1小时区间（以每小时15分为起始）取对应区间内的第一个value值。

此前尝试的两种方案存在核心问题：

1. 直接使用toStartOfInterval按小时聚合，会将时间舍入到整点，不符合9:15起始的需求；
2. 给整点结果加15分钟的方式，仅修改了输出的时间标签，实际聚合区间仍是整点到下一个整点，并非从15分开始的目标区间。

解决方案 #

要实现以每小时15分为起始的1小时区间聚合，核心思路是通过时间偏移调整分组基准，确保聚合区间为[X:15, (X+1):15)：

基础查询

SELECT
    addMinutes(toStartOfInterval(subtractMinutes(time_stamp, 15), INTERVAL 1 HOUR, 'Asia/Kolkata'), 15) AS ts,
    any(value) AS first_value
FROM hdqlm
WHERE time_stamp >= toDateTime('2023-03-01 09:15:00', 'Asia/Kolkata') 
  AND time_stamp <= toDateTime('2023-03-01 23:15:00', 'Asia/Kolkata')
GROUP BY ts
ORDER BY ts

逻辑说明

• subtractMinutes(time_stamp, 15)：将原时间偏移15分钟，例如9:15转为9:00、10:15转为10:00，以此对齐整点分组基准；
• toStartOfInterval(..., INTERVAL 1 HOUR, 'Asia/Kolkata')：对偏移后的时间按小时取整点，此时每个整点对应的原时间区间恰好是[X:15, (X+1):15)；
• addMinutes(..., 15)：将分组标签偏移回15分钟，得到我们需要的区间起始时间（如9:15、10:15等）；
• WHERE条件过滤出目标时间范围，避免无关区间的无效结果。

更精准的取值方式 #

如果需要严格获取区间内最早时间对应的value（而非任意第一个值），可以将any(value)替换为minBy(value, time_stamp)，确保结果的准确性：

SELECT
    addMinutes(toStartOfInterval(subtractMinutes(time_stamp, 15), INTERVAL 1 HOUR, 'Asia/Kolkata'), 15) AS ts,
    minBy(value, time_stamp) AS first_value
FROM hdqlm
WHERE time_stamp >= toDateTime('2023-03-01 09:15:00', 'Asia/Kolkata') 
  AND time_stamp <= toDateTime('2023-03-01 23:15:00', 'Asia/Kolkata')
GROUP BY ts
ORDER BY ts

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