Clickhouse中14天按7天分组异常：结果多一组问题排查

我有2023-07-22到2023-08-04共14天的日期数据，想要按7天分组，最终得到每组最后一天的2个日期结果。执行以下SQL查询：

SELECT toStartOfInterval(date, toIntervalDay(7)) AS week
FROM
(
  SELECT arrayJoin([toDate('2023-07-22'), toDate('2023-08-04')]) AS date
  ORDER BY date ASC WITH FILL STEP 1
)
GROUP BY week
ORDER BY week ASC

但返回了3行不符合预期的结果：

┌────────────────week─┐
│ 2023-07-20 00:00:00 │
│ 2023-07-27 00:00:00 │
│ 2023-08-03 00:00:00 │
└─────────────────────┘

出现了未预期的2023-07-20，与预期的2023-07-29 00:00:00、2023-08-04 00:00:00不符。

问题原因 #

toStartOfInterval(date, toIntervalDay(7)) 是从**Unix纪元起始日（1970-01-01）**这个固定起点按7天区间划分，并非以你的数据起始日期2023-07-22为基准。2023-07-22落在1970-01-01开始的第N个7天区间（2023-07-20至2023-07-26），因此分组起始日被计算为2023-07-20，导致多生成了一组数据。

正确解决方案 #

以数据起始日期2023-07-22为基准计算偏移量，自定义分组逻辑，确保分组从起始日开始按7天划分，最终得到每组的最后一天。

正确查询语句：

with
    toDate('2023-07-22') as DATE_FROM,
    toDate('2023-08-04') as DATE_TO,
    7 as DAYS_LENGTH,
    DATE_FROM % DAYS_LENGTH as DAYS_SHIFT
select
    date - (date - DAYS_SHIFT) % DAYS_LENGTH as days_group
FROM
(
    SELECT arrayJoin([DATE_FROM, DATE_TO]) AS date
    ORDER BY date ASC WITH FILL STEP 1
)
GROUP BY days_group
order by days_group
format PrettyCompactMonoBlock
;

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