Что такое Window Functions в SQL и как с их помощью найти 3 последних сообщения для каждого пользователя в одном запросе?
## Что такое Window Functions в SQL
Оконные функции (Window Functions) — это мощный инструмент SQL, который позволяет выполнять вычисления над набором строк, связанных с текущей строкой, без группировки и потери исходных данных. В отличие от агрегатных функций (SUM, COUNT, AVG), оконные функции не сворачивают строки в одну — каждая строка остаётся в результирующем наборе, но получает дополнительное вычисленное значение.
Оконные функции появились в стандарте SQL:2003 и поддерживаются в PostgreSQL, MySQL 8+, SQL Server, Oracle и других современных СУБД.
### Синтаксис оконной функции
sql
ФУНКЦИЯ() OVER (
PARTITION BY колонка_группировки
ORDER BY колонка_сортировки
ROWS/RANGE BETWEEN …
)
— **PARTITION BY** — разбивает данные на независимые группы (окна), аналог GROUP BY, но без свёртки строк.
— **ORDER BY** — задаёт порядок строк внутри каждого окна.
— **ROWS/RANGE** — дополнительно ограничивает рамку окна.
### Основные оконные функции
— **ROW_NUMBER()** — присваивает уникальный порядковый номер каждой строке внутри окна.
— **RANK()** — ранжирует строки, пропуская номера при одинаковых значениях.
— **DENSE_RANK()** — ранжирует без пропуска номеров.
— **LAG() / LEAD()** — обращаются к предыдущей или следующей строке.
— **SUM(), AVG(), COUNT()** — агрегаты в оконном режиме.
### Задача: найти 3 последних сообщения для каждого пользователя
Предположим, есть таблица `messages`:
| id | user_id | message | created_at |
|—-|———|———|————|
| 1 | 1 | Привет | 2024-01-01 |
| 2 | 1 | Как дела? | 2024-01-02 |
| 3 | 2 | Текст | 2024-01-03 |
Решение через ROW_NUMBER():
sql
SELECT *
FROM (
SELECT
id,
user_id,
message,
created_at,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC
) AS rn
FROM messages
) ranked
WHERE rn <= 3;
**Как это работает:**
1. Внутренний запрос присваивает каждому сообщению номер `rn` внутри группы пользователя, начиная с 1 для самого нового.
2. Внешний запрос фильтрует только строки с `rn <= 3` — то есть три последних сообщения.
### Почему не использовать подзапрос с LIMIT?
Простой `LIMIT` работает на весь результат запроса, а не для каждой группы. Оконные функции решают эту задачу элегантно и в один проход по данным, что значительно эффективнее.
### Советы по производительности
— Создайте составной индекс по `(user_id, created_at DESC)` — это ускорит сортировку внутри окон.
— В PostgreSQL можно использовать `FETCH FIRST` вместо подзапроса через `LATERAL JOIN`.
— Для очень больших таблиц рассмотрите материализованные CTE.
Оконные функции — незаменимый инструмент для аналитических запросов, рейтингов, скользящих средних и задач типа «топ-N на группу».