windowFunnel - Hologres: Central de Documentação do Alibaba Cloud

Limitações

Somente o Hologres V0.9 ou superior oferece suporte à função windowFunnel.

Pré-requisitos

Antes de começar, verifique se você tem:

Uma instância do Hologres com a versão V0.9 ou superior
Acesso de superusuário ao banco de dados de destino

Como superusuário, instale a extensão flow_analysis antes de chamar qualquer função de funil:

CREATE extension flow_analysis; --Install the extension.

A instalação da extensão ocorre no nível do banco de dados. Instale-a apenas uma vez por banco de dados.

Por padrão, a extensão é carregada no schema public e não pode ser movida para outro schema. Para chamar a função a partir de um schema diferente, use o nome totalmente qualificado, como public.windowFunnel.

Funcionamento

A função windowFunnel processa os dados de eventos da seguinte forma:

Ao encontrar a primeira condição da cadeia, a função define o contador de eventos como 1 e inicia a janela de tempo deslizante.
Cada condição subsequente que corresponder à ordem esperada incrementa o contador. Se a sequência for interrompida, a contagem para.

Valor de retorno: Um número inteiro que representa a quantidade máxima de condições consecutivas correspondentes na cadeia dentro da janela de tempo deslizante.

Considerando uma janela de tempo longa o suficiente para conter todos os eventos:

Eventos do usuário	Condições especificadas	Valor de retorno
c1, c2, c3, c4	c1, c2, c3	3
c4, c3, c2, c1	c1, c2, c3	1
c4, c3	c1, c2, c3	0

Sintaxe

windowFunnel(window, mode, timestamp, cond1, cond2, ..., condN)

Parâmetros:

Parâmetro	Descrição
`window`	Duração da janela de tempo. A função windowFunnel usa o momento da primeira ocorrência do evento correspondente como ponto inicial e extrai os dados dos eventos subsequentes com base nessa duração.
`mode`	Modo de correspondência. Valores aceitos: `default` (padrão) e `strict`. Consulte Modos de correspondência para obter detalhes.
`timestamp`	Coluna que registra os horários dos eventos. Tipos de dados suportados: TIMESTAMP, INT e BIGINT.
`cond`	Condição booleana que representa uma etapa do funil. Especifique as condições na ordem esperada de ocorrência dos eventos.

Modos de correspondência

Ambos os modos usam a mesma lógica de cadeia de eventos. Considere as condições A→B→C e os seguintes eventos do usuário:

Modo default — corresponde ao maior número possível de eventos, começando pelo primeiro evento dentro da janela de tempo especificada.

Eventos do usuário	Resultado
A, B, C	3 — todas as três condições correspondidas
A, B, A, C	3 — o A repetido é ignorado; a correspondência continua até C

Modo strict — interrompe a correspondência ao encontrar um evento repetido.

Eventos do usuário	Resultado
A, B, C	3 — todas as três condições correspondidas
A, B, A, C	2 — a repetição de A interrompe a correspondência; apenas A e B são contados

Exemplos

Os exemplos a seguir usam o conjunto de dados de eventos públicos do GitHub. Para importar esse conjunto de dados para sua instância do Hologres, consulte Importar conjuntos de dados públicos com poucos cliques.

Schema da tabela do conjunto de dados:

BEGIN;
CREATE TABLE hologres_dataset_github_event.hologres_github_event (
    id BIGINT,
    actor_id BIGINT,
    actor_login TEXT,
    repo_id BIGINT,
    repo_name TEXT,
    org_id BIGINT,
    org_login TEXT,
    type TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    action TEXT,
    iss_or_pr_id BIGINT,
    number BIGINT,
    comment_id BIGINT,
    commit_id TEXT,
    member_id BIGINT,
    rev_or_push_or_rel_id BIGINT,
    ref TEXT,
    ref_type TEXT,
    state TEXT,
    author_association TEXT,
    language TEXT,
    merged BOOLEAN,
    merged_at TIMESTAMP WITH TIME ZONE,
    additions BIGINT,
    deletions BIGINT,
    changed_files BIGINT,
    push_size BIGINT,
    push_distinct_size BIGINT,
    hr TEXT,
    month TEXT,
    year TEXT,
    ds TEXT
);

CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'orientation', 'column');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'bitmap_columns', 'actor_login,repo_name,org_login,type,action,commit_id,ref,ref_type,state,author_association,language,hr,month,year,ds');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'clustering_key', 'created_at:asc');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'dictionary_encoding_columns', 'actor_login:auto,repo_name:auto,org_login:auto,type:auto,action:auto,commit_id:auto,ref:auto,ref_type:auto,state:auto,author_association:auto,language:auto,hr:auto,month:auto,year:auto,ds:auto');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'distribution_key', 'id');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'segment_key', 'created_at');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'time_to_live_in_seconds', '3153600000');

COMMENT ON TABLE hologres_dataset_github_event.hologres_github_event IS NULL;
END;

Calcular níveis de funil por usuário

A consulta a seguir identifica o progresso de cada usuário no caminho de conversão CreateEvent → PushEvent → IssuesEvent dentro de uma janela de 30 minutos, durante um período de três dias.

--Calculate the funnel data of each user.
SELECT
    actor_id,
    windowFunnel (1800, 'default', created_at,  type = 'CreateEvent',type = 'PushEvent',type = 'IssuesEvent') AS level
FROM
    hologres_dataset_github_event.hologres_github_event
WHERE
    created_at >= TIMESTAMP '2024-01-28 10:00:00+08'
    AND created_at < TIMESTAMP '2024-01-31 10:00:00+08'
GROUP BY
    actor_id;

A coluna level no resultado indica quantos eventos do funil cada usuário concluiu:

Nível	Significado
0	Não concluiu o primeiro evento (CreateEvent)
1	Concluiu apenas o CreateEvent
2	Concluiu CreateEvent e PushEvent
3	Concluiu todos os três eventos

Exemplo de saída:

 actor_id   | level
------------+------
143037332   | 0
38708562    | 0
157624788   | 1
137850795   | 1
69616418    | 2
158019532   | 2
727125      | 3

Agregar usuários por estágio do funil

Para contar quantos usuários alcançaram cada estágio, use uma soma cumulativa sobre a distribuição de níveis:

WITH level_detail AS (
    SELECT
        level,
        COUNT(1) AS count_user
    FROM (
        SELECT
            actor_id,
            windowFunnel (1800, 'default', created_at, type = 'CreateEvent', type = 'PushEvent',type = 'IssuesEvent') AS level
        FROM
            hologres_dataset_github_event.hologres_github_event
        WHERE
            created_at >= TIMESTAMP '2024-01-28 10:00:00+08'
            AND created_at < TIMESTAMP '2024-01-31 10:00:00+08'
        GROUP BY
            actor_id) AS basic_table
    GROUP BY
        level
    ORDER BY
        level ASC
)
SELECT  CASE level    WHEN 0 THEN 'total'
                      WHEN 1 THEN 'CreateEvent'
                      WHEN 2 THEN 'PushEvent'
                      WHEN 3 THEN 'IssuesEvent'
                      END AS type
        ,SUM(count_user) over ( ORDER BY level DESC )
FROM
    level_detail
GROUP BY
    level,
    count_user
ORDER BY
    level ASC;

Resultado:

  type        |  sum
--------------+--------
total         | 1338166
CreateEvent   | 461088
PushEvent     | 202221
IssuesEvent   | 4727

A linha total representa a contagem cumulativa de todos os usuários analisados. Cada linha subsequente mostra quantos usuários alcançaram pelo menos aquele estágio no funil.