Простой

11 мин

7

Привет, Хабр! Я — Дмитрий Селищев, в компании «Синимекс» совмещаю роли руководителя подразделения и разработчика баз данных. В этом материале хочу поделиться историей о том, как простые, но не всегда очевидные приемы помогают кардинально ускорить SQL‑запросы. Мы поговорим о стандартных конструкциях CASE и FILTER, которые позволяют писать более чистый код и, что важнее, на порядки сокращать время построения сложных отчетов. Давайте на живых примерах посмотрим, как это работает.

Агрегатные функции: вспоминаем основы

Представьте: нужно посчитать количество записей, которые удовлетворяют какому‑то условию. Задача, казалось бы, из учебника. Для этого в любой СУБД, поддерживающей стандарт SQL аж с версии 89-го года, есть специальный инструмент — агрегатные функции. Агрегатная функция — это операция, которая берёт набор значений и возвращает одно‑единственное итоговое значение. Самый известный представитель этого семейства — COUNT.

Хотим общее число заказов? Пожалуйста:

SELECT COUNT(*) FROM orders;

А сколько из них отменено?

SELECT COUNT(*) FROM orders WHERE status = 'CANCELED';

Или, например, сколько «зависло» в обработке дольше семи дней?

SELECT COUNT(*) FROM orders WHERE dt_finish - dt_start > 7;

Вроде бы всё просто. 

Не лишним будет напомнить базовое правило стандарта SQL: агрегатные функции, применяемые к столбцу или выражению, автоматически исключают NULL из вычислений. Поэтому count(status) вернёт нам только количество записей с заполненным, то есть не‑NULL, статусом. В то же время count(*) честно посчитает все строки, попавшие под условие отбора WHERE. Раньше в запросах можно было встретить вариант count(1), который на старых версиях СУБД мог дать выигрыш в производительности, но современные оптимизаторы обрабатывают обе эти конструкции практически одинаково. Так что count(*) — это простой и понятный стандарт де‑факто.

Подзапросы: просто, но неэффективно

С одиночными метриками разобрались. Но в реальной жизни аналитик редко просит что‑то одно. Ему, как правило, нужно всё и сразу в одной табличке: и общее число заказов, и отменённые, и «зависшие» — чтобы потом, например, посчитать их долю от общего числа. Собирать это на стороне приложения, отправив в базу три отдельных запроса, — моветон. Иногда это всё требуется получить одним махом на стороне базы данных (как часть более крупного отчёта), в одной строке.

Первое, что приходит на ум, — подзапросы, то есть SELECT‑запросы, вложенные в основной оператор:

SELECT
    COUNT(*) AS cnt_common,
    (SELECT COUNT(*) FROM orders WHERE status = 'CANCELED') AS cnt_canceled,
    (SELECT COUNT(*) FROM orders WHERE dt_finish - dt_start > 7) AS cnt_longwait
FROM orders;

На первый взгляд, цель достигнута. Но только на первый. Дьявол, как всегда, кроется в плане запроса (query plan) — той последовательности шагов, которую оптимизатор выстраивает для получения результата. В нашем случае база будет вынуждена трижды обратиться к таблице orders (рис. 1).

Даже если для каждого подзапроса будут использованы индексы, это всё равно будут три отдельные операции сканирования (Index Scan или Seq Scan) одной и той же таблицы для получения трёх разных метрик (рис. 2).

Второй минус — читаемость и поддержка. Представьте, что аналитик просит добавить фильтр по городу или дате. Это условие придётся, как под копирку, вставить в каждый подзапрос. Забыли про один — получили неверные цифры. Код разрастается и превращается в минное поле для ошибок. 

Но настоящее веселье начинается, когда нужна группировка — скажем, те же метрики, но в разрезе по каждой точке продаж. Нам придётся превратить наши простые подзапросы в коррелированные (correlated subqueries). Это такой вид подзапроса, который для своей работы использует значения из внешнего запроса. Говоря по‑простому, он выполняется для каждой строки внешнего запроса, что создаёт серьёзную нагрузку на базу данных, даже если оптимизатор пытается минимизировать издержки. Для корректного вычисления агрегата нам необходимо продублировать в каждый подзапрос условие отбора строк из основного запроса, а также добавить в условие поле, по которому происходит группировка. Выглядит это и для разработчика чудовищно (рис. 3):

И для базы не менее страшно (рис. 4):

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

Решение №1: условная агрегация с CASE

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

На помощь приходит старый добрый оператор CASE. Идея проста: вместо того чтобы заставлять базу данных многократно бегать по таблице для каждого условия, мы пройдём по ней один раз, а внутри агрегатной функции с помощью CASE сами решим, какие строки считать, а какие — нет. Для этого мы создадим так называемое вычисляемое поле (calculated field) прямо внутри агрегации.

Выглядеть это будет так:

SELECT
    pos,
    COUNT(*) AS cnt_common_pos,
    COUNT(CASE WHEN status = 'CANCELED' THEN 1 END) AS cnt_canceled,
    COUNT(CASE WHEN dt_finish - dt_start > 7 THEN 1 END) AS cnt_longwait
FROM orders
WHERE user_id = 123 AND dt_start BETWEEN '2026-05-18' AND '2026-05-18'
GROUP BY pos;

Конструкция CASE WHEN (условие) THEN 1 END вернёт нам единицу, если условие истинно, и NULL в противном случае (поскольку ветка ELSE не указана, по умолчанию возвращается NULL). А как мы помним из первой части, COUNT элегантно игнорирует NULL‑значения. В итоге он посчитает только то, что нам нужно.

Что при этом происходит под капотом? Исполнительный механизм СУБД (query executor) теперь работает куда эффективнее. Вместо нескольких проходов по таблице orders он считывает её всего один раз. Все строки, прошедшие через основной WHERE, попадают в единый этап агрегации, а оператор CASE уже на лету разбирает их по нужным «корзинам» (рис. 5).

Кстати, без малейшего ущерба для логики COUNT можно заменить на SUM. В этом случае мы просим базу суммировать единицы для подходящих строк и нули для всех остальных: SUM(CASE WHEN status = 'CANCELED' THEN 1 ELSE 0 END). Результат будет тот же. Более того, этот подход позволяет считать не только количество, но и, например, сумму отменённых заказов, подставив вместо единицы поле с суммой.

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

Решение 2: элегантный FILTER (SQL:2003)

Второй способ избавиться от подзапросов — для ценителей синтаксического сахара. Он более специфичен, но и более изящен. Речь о конструкции FILTER, которая появилась в стандарте SQL:2003. 

Идея FILTER в том, чтобы дать агрегатной функции свой собственный, локальный WHERE. Вместо того чтобы городить огород из CASE, мы прямо указываем, по какому условию считать строки для каждой конкретной агрегации.

Давайте перепишем наш многострадальный запрос с его помощью:

SELECT
    pos,
    COUNT(*) AS cnt_common_pos,
    COUNT(*) FILTER (WHERE status = 'CANCELED') AS cnt_canceled,
    COUNT(*) FILTER (WHERE dt_finish - dt_start > 7) AS cnt_longwait
FROM orders
WHERE user_id = 123 AND dt_start BETWEEN '2026-05-18' AND '2026-05-18'
GROUP BY pos;

Согласитесь, читается это почти как обычный текст: «посчитай всё, отфильтровав по статусу 'CANCELED'». Намерение выражено куда яснее, чем в конструкции с CASE, где мы, по сути, считали единицы, которые сами же и генерировали.

Что до производительности, то здесь сюрпризов нет. План выполнения такого запроса будет аналогичен плану для запроса с CASE. Исполнитель запросов всё так же единожды просканирует таблицу и на лету вычислит агрегаты. Считается, что в некоторых СУБД FILTER может дать незначительный выигрыш в производительности за счет более явного синтаксиса, но на практике эта разница чаще всего минимальна и не является решающим фактором при выборе.

Конструкция FILTER давно перестала быть экзотикой и закреплена в большинстве актуальных СУБД (PostgreSQL 9.4+, SQLite 3.30+, Oracle 21c+ и др.). Для современных проектов это естественный и читаемый способ записи условных агрегаций. В то же время, проверенный годами CASE остаётся незаменимым, если вам нужна максимальная переносимость кода между разными, в том числе устаревшими, версиями СУБД.

Итак, с агрегатами для подсчёта разных метрик в одной таблице мы разобрались. Это, так сказать, задачи на плоскости. А теперь давайте нырнём в третье измерение и посмотрим на случай похитрее.

Когда задача сложнее: self-join для разворота строк

Иногда в отчёте для данных одной строки необходимо отразить сведения, которые лежат в других строках той же таблицы. Этот приём называют «разворотом» или «пивотингом» (от англ. pivot) данных. Классический пример — история статусов заказа. Предположим, у нас в таблице order_hist ведётся история статуса заказа, где каждая запись — это отдельный этап жизни заказа: «в сборке», «в доставке», «в ожидании самовывоза», с указанием времени начала и окончания этого статуса.

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

Первая мысль, которая приходит в голову, — это соединение таблицы самой с собой, или self‑join. Идея в том, чтобы взять основную таблицу и «приклеить» её к себе же столько раз, сколько статусов нам нужно вытащить в отдельные колонки:

SELECT
    ord_main.order_id,
    ord_collecting.dt_finish - ord_collecting.dt_start AS collecting_time,
    ord_shipping.dt_finish - ord_shipping.dt_start AS shipping_time,
    ord_pickup.dt_finish - ord_pickup.dt_start AS pickup_time
FROM
    order_hist AS ord_main
LEFT JOIN
    order_hist AS ord_collecting ON (ord_collecting.order_id = ord_main.order_id AND ord_collecting.status = 'COLLECTING')
LEFT JOIN
    order_hist AS ord_shipping ON (ord_shipping.order_id = ord_main.order_id AND ord_shipping.status = 'SHIPPING')
LEFT JOIN
    order_hist AS ord_pickup ON (ord_pickup.order_id = ord_main.order_id AND ord_pickup.status = 'PICKUP')
WHERE ... ; -- здесь могут быть условия отбора заказов из ord_main

Мы используем LEFT JOIN, чтобы не потерять заказы, у которых мог отсутствовать какой‑то из статусов. В условии ON мы связываем строки по order_id и тут же отбираем запись с нужным статусом. Просто, как топор. Но, как и в случае с топором, этим инструментом легко отрубить себе ногу, если говорить о производительности. 

Для выполнения такого запроса оптимизатору СУБД придётся сначала построить и оценить громоздкое множество возможных планов. И даже выбрав оптимальный план, базе придётся выполнить три операции чтения и соединения. Пусть даже чтение данных будет быстрым за счёт индексов, каждое дополнительное соединение усложняет общую работу. Вы с таким же успехом можете отправить трёх разных курьеров за тремя посылками на один и тот же склад. Каждый будет заново искать адрес, парковаться и оформлять пропуск, хотя один толковый сотрудник мог бы прийти и забрать всё сразу. Здесь та же история: каждое соединение — это дополнительные накладные расходы, которые усложняют план выполнения запроса и могут привести к его неэффективности (рис. 6).

Кстати, для полноты картины: если статус ещё не завершён (то есть dt_finish равен NULL), для корректного расчёта его текущей длительности мы бы использовали конструкцию coalesce(ord_collecting.dt_finish, now()), подставляя текущее время. Но для чистоты примера мы этот момент опустим.

В итоге мы получаем рабочий, но громоздкий и неэффективный запрос. А если статусов не три, а десять? Конструкция превратится в нечитаемого монстра, а время выполнения улетит в космос. К счастью, мы можем применить уже знакомый нам трюк с агрегацией, чтобы заставить базу работать умнее, а не усерднее.

Агрегация с CTE: элегантная альтернатива множественным JOIN-ам

Так как же нам одолеть этого многоголового монстра из JOIN‑ов? Вместо того чтобы заставлять базу данных сшивать таблицу саму с собой, мы можем пойти по уже знакомому пути: пройтись по таблице один раз и «разложить» нужные нам данные по колонкам с помощью агрегации.

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

Давайте перепишем наш запрос, предварительно отобрав все строки с нужными статусами, а затем используя агрегатные функции для «схлопывания» строк с одинаковым order_id.

WITH filtered_order_hist AS (
    SELECT 
        order_id, 
        status,
        dt_finish - dt_start AS status_time
    FROM order_hist
    WHERE status IN ('COLLECTING', 'SHIPPING', 'PICKUP')
)
SELECT 
    order_id,
    SUM(status_time) FILTER (WHERE status = 'COLLECTING') AS collecting_time,
    SUM(status_time) FILTER (WHERE status = 'SHIPPING') AS shipping_time,
    SUM(status_time) FILTER (WHERE status = 'PICKUP') AS pickup_time
FROM filtered_order_hist
GROUP BY order_id;

Что здесь происходит? Сначала в блоке WITH мы создаём наш именованный подзапрос filtered_order_hist. В него мы отбираем только те строки из order_hist, которые нам интересны, и сразу же вычисляем длительность каждого этапа (рис. 7). А затем, в основном запросе, мы делаем то, что уже умеем: группируем по order_id и с помощью SUM и FILTER «собираем» нашу итоговую строку.

Здесь можно безопасно применять суммирование, так как условное выражение внутри SUM вычисляет длительность только для одной строки заказа с нужным статусом, поэтому в результате группировки возвращается именно это единственное значение (рис. 8).

Такой запрос не только легче читается и поддерживается. Главное, что оптимизатору СУБД теперь не приходится блуждать по лабиринту возможных планов и оценивать десятки вариантов с повторными чтениями таблиц. А исполнитель запросов вместо нескольких чтений и соединений выполнит всего одно сканирование и одну агрегацию. Как я уже упоминал выше, с каждым дополнительным JOIN сложность плана растет, и время выполнения возрастает многократно. На реальном проекте похожий запрос с пятью self‑join'ами после такого преобразования ускорился с 2 минут до 1,4 секунды.

Заключение

Надеюсь, мне удалось показать, как замена множественных JOIN‑ов и подзапросов на условную агрегацию способна ускорить отчеты в десятки раз, превратив минуты ожидания в секунды. Этот подход не только повышает производительность, но и делает код значительно чище и проще для понимания. 

Интересно было бы услышать в комментариях, чем вы предпочитаете пользоваться: универсальным и портируемым CASE или более современным и лаконичным FILTER? 

Если статья оказалась для вас полезной, подписывайтесь на наш хаб, чтобы не пропустить новые материалы от наших инженеров. 

Спасибо за внимание и удачи в оптимизации!