如何在ClickHouse中按指定起始时间间隔分组数据

需求描述 #

需要编写ClickHouse查询语句，将指定时间范围内的数据从该范围起始日期开始，按指定天数（x天）为间隔分组统计。

示例场景 #

时间范围：05/11/2023 至 05/12/2023，按3天分组的预期结果：

遇到的问题 #

使用toStartOfInterval方法时，生成的间隔是基于1970-01-01计算的，无法保证分组从指定时间范围的起始日期开始。

解决方案 #

核心逻辑是通过计算数据日期与指定起始日期的偏移量，将数据映射到以起始日期为基准的间隔分组中。

针对Date类型字段的查询 #

假设表名为your_table，时间字段为event_date，起始日期'2023-11-05'，间隔3天，结束日期'2023-12-05'：

WITH 
  '2023-11-05' AS start_date,
  3 AS interval_days,
  '2023-12-05' AS end_date
SELECT
  toDate(start_date) + toIntervalDay((toUInt32(event_date - toDate(start_date)) / interval_days) * interval_days) AS group_date,
  count(*) AS count
FROM your_table
WHERE event_date BETWEEN toDate(start_date) AND toDate(end_date)
GROUP BY group_date
ORDER BY group_date

针对DateTime类型字段的查询 #

如果时间字段是DateTime类型，只需调整时间转换逻辑：

WITH 
  '2023-11-05 00:00:00' AS start_datetime,
  3 AS interval_days,
  '2023-12-05 23:59:59' AS end_datetime
SELECT
  toDateTime(start_datetime) + toIntervalSecond((toUInt32(event_datetime - toDateTime(start_datetime)) / (interval_days * 86400)) * (interval_days * 86400)) AS group_datetime,
  count(*) AS count
FROM your_table
WHERE event_datetime BETWEEN toDateTime(start_datetime) AND toDateTime(end_datetime)
GROUP BY group_datetime
ORDER BY group_datetime

逻辑说明 #

1. 计算数据日期与起始日期的时间差（天数/秒数）
2. 将时间差按指定间隔取整，得到该数据所属分组的偏移量
3. 把偏移量加回起始日期，得到分组的基准日期/时间
4. 按分组基准值统计并排序

该方案确保所有分组严格从指定的起始日期开始，完全匹配预期结果，且支持自定义起始日期、间隔天数和结束日期。

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