Віконні функції

Автоматизація конвеєрів даних у Snowflake

Emily Melhuish

Technical Curriculum Developer, Snowflake

Що таке віконна функція?

Обчислюйте по рядках без їх згортання

Обчислює результат для кожного рядка з урахуванням оточення — його вікна
На відміну від GROUP BY: кожен рядок на вході — кожен на виході
Визначається клаузою OVER

3 основні категорії:

Ранжування: ROW_NUMBER(), RANK(), DENSE_RANK()
Агрегація: SUM(), AVG(), COUNT() with OVER()
Навігація: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Детальніше в курсі DataCamp Window Functions in Snowflake

Клауза 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 і LEAD

Вхідні дані

Вхід для SQL LAG і LEAD

Вихідні дані

Вихід LAG і LEAD

Наростаючі підсумки та ковзні середні

Віконні фрейми

Фрейми ROWS vs RANGE

ROWS BETWEEN: рахує фізичні позиції рядків — точні зсуви незалежно від значень

-- ROWS: завжди бере рівно N попередніх фізичних рядків
SUM(credits) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Включає поточний і два попередні

RANGE BETWEEN: використовує значення, групує рядки з однаковими значеннями в ORDER BY

-- RANGE: включає всі рядки, що збігаються на межовому значенні
SUM(credits) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

Використовуйте RANGE лише коли логіка про межі значень

Перейдемо до практики!

Автоматизація конвеєрів даних у Snowflake