Exemplos

Esta seção contém exemplos de uso do HyperLogLog com o Amazon Redshift.

Exemplo: retornar cardinalidade em uma subconsulta

O exemplo a seguir retorna a cardinalidade para cada esboço em uma subconsulta para uma tabela chamada Vendas.

CREATE TABLE Sales (customer VARCHAR, country VARCHAR, amount BIGINT);
INSERT INTO Sales VALUES ('David Joe', 'Greece', 14.5),  ('David Joe', 'Greece', 19.95), ('John Doe', 'USA', 29.95), ('John Doe', 'USA', 19.95), ('George Spanos', 'Greece', 9.95), ('George Spanos', 'Greece', 2.95);

A consulta a seguir gera um esboço HLL para os clientes de cada país e extrai a cardinalidade. Isso mostra clientes únicos de cada país.

SELECT hll_cardinality(sketch), country
FROM (SELECT hll_create_sketch(customer) AS sketch, country
        FROM Sales
        GROUP BY country) AS hll_subquery;
        
hll_cardinality | country
----------------+---------
            1   | USA
            2   | Greece
 ...

Exemplo: retorna um tipo HLLSKETCH de esboços combinados em uma subconsulta

O exemplo a seguir retorna um único tipo HLLSKETCH que representa a combinação de esboços individuais de uma subconsulta. Os esboços são combinados usando a função agregada HLL_COMBINE.

SELECT hll_combine(sketch)
FROM (SELECT hll_create_sketch(customer) AS sketch
        FROM Sales
        GROUP BY country) AS hll_subquery
       
                                        hll_combine
--------------------------------------------------------------------------------------------
 {"version":1,"logm":15,"sparse":{"indices":[29808639,35021072,47612452],"values":[1,1,1]}}
(1 row)       
       

Exemplo: retorna um esboço do HyperLogLog da combinação de vários esboços

Para o exemplo a seguir, suponha que a tabela page-users armazena esboços pré-agregados para cada página que os usuários visitaram em um determinado site. Cada linha nesta tabela contém um esboço HyperLogLog que representa todos os IDs de usuário que mostram as páginas visitadas.

page_users
-- +----------------+-------------+--------------+
-- | _PARTITIONTIME | page         | sketch |
-- +----------------+-------------+--------------+
-- | 2019-07-28     | homepage     | CHAQkAQYA... |
-- | 2019-07-28     | Product A    | CHAQxPnYB... |
-- +----------------+-------------+--------------+

O exemplo a seguir une os vários esboços pré-agregados e gera um único esboço. Este esboço encapsula a cardinalidade coletiva que cada esboço encapsula.

SELECT hll_combine(sketch) as sketch
FROM page_users

A saída será semelhante à seguinte.

-- +-----------------------------------------+
-- | sketch |
-- +-----------------------------------------+
-- | CHAQ3sGoCxgCIAuCB4iAIBgTIBgqgIAgAwY.... |
-- +-----------------------------------------+

Quando um novo esboço é criado, você pode usar a função HLL_CARDINALITY para obter os valores distintos coletivos, como mostrado a seguir.

SELECT hll_cardinality(sketch)
FROM ( 
  SELECT
  hll_combine(sketch) as sketch
  FROM page_users
) AS hll_subquery
       

A saída será semelhante à seguinte.

-- +-------+
-- | count |
-- +-------+
-- | 54356 |
-- +-------+

Exemplo: gerar esboços do HyperLogLog sobre dados do S3 usando tabelas externas

Os exemplos a seguir armazenam em cache os esboços do HyperLogLog para evitar acessar diretamente o Amazon S3 para estimativa de cardinalidade.

Você pode pré-agregar e armazenar em cache esboços do HyperLogLog em tabelas externas definidas para armazenar dados do Amazon S3. Ao fazer isso, você pode extrair estimativas de cardinalidade sem acessar os dados básicos subjacentes.

Por exemplo, suponha que você tenha descarregado um conjunto de arquivos de texto delimitados por tabulação no Amazon S3. Execute a consulta a seguir para definir uma tabela externa chamada sales no esquema externo do Amazon Redshift chamadospectrum. O bucket do Amazon S3 para este exemplo está na Região da AWS do Leste dos EUA (Norte da Virgínia).

create external table spectrum.sales(
salesid integer,
listid integer,
sellerid smallint,
buyerid smallint,
eventid integer,
dateid integer,
qtysold integer,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
row format delimited
fields terminated by '\t' stored as textfile
location 's3://redshift-downloads/tickit/spectrum/sales/';

Suponha que você queira computar os compradores distintos que compraram um item em datas arbitrárias. Para fazer isso, o exemplo a seguir gera esboços para os IDs do comprador para cada dia do ano e armazena o resultado na tabela hll_sales do Amazon Redshift.

CREATE TABLE hll_sales AS
SELECT saletime, hll_create_sketch(buyerid) AS sketch
FROM spectrum.sales
GROUP BY saletime;
            
SELECT TOP 5 * FROM hll_sales;

A saída será semelhante à seguinte.

-- hll_sales

-- | saletime        | sketch                                                              |
-- +-----------------+---------------------------------------------------------------------+
-- | 7/22/2008 8:30  | {"version":1,"logm":15,"sparse":{"indices":[9281416],"values":[1]}}
-- | 2/19/2008 0:38  | {"version":1,"logm":15,"sparse":{"indices":[48735497],"values":[3]}}
-- | 11/5/2008 4:49  | {"version":1,"logm":15,"sparse":{"indices":[27858661],"values":[1]}}
-- | 10/27/2008 4:08 | {"version":1,"logm":15,"sparse":{"indices":[65295430],"values":[2]}}
-- | 2/16/2008 9:37  | {"version":1,"logm":15,"sparse":{"indices":[56869618],"values":[2]}}
-- +---------------- +---------------------------------------------------------------------+

A consulta a seguir mostra o número estimado de consumidores distintos que compraram um item durante a sexta-feira após a Ação de Graças em 2008.

SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers
FROM hll_sales
WHERE trunc(saletime) = '2008-11-28';     

A saída será semelhante à seguinte.

distinct_buyers
---------------
386

Suponha que você queira o número de usuários distintos que compraram um item em um determinado intervalo de datas. Um exemplo pode ser da sexta-feira após o Dia de Ação de Graças até a segunda-feira seguinte. Para obter isso, a consulta a seguir usa a função agregada hll_combine. Esta função permite evitar a contagem dupla de compradores que compraram um item em mais de um dia do intervalo selecionado.

SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers
FROM hll_sales
WHERE saletime BETWEEN '2008-11-28' AND '2008-12-01';

A saída será semelhante à seguinte.

distinct_buyers
---------------
1166

Para manter a tabela hll_sales atualizada, execute a consulta a seguir no final de cada dia. Isso gera um esboço do HyperLogLog com base nos IDs dos compradores que compraram um item hoje e o adiciona à tabela hll_sales.

INSERT INTO hll_sales 
SELECT saletime, hll_create_sketch(buyerid) 
FROM spectrum.sales 
WHERE TRUNC(saletime) = to_char(GETDATE(), 'YYYY-MM-DD')
GROUP BY saletime;