bit_construct e bit_match - Hologres: Central de Documentação do Alibaba Cloud

Contexto

Na segmentação de público, um único usuário geralmente possui vários registros, cada um correspondendo a uma condição diferente. Encontrar usuários que atendem a uma combinação específica de condições — por exemplo, aqueles que adicionaram um produto ao carrinho de compras e também o salvaram na página de favoritos — tradicionalmente exige várias rodadas de filtragem condicional e instruções JOIN. Esse processo resulta em SQL complexo e alto consumo de recursos.

Disponíveis no Hologres V0.10 e versões posteriores, as funções bit_construct e bit_match substituem esse padrão de múltiplos JOINs por uma única passagem de agregação.

A tabela de detalhes a seguir ilustra o problema. O usuário A atende tanto à condição click shopping cart quanto à view favorites page, enquanto o usuário B não atende a ambas.

user	action	page
A	click	shopping cart
B	click	home page
A	view	favorites page
B	click	shopping cart
A	click	favorites page

Pré-requisitos

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

Hologres V0.10 ou posterior. Verifique sua versão atual no console do Hologres. Se sua versão for anterior à V0.10, consulte Erros comuns que causam falha na preparação da atualização ou entre em contato com o grupo DingTalk do Hologres. Para mais informações, consulte Como obtenho mais suporte online?
A extensão flow_analysis ativada no banco de dados de destino (consulte Ativar a extensão)

Ativar a extensão

A extensão flow_analysis tem escopo de banco de dados. Execute a instrução a seguir uma vez por banco de dados. Caso crie um novo banco de dados, execute-a novamente.

CREATE EXTENSION flow_analysis;

Para desinstalar a extensão:

DROP EXTENSION flow_analysis;

Um superusuário deve executar essas instruções.

bit_construct

Disponível no Hologres V0.10 e versões posteriores.

Avalia até 32 condições de filtro booleanas por linha e codifica os resultados como um bitmap de inteiros de 32 bits.

Sintaxe

bit_construct(
  a  := <bool_expr>,
  b  := <bool_expr>,
  ...,
  a6 := <bool_expr>
)

Parâmetros

Parâmetro	Tipo	Descrição
`a`, `b`, ... `z`, `a1`–`a6`	`bool`	Condições de filtro. Há suporte para até 32 condições. Os nomes válidos variam de `a` a `z` e de `a1` a `a6`.

Valor retornado

int — um bitmap que representa as condições satisfeitas.

bit_match

Disponível no Hologres V0.10 e versões posteriores.

Avalia uma expressão lógica em relação a um bitmap produzido por bit_construct para determinar se um usuário satisfaz a combinação necessária de condições.

Sintaxe

bit_match('expression', bitmask)

Parâmetros

Parâmetro	Tipo	Descrição	Exemplo
`expression`	`text`	Expressão lógica sobre os rótulos de condição definidos em `bit_construct`. Oferece suporte aos operadores `&` (AND), `	` (OR), `!` (NOT) e `^` (XOR).	`'a&b'`
`bitmask`	`int`	Bitmap retornado por `bit_construct` ou agregado por `bit_or`.	—

Exemplo de uso

O exemplo completo a seguir localiza usuários que adicionaram um produto ao carrinho de compras e também o salvaram na página de favoritos.

Etapa 1: Ativar a extensão

CREATE EXTENSION flow_analysis;

Etapa 2: Criar uma tabela e inserir dados de amostra

create table ods_app_dwd(
event_time timestamptz,
uid bigint,
action text,
page text,
product_code text,
from_days int
);

insert into ods_app_dwd values('2021-04-03 10:01:30', 274649163, 'click', 'shopping cart', 'MDS', 1);
insert into ods_app_dwd values('2021-04-03 10:04:30', 274649163, 'view', 'favorites page', 'MDS', 4);
insert into ods_app_dwd values('2021-04-03 10:06:30', 274649165, 'click', 'shopping cart', 'MMS', 8);
insert into ods_app_dwd values('2021-04-03 10:09:30', 274649165, 'view', 'shopping cart', 'MDS', 10);

Etapa 3: Consultar o público-alvo

Dois padrões de consulta estão disponíveis. Ambos usam bit_construct para rotular as condições de cada linha, bit_or para agregar por usuário e bit_match para filtrar os usuários que satisfazem ambas as condições.

A função bit_or executa uma operação OR lógica em todos os registros de um determinado usuário: se qualquer registro satisfizer a condição a, considera-se que o usuário atende à condição a.

Uso da cláusula WHERE

Filtre as linhas com uma cláusula WHERE antes da agregação. Quanto menos linhas a cláusula WHERE corresponder, melhor será o desempenho da consulta.

WITH tbl as (
SELECT uid, bit_or(bit_construct(
  a := (action='click' and page='shopping cart'),
  b := (action='view' and page='favorites page'))) as uid_mask
  FROM ods_app_dwd
WHERE event_time > '2021-04-03 10:00:00' AND event_time < '2021-04-04 10:00:00'
GROUP BY uid )
SELECT uid from tbl where bit_match('a&b', uid_mask);

bit_construct: a condição a captura usuários que clicaram no carrinho de compras; a condição b captura usuários que visualizaram a página de favoritos.
bit_or: agrega todos os registros por usuário. Um usuário satisfaz uma condição se pelo menos um de seus registros corresponder a ela.
bit_match('a&b', ...): retorna apenas usuários que satisfazem tanto a condição a quanto a condição b.

Uso da cláusula HAVING

Use uma cláusula HAVING para aplicar o filtro de condição diretamente na etapa de agregação.

SELECT uid FROM (
    SELECT uid, bit_or(bit_construct(
      a := (action='click' AND page='shopping cart'),
      b := (action='view' AND page='favorites page'))) as uid_mask
    FROM ods_app_dwd
    WHERE event_time > '2021-04-03 10:00:00' AND event_time < '2021-04-04 10:00:00'
    GROUP BY uid
    HAVING bit_match('a&b', bit_or(bit_construct(
      a := (action='click' and page='shopping cart'),
      b := (action='view' and page='favorites page'))))
) t