如何在ClickHouse中按固定7天间隔而非日历间隔进行数据分桶？

你的问题核心在于分组逻辑未同步调整：虽然你在SELECT语句里把toStartOfWeek的结果偏移了5天，但GROUP BY依然基于日历周的toStartOfWeek(datefield)，所以统计的还是原日历周的数据，只是显示日期做了偏移，统计结果自然不会变化。

要实现从指定日期（如2022-04-01）开始的固定7天间隔分桶，需基于日期与起始点的天数差计算分组键，具体方案如下：

核心逻辑 #

以2022-04-01为固定起始点，对每个日期字段执行以下计算：

1. 计算该日期与起始点的天数差
2. 将天数差整除7，得到该日期所属的"7天桶序号"
3. 用序号乘以7再加回起始点，得到当前日期所属的7天桶起始日期

修正后的查询语句 #

SELECT
    -- 计算datefield2所属的固定7天桶起始日期
    toDate('2022-04-01') + floor((toDate(datefield2) - toDate('2022-04-01')) / 7) * 7 AS bucket_date2,
    -- 计算datefield1所属的固定7天桶起始日期
    toDate('2022-04-01') + floor((toDate(datefield1) - toDate('2022-04-01')) / 7) * 7 AS bucket_date1,
    count(distinct user_id) AS unique_users
FROM my_db.my_table 
WHERE 
    datefield1 BETWEEN '2022-04-01 00:00:00' AND '2022-05-30 23:59:59'
    AND datefield2 BETWEEN '2022-04-01 00:00:00' AND '2022-05-30 23:59:59'
GROUP BY bucket_date2, bucket_date1
ORDER BY bucket_date2, bucket_date1
LIMIT 100 OFFSET 0;

逻辑说明 #

• toDate(datefield) - toDate('2022-04-01')：得到目标日期与起始点的天数差（返回数值类型）
• floor(天数差 /7)：向下取整得到桶的序号（0代表2022-04-01_{2022-04-07，1代表2022-04-08}2022-04-14，以此类推）
• 乘以7再加回起始点，得到每个桶的起始日期，确保分桶是严格的7天固定间隔

时间精度优化方案 #

如果你的日期字段是DateTime类型，需要保留时间精度的固定7天间隔，可改用toUnixTimestamp()计算：

SELECT
    toDateTime(toUnixTimestamp('2022-04-01 00:00:00') + floor((toUnixTimestamp(datefield2) - toUnixTimestamp('2022-04-01 00:00:00')) / (7*86400)) * 7*86400) AS bucket_date2,
    toDateTime(toUnixTimestamp('2022-04-01 00:00:00') + floor((toUnixTimestamp(datefield1) - toUnixTimestamp('2022-04-01 00:00:00')) / (7*86400)) * 7*86400) AS bucket_date1,
    count(distinct user_id) AS unique_users
FROM my_db.my_table 
WHERE 
    datefield1 BETWEEN '2022-04-01 00:00:00' AND '2022-05-30 23:59:59'
    AND datefield2 BETWEEN '2022-04-01 00:00:00' AND '2022-05-30 23:59:59'
GROUP BY bucket_date2, bucket_date1
ORDER BY bucket_date2, bucket_date1
LIMIT 100 OFFSET 0;

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