Оконные функции (Window Functions)

Оконные функции — это главное отличие middle от junior. Без них нельзя посчитать топ-N в каждой группе, скользящие средние, running totals, или сравнения с предыдущей строкой.

Базовый синтаксис

function() OVER (
    PARTITION BY column
    ORDER BY column
    ROWS BETWEEN ... AND ...
)

3 части:

• PARTITION BY — разбить на группы (как GROUP BY, но строки сохраняются)
• ORDER BY — порядок внутри группы
• ROWS BETWEEN — какие строки включать (по умолчанию: все до текущей)

ROW_NUMBER — нумерация

SELECT
    user_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_n
FROM orders;

Каждому заказу — порядковый номер для своего юзера. Первый заказ юзера = 1.

RANK, DENSE_RANK — ранжирование

RANK()       OVER (...)   -- 1, 1, 3, 4 (пропуски при дубликатах)
DENSE_RANK() OVER (...)   -- 1, 1, 2, 3 (без пропусков)
ROW_NUMBER() OVER (...)   -- 1, 2, 3, 4 (всегда уникально)

Топ-3 в каждой группе

WITH ranked AS (
    SELECT
        category,
        product,
        sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
    FROM products
)
SELECT category, product, sales
FROM ranked
WHERE rn <= 3;

LAG, LEAD — соседние строки

SELECT
    day,
    sales,
    LAG(sales)  OVER (ORDER BY day) AS sales_yesterday,
    LEAD(sales) OVER (ORDER BY day) AS sales_tomorrow
FROM daily;

DoD growth:

SELECT
    day,
    sales - LAG(sales) OVER (ORDER BY day) AS delta
FROM daily;

Running total — накопительная сумма

SELECT
    day,
    sales,
    SUM(sales) OVER (ORDER BY day) AS cumulative_sales
FROM daily;

Скользящая средняя (последние 7 дней)

SELECT
    day,
    sales,
    AVG(sales) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d
FROM daily;

NTILE — разбиение на квантили

SELECT
    user_id,
    revenue,
    NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
FROM users;

Каждому юзеру — номер квартиля (1 = топ 25%).

Что важно запомнить

Задача	Функция
Уникальный номер	ROW_NUMBER()
Топ-N в группе	ROW_NUMBER() PARTITION BY
Рост к вчера	LAG()
Running total	SUM() OVER (ORDER BY ...)
Скользящая	AVG() OVER (ROWS BETWEEN ...)
Квартили	NTILE(4)

• PARTITION BY ≠ GROUP BY: оконная сохраняет строки, GROUP BY коллапсирует
• На собеседовании middle+: window functions — обязательный блок вопросов
• Чем сложнее аналитика, тем чаще без окон не обойтись
• В Postgres / Snowflake / BigQuery — синтаксис почти идентичный