Hologres:range_funnel

range_funnel

Sintaxe

range_funnel(window, event_size, range_begin, range_end, interval, event_ts, event_bits[, use_interval_window[, mode]])

Parâmetros

Valor de retorno

A função range_funnel retorna um array BIGINT[]. Cada elemento é um inteiro codificado de 64 bits:

• Bits 8–63 (56 bits): o carimbo de data e hora de início do intervalo.

• Bits 0–7 (8 bits): a etapa do funil alcançada nesse intervalo.

Decodifique o array com range_funnel_time e range_funnel_level antes de interpretar os resultados. Uma entrada NULL na saída decodificada (\N) representa o agregado de todos os intervalos.

Funções de decodificação

Use UNNEST para expandir o array e, em seguida, envolva cada elemento com range_funnel_time ou range_funnel_level.

Sintaxe

range_funnel_time(range_funnel())
range_funnel_level(range_funnel())

As funções range_funnel_time e range_funnel_level requerem o Hologres V2.1.6 ou posterior.

Analisar conversões diárias ao longo de um período de vários dias

Este exemplo usa o public GitHub event dataset para rastrear quantos usuários progrediram de CreateEvent para PushEvent a cada dia durante um período de três dias.

Parâmetros de análise:

• Janela de tempo: 1 hora (3.600 segundos)

• Período de análise: 2024-01-29 a 2024-01-31 (3 dias)

• Caminho de conversão: CreateEvent → PushEvent

• Intervalo de agrupamento: 1 dia (86.400 segundos)

A coluna type contém valores de texto, mas event_bits requer um bitmap de 32 bits. Use bit_construct para converter o campo de texto em um bitmap.

Etapa 1: Execute a consulta de funil (saída codificada)

SELECT
    actor_id,
    range_funnel(3600, 2, '2024-01-29', '2024-01-31', 86400, created_at::TIMESTAMP, bits) AS result
FROM (
    SELECT
        actor_id,
        created_at::TIMESTAMP,
        type,
        bit_construct(a := type = 'CreateEvent', b := type = 'PushEvent') AS bits
    FROM hologres_dataset_github_event.hologres_github_event
    WHERE ds >= '2024-01-29' AND ds <= '2024-01-31'
) tt
GROUP BY actor_id
ORDER BY actor_id;

Exemplo de saída (codificada):

actor_id  | result
----------+------------------------------------------------------------
17        | {436860518400,436882636800,9223372036854775552}
47        | {436860518400,436882636800,9223372036854775552}
235       | {436860518401,436882636800,9223372036854775553}

Um result vazio significa que os eventos do usuário não corresponderam ao funil em nenhum intervalo.

Etapa 2: Decodifique os resultados por usuário e dia

SELECT actor_id,
    TO_TIMESTAMP(range_funnel_time(result)) AS res_time,  -- Interval start time
    range_funnel_level(result) AS res_level               -- Funnel step reached
FROM (
    SELECT actor_id, result, COUNT(1) AS cnt FROM (
        SELECT actor_id,
            UNNEST(range_funnel(3600, 2, '2024-01-29', '2024-01-31', 86400, created_at::TIMESTAMP, bits)) AS result
        FROM (
            SELECT actor_id, created_at::TIMESTAMP, type,
                bit_construct(a := type = 'CreateEvent', b := type = 'PushEvent') AS bits
            FROM hologres_dataset_github_event.hologres_github_event
            WHERE ds >= '2024-01-29' AND ds <= '2024-01-31'
        ) a
        GROUP BY actor_id
    ) a
    GROUP BY actor_id, result
) a
ORDER BY actor_id, res_time
LIMIT 10000;

Exemplo de saída:

actor_id  | res_time              | res_level
----------+-----------------------+-----------
17        | 2024-01-29 08:00:00+08 | 0
17        | 2024-01-30 08:00:00+08 | 0
75        | 2024-01-29 08:00:00+08 | 2
75        | \N                    | 2
76        | 2024-01-29 08:00:00+08 | 0
76        | 2024-01-30 08:00:00+08 | 1
141       | 2024-01-29 08:00:00+08 | 2
141       | \N                    | 2
211       | 2024-01-30 08:00:00+08 | 1
235       | 2024-01-30 08:00:00+08 | 0
235       | \N                    | 1

Leitura dos resultados:

• res_level = 0: o usuário não acionou nenhum evento correspondente naquele dia.

• res_level = 1: o usuário concluiu a etapa 1 (CreateEvent), mas não a etapa 2.

• res_level = 2: o usuário concluiu ambas as etapas dentro da janela de 1 hora.

• res_time = \N: o resultado agregado de todo o período de análise (e não de um único dia).

Interpretação linha por linha:

• actor 17: alcançou o nível 0 em 29 e 30 de janeiro: nenhum evento correspondente em nenhum dos dias.

• actor 75: alcançou o nível 2 em 29 de janeiro (concluiu o caminho completo CreateEvent → PushEvent em 1 hora), conforme confirmado pelo agregado \N, que também mostra o nível 2.

• actor 76: alcançou o nível 0 em 29 de janeiro (sem correspondência) e o nível 1 em 30 de janeiro (CreateEvent ocorreu, mas PushEvent não ocorreu em seguida dentro de 1 hora).

• actor 235: alcançou o nível 0 em 30 de janeiro e o nível 1 no agregado geral (\N), o que significa que um CreateEvent foi correspondido ao longo do período, mas o PushEvent nunca ocorreu em seguida dentro da janela.

Etapa 3: Resuma as contagens diárias de etapas

Esta consulta consolida os resultados por usuário em um resumo diário de etapas. Cada valor de res_cnt é cumulativo: o nível N inclui todos os usuários que alcançaram pelo menos N etapas, pois cada nível superior é um subconjunto do anterior.

SELECT res_time, res_level, SUM(cnt) OVER (PARTITION BY res_time ORDER BY res_level DESC) AS res_cnt
FROM (
    SELECT
        TO_TIMESTAMP(range_funnel_time(result)) AS res_time,  -- Interval start time
        range_funnel_level(result) AS res_level,              -- Funnel step reached
        cnt
    FROM (
        SELECT result, COUNT(1) AS cnt FROM (
            SELECT actor_id,
                UNNEST(range_funnel(3600, 2, '2024-01-28', '2024-01-31', 86400, created_at::TIMESTAMP, bits)) AS result
            FROM (
                SELECT actor_id, created_at::TIMESTAMP, type,
                    BIT_CONSTRUCT(a := type = 'CreateEvent', b := type = 'PushEvent') AS bits
                FROM hologres_dataset_github_event.hologres_github_event
                WHERE ds >= '2024-01-28' AND ds <= '2024-01-30'
            ) a
            GROUP BY actor_id
        ) a
        GROUP BY result
    ) a
) a
WHERE res_level > 0
GROUP BY res_time, res_level, cnt
ORDER BY res_time, res_level;

Exemplo de saída:

res_time               | res_level | res_cnt
-----------------------+-----------+---------
2024-01-28 08:00:00+08 | 1         | 131212
2024-01-28 08:00:00+08 | 2         | 62371
2024-01-29 08:00:00+08 | 1         | 172505
2024-01-29 08:00:00+08 | 2         | 79667
2024-01-30 08:00:00+08 | 1         | 198585
2024-01-30 08:00:00+08 | 2         | 90291
\N                     | 1         | 440332
\N                     | 2         | 208942

Em 2024-01-28, 131.212 usuários alcançaram pelo menos a etapa 1 (CreateEvent), e 62.371 deles também concluíram a etapa 2 (PushEvent) dentro da janela de 1 hora. A linha \N mostra o agregado de três dias: 440.332 usuários alcançaram a etapa 1 e 208.942 concluíram o caminho completo.

Contar eventos diferentes simultâneos como conversões separadas (mode='1')

Por padrão (mode='0'), se dois eventos diferentes ocorrerem no mesmo carimbo de data e hora, apenas um conta. defina mode='1' para contar cada evento simultâneo distinto como uma conversão separada.

crie uma tabela de teste e insira dados de exemplo:

CREATE TABLE funnel_test (
    uid INT,
    event TEXT,
    create_time TIMESTAMPTZ
);

INSERT INTO funnel_test VALUES
    (11, 'login', '2024-09-26 16:15:28+08'),
    (11, 'watch', '2024-09-26 16:15:28+08'),  -- same timestamp as login
    (11, 'buy',   '2024-09-26 16:16:28+08'),
    (22, 'login', '2024-09-26 16:15:28+08'),
    (22, 'watch', '2024-09-26 16:16:28+08'),
    (22, 'buy',   '2024-09-26 16:17:28+08');

Para o uid 11, login e watch ocorrem no mesmo carimbo de data e hora. Com mode='1', ambos os eventos contam para a cadeia do funil.

SELECT res_time, res_level, SUM(cnt) OVER (PARTITION BY res_time ORDER BY res_level DESC) AS res_cnt
FROM (
    SELECT
        TO_TIMESTAMP(range_funnel_time(result)) AS res_time,
        range_funnel_level(result) AS res_level,
        cnt
    FROM (
        SELECT result, COUNT(1) AS cnt FROM (
            SELECT uid,
                UNNEST(range_funnel(3600, 3, '2024-09-26', '2024-09-27', 86400, create_time::TIMESTAMP, bits, false, '1')) AS result
            FROM (
                SELECT uid, create_time::TIMESTAMP, event,
                    BIT_CONSTRUCT(a := event = 'login', b := event = 'watch', c := event = 'buy') AS bits
                FROM funnel_test
            ) a
            GROUP BY uid
        ) a
        GROUP BY result
    ) a
) a
GROUP BY res_time, res_level, cnt
ORDER BY res_time, res_level;

Saída:

res_time               | res_level | res_cnt
-----------------------+-----------+---------
2024-09-26 08:00:00+08 |         3 |       2
                       |         3 |       2
(2 rows)

Ambos os usuários alcançaram o nível 3:

• uid 11: login e watch ocorrem no mesmo carimbo de data e hora. Com mode='1', ambos contam como conversões separadas: login corresponde à etapa 1 e watch corresponde à etapa 2 simultaneamente, e então buy corresponde à etapa 3. A cadeia completa login → watch → buy é correspondida.

• uid 22: os eventos ocorrem em carimbos de data e hora diferentes, portanto, a cadeia é correspondida normalmente, independentemente do modo.

Analisar conversões entre dias com uma janela de tempo de vários dias (use_interval_window=true)

Quando o caminho de conversão abrange vários dias, defina use_interval_window=true. O parâmetro window especifica então a quantidade de intervalos de agrupamento (dias corridos neste exemplo) a serem incluídos na janela deslizante.

crie uma tabela de teste e insira dados de exemplo:

CREATE TABLE funnel_test_2 (
    uid INT,
    event TEXT,
    create_time TIMESTAMPTZ
);

INSERT INTO funnel_test_2 VALUES
    (11, 'login', '2024-09-24 16:15:28+08'),
    (11, 'watch', '2024-09-25 16:15:28+08'),
    (11, 'buy',   '2024-09-26 16:16:28+08'),
    (22, 'login', '2024-09-24 16:15:28+08'),
    (22, 'watch', '2024-09-25 16:16:28+08'),
    (22, 'buy',   '2024-09-26 16:17:28+08');

Os eventos de ambos os usuários abrangem três dias corridos separados. defina window=3 com use_interval_window=true para capturar conversões que começam em um dia e são concluídas nos dois dias seguintes.

-- Time window: 3 calendar days
SELECT
    TO_TIMESTAMP(range_funnel_time(result)) AS res_time,
    range_funnel_level(result) AS res_level,
    cnt
FROM (
    SELECT result, COUNT(1) AS cnt FROM (
        SELECT uid,
            UNNEST(range_funnel(3, 3, '2024-09-24', '2024-09-27', 86400, create_time::TIMESTAMP, bits, true, '1')) AS result
        FROM (
            SELECT uid, create_time::TIMESTAMP, event,
                BIT_CONSTRUCT(a := event = 'login', b := event = 'watch', c := event = 'buy') AS bits
            FROM funnel_test_2
        ) a
        GROUP BY uid
    ) a
    GROUP BY result
) a;

Saída:

res_time               | res_level | cnt
-----------------------+-----------+-----
2024-09-26 08:00:00+08 |         0 |   2
                       |         3 |   2
2024-09-24 08:00:00+08 |         3 |   2
2024-09-25 08:00:00+08 |         0 |   2
(4 rows)

Interpretação linha por linha:

• 2024-09-24, nível 3: Ambos os usuários começaram com login em 24 de setembro. Com uma janela de 3 dias, a função verifica os dias 24, 25 e 26 de setembro, cobrindo watch (25 de setembro) e buy (26 de setembro). Todas as três etapas são correspondidas, então ambos os usuários alcançam o nível 3 a partir deste dia.

• 2024-09-25, nível 0: watch ocorre em 25 de setembro, mas login (etapa 1) não ocorreu em 25 de setembro, logo nenhuma cadeia começa aqui.

• 2024-09-26, nível 0: buy ocorre em 26 de setembro, mas novamente nenhuma cadeia começa neste dia porque login está ausente.

• \N, nível 3: O agregado geral confirma que ambos os usuários concluíram o funil completo ao longo do período.