Оконные функции на собесе Data Engineer

Что спрашивают у DE

DA-уровень оконных функций — это ROW_NUMBER OVER (PARTITION BY user_id ORDER BY date) для дедупликации. DE-уровень — это «объясни, чем ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW отличается от RANGE BETWEEN ..., и почему второй опаснее на одинаковых датах».

Главная боль без понимания frames — кандидат пишет running total через RANGE (дефолт), на одинаковых датах получает суммирование «с захватом», в отчёте дубли. Через два месяца аналитик находит несоответствие, DE откатывается на полтора квартала.

Эта статья — про продвинутые оконки, которые гоняют именно на DE-собесе: frames, ranking, аналитические функции, edge cases.

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

SELECT
    user_id,
    event_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM events;

Три части:

• PARTITION BY — на какие группы делить (опционально; без неё окно — вся таблица)
• ORDER BY — порядок внутри окна (для ranking/lag — обязательно)
• frame clause (ROWS/RANGE BETWEEN ... AND ...) — какие строки внутри окна включать

Frames — самое неочевидное. Дефолтный frame, если есть ORDER BY:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Без ORDER BY:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Поэтому SUM(amount) OVER () без ORDER BY — это сумма по всей партиции, а с ORDER BY — running total. Молчаливое поведение, на собесе обязательно спросят.

ROWS vs RANGE

ROWS считает строки. RANGE считает значения по ORDER BY ключу.

-- ROWS: ровно N предыдущих строк
SUM(amount) OVER (
    ORDER BY event_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

-- RANGE: все строки, у которых event_date в диапазоне
SUM(amount) OVER (
    ORDER BY event_date
    RANGE BETWEEN INTERVAL '2 day' PRECEDING AND CURRENT ROW
)

Опасность дефолтного RANGE: если в окне есть несколько строк с одинаковым event_date, RANGE включит ВСЕ строки с тем же значением.

event_date  | amount | running_rows | running_range
2026-05-01  | 100    | 100          | 250    -- три строки 05-01, RANGE захватил все
2026-05-01  | 50     | 150          | 250
2026-05-01  | 100    | 250          | 250
2026-05-02  | 30     | 180          | 280

Для running total по строкам — всегда ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Особенность Postgres / Spark / ClickHouse: RANGE BETWEEN N PRECEDING AND CURRENT ROW поддерживается не везде. Postgres 11+ и Spark 3+ — да. ClickHouse — частично (через RANGE BETWEEN INTERVAL ...). На собесе уточнить движок.

LAG, LEAD, FIRST_VALUE

LAG(col, N) — значение из строки N назад. LEAD(col, N) — N вперёд.

-- разница между текущей и предыдущей покупкой клиента
SELECT
    user_id,
    event_date,
    amount,
    amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY event_date) AS delta
FROM events;

LAG(amount, 1, 0) — третий аргумент это default для первой строки (без него — NULL).

FIRST_VALUE / LAST_VALUE — первое / последнее значение во frame:

-- первая покупка клиента (без подзапроса)
FIRST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY event_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Грабля LAST_VALUE: дефолтный frame — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это значит «последнее значение от начала до текущей строки», то есть просто текущая. Чтобы получить настоящий last — явно задать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Ловушка частая, на собесе тестируют.

ranking функции:

• ROW_NUMBER() — уникальный ранг 1, 2, 3
• RANK() — с пропусками (1, 1, 3)
• DENSE_RANK() — без пропусков (1, 1, 2)
• NTILE(N) — разбивает окно на N равных квантилей
• PERCENT_RANK() — относительный ранг от 0 до 1
• CUME_DIST() — кумулятивная доля

Дедупликация через ROW_NUMBER

Базовый паттерн дедупликации:

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY event_id
            ORDER BY ingested_at DESC
        ) AS rn
    FROM raw_events
)
SELECT * FROM ranked WHERE rn = 1;

PARTITION BY — бизнес-ключ дедупликации. ORDER BY — какая версия «победит»: чаще всего самая новая по ingested_at или с наибольшей версией.

Грабля: оконки нельзя использовать в WHERE напрямую:

-- так не работает
SELECT * FROM raw_events
WHERE ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at) = 1;

Только через CTE/подзапрос. Это типичный «вопрос с подвохом» на собесе.

Альтернатива на больших данных — QUALIFY (Snowflake/BigQuery/ClickHouse 23+):

SELECT * FROM raw_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC) = 1;

В Postgres QUALIFY нет — только CTE.

Running total и moving average

Running total (累計):

SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY event_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS lifetime_revenue

Безопасно, потому что явно ROWS.

Moving average за 7 дней (по строкам):

AVG(daily_amount) OVER (
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS ma_7d

Moving average по дате (с пропусками):

AVG(daily_amount) OVER (
    ORDER BY event_date
    RANGE BETWEEN INTERVAL '6 day' PRECEDING AND CURRENT ROW
) AS ma_7d_calendar

Разница: первый вариант берёт 7 строк независимо от пропусков в датах (если пропустили день — окно «съезжает»). Второй — ровно последние 7 календарных дней (если пропуск — посчитает по фактически имеющимся).

Уникальные пользователи в скользящем окне (28 дней) — частая задача. Postgres не поддерживает COUNT(DISTINCT) OVER:

-- так нельзя в Postgres
COUNT(DISTINCT user_id) OVER (
    ORDER BY day
    RANGE BETWEEN INTERVAL '27 day' PRECEDING AND CURRENT ROW
)

Workaround — коррелированный подзапрос или агрегация в CTE по дням и self-join:

SELECT
    d.day,
    (SELECT COUNT(DISTINCT e.user_id)
     FROM events e
     WHERE e.created_at >= d.day - INTERVAL '27 day'
       AND e.created_at <  d.day + INTERVAL '1 day') AS mau_28d
FROM (SELECT generate_series('2026-01-01'::DATE, '2026-05-01', '1 day') AS day) d;

В ClickHouse — uniqExact() OVER () или скользящий через arrayJoin.

Частые ошибки

Дефолтный RANGE на одинаковых ORDER BY значениях. Running total раздувается, когда в окне есть строки с одинаковым ключом сортировки. Всегда ROWS BETWEEN ....

LAST_VALUE без явного frame. Возвращает текущую строку, не последнюю в партиции. Явно ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Оконка в WHERE. Не работает в стандартном SQL. Только через CTE, подзапрос или QUALIFY (где поддерживается).

Использование RANK вместо ROW_NUMBER при дедупликации. RANK даёт ранг 1 нескольким строкам с одинаковым ORDER BY ключом — после WHERE rn = 1 останутся дубли. Для уникальности — только ROW_NUMBER.

COUNT(DISTINCT) в окне на Postgres. Не поддерживается. Подзапрос с GROUP BY по дате или CTE с pre-aggregation.

Переусложнённый ORDER BY в окне. Если PARTITION BY user_id ORDER BY event_date, event_id — лишние поля в ORDER BY делают сортировку дороже. Минимально необходимый ключ.

Связанные темы

• SQL для Data Engineer: собеседование
• Подготовка к собесу Data Engineer
• Оконные функции в SQL: гайд
• Задачи на оконные функции для собеса
• Window functions advanced

FAQ

Какая разница между PARTITION BY и GROUP BY?

GROUP BY сворачивает строки — на выходе одна строка на группу. PARTITION BY сохраняет все строки и считает агрегат внутри окна для каждой. Если нужен SUM по клиенту рядом с каждой покупкой — оконка. Если нужна одна строка на клиента — GROUP BY.

Можно ли использовать оконки в HAVING?

Нет. HAVING фильтрует уже агрегированные группы, оконки работают на уровне строк. Если нужно отфильтровать «оставить только тех, у кого ранг = 1» — оборачивать в CTE/подзапрос и WHERE rn = 1.

Что быстрее — оконка или подзапрос с MAX?

Чаще оконка: один проход по данным. Подзапрос с LEFT JOIN (SELECT user_id, MAX(date) FROM ... GROUP BY 1) — два прохода + join. Но на простых случаях разница в плане может быть в пользу подзапроса; смотри EXPLAIN.

Как считать процентили (percentile)?

В Postgres — PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY ...). В ClickHouse — quantile(0.5)(amount) OVER (...). Если нужен ровный квантиль из значений датасета — PERCENTILE_DISC. Для приблизительных на больших данных — quantileTDigest.

Можно ли вкладывать оконки?

Нет, нельзя писать SUM(SUM(x) OVER (...)) OVER (...). Если нужно — две оконки через CTE: первый CTE считает первую агрегацию, второй применяет вторую.

Это официальная информация?

Нет. Статья основана на стандарте SQL:2003 и документации Postgres / ClickHouse / Spark. Поведение RANGE с интервалами и QUALIFY зависит от движка.

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.