Clickhouse中基于时间序列按字段变更分组并聚合时长的解决方案咨询

当然有可行的方案！这是典型的**连续相同分组（岛屿问题）**场景，我们可以通过ClickHouse的窗口函数轻松实现你的需求，核心思路是先给连续相同的f_user+f_tile组合打上分组标识，再对每个分组计算时间跨度。

具体SQL实现 #

假设你的表名为user_tile_logs，可以用以下查询得到目标结果：

WITH grouped_data AS (
    SELECT 
        f_datetime,
        f_user,
        f_tile,
        -- 生成连续分组的标识：当当前行的f_tile与上一行不同时，分组ID递增
        sum(CASE WHEN f_tile = lag(f_tile) OVER (PARTITION BY f_user ORDER BY f_datetime) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY f_user ORDER BY f_datetime) AS continuous_group_id
    FROM user_tile_logs
)
SELECT 
    f_user,
    f_tile,
    -- 计算分组的持续时长，转换为分钟
    toUInt32(dateDiff('minute', min(f_datetime), max(f_datetime))) AS f_duration
FROM grouped_data
GROUP BY f_user, f_tile, continuous_group_id
ORDER BY min(f_datetime);

逻辑详解 #

1. 生成连续分组标识

   • 用lag(f_tile) OVER (PARTITION BY f_user ORDER BY f_datetime)获取当前用户上一条记录的f_tile值
   • 通过CASE判断当前行与上一行的f_tile是否一致：不一致则标记为1，一致则标记为0
   • 用sum() OVER (...)累加标记值，这样连续相同的f_tile会被分配到同一个continuous_group_id中

2. 聚合计算时长

   • 按f_user、f_tile和continuous_group_id分组，确保只聚合连续相同的组
   • 用dateDiff('minute', min(f_datetime), max(f_datetime))计算每组的时间跨度，转换为分钟数

验证你的示例数据 #

代入你提供的测试数据后，查询会输出：

完全符合你预期的结果。

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