WINDOW_FUNNEL (Lakehouse v1)

Ähnlich wie windowFunnel in ClickHouse (beide stammen vom selben Autor) sucht die Funktion in einem gleitenden Zeitfenster nach Ereignisketten und berechnet die maximale Anzahl von Ereignissen aus der Kette.

Die Funktion arbeitet nach folgendem Algorithmus:

• Die Funktion sucht nach Daten, die die erste Bedingung der Kette auslösen, und setzt den Ereigniszähler auf 1. Dies ist der Zeitpunkt, an dem das gleitende Fenster beginnt.

• Treten Ereignisse aus der Kette innerhalb des Fensters nacheinander auf, wird der Zähler erhöht. Wird die Ereignisfolge unterbrochen, wird der Zähler nicht erhöht.

• Enthalten die Daten mehrere Ereignisketten mit unterschiedlichen Abschlussstufen, gibt die Funktion nur die Länge der längsten Kette aus.

SQL-Syntax

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

Argumente

• <timestamp> – Name der Spalte, die den Zeitstempel enthält. Unterstützte Datentypen: Ganzzahltypen und Datums-/Zeittypen.
• <cond> – Bedingungen oder Daten, die die Ereigniskette beschreiben. Muss den Datentyp Boolean haben.

Parameter

• <window> – Länge des gleitenden Fensters, also das Zeitintervall zwischen der ersten und der letzten Bedingung. Die Einheit von window hängt vom timestamp selbst ab und variiert. Bestimmt wird sie mit dem Ausdruck timestamp of cond1 <= timestamp of cond2 <= ... <= timestamp of condN <= timestamp of cond1 + window.

Rückgabewert

Die maximale Anzahl aufeinanderfolgend ausgelöster Bedingungen aus der Kette innerhalb des gleitenden Zeitfensters. Alle Ketten in der Auswahl werden analysiert.

Typ: UInt8.

Beispiel

Ermitteln Sie, ob ein festgelegter Zeitraum ausreicht, damit ein Benutzer im Onlineshop ein Telefon auswählt und es zweimal kauft.

Legen Sie die folgende Ereigniskette fest:

1. Der Benutzer hat sich im Shop bei seinem Konto angemeldet (event_name = 'login').
2. Der Benutzer ruft die Seite auf (event_name = 'visit').
3. Der Benutzer legt den Artikel in den Warenkorb (event_name = 'cart').
4. Der Benutzer schließt den Kauf ab (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');

Eingabetabelle:

┌─────────┬────────────┬────────────────────────────┐
│ 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 │
└─────────┴────────────┴────────────────────────────┘

Ermitteln Sie, wie weit der Benutzer user_id bei einem gleitenden Fenster von einer Stunde in der Kette kommen konnte.

Abfrage:

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;

Hinweis

Der Typ von event_timestamp ist timestamp; 3600000000 entspricht einem Zeitfenster von einer Stunde.

Ergebnis:

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

• Die Stufe von Benutzer 100126 ist 2 (login -> visit) .
• Die Stufe von Benutzer 100125 ist 3 (login -> visit -> cart).
• Die Stufe von Benutzer 100123 ist 4 (login -> visit -> cart -> purchase).