SQL hot to ger a count of swaps for column with two possible values-CodePudding

I have next table, and i want get count of swaps (one -> two, two -> one) for column value. column "value" have only two possble values : one / two. DB is clickhouse

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

So in this case we should get 4 count of swaps

count(*)
4

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'))

CodePudding user response：

SELECT arrayCount(j -> (j != 0), arrayDifference(arraySort(i -> (i.1), groupArray((time, if(value = 'one', 1, 2)))).2)) AS res
FROM gsegsdhfhdgfhgdfhdjhhjghjh
┌─res─┐
│   4 │
└─────┘

SELECT countIf((value != prev) AND (prev != '')) AS res
FROM
(
    SELECT
        value,
        any(value) OVER (ORDER BY time ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev
    FROM gsegsdhfhdgfhgdfhdjhhjghjh
)
┌─res─┐
│   4 │
└─────┘

https://kb.altinity.com/altinity-kb-queries-and-syntax/lag-lead/