clickhouse中查询某值每次变化的时间

Question

问题遇到的现象和发生背景

clickhouse有一fan表 一秒一条数据 表中有存入时间 DS值(int类型变化范围1-6)
求DS每次变化的时间

问题相关代码，请勿粘贴截图

我的解答思路和尝试过的方法

查询所有的DS信息 在程序中for循环记录DS值的变化时间
由于表中数据量很大(千万级数据)
接口又是轮巡调用 会很慢

我想要达到的结果

有没有一种方法可以筛选DS字段每次变化的时间(跳过DS字段相同值的数据)
表数据
id | -- creat_time -- | DS |
1 │ 2022-04-05 07:02:01 │ 1
1 │ 2022-04-05 07:02:02 │ 4
1 │ 2022-04-05 07:02:03 │ 1
1 │ 2022-04-05 07:02:04 │ 1
1 │ 2022-04-05 07:02:05 │ 2
1 │ 2022-04-05 07:02:06 │ 2
1 │ 2022-04-05 07:02:07 │ 1
1 │ 2022-04-05 07:02:08 │ 1
1 │ 2022-04-05 07:02:09 │ 4
1 │ 2022-04-05 07:02:10 │ 4
1 │ 2022-04-05 07:02:11 │ 4
1 │ 2022-04-05 07:02:12 │ 4

查询后结果
id | -- creat_time -- | DS |
1 │ 2022-04-05 07:02:01 │ 1
1 │ 2022-04-05 07:02:02 │ 4
1 │ 2022-04-05 07:02:03 │ 1
1 │ 2022-04-05 07:02:05 │ 2
1 │ 2022-04-05 07:02:07 │ 1
1 │ 2022-04-05 07:02:09 │ 4

Answer 1

clickehouse支持窗口函数，可以使用lag配合sum来获取变化的位置

---

大概像下面这样，我这里没数据环境不好测试。

select fanId,min(create_time) create_time from (
select t.*,
sum(lag) over (PARTITION BY fanId ORDER BY create_time) s from 
(select id ,create_time, DS ,fanId,
   case when any(DS) OVER (PARTITION BY fanId ORDER BY create_time  ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = DS 
   then 0 else 1 end lag 
from fan) t) t2
group by fanId,s

原理就是上一行和当前行相等时计为0，不等时计为1，然后使用滑动窗口求和，就能在每次数据变化的时候加1，这样就能把数据按顺序切分开了，再按这个字段group by，就可以取每组最大和最小的时间