Funzioni di aggregazione intermedie

Automazione delle pipeline di dati in Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Ordine delle operazioni

Ordine delle operazioni.png

SQL non viene eseguito nell'ordine in cui lo scrivi

Righe: crea il dataset
Gruppi: riassumilo
Risultato: modella l'output

¹ * Snowflake Learning Material

Funzioni di aggregazione comuni

COUNT(*) / COUNT(DISTINCT col) — total rows; unique values
SUM — column total
AVG — mean value
MIN / MAX — range

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;

Il problema di più query

Restituisci l'uso per regione e warehouse

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

Restituisci i subtotali per regione

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

Restituisci il totale

SELECT 
SUM(credits_used) 
FROM usage;

GROUPING SETS

Input

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

Output

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: dai significato ai NULL con una CASE

ROLLUP

Input

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

Output

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

Input

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

Output

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

Schermata dell'output della tabella markdown

Passons à la pratique !

Automazione delle pipeline di dati in Snowflake