Перейти на PRO

Что такое оконные функции в SQL и какие их основные типы вы знаете?

Тип вопроса: Базы данных и SQL Вероятность: 10% Категория: QA Тестировщик

Ответ

Оконные функции (Window Functions) выполняют вычисления над набором строк, связанных с текущей строкой («окном»), без сворачивания результатов в одну строку, в отличие от GROUP BY.

Ключевые компоненты:

Функция используется с предложением OVER(), которое определяет окно:

  • PARTITION BY — разделяет строки на группы (аналогично GROUP BY).
  • ORDER BY — задает порядок строк внутри окна.
  • ROWS/RANGE BETWEEN — определяет границы окна (например, UNBOUNDED PRECEDING).

Основные типы оконных функций:

  1. Агрегатные: SUM(), AVG(), COUNT(), MIN(), MAX().

    SELECT 
    employee_id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;

  2. Ранжирующие: Присваивают порядковый номер или ранг.

    • ROW_NUMBER() — уникальный номер строки в рамках окна.
    • RANK() и DENSE_RANK() — ранг с пропусками и без пропусков при одинаковых значениях.
    • NTILE(n) — разбивает строки на n групп. 
      SELECT 
name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) as position,
RANK() OVER (ORDER BY score DESC) as rank_with_gaps
FROM contestants;

  3. Функции смещения (Value Functions): Обращаются к данным других строк в окне.

    • LAG(column, offset) — значение из строки, находящейся выше.
    • LEAD(column, offset) — значение из строки, находящейся ниже.
    • FIRST_VALUE(column), LAST_VALUE(column) — первое/последнее значение в окне. 
      SELECT 
date,
revenue,
LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue,
revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_growth
FROM sales;

Преимущество: Позволяют выполнять сложные аналитические запросы (скользящие средние, накопительные суммы, сравнение со смещением) без использования коррелированных подзапросов, что часто эффективнее.

Ответ 18+ 🔞

Давай разберём эту штуку, которая всех так пугает — оконные функции. Представь, что ты смотришь на таблицу через окошко, но не просто так, а с умом. Это не GROUP BY, который всё скукоживает в одну строку, а гораздо хитрее.

Из чего эта магия состоит?

Всё крутится вокруг предложения OVER(). Это как сказать: «Слушай, SQL, я сейчас буду работать не со всей таблицей сразу, а с вот этим кусочком — окном».

  • PARTITION BY — это как разбить всех на группы. Отдел продаж — в одну кучку, маркетинг — в другую. Аналогия с GROUP BY тут почти полная, только строки не схлопываются.
  • ORDER BY — внутри каждой кучки устанавливает порядок. Кто по зарплате, кто по дате — как скажешь.
  • ROWS/RANGE BETWEEN — вот это уже мощь! Ты можешь указать границы окна. Например, «бери все строки от начала раздела до текущей» или «три строки до и две после». Пиздец как гибко.

Какие бывают эти функции? Три основных банды:

  1. Агрегатные бандиты. Те же самые SUM(), AVG(), но они не сворачивают результат! Они как бы «размазывают» его по всем строкам окна.

    SELECT 
    employee_id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;

    Смысл: каждый работяга видит не только свою зарплату, но и среднюю по своему отделу, чтобы знать, на кого обижаться.

  2. Ранжирующие выебонщики. Их задача — всех пронумеровать и построить по ранжиру.

    • ROW_NUMBER() — просто даёт уникальный номер по порядку. Первый, второй, третий... Даже если значения одинаковые, номера будут разными.
    • RANK() и DENSE_RANK() — а вот эти уже с приколом. Если два человека набрали одинаковые очки, они получат одинаковый ранг. RANK() потом сделает прыжок (были 1 и 1, следующий будет 3), а DENSE_RANK() — нет (следующий будет 2). Разницу чувствуешь?
    • NTILE(n) — делит всех на n примерно равных групп. Типа «разбей этих лохов на 4 квартиля». 
      SELECT 
name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) as position, -- Просто место
RANK() OVER (ORDER BY score DESC) as rank_with_gaps -- Место с учётом ничьих
FROM contestants;

  3. Функции смещения, или «загляни к соседу». Самые, блядь, полезные на практике.

    • LAG(column, offset) — смотрит на строку выше. «А какая выручка была вчера?»
    • LEAD(column, offset) — смотрит на строку ниже. «А какая будет завтра?»
    • FIRST_VALUE(column), LAST_VALUE(column) — берут первое или последнее значение в окне. Осторожно с LAST_VALUE по умолчанию — читай документацию, а то обосрёшься. 
      SELECT 
date,
revenue,
LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue, -- Вчерашняя выручка
revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_growth -- На сколько выросло/упало
FROM sales;

В чём, сука, кайф? Раньше для таких штук приходилось городить адские коррелированные подзапросы, которые тормозили как черепаха в патоках. Оконные функции делают это нативно, быстро и элегантно. Скользящие средние, накопительные итоги, сравнение с предыдущим периодом — всё это теперь делается в пару строчек. Ёпта, красота!

© ХакСобесов, 2026
support@hacksobesov.com