Подсчёт количества переходов для value SQL clickhouse

Question

У меня есть следующая таблица. Мне нужно посчитать count для переходов one -> two , two -> one. Колонка Value содержит только два возможных значения: one / two . БД - 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

То есть в данном случае должно получиться

count(*)
4

Answer 1

ClickHouse не поддерживает LAG/LEAD, посему эмулируем через одно место:

SELECT SUM(CASE WHEN value = ANY(value) OVER (ORDER BY time 
                                              ROWS BETWEEN 1 PRECEDING 
                                                       AND 1 PRECEDING) 
                THEN 0
                ELSE 1 
                END) - 1
FROM tablename

Единицу вычитаем, потому что выражение считает и переход от несуществующей предначальной записи (для неё возвращается NULL) к самой первой.

Answer 2

Попробуйте использовать sequenceCount-функцию:

SELECT
    sequenceCount('(?1)(?2)')(time, value = 'one', value = 'two') AS one_two_count,
    sequenceCount('(?1)(?2)')(time, value = 'two', value = 'one') AS two_one_count,
    one_two_count + two_one_count AS result
FROM 
(
    SELECT
        toUInt64(toDateTime64(data.1, 3)) AS time,
        data.2 AS value
    FROM 
    ( /* emulate the test data set */
        SELECT arrayJoin([('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')]) AS data
    )
)

/*
┌─one_two_count─┬─two_one_count─┬─result─┐
│             2 │             2 │      4 │
└───────────────┴───────────────┴────────┘
*/

БЛОГ НА HUSL

Подсчёт количества переходов для value SQL clickhouse

Ответы (2 шт):