Что такое оконные функции?

Question

IT Interview Practice · Accepted Answer

**Оконные функции** выполняют вычисления над набором строк ("окном") **связанных с текущей строкой**, *без их объединения в одну выходную строку* (в отличие от агрегатных функций с `GROUP BY`). Они позволяют выполнять мощные аналитические запросы — накопительные суммы, рейтинги, сравнения с другими строками — сохраняя при этом каждую строку в результате.

## Ключевое отличие от GROUP BY

```text
GROUP BY      → collapses groups into ONE row each (you lose individual rows)
Window function → computes across related rows but KEEPS every row
→ You get per-row detail AND aggregate/ranking info together.
```

## Функции ранжирования

```sql
SELECT
  name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,     -- unique sequential rank
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,  -- rank within dept
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
```

Предложение `OVER (...)` определяет окно. **`PARTITION BY`** делит строки на группы (как GROUP BY, но для каждой строки), а **`ORDER BY`** упорядочивает строки внутри каждого раздела. `ROW_NUMBER`/`RANK`/`DENSE_RANK` присваивают ранги.

## Агрегатные оконные функции (накопительные суммы, агрегаты по группам)

```sql
SELECT
  date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,            -- cumulative sum
  AVG(amount) OVER (PARTITION BY category) AS category_avg,      -- avg per category, kept per row
  amount - AVG(amount) OVER (PARTITION BY category) AS diff_from_avg
FROM transactions;
```

Агрегаты в качестве оконных функций вычисляют накопительные суммы, скользящие средние или агрегаты по разделам, сохраняя каждую строку — это невозможно только с GROUP BY.

## LAG/LEAD — сравнение с другими строками

```sql
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,             -- the previous row's value
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth,        -- month-over-month change
  LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;
```

**`LAG`/`LEAD`** получают значения из предыдущих/следующих строк — идеальны для сравнения между периодами (сравнение месяц к месяцу и т. д.).

## Почему это важно

Оконные функции — это мощная, продвинутая функция SQL, необходимая для **аналитических запросов**, и понимание их — это ценное знание уровня senior для анализа данных, отчетности и сложного запросов к БД.

Их определяющая возможность — выполнение вычислений над связанными строками **при сохранении каждой строки** (в отличие от GROUP BY, который объединяет строки) — позволяет использовать целый класс аналитики, который иначе сложен или невозможен: **ранжирование** (`ROW_NUMBER`/`RANK`/`DENSE_RANK` — поиск топ-N по группам, присвоение рангов), **накопительные суммы и скользящие средние** (кумулятивные суммы, агрегаты по разделам, отображаемые рядом с каждой строкой), и **сравнение строк между собой** (`LAG`/`LEAD` для анализа между периодами, например сравнение месяц к месяцу).

Это обычные, ценные аналитические задачи (рейтинги, накопительные метрики, анализ тенденций, сравнение каждой строки со средним показателем её группы), которые оконные функции элегантно решают в одном запросе, тогда как старый SQL требовал сложные самосоединения или подзапросы.

Понимание синтаксиса `OVER (PARTITION BY ... ORDER BY ...)` (определение окна), функций ранжирования, агрегатных оконных функций (накопительные суммы, агрегаты по разделам) и `LAG`/`LEAD` (доступ к другим строкам) охватывает мощный аналитический инструментарий.

Поскольку анализ данных и сложная отчетность — это важные применения SQL, а оконные функции — это современный, мощный способ выражения ранжирования, текущих расчётов и сравнений строк (значительно более мощный и элегантный, чем альтернативы), овладение оконными функциями — это ценное, часто применяемое знание для аналитического SQL, которое отличает разработчиков, способных писать сложные запросы к данным — признак продвинутого мастерства SQL и частая тема в ролях, ориентированных на данные, и на собеседованиях.

Ключевое отличие от GROUP BY

Функции ранжирования

Агрегатные оконные функции (накопительные суммы, агрегаты по группам)

LAG/LEAD — сравнение с другими строками

Почему это важно

Похожие вопросы