Vensterfuncties

Automatisering van datapijplijnen in Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Wat is een vensterfunctie?

Reken over rijen zonder ze samen te voegen

Berekent per rij met een omliggende set — het venster
Anders dan GROUP BY: elke rij erin, elke rij eruit
Gedefinieerd door de OVER-clausule

3 hoofdtypen:

Rangen: ROW_NUMBER(), RANK(), DENSE_RANK()
Aggregatie: SUM(), AVG(), COUNT() with OVER()
Navigatie: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Meer info in DataCamp’s cursus Window Functions in Snowflake

De OVER()-clausule

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	REGIO	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 en LEAD

Input

SQL Lag en Lead input

Output

Lag en Lead output

Doorlopende totalen en rollende gemiddelden

ROWS vs RANGE-frames

ROWS BETWEEN: telt fysieke rijen — exacte offsets, ongeacht waarde

-- ROWS: pakt altijd exact N voorafgaande fysieke rijen
SUM(credits) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Inclusief huidige rij en twee ervoor

RANGE BETWEEN: gebruikt waarden, groepeert rijen met dezelfde ORDER BY-waarden

-- RANGE: neemt alle rijen mee die gelijk zijn aan de grenswaarde
SUM(credits) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

Gebruik Range alleen als je logica over waardengrenzen gaat

Laten we oefenen!

Automatisering van datapijplijnen in Snowflake