ClickHouse按date_start差值小于15分钟分组求和的实现问询

这类需求属于典型的连续会话式分组，不需要递归遍历数据，通过窗口函数组合即可高效实现，核心逻辑拆分为三步：

• 按date_start升序对全量数据排序，通过窗口函数取当前行对应的上一行date_start值，首行无前置值时记为NULL
• 对比当前行与上一行的时间差：若差值超过15分钟，或当前行无前置时间，则标记当前行为新分组的起点
• 对所有新分组标记做累计求和，累加值相同的行属于同一分组，最后按分组聚合即可

完整可运行SQL #

以下SQL内置了题目给出的示例数据，可直接执行验证：

WITH source_data AS (
    -- 实际使用时替换为你的业务表查询即可
    SELECT toDateTime('2020-12-05 11:00:00') AS date_start,
           toDateTime('2020-12-05 11:15:00') AS date_end,
           1 AS value
    UNION ALL
    SELECT toDateTime('2020-12-05 11:15:00'), toDateTime('2020-12-05 11:30:00'), 2
    UNION ALL
    SELECT toDateTime('2020-12-05 11:30:00'), toDateTime('2020-12-05 11:45:00'), 3
    UNION ALL
    SELECT toDateTime('2020-12-05 13:00:00'), toDateTime('2020-12-05 13:15:00'), 4
),
-- 标记每行是否为新分组起点
flagged_rows AS (
    SELECT
        date_start,
        date_end,
        value,
        lag(date_start) OVER (ORDER BY date_start) AS prev_row_start,
        multiIf(
            -- 首行直接标记为新组起点
            prev_row_start IS NULL, 1,
            -- 与上一行间隔超过15分钟则为新组起点，<=15分钟保持同组（匹配示例的分组规则）
            date_diff('minute', prev_row_start, date_start) > 15, 1,
            0
        ) AS is_group_start
    FROM source_data
),
-- 累计标记值得到统一组ID
group_id_calc AS (
    SELECT
        date_start,
        date_end,
        value,
        sum(is_group_start) OVER (ORDER BY date_start) AS group_id
    FROM flagged_rows
)
-- 按组聚合输出结果
SELECT
    min(date_start) AS date_start,
    max(date_end) AS date_end,
    sum(value) AS sum
FROM group_id_calc
GROUP BY group_id
ORDER BY date_start;

执行结果 #

执行SQL后输出与题目期望完全一致：

┌───────────date_start─┬────────────date_end─┬─sum─┐
│ 2020-12-05 11:00:00  │ 2020-12-05 11:45:00 │   6 │
│ 2020-12-05 13:00:00  │ 2020-12-05 13:15:00 │   4 │
└───────────────────────┴───────────────────────┴─────┘

关键说明 #

• 阈值匹配逻辑：示例中相邻行date_start差为15分钟时被划入同一分组，因此判断新组的阈值设置为差值>15分钟，完全贴合示例的分组规则，如需严格按“差值小于15分钟归为同组”的字面规则实现，只需把判断条件改为差值>=15时标记新组即可。
• 性能表现：该实现全程为单遍扫描计算，ClickHouse对窗口函数执行有专门的优化，即使面对千万级以上的数据集也能保持较好的查询效率。

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