Базы данных
SQL: оконные функции, CTE, рекурсия
Запрос «топ-3 продукта по каждой категории» без оконных функций требует коррелированного подзапроса с O(n²) сложностью - при миллионе строк это минуты ожидания. С `ROW_NUMBER() OVER(PARTITION BY category ORDER BY revenue DESC)` - один проход по данным, секунды.
- **Аналитика e-commerce:** скользящее среднее продаж, сравнение с предыдущим периодом через LAG() - оконные функции в каждом BI-дашборде
- **Оргструктура:** `WITH RECURSIVE` для построения дерева подчинённости, расчёта бюджетов по иерархии
- **Рекомендации:** LATERAL JOIN для топ-N на группу - следующий просмотренный товар, последние заказы пользователя
- **SRE метрики:** PERCENTILE_CONT для p95/p99 latency, FILTER для подсчёта ошибок по типам в одном запросе
Оконные функции
Запрос «топ-3 продукта по каждой категории» без оконных функций требует коррелированного подзапроса или самосоединения с O(n²) сложностью. С `ROW_NUMBER() OVER(PARTITION BY ...)` это один проход по данным.
Оконная функция вычисляет результат для каждой строки, **не схлопывая** их в группы - в отличие от `GROUP BY`. Строки остаются в результате, к ним добавляются дополнительные вычисленные значения.
LAG и LEAD позволяют обращаться к предыдущей и следующей строке в рамках окна - незаменимы для анализа временных рядов.
**Синтаксис OVER():** `функция() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...)`. Все три части опциональны. `OVER()` без аргументов - всё множество строк как одно окно.
Три продавца продали на 500, 500, 300. RANK() даст им номера:
CTE: именованные подзапросы
Сложный запрос из вложенных подзапросов читается снизу вверх и «наизнанку». `WITH` позволяет дать подзапросу имя и обращаться к нему как к временной таблице - запрос читается сверху вниз в порядке выполнения.
Несколько CTE в одном запросе - каждый может ссылаться на предыдущий. Это позволяет строить пошаговые преобразования данных.
**Materialized vs не-materialized:** в PostgreSQL CTE по умолчанию вычисляется один раз и кешируется (materialized). Если CTE используется много раз, это экономит ресурсы. Если оптимизатор мог бы встроить его в основной запрос - используйте `NOT MATERIALIZED` (PostgreSQL 12+).
CTE `WITH stats AS (SELECT ...)` используется в запросе дважды. PostgreSQL вычислит подзапрос:
Рекурсивные CTE
Дерево категорий или оргструктура компании - иерархические данные, хранящиеся в таблице с `parent_id`. Получить всех потомков узла через обычный JOIN невозможно без заранее известной глубины. `WITH RECURSIVE` решает это в одном запросе.
**Бесконечная рекурсия:** если в данных есть цикл (`A → B → A`), `WITH RECURSIVE` зациклится. Защита: добавить счётчик глубины `WHERE depth < N` или использовать массив пути и проверять наличие текущего id через `id != ANY(path)`.
В `WITH RECURSIVE` секция `UNION ALL` содержит JOIN CTE с основной таблицей. Что произойдёт если этот JOIN не вернёт строк на очередной итерации?
LATERAL JOIN: коррелированный подзапрос в FROM
Задача: для каждого пользователя получить его 3 последних заказа. `GROUP BY` не подходит - нужны строки, а не агрегат. Подзапрос в `SELECT` возвращает одно значение, не несколько строк. `LATERAL JOIN` решает это: подзапрос в `FROM` может ссылаться на колонки из предыдущих таблиц.
Ключевое отличие от коррелированного подзапроса в `SELECT`: LATERAL возвращает **множество строк**, а не одно значение. Планировщик PostgreSQL оптимизирует его как nested loop с index scan по `user_id`.
| Подход | Возвращает | Строк на пользователя | Производительность |
|---|---|---|---|
| Подзапрос в SELECT | Одно значение | 1 | O(n) подзапросов |
| JOIN + ROW_NUMBER | Строки | N | Один проход, хорошо |
| LATERAL JOIN | Строки с LIMIT | N (с ограничением) | Nested loop + index |
Чем `LEFT JOIN LATERAL (...) ON true` принципиально отличается от обычного подзапроса в SELECT?
Продвинутая агрегация
Стандартные `COUNT`, `SUM`, `AVG` покрывают 80% задач. Для остальных 20% - `FILTER`, массивные агрегаты, статистические функции и условная агрегация.
**FILTER vs CASE WHEN внутри агрегата:** `SUM(CASE WHEN ... THEN amount ELSE 0 END)` работает везде, но `SUM(amount) FILTER (WHERE ...)` читается чище, и в PostgreSQL чуть быстрее - планировщик оптимизирует его отдельно.
AVG() показывает типичное значение метрики производительности
AVG() сильно искажается выбросами. P95/P99 и медиана точнее описывают реальный пользовательский опыт
1000 запросов по 10ms и 1 запрос по 10000ms дают AVG=19.8ms, но p99=10000ms. AVG скрывает проблему, перцентиль показывает
`PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY response_time)` вычисляет:
SQL Advanced: ключевые идеи
- Оконные функции (`OVER`) вычисляют по группе строк, не схлопывая их - `ROW_NUMBER`, `RANK`, `LAG`/`LEAD`, скользящие агрегаты
- CTE (`WITH`) дают имена подзапросам и делают запрос читаемым сверху вниз; несколько CTE могут ссылаться друг на друга
- `WITH RECURSIVE` обходит иерархические структуры через базовый случай + `UNION ALL` + рекурсивный шаг до пустого результата
- LATERAL JOIN возвращает несколько строк на каждую строку внешнего запроса - идеален для топ-N на группу
Связанные темы
Продвинутый SQL строится на фундаменте реляционной модели и оптимизаторе запросов:
- SQL Basics — Фундамент: SELECT, JOIN, GROUP BY, подзапросы
- Query Optimization — EXPLAIN ANALYZE для оконных функций и LATERAL
- Индексы PostgreSQL — Индексы ускоряют PARTITION BY и ORDER BY в оконных функциях
Вопросы для размышления
- Чем оконная функция принципиально отличается от GROUP BY, и когда нельзя заменить одно другим?
- В каких ситуациях рекурсивный CTE предпочтительнее хранения глубины/пути в отдельной таблице?
- Почему медиана и перцентили точнее среднего для измерения производительности систем?