WINDOW_FUNNEL (Lakehouse v1)

Podobnie jak windowFunnel w ClickHouse (obie funkcje stworzył ten sam autor), wyszukuje łańcuchy zdarzeń w przesuwanym oknie czasowym i oblicza maksymalną liczbę zdarzeń z łańcucha.

Funkcja działa według następującego algorytmu:

Funkcja wyszukuje dane, które spełniają pierwszy warunek w łańcuchu, i ustawia licznik zdarzeń na 1. To moment rozpoczęcia przesuwanego okna.
Jeśli zdarzenia z łańcucha występują kolejno w obrębie okna, licznik jest zwiększany. Jeśli sekwencja zdarzeń zostanie przerwana, licznik nie jest zwiększany.
Jeśli dane zawierają wiele łańcuchów zdarzeń w różnych punktach ukończenia, funkcja zwraca tylko rozmiar najdłuższego łańcucha.

Składnia SQL

WINDOW_FUNNEL( <window> )( <timestamp>, <cond1>, <cond2>, ..., <condN> )

Argumenty

<timestamp> — nazwa kolumny zawierającej znacznik czasu. Obsługiwane typy danych: typy całkowite i typy daty i czasu.
<cond> — warunki lub dane opisujące łańcuch zdarzeń. Muszą mieć typ danych Boolean.

Parametry

<window> — długość przesuwanego okna, czyli odstęp czasu między pierwszym a ostatnim warunkiem. Jednostka window zależy od samego timestamp i jest zmienna. Określa ją wyrażenie timestamp of cond1 <= timestamp of cond2 <= ... <= timestamp of condN <= timestamp of cond1 + window.

Zwracana wartość

Maksymalna liczba kolejnych spełnionych warunków z łańcucha w obrębie przesuwanego okna czasowego. Analizowane są wszystkie łańcuchy w wybranych danych.

Typ: UInt8.

Przykład

Ustal, czy określony przedział czasu wystarcza, aby użytkownik wybrał telefon (SELECT) i dwukrotnie go kupił w sklepie internetowym.

Ustaw następujący łańcuch zdarzeń:

Użytkownik zalogował się na swoje konto w sklepie (event_name = 'login').
Użytkownik wszedł na stronę (event_name = 'visit').
Użytkownik dodał produkt do koszyka (event_name = 'cart').
Użytkownik sfinalizował zakup (event_name = 'purchase').

CREATE TABLE events(user_id BIGINT, event_name VARCHAR, event_timestamp TIMESTAMP);

INSERT INTO events VALUES(100123, 'login', '2022-05-14 10:01:00');
INSERT INTO events VALUES(100123, 'visit', '2022-05-14 10:02:00');
INSERT INTO events VALUES(100123, 'cart', '2022-05-14 10:04:00');
INSERT INTO events VALUES(100123, 'purchase', '2022-05-14 10:10:00');

INSERT INTO events VALUES(100125, 'login', '2022-05-15 11:00:00');
INSERT INTO events VALUES(100125, 'visit', '2022-05-15 11:01:00');
INSERT INTO events VALUES(100125, 'cart', '2022-05-15 11:02:00');

INSERT INTO events VALUES(100126, 'login', '2022-05-15 12:00:00');
INSERT INTO events VALUES(100126, 'visit', '2022-05-15 12:01:00');

Tabela wejściowa:

┌─────────┬────────────┬────────────────────────────┐
│ user_id │ event_name │ event_timestamp            │
├─────────┼────────────┼────────────────────────────┤
│  100123 │ login      │ 2022-05-14 10:01:00.000000 │
│  100123 │ visit      │ 2022-05-14 10:02:00.000000 │
│  100123 │ cart       │ 2022-05-14 10:04:00.000000 │
│  100123 │ purchase   │ 2022-05-14 10:10:00.000000 │
│  100125 │ login      │ 2022-05-15 11:00:00.000000 │
│  100125 │ visit      │ 2022-05-15 11:01:00.000000 │
│  100125 │ cart       │ 2022-05-15 11:02:00.000000 │
│  100126 │ login      │ 2022-05-15 12:00:00.000000 │
│  100126 │ visit      │ 2022-05-15 12:01:00.000000 │
└─────────┴────────────┴────────────────────────────┘

Sprawdź, jak daleko w łańcuchu dotarł użytkownik user_id przy oknie przesuwanym o godzinę.

Zapytanie:

SELECT
    level,
    count() AS count
FROM
(
    SELECT
        user_id,
        window_funnel(3600000000)(event_timestamp, event_name = 'login', event_name = 'visit', event_name = 'cart', event_name = 'purchase') AS level
    FROM events
    GROUP BY user_id
)
GROUP BY level ORDER BY level ASC;

Wynik:

┌───────┬───────┐
│ level │ count │
├───────┼───────┤
│     2 │     1 │
│     3 │     1 │
│     4 │     1 │
└───────┴───────┘

Użytkownik 100126 ma poziom 2 (login -> visit) .
Użytkownik 100125 ma poziom 3 (login -> visit -> cart).
Użytkownik 100123 ma poziom 4 (login -> visit -> cart -> purchase).