Fonctions fenêtre

Automatisation des pipelines de données dans Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Qu’est-ce qu’une fonction fenêtre?

Calculer sur des lignes sans les regrouper

Calcule un résultat par ligne à partir d’un ensemble voisin — sa fenêtre
Contrairement à GROUP BY : chaque ligne en entrée, chaque ligne en sortie
Défini par la clause OVER

3 catégories principales :

Classement : ROW_NUMBER(), RANK(), DENSE_RANK()
Agrégation : SUM(), AVG(), COUNT() with OVER()
Navigation : LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Plus d’info dans le cours DataCamp Window Functions in Snowflake

La clause 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 et LEAD

Entrée

Entrée SQL Lag et Lead

Sortie

Sortie Lag et Lead

Totaux cumulés et moyennes mobiles

Cadres ROWS vs RANGE

ROWS BETWEEN: compte les positions physiques — décalages exacts, peu importe la valeur

-- ROWS : prend toujours exactement N lignes physiques précédentes
SUM(credits) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Inclut la ligne courante et les deux précédentes

RANGE BETWEEN: utilise les valeurs, regroupe les lignes avec les mêmes valeurs de ORDER BY

-- RANGE : inclut toutes les lignes à la valeur limite (ex æquo)
SUM(credits) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

N’utilisez RANGE que si la logique porte sur des bornes de valeur

Passons à la pratique !

Automatisation des pipelines de données dans Snowflake