finder_group_funnel

Pré-requisitos

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

Hologres V2.2.32 ou posterior
Acesso de superusuário ao banco de dados de destino

Instale a extensão

Execute a seguinte instrução como superusuário para instalar a extensão flow_analysis:

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 carrega no schema public e não pode ser carregada em outros schemas. Para chamar funções de um schema diferente, use o formato public.function_name — por exemplo, public.windowFunnel.

Como funciona

A função finder_group_funnel processa eventos em três estágios:

Atribuição de slot — Para cada linha de evento, server_timestamp determina a qual slot de etapa (dia, hora ou outro intervalo) o evento pertence.
Ordenação — Dentro de cada slot, os eventos são ordenados por client_timestamp para estabelecer a sequência.
Agrupamento e rastreamento — A partir de group_event_index, os usuários são atribuídos a um grupo com base em seu valor de group_dimension. Em seguida, a função rastreia o progresso de cada usuário na lista ordenada de check_event dentro da duração da window.

O resultado é um valor codificado em BINARY por usuário e por grupo. Encaminhe a saída por finder_group_funnel_res e finder_group_funnel_text_group para ler o progresso do funil e os rótulos dos grupos.

Agrupa eventos pela dimensão especificada e calcula os resultados do funil por usuário.

Sintaxe

finder_group_funnel(
  window,
  start_timestamp,
  step_interval,
  step_numbers,
  num_events,
  attr_related,
  group_event_index,
  time_zone,
  is_relative_window,
  server_timestamp,
  client_timestamp,
  group_dimension,
  [prop1, prop2, ...],
  check_event1, check_event2, ...
)

Parâmetros

Parâmetro	Obrigatório	Tipo	Descrição
`window`	Sim	—	Tamanho da janela de análise. Unidade: milissegundos.
`start_timestamp`	Sim	TIMESTAMP / TIMESTAMPTZ	Hora de início da análise.
`step_interval`	Sim	—	Duração de uma etapa (granularidade da análise de conversão). Unidade: segundos. Por exemplo, `86400` = um dia.
`step_numbers`	Sim	—	Número de etapas a analisar. Por exemplo, `step_interval=86400` e `step_numbers=7` significa que a função analisa dados diários de funil por 7 dias a partir de `start_timestamp`.
`num_events`	Sim	—	Número total de eventos a rastrear no funil.
`attr_related`	Sim	UINT8	Indica quais eventos possuem propriedades associadas. Em forma binária, se o `i`-ésimo bit for 1, o `(i+1)`-ésimo evento tem propriedades associadas. O número de expressões `prop` deve ser igual ao número de bits 1. Defina como `0` se nenhum evento tiver propriedades associadas.
`group_event_index`	Sim	—	Evento onde o agrupamento começa. Se definido como `1`, os usuários são agrupados assim que completam o primeiro evento. Se definido como `2`, o agrupamento inicia no segundo evento — usuários que não alcançam o segundo evento são atribuídos ao grupo `unreach`.
`time_zone`	Sim	TEXT	Fuso horário dos timestamps de entrada, no formato padrão (por exemplo, `Asia/Shanghai`). Este parâmetro afeta a saída apenas quando `is_relative_window` é `true`.
`is_relative_window`	Sim	BOOLEAN	Define se devem ser usados limites de dias civis em vez de uma janela de duração fixa. Padrão: `false`. Quando definido como `true`: `window` deve ser um múltiplo inteiro de 86.400.000 e `step_interval` deve ser `86400`. Consulte a seção Janela de dia civil abaixo para detalhes.
`server_timestamp`	Sim	TIMESTAMP / TIMESTAMPTZ	Hora do evento no lado do servidor. Usado para determinar a qual slot de etapa cada evento pertence.
`client_timestamp`	Sim	TIMESTAMP / TIMESTAMPTZ	Hora do evento no lado do cliente. Deve corresponder ao tipo de dados de `start_timestamp`. Usado para ordenar eventos.
`group_dimension`	Sim	TEXT	Campo pelo qual agrupar. Apenas campos TEXT são suportados. Para agrupar por vários campos, combine-os com `concat_ws` ou `concat`.
`prop`	Não	—	Propriedades associadas para eventos com bits `attr_related` definidos como 1. Todas as expressões `prop` devem ter o mesmo tipo de dados.
`check_event`	Sim	—	Lista ordenada de eventos de conversão. Eventos correspondentes dentro da duração da `window` contam como conversões válidas. Exemplo: `event = 'E0001', event = 'E0002', event = 'E0003'`.

Janela de dia civil

Quando is_relative_window=true, a janela usa limites de dias civis em vez de uma duração decorrida fixa:

Um dia civil vai de 00:00:00 a 23:59:59.
O primeiro dia abrange do momento do evento até 23:59:59 do mesmo dia.
Cada dia subsequente é um dia civil completo.

Utilize janelas de dia civil quando os períodos de relatório do funil precisarem se alinhar aos dias comerciais (meia-noite a meia-noite) em vez de intervalos contínuos de 24 horas a partir do primeiro evento.

Valor de retorno

Resultado codificado do tipo BINARY. Decodifique-o com finder_group_funnel_res e finder_group_funnel_text_group.

Exemplo

O exemplo a seguir agrupa os resultados do funil por província em uma janela de 3 dias. O usuário 1111 é de Pequim e o usuário 2222 é de Zhejiang.

Etapa 1: Crie a tabela de teste e insira dados.

CREATE TABLE finder_group_funnel_test(
  id INT,
  event_time TIMESTAMP,
  event TEXT,
  province TEXT,
  city TEXT
);

INSERT INTO finder_group_funnel_test VALUES
(1111, '2024-01-02 00:00:00', 'registration', 'Beijing',  'Beijing'),
(1111, '2024-01-02 00:00:01', 'logon',        'Beijing',  'Beijing'),
(1111, '2024-01-02 00:00:02', 'payment',      'Beijing',  'Beijing'),
(1111, '2024-01-02 00:00:03', 'exit',         'Beijing',  'Beijing'),

(1111, '2024-01-03 00:00:00', 'registration', 'Beijing',  'Beijing'),
(1111, '2024-01-03 00:00:01', 'logon',        'Beijing',  'Beijing'),
(1111, '2024-01-03 00:00:02', 'payment',      'Beijing',  'Beijing'),

(1111, '2024-01-04 00:00:00', 'registration', 'Beijing',  'Beijing'),
(1111, '2024-01-04 00:00:01', 'logon',        'Beijing',  'Beijing'),

(2222, '2024-01-02 00:00:00', 'registration', 'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:00', 'logon',        'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:01', 'payment',      'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:03', 'payment',      'Zhejiang', 'Hangzhou');

Etapa 2: Agrupe os resultados por província em uma janela de 3 dias.

SELECT
    id,
    UNNEST(
        finder_group_funnel(
            86400000 * 3,                                              -- 3-day window
            EXTRACT(epoch FROM TIMESTAMP '2024-01-02 00:00:00')::BIGINT,
            86400,                                                     -- step_interval: 1 day
            3,                                                         -- step_numbers: 3 days
            4,                                                         -- num_events: 4 events
            0,                                                         -- attr_related: no associated properties
            1,                                                         -- group_event_index: group on first event
            'Asia/Shanghai',
            FALSE,
            event_time,
            event_time,
            province,                                                  -- group_dimension
            event = 'registration',
            event = 'logon',
            event = 'payment',
            event = 'exit'
        )
    ) AS result
FROM finder_group_funnel_test
GROUP BY id;

O resultado é codificado em BINARY. Cada linha mostra o ID do usuário e seu rótulo de grupo (valor da província ou unreach):

  id  | result
------+---------
 2222 | Zhejiang
 2222 | unreach
 1111 | Beijing
 1111 | unreach
(4 rows)

Para decodificar o progresso detalhado do funil para cada grupo, use finder_group_funnel_res.

finder_group_funnel_res

Decodifica o resultado BINARY de finder_group_funnel em detalhes do funil por usuário para cada etapa.

Sintaxe

finder_group_funnel_res(finder_group_funnel(...))

Parâmetros

Parâmetro	Descrição
`finder_group_funnel()`	Resultado BINARY de `finder_group_funnel`.

Valor de retorno

Array de inteiros mostrando o evento mais avançado alcançado na janela geral e em cada intervalo de etapa.

Exemplo

Com a tabela do exemplo de finder_group_funnel, decodifique o progresso diário do funil de cada usuário:

SELECT
    id,
    finder_group_funnel_res(result) AS res
FROM (
    SELECT
        id,
        UNNEST(
            finder_group_funnel(
                86400000 * 3,
                EXTRACT(epoch FROM TIMESTAMP '2024-01-02 00:00:00')::BIGINT,
                86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE,
                event_time, event_time,
                province,
                event = 'register',
                event = 'logon',
                event = 'pay',
                event = 'exit'
            )
        ) AS result
    FROM finder_group_funnel_test
    GROUP BY id
) a;

Resultado:

  id  |    res
------+-----------
 1111 | {4,4,3,2}
 1111 | {0,0,0,0}
 2222 | {3,3,0,0}
 2222 | {0,0,0,0}

Leitura do array de resultados: Cada array possui 1 + step_numbers elementos. O primeiro elemento indica o evento mais avançado alcançado em toda a janela; os elementos restantes indicam o evento mais avançado alcançado dentro de cada intervalo de etapa (dia, neste caso).

Para o usuário 1111 com resultado {4,4,3,2}:

Posição	Valor	Significado
Geral (janela de 3 dias)	4	Alcançou o 4º evento (`exit`) dentro de 3 dias
Dia 1	4	Alcançou `exit` no dia 1
Dia 2	3	Alcançou o 3º evento (`payment`) no dia 2
Dia 3	2	Alcançou o 2º evento (`logon`) no dia 3

finder_group_funnel_text_group

Decodifica o rótulo do grupo (valor da dimensão ou unreach) do resultado BINARY. Use-o juntamente com finder_group_funnel_res para obter o nome do grupo e os dados do funil em uma única consulta.

Sintaxe

finder_group_funnel_text_group(finder_group_funnel(...))

Parâmetros

Parâmetro	Descrição
`finder_group_funnel()`	Resultado BINARY de `finder_group_funnel`.

Valor de retorno

Rótulo do grupo decodificado como texto — o valor da dimensão (por exemplo, Beijing) ou unreach.

Exemplo

A consulta a seguir decodifica o rótulo do grupo e o progresso diário do funil em uma única passagem:

SELECT
    id,
    finder_group_funnel_text_group(result) AS key,
    finder_group_funnel_res(result) AS res
FROM (
    SELECT
        id,
        UNNEST(
            finder_group_funnel(
                86400000 * 3,
                EXTRACT(epoch FROM TIMESTAMP '2024-01-02 00:00:00')::BIGINT,
                86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE,
                event_time, event_time,
                province,
                event = 'registration',
                event = 'logon',
                event = 'payment',
                event = 'exit'
            )
        ) AS result
    FROM finder_group_funnel_test
    GROUP BY id
) a;

Resultado:

  id  |   key    |    res
------+----------+-----------
 2222 | Zhejiang | {3,3,0,0}
 2222 | unreach  | {0,0,0,0}
 1111 | Beijing  | {4,4,3,2}
 1111 | unreach  | {0,0,0,0}
(4 rows)

funnel_rep

Agrega os resultados do funil por usuário (de finder_group_funnel_res) em contagens de conversão no nível do grupo em cada etapa do funil.

Sintaxe

funnel_rep(step_number, num_events, funnel_res)

Parâmetros

Parâmetro	Obrigatório	Tipo	Descrição
`step_number`	Sim	UINT	Número de slots de tempo. Geralmente igual a `step_numbers` em `finder_group_funnel`.
`num_events`	Sim	UINT	Total de eventos no funil. Geralmente igual ao número de expressões `check_event`.
`funnel_res`	Sim	—	Detalhes da etapa de conversão por usuário, gerados como saída de `finder_group_funnel` ou `FINDER_FUNNEL`.

Valor de retorno

Array de strings no formato {"overall","step1","step2",...}. Cada elemento é uma lista separada por vírgulas com as contagens de usuários que alcançaram os eventos de 1 a N:

O primeiro elemento é a contagem geral em toda a janela.
Cada elemento subsequente é a contagem dentro daquele intervalo de etapa.

Exemplo

Com os dados do exemplo de finder_group_funnel, calcule quantos usuários alcançaram cada evento em uma janela de 3 dias com etapa de 1 dia:

-- 3-day window, 1-day step: count users reaching each event per day.
SELECT
    funnel_rep(3, 4, funnel_res)
FROM (
    SELECT
        id,
        FINDER_FUNNEL(
            86400000 * 3,
            EXTRACT(epoch FROM TIMESTAMP '2024-01-02 00:00:00')::BIGINT,
            86400, 3, 4, 0, 'Asia/Shanghai', FALSE,
            event_time, event_time,
            event = 'registration',
            event = 'logon',
            event = 'payment',
            event = 'exit'
        ) AS funnel_res
    FROM finder_group_funnel_test
    GROUP BY id
) a;

Resultado:

                funnel_rep
-------------------------------------------
 {"2,2,2,1","2,2,2,1","1,1,1,0","1,1,0,0"}
(1 row)

Leitura deste resultado com step_numbers=3 e num_events=4:

Elemento	Valor	Significado
Geral	`2,2,2,1`	Em 3 dias: 2 usuários alcançaram o evento 1, 2 alcançaram o evento 2, 2 alcançaram o evento 3, 1 alcançou o evento 4
Dia 1	`2,2,2,1`	No dia 1: mesmo padrão
Dia 2	`1,1,1,0`	No dia 2: 1 usuário alcançou os eventos 1–3, nenhum alcançou o evento 4
Dia 3	`1,1,0,0`	No dia 3: 1 usuário alcançou os eventos 1–2, nenhum alcançou os eventos 3–4

Exemplos completos

Cenário 1: Agrupar resultados do funil por dimensão em uma janela de vários dias

Objetivo: Uma equipe de e-commerce deseja comparar como usuários de Pequim, Xangai e Zhejiang convertem em um fluxo de 4 etapas (registro → login → pagamento → saída) dentro de uma janela contínua de 3 dias, com detalhamentos diários. A equipe espera que os usuários de Pequim apresentem a maior taxa de conclusão em todos os dias.

Etapa 1: Crie a tabela de teste e insira dados.

CREATE TABLE finder_group_funnel_test_1(
  id INT,
  event_time TIMESTAMP,
  event TEXT,
  province TEXT,
  city TEXT
);

INSERT INTO finder_group_funnel_test_1 VALUES
(1111, '2024-01-02 00:00:00', 'registration', 'Beijing',  'Beijing'),
(1111, '2024-01-02 00:00:01', 'logon',        'Beijing',  'Beijing'),
(1111, '2024-01-02 00:00:02', 'payment',      'Beijing',  'Beijing'),
(1111, '2024-01-02 00:00:03', 'exit',         'Beijing',  'Beijing'),

(1111, '2024-01-03 00:00:00', 'registration', 'Beijing',  'Beijing'),
(1111, '2024-01-03 00:00:01', 'logon',        'Beijing',  'Beijing'),
(1111, '2024-01-03 00:00:02', 'payment',      'Beijing',  'Beijing'),

(1111, '2024-01-04 00:00:00', 'registration', 'Beijing',  'Beijing'),
(1111, '2024-01-04 00:00:01', 'logon',        'Beijing',  'Beijing'),

(2222, '2024-01-02 00:00:00', 'registration', 'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:00', 'logon',        'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:01', 'payment',      'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:03', 'payment',      'Zhejiang', 'Hangzhou'),

(3333, '2024-01-02 00:00:00', 'registration', 'Shanghai', 'Shanghai'),
(3333, '2024-01-02 00:00:00', 'logon',        'Shanghai', 'Shanghai'),
(3333, '2024-01-02 00:00:01', 'payment',      'Shanghai', 'Shanghai'),
(3333, '2024-01-02 00:00:03', 'payment',      'Shanghai', 'Shanghai'),
(3333, '2024-01-02 00:00:04', 'exit',         'Shanghai', 'Shanghai');

Etapa 2: Consulte dados diários do funil agrupados por província.

SELECT
    key,
    funnel_rep(3, 4, res) AS ans
FROM (
    SELECT
        id,
        finder_group_funnel_text_group(result) AS key,
        finder_group_funnel_res(result) AS res
    FROM (
        SELECT
            id,
            UNNEST(
                finder_group_funnel(
                    86400000 * 3,
                    EXTRACT(epoch FROM TIMESTAMP '2024-01-02 00:00:00')::BIGINT,
                    86400, 3, 4, 0, 1, 'Asia/Shanghai', FALSE,
                    event_time, event_time,
                    province,
                    event = 'Register',
                    event = 'Log on',
                    event = 'Payment',
                    event = 'Exit'
                )
            ) AS result
        FROM finder_group_funnel_test_1
        GROUP BY id
    ) a
) b
GROUP BY key;

Resultado:

   key    |                    ans
----------+-------------------------------------------
 Beijing  | {"1,1,1,1","1,1,1,1","1,1,1,0","1,1,0,0"}
 unreach  | {"0,0,0,0","0,0,0,0","0,0,0,0","0,0,0,0"}
 Shanghai | {"1,1,1,1","1,1,1,1","0,0,0,0","0,0,0,0"}
 Zhejiang | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
(4 rows)

O usuário de Pequim completou todos os 4 eventos no dia 1 e continuou convertendo nos dias 2 e 3. O usuário de Xangai também completou todos os 4 eventos, mas apenas no dia 1 (sem atividade subsequente). O usuário de Zhejiang alcançou apenas 3 dos 4 eventos.

Cenário 2: Agrupar resultados do funil por dia civil

Objetivo: A mesma equipe deseja observar dados do funil alinhados aos limites de dias civis (meia-noite a meia-noite) em vez de uma janela contínua de tempo decorrido. Isso corresponde à definição típica dos períodos de relatórios comerciais. Com uma janela contínua, o evento exit em 2024-01-05 ainda poderia cair dentro de 3 dias do primeiro evento; com limites de dias civis, isso não ocorre.

Etapa 1: Crie a tabela de teste e insira dados.

CREATE TABLE finder_group_funnel_test_2(
  id INT,
  event_time TIMESTAMP,
  event TEXT,
  province TEXT,
  city TEXT
);

INSERT INTO finder_group_funnel_test_2 VALUES
(1111, '2024-01-02 00:00:02', 'registration', 'Beijing',  'Beijing'),
(1111, '2024-01-02 00:00:03', 'logon',        'Beijing',  'Beijing'),

(1111, '2024-01-03 00:00:04', 'payment',      'Beijing',  'Beijing'),

(1111, '2024-01-05 00:00:01', 'exit',         'Beijing',  'Beijing'),

(2222, '2024-01-02 00:00:00', 'registration', 'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:00', 'logon',        'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:01', 'payment',      'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:03', 'payment',      'Zhejiang', 'Hangzhou');

Etapa 2: Consulte com limites de dias civis (is_relative_window=TRUE).

SELECT
    key,
    funnel_rep(3, 4, res) AS ans
FROM (
    SELECT
        id,
        finder_group_funnel_text_group(result) AS key,
        finder_group_funnel_res(result) AS res
    FROM (
        SELECT
            id,
            UNNEST(
                finder_group_funnel(
                    86400000 * 3,
                    EXTRACT(epoch FROM TIMESTAMP '2024-01-02 00:00:00')::BIGINT,
                    86400, 3, 4, 0, 1, 'Asia/Shanghai', TRUE, -- calendar-day window
                    event_time, event_time,
                    province,
                    event = 'register',
                    event = 'logon',
                    event = 'pay',
                    event = 'exit'
                )
            ) AS result
        FROM finder_group_funnel_test_2
        GROUP BY id
    ) a
) b
GROUP BY key;

Resultado:

   key    |                    ans
----------+-------------------------------------------
 unreach  | {"0,0,0,0","0,0,0,0","0,0,0,0","0,0,0,0"}
 Zhejiang | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
 Beijing  | {"1,1,1,0","1,1,1,0","0,0,0,0","0,0,0,0"}
(3 rows)

Com limites de dias civis, o usuário 1111 (Pequim) completa apenas 3 dos 4 eventos dentro da janela de 3 dias. Seu evento exit ocorre em 2024-01-05, fora da janela de dias civis iniciada em 2024-01-02 (que cobre 2, 3 e 4 de janeiro). Sob uma janela contínua, o mesmo evento seria capturado porque ocorre dentro de 3 × 86.400 segundos após o primeiro evento.

Limitações

Limitação	Detalhes
Requisito de versão	Apenas Hologres V2.2.32 e posterior suportam `finder_group_funnel`.
Tipo de campo `group_dimension`	Apenas campos TEXT são suportados. Para agrupar por vários campos, use `concat_ws` ou `concat`.
Restrições de janela de dia civil	Quando `is_relative_window=true`, `window` deve ser um múltiplo inteiro de 86.400.000 e `step_interval` deve ser `86400`.
Schema da extensão	A extensão `flow_analysis` carrega apenas no schema `public` e não pode ser carregada em outros schemas.