Оконные функции и CTE в MySQL и PostgreSQL: кто считает аналитику лучше

Обе СУБД сегодня умеют считать ранги, накопительные итоги и скользящие средние прямо в запросе — но PostgreSQL получил этот инструментарий на десятилетие раньше MySQL и до сих пор предлагает больше возможностей для сложной аналитики.

Что вообще такое оконные функции и зачем они нужны?

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

Это избавляет от громоздких подзапросов и самосоединений таблиц, которые раньше приходилось писать для похожих задач. Запрос становится короче, читаемее и обычно выполняется быстрее, потому что СУБД сама оптимизирует расчёт внутри одного прохода по данным.

Когда оконные функции появились в MySQL?

Долгое время это было главным аргументом в пользу PostgreSQL для аналитических нагрузок: MySQL просто не умел считать оконные функции. Ситуация изменилась с выходом MySQL 8.0 в две тысячи восемнадцатом году — тогда разработчики добавили полноценную поддержку синтаксиса OVER(), ранжирующих функций RANK, DENSE_RANK, ROW_NUMBER и агрегатов с окнами.

Интересный факт: стандарт SQL:2003 описал оконные функции ещё в начале двухтысячных, и PostgreSQL реализовал их в версии 8.4, вышедшей в две тысячи девятом году. То есть MySQL отставал от стандарта и от конкурента почти на десять лет — и всё это время разработчикам приходилось выкручиваться через переменные сессии и хитрые подзапросы.

Чем набор оконных функций в PostgreSQL богаче?

Формально базовый синтаксис в обеих СУБД похож — есть PARTITION BY для деления на группы, ORDER BY для сортировки внутри окна и сами функции вроде SUM, AVG, LAG, LEAD. Но PostgreSQL идёт дальше: поддерживает произвольные рамки окна с указанием диапазона по значению (RANGE), а не только по строкам, умеет работать с исключением текущей строки из расчёта (EXCLUDE) и предлагает больше встроенных статистических функций.

MySQL реализовал основной набор, которого хватает для девяноста процентов практических задач: ранжирование, накопительные суммы, сравнение с соседними строками. Но для узкоспециализированной статистики — вроде процентилей и сложных рамок окна — PostgreSQL остаётся более гибким инструментом.

Что такое CTE и зачем они нужны в запросах?

Общие табличные выражения, или CTE (Common Table Expressions), задаются конструкцией WITH и позволяют вынести часть логики запроса во временную именованную выборку, на которую потом можно ссылаться, как на обычную таблицу. Это удобно, когда запрос состоит из нескольких логических шагов: сначала отфильтровать данные, потом сгруппировать, потом присоединить к другой выборке.

Без CTE такие многоступенчатые запросы превращаются в вложенные подзапросы, которые сложно читать и ещё сложнее отлаживать. С CTE запрос превращается в понятную последовательность шагов — почти как код программы, разбитый на функции. Обе СУБД поддерживают базовый синтаксис WITH, и здесь принципиальной разницы для новичка нет.

А рекурсивные CTE — тут есть разница?

Рекурсивные CTE нужны для работы с иерархическими данными: деревьями категорий, оргструктурами, цепочками подчинённости, где каждая запись может ссылаться на родителя. Классический пример — вывести всех сотрудников с их менеджерами на любом уровне вложенности одним запросом.

Обе СУБД умеют выполнять рекурсивные CTE через конструкцию WITH RECURSIVE. Но у PostgreSQL исторически более зрелая реализация: есть встроенная защита от бесконечных циклов через ключевое слово CYCLE, более гибкий контроль над условиями остановки рекурсии и в целом более предсказуемое поведение на больших иерархиях. В MySQL поддержка рекурсивных CTE появилась одновременно с оконными функциями, в восьмой версии, и по возможностям пока чуть скромнее.

Какие практические задачи решают эти инструменты?

На реальных проектах оконные функции и CTE закрывают очень частые бизнес-задачи:

• рейтинги и топы — например, топ-5 товаров по продажам в каждой категории;
• накопительные итоги — сколько денег заработано с начала месяца на каждый день;
• сравнение соседних периодов — рост или падение выручки относительно прошлого месяца;
• дедупликация — поиск и удаление повторяющихся записей с сохранением только одной по заданному правилу;
• построение отчётов по иерархиям — от структуры каталога товаров до дерева согласований документов.

Раньше все эти задачи решались либо на стороне приложения, либо громоздкими SQL-конструкциями. Сейчас правильно написанный запрос с оконными функциями и CTE делает то же самое за один проход по базе, разгружая и сервер приложений, и сеть.

Есть ли ощутимая разница в скорости выполнения?

На небольших и средних объёмах данных разница между СУБД в скорости расчёта оконных функций и CTE обычно малозаметна для конечного пользователя. Гораздо сильнее на производительность влияют индексы, объём данных, попадающих под условие WHERE, и то, насколько сервер настроен под конкретную нагрузку.

Но есть нюанс: обычные, нерекурсивные CTE в PostgreSQL до недавнего времени материализовались — то есть база сначала полностью считала промежуточный результат, а потом уже с ним работала. Это иногда мешало оптимизатору строить эффективный план запроса. В современных версиях PostgreSQL это поведение исправили, и оптимизатор сам решает, встраивать CTE в основной запрос или считать его отдельно. В MySQL похожего механизма материализации по умолчанию нет, и запрос чаще оптимизируется как единое целое.

Что в итоге выбрать для проекта с аналитикой?

Если проект строится на PostgreSQL или только выбирает СУБД и предполагает сложную аналитику — построение отчётов, работу с иерархиями, статистические расчёты прямо в базе, — PostgreSQL даст больше готовых инструментов и меньше поводов писать логику на стороне приложения.

Если проект уже живёт на MySQL и не требует экзотических рамок окна или сверхсложной рекурсии, современная восьмая версия закрывает практически все типовые аналитические сценарии, и переезжать на другую СУБД ради этого не имеет смысла. Важно только следить за версией: старые версии MySQL, где оконные функции ещё не появились, для аналитики подходят плохо.

Тем, кто не хочет вручную заниматься настройкой версии сервера, индексами и мониторингом производительности запросов, стоит присмотреться к готовым облачным решениям: сервис баз данных «Клауд АйСи» разворачивает актуальные версии MySQL и PostgreSQL с нужными параметрами и берёт на себя техническое обслуживание, оставляя команде разработки время на сами запросы и аналитику, а не на администрирование сервера.