WINDOW_FUNNEL (Lakehouse v1)

Semelhante a windowFunnel no ClickHouse (ambas foram criadas pelo mesmo autor), procura cadeias de eventos em uma janela de tempo deslizante e calcula o número máximo de eventos da cadeia.

A função segue este algoritmo:

A função procura os dados que acionam a primeira condição da cadeia e define o contador de eventos como 1. Esse é o momento em que a janela deslizante começa.
Se os eventos da cadeia ocorrerem em sequência dentro da janela, o contador é incrementado. Se a sequência de eventos for interrompida, o contador não é incrementado.
Se os dados tiverem várias cadeias de eventos em diferentes pontos de conclusão, a função retorna apenas o tamanho da maior cadeia.

Sintaxe SQL

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

Argumentos

<timestamp> — nome da coluna que contém o timestamp. Tipos de dados com suporte: tipos inteiros e tipos de data e hora.
<cond> — condições ou dados que descrevem a cadeia de eventos. Deve ser do tipo de dados Boolean.

Parâmetros

<window> — tamanho da janela deslizante, que é o intervalo de tempo entre a primeira e a última condição. A unidade de window depende do próprio timestamp e varia. É determinada usando a expressão timestamp of cond1 <= timestamp of cond2 <= ... <= timestamp of condN <= timestamp of cond1 + window.

Valor retornado

O número máximo de condições consecutivas acionadas da cadeia dentro da janela de tempo deslizante. Todas as cadeias da seleção são analisadas.

Tipo: UInt8.

Exemplo

Determine se um período de tempo definido é suficiente para o usuário selecionar (SELECT) um telefone e comprá-lo duas vezes na loja online.

Defina a seguinte cadeia de eventos:

O usuário fez login na própria conta na loja (event_name = 'login').
O usuário acessou a página (event_name = 'visit').
O usuário adicionou ao carrinho de compras (event_name = 'cart').
O usuário concluiu a compra (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 de entrada:

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

Descubra até onde o usuário user_id conseguiu avançar na cadeia em uma janela deslizante de uma hora.

Consulta:

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;

Resultado:

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

O nível do usuário 100126 é 2 (login -> visit) .
O nível do usuário 100125 é 3 (login -> visit -> cart).
O nível do usuário 100123 é 4 (login -> visit -> cart -> purchase).