如何统计ClickHouse中双值列的状态切换次数

需求：数据表中value字段仅包含one和two两个值，需统计该字段的状态切换次数（即one→two或two→one的切换）。

示例数据：

time                        value
2022-09-19 04:03:06.845     one
2022-09-19 04:03:06.846     two
2022-09-19 04:03:08.055     two
2022-09-19 04:03:08.141     one
2022-09-19 04:03:08.160     one
2022-09-19 04:03:08.258     one
2022-09-19 04:03:08.757     two
2022-09-19 04:03:08.983     two
2022-09-19 04:03:12.051     two
2022-09-19 04:03:12.785     two
2022-09-19 04:03:12.860     one

预期统计结果：

count(*)
4

实现SQL #

使用LAG窗口函数获取按时间排序后的上一行value，对比当前行与上一行的值是否不同，统计不同的记录数量即可：

SELECT count(*) AS switch_count
FROM (
    SELECT 
        value,
        LAG(value) OVER (ORDER BY time) AS prev_value
    FROM gsegsdhfhdgfhgdfhdjhhjghjh
) t
WHERE value != prev_value

测试表创建语句 #

create table gsegsdhfhdgfhgdfhdjhhjghjh(time DateTime64,  value String) 
Engine = Memory as
select * from values (
('2022-09-19 04:03:06.845','one'),
('2022-09-19 04:03:06.846','two'),
('2022-09-19 04:03:08.055','two'),
('2022-09-19 04:03:08.141','one'),
('2022-09-19 04:03:08.160','one'),
('2022-09-19 04:03:08.258','one'),
('2022-09-19 04:03:08.757','two'),
('2022-09-19 04:03:08.983','two'),
('2022-09-19 04:03:12.051','two'),
('2022-09-19 04:03:12.785','two'),
('2022-09-19 04:03:12.860','one'))

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