Ejemplos

Ejemplo: devolver la cardinalidad en una subconsulta

En el siguiente ejemplo, se devuelve la cardinalidad de cada boceto en una subconsulta para una tabla denominada Ventas.

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);

La siguiente consulta genera un boceto HLL para los clientes de cada país y extrae la cardinalidad. Esto muestra 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
 ...

Ejemplo: devolver un tipo HLLSKETCH a partir de bocetos combinados en una subconsulta

En el siguiente ejemplo, se devuelve un único tipo HLLSKETCH que representa la combinación de bocetos individuales a partir de una subconsulta. Los bocetos se combinan mediante la función de agrupación HLL_COMBINE.

SELECT hll_combine(sketch)
FROM (SELECT hll_create_sketch(customers) 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)       
       

Ejemplo: devolver un boceto de HyperLogLog a partir de la combinación de varios bocetos

Para el siguiente ejemplo, suponga que la tabla page-users almacena bocetos agrupados previamente para cada página que los usuarios visitaron en un sitio web determinado. Cada fila de esta tabla contiene un boceto de HyperLogLog que representa todos los ID de usuario que muestran las páginas visitadas.

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

En el siguiente ejemplo, se unen los bocetos agrupados previamente y se genera un solo boceto. Este boceto encapsula la cardinalidad colectiva que abarca cada boceto.

SELECT hll_combine(sketch) as sketch
FROM page_users

El resultado tiene un aspecto similar al siguiente.

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

Cuando se crea un nuevo boceto, puede utilizar la función HLL_CARDINALITY para obtener los valores distintos colectivos, como se muestra a continuación.

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

El resultado tiene un aspecto similar al siguiente.

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

Ejemplo: generar bocetos de HyperLogLog a partir de datos de S3 mediante tablas externas

Los siguientes ejemplos almacenan en caché los bocetos de HyperLogLog para evitar el acceso directo a Amazon S3 para la estimación de la cardinalidad.

Puede agrupar previamente y almacenar en caché bocetos de HyperLogLog en tablas externas definidas para contener datos de Amazon S3. Al hacer esto, puede extraer estimaciones de cardinalidad sin acceder a los datos base subyacentes.

Por ejemplo, suponga que ha llevado un conjunto de archivos de texto delimitado por tabulaciones en Amazon S3. Ejecuta la siguiente consulta para definir una tabla externa denominada sales en el esquema externo de Amazon Redshift denominado spectrum. El bucket de Amazon S3 para este ejemplo está en la Región de AWS Este de EE. UU. (Norte de Virginia).

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/';

Supongamos que desea calcular la cantidad de compradores distintos que adquirieron un elemento en fechas arbitrarias. Para ello, en el siguiente ejemplo, se generan bocetos para los ID de comprador para cada día del año y se almacena el resultado en la tabla hll_sales de 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;

El resultado tiene un aspecto similar al siguiente.

-- 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]}}
-- +---------------- +---------------------------------------------------------------------+

La siguiente consulta muestra el número estimado de compradores distintos que adquirieron un elemento durante el viernes después de Acción de Gracias en 2008.

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

El resultado tiene un aspecto similar al siguiente.

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

Suponga que desea conocer el número de usuarios distintos que compraron un elemento en un cierto intervalo de fechas. Un ejemplo podría ser desde el viernes después de Acción de Gracias hasta el lunes siguiente. Para obtener esto, la siguiente consulta utiliza la función de agrupación hll_combine. Esta función le permite evitar el recuento doble de los compradores que adquirieron un elemento en más de un día del rango seleccionado.

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

El resultado tiene un aspecto similar al siguiente.

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

Para mantener la tabla hll_sales actualizada, ejecute la siguiente consulta al final de cada día. Al hacerlo, se genera un boceto de HyperLogLog en función de los ID de los compradores que adquirieron un elemento hoy y lo agregan a la tabla 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;