Funciones de agregación intermedias

Automatización de canalizaciones de datos en Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Orden de operaciones

Orden de operaciones.png

SQL no se ejecuta en el orden en que lo escribes

Filas: crea el conjunto de datos
Grupos: resúmelo
Resultado: da forma a la salida

¹ * Snowflake Learning Material

Funciones de agregación comunes

COUNT(*) / COUNT(DISTINCT col) — filas totales; valores únicos
SUM — total de la columna
AVG — media
MIN / MAX — rango

SELECT COUNT(*) AS total_shipments,
       COUNT(DISTINCT carrier_id) AS unique_carriers,
       SUM(delivery_days) AS total_days,
       AVG(delivery_days) AS avg_days
FROM shipments;

El problema de múltiples consultas

Devolver uso por región y almacén

SELECT 
region
, warehouse_name
, SUM(credits_used) 
FROM usage 
GROUP BY region, warehouse_name;

Devolver subtotales por región

SELECT region
, SUM(credits_used) 
FROM usage GROUP BY region;

Devolver total de uso

SELECT 
SUM(credits_used) 
FROM usage;

GROUPING SETS

Entrada

SELECT region
, warehouse_name
, SUM(credits_used) AS total 
FROM usage 
GROUP BY GROUPING SETS ( 
  (region, warehouse_name)
  , (region)
  , () 
);

Salida

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25
NULL	NULL	2510.75

Nota: Da significado a los NULL con una sentencia CASE

ROLLUP

Entrada

SELECT 
region
, warehouse_name
, SUM(credits_used) AS total 
FROM usage 
GROUP BY ROLLUP (region, warehouse_name);

Salida

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25
NULL	NULL	2510.75

CUBE

Entrada

SELECT 
region
, warehouse_name
, SUM(credits_used) AS total
FROM usage 
GROUP BY CUBE (region, warehouse_name);

Salida

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25	}
NULL	harbr_wh_prod	2130.75
NULL	harbr_wh_dev	380.00
NULL	NULL	2510.75

GROUPING() y GROUPING_ID()

SELECT region, warehouse_name, SUM(credits_used) AS total_credits, GROUPING(region) AS is_region_agg, GROUPING(warehouse_name) AS is_wh_agg , 
GROUPING_ID(region, warehouse_name) AS grouping_level 
FROM usage 
GROUP BY ROLLUP (region, warehouse_name);

Captura de pantalla de la salida de la tabla en Markdown

¡Vamos a practicar!

Automatización de canalizaciones de datos en Snowflake