Оконные функции SQL

Оконные функции - мощный инструмент SQL, который позволяет выполнять вычисления по группам строк без их схлопывания. Это must-have навык для аналитика данных.

Синтаксис оконных функций

ROW_NUMBER, RANK, DENSE_RANK

Функции нумерации строк - самые частые на собеседованиях.

-- Пронумеровать заказы каждого клиента по дате
SELECT 
    client_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY client_id 
        ORDER BY order_date
    ) as order_num
FROM orders;

Типичная задача: топ-N по группам

-- Топ-3 заказа каждого клиента по сумме
WITH ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY client_id 
            ORDER BY amount DESC
        ) as rn
    FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

LAG и LEAD

Доступ к предыдущей (LAG) и следующей (LEAD) строке.

-- Сравнить продажи с предыдущим днем
SELECT 
    sale_date,
    revenue,
    LAG(revenue) OVER (ORDER BY sale_date) as prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY sale_date) as diff
FROM daily_sales;

LAG/LEAD с параметрами

-- LAG(столбец, смещение, значение_по_умолчанию)
LAG(revenue, 1, 0)  -- предыдущая строка, 0 если нет
LAG(revenue, 7)     -- 7 строк назад (неделя)
LEAD(revenue, 1)    -- следующая строка

Агрегатные функции с OVER

SUM, AVG, COUNT, MIN, MAX можно использовать как оконные.

-- Накопительная сумма (running total)
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Процент от общей суммы
SELECT 
    category,
    revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) as pct_total
FROM category_sales;

-- Скользящее среднее за 7 дней
SELECT 
    sale_date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as ma_7d
FROM daily_sales;

FIRST_VALUE и LAST_VALUE

-- Первый и последний заказ клиента
SELECT 
    client_id,
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY client_id 
        ORDER BY order_date
    ) as first_order,
    LAST_VALUE(amount) OVER (
        PARTITION BY client_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last_order
FROM orders;

Фреймы окна (ROWS/RANGE)

Задачи с собеседований

1. Найти дубликаты

WITH duplicates AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY created_at
        ) as rn
    FROM users
)
SELECT * FROM duplicates WHERE rn > 1;

2. Рассчитать retention D1

WITH first_activity AS (
    SELECT 
        user_id,
        MIN(DATE(event_time)) as first_date
    FROM events
    GROUP BY user_id
),
d1_activity AS (
    SELECT DISTINCT 
        user_id, 
        DATE(event_time) as active_date
    FROM events
)
SELECT 
    f.first_date,
    COUNT(DISTINCT f.user_id) as cohort_size,
    COUNT(DISTINCT d1.user_id) as retained_d1,
    ROUND(100.0 * COUNT(DISTINCT d1.user_id) / COUNT(DISTINCT f.user_id), 2) as retention
FROM first_activity f
LEFT JOIN d1_activity d1 
    ON f.user_id = d1.user_id 
    AND d1.active_date = f.first_date + 1
GROUP BY f.first_date;

3. Топ-1 товар по категории

SELECT category, product, revenue
FROM (
    SELECT 
        category,
        product,
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY revenue DESC
        ) as rn
    FROM products
) t
WHERE rn = 1;