Funzioni finestra

Automazione delle pipeline di dati in Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Che cos'è una funzione finestra?

Calcola su più righe senza collassarle

Calcola un risultato per riga usando un insieme circostante — la sua finestra
Diverso da GROUP BY: ogni riga entra, ogni riga esce
Definita dalla clausola OVER

3 categorie principali:

Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()
Aggregazione: SUM(), AVG(), COUNT() con OVER()
Navigazione: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Altre info nel corso DataCamp Window Functions in Snowflake

La clausola OVER()

SELECT shipment_id
, region
, delivery_days
, AVG(delivery_days) 
    OVER ( PARTITION BY region ORDER BY dispatched_at ) AS avg_days_in_region 
FROM shipments;

SHIPMENT_ID	REGION	DELIVERY_DAYS	RUNNING_AVG_DAYS_IN_REGION
SHP-001	EMEA	3	3.00
SHP-002	EMEA	5	4.00
SHP-003	EMEA	4	4.00
SHP-004	APAC	6	6.00

ROW_NUMBER vs RANK vs DENSE_RANK

SELECT
    shipment_id AS shipment, delivery_days AS days,
    ROW_NUMBER() OVER (ORDER BY delivery_days) AS row_number,
    RANK()       OVER (ORDER BY delivery_days) AS rank,
    DENSE_RANK() OVER (ORDER BY delivery_days) AS dense_rank
FROM shipments ORDER BY delivery_days;

SHIPMENT	DELIVERY_DAYS	ROW_NUMBER	RANK	DENSE_RANK
SHP-001	3	1	1	1
SHP-002	5	2	2	2
SHP-003	5	3	2	2
SHP-004	7	4	4	3

LAG e LEAD

Input

Input SQL per LAG e LEAD

Output

Output di LAG e LEAD

Totali progressivi e medie mobili

frame di finestra

Frame ROWS vs RANGE

ROWS BETWEEN: conta le posizioni fisiche delle righe — offset esatti indipendenti dal valore

-- ROWS: prende sempre esattamente N righe fisiche precedenti
SUM(credits) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Include la riga corrente e le due precedenti

RANGE BETWEEN: usa i valori, raggruppa le righe con gli stessi valori in ORDER BY

-- RANGE: include tutte le righe a parità del valore di soglia
SUM(credits) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

Usa RANGE solo quando la logica riguarda soglie di valore

Passons à la pratique !

Automazione delle pipeline di dati in Snowflake