Okenní funkce

Automatizace datových pipeline ve Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Co je okenní funkce?

Výpočty přes řádky bez jejich sloučení

Vypočítá výsledek pro každý řádek na základě okolní sady – svého okna
Na rozdíl od GROUP BY: každý řádek vstoupí, každý řádek vystoupí
Definováno klauzulí OVER

3 hlavní kategorie:

Pořadí: ROW_NUMBER(), RANK(), DENSE_RANK()
Agregace: SUM(), AVG(), COUNT() with OVER()
Navigace: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Více informací v kurzu DataCamp Window Functions in Snowflake

Klauzule 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 a LEAD

Vstup

Vstup pro SQL LAG a LEAD

Výstup

Výstup LAG a LEAD

Průběžné součty a klouzavé průměry

Rámce ROWS vs RANGE

ROWS BETWEEN: počítá fyzické pozice řádků – přesné posuny bez ohledu na hodnotu

-- ROWS: always picks exactly N preceding physical rows
SUM(credits) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Includes current row and two preceding

RANGE BETWEEN: pracuje s hodnotami, seskupuje řádky se stejnou hodnotou ORDER BY

-- RANGE: includes all rows tied at the boundary value
SUM(credits) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

Použijte RANGE pouze tehdy, kdy logika závisí na hodnotových hranicích

Pojďme si procvičit!

Automatizace datových pipeline ve Snowflake