ClickHouse/ClickHouse

windowFunnel `strict_increase` behaves incorrectly if there is chain with events with the repeating subchain

Aperta

#52.844 aperta il 31 lug 2023

 (3 commenti) (0 reazioni) (0 assegnatari)C++ (8400 fork)batch import
bughelp wanted

Metriche repository

Star
 (47.419 stelle)
Metriche merge PR
 (Merge medio 2g 2h) (1000 PR mergiate in 30 g)

Descrizione

Does it reproduce on recent release?

Yes, 26.2

How to reproduce https://fiddle.clickhouse.com/db111d9b-14d0-41bc-bf56-310b6f1fa0ec

SELECT
    recording_id,
    windowFunnel(100000, 'strict_increase')(time, event_type = 'page_visit0', event_type = 'page_visit1', event_type = 'page_visit2', event_type = 'page_visit3') AS level
FROM
(
    SELECT
        number AS recording_id,
        event_type,
        time
    FROM numbers(1)
    ARRAY JOIN
        ['page_visit0', 'page_visit1', 'page_visit2', 'page_visit3', 'page_visit1', 'page_visit2', 'page_visit3', 'page_visit1', 'page_visit2', 'page_visit3'] AS event_type,
        [1, 2, 2, 2, 3, 3, 3, 4, 4, 4] AS time
)
GROUP BY recording_id

Result:

┌─recording_id─┬─level─┐
│            0 │     2 │
└──────────────┴───────┘

Expected:

┌─recording_id─┬─level─┐
│            0 │     4 │
└──────────────┴───────┘

ClickHouse should return 4, because it can take this chain: page_visit0 at 1, page_visit1 at 2, page_visit2 at 3, page_visit3 at 4

Additional context

Basically here:

https://github.com/ClickHouse/ClickHouse/blob/15c77afd8e2389740be5cf5120d54d11b8e4b8c2/src/AggregateFunctions/AggregateFunctionWindowFunnel.h#L193-L205

We iterate through matched event_id, (which is position of cond in windowFunnel(ts, 0, 1, 2, 3) function) And check if previous cond (event_idx - 1) have been matched earlier, than we replace latest match of current event_id with new time.

                    events_timestamp[event_idx] = std::make_pair(first_timestamp, timestamp);

Which seems incorrect at least for strict_increase modifier

May be something like:

            else if (events_timestamp[event_idx - 1].has_value() && !events_timestamp[event_idx].has_value())

Should be here instead (do not update current event_idx if it already exists)

Guida contributor