Оптимизируем аналитические SQL запросы

Привет! Если ты читаешь эту статью, значит явно интересуешься как писать оптимальные SQL запросы для чтения данных из баз данных или аналитических хранилищ.

Это важный навык для аналитиков, инженеров, разработчиков, тестировщиков и всех кто пишет на SQL.

Здесь рассмотрим кейсы как писать SELECT запросы эффективно, без создания индексов.

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

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

Хост: 94.26.239.237
Порт: 5432
База данных: sandbox
Логин: sandbox_user
Пароль: uth3u74g2

Давай подумаем на что влияет то, как написан твой запрос.

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

База данных как твой телефон имеет ограниченные ресурсы: память хранения, оперативная память, ядра процессора. Неоптимальные запросы задействуют больше ресурсов для их выполнения, что может привести к зависаниям или даже падениям базы данных, что очень критично для бизнес-процессов. Также от ресурсов напрямую зависят затраты денег на них, поэтому все стремятся к оптимизации.

В нормальных компаниях принято писать запросы в едином стиле (style guide), чтобы дальше этот код можно было удобно поддерживать и развивать.

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

Давай разберём как писать оптимизированные запросы, а как делать не стоит.

Вместо привычной * стоит явно указывать только те столбцы, которые нужны из данных. Например, тебе для отчета необходимо выгружать заказы за сегодня, но при этом нужные только конкретный набор столбцов: id заказа, дата заказа, стоимость и кол-во продуктов.

Явное перечисление столбцов сократит затраченную память для считывания данных с жестких дисков и отображением этих данных тебе в пользовательском интерфейсе.

-- ТАК НЕ СТОИТ ПИСАТЬ ЕСЛИ НУЖНЫЕ НЕ ВСЕ СТОЛБЦЫ SELECT * FROM PUBLIC.ORDERS WHERE ORDER_DATE::DATE = CURRENT_DATE ; -- ПИШИ ТАК С ПЕРЕЧИСЛЕНИЕМ ЯВНО НУЖНЫХ СТОЛБЦОВ SELECT ID, ORDER_DATE, AMOUNT, QUANTITY FROM PUBLIC.ORDERS WHERE ORDER_DATE::DATE = CURRENT_DATE ;

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

Часто у аналитиков вижу ошибку, которую называю "фиктивной фильтрацией"

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

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

-- КАК ПИСАТЬ НЕ СТОИТ WITH orders_joined AS ( SELECT id, order_date, customer_id, order_type, amount * quantity * (1 - (coalesce(discount::numeric,0) / 100)) AS order_amount, customer_name, gender FROM public.orders LEFT JOIN public.customers USING (customer_id) ) SELECT customer_name, gender, min(order_date) AS first_order, max(order_date) AS last_order, count(DISTINCT id) AS order_count, sum(order_amount) AS overall_order_amount FROM orders_joined WHERE order_type = 'paid' --ФИЛЬТРАЦИЯ СТОИТ В ФИНАЛЬНОМ ЗАПРОСЕ ПОСЛЕ CTE AND order_date >= '2026-01-01' GROUP BY 1,2 ;

-- КАК ТОТ ЖЕ САМЫЙ ЗАПРОС НАПИСАТЬ ОПТИМАЛЬНО -- КАК МИНИМУМ ПЕРЕНЕСТИ ФИЛЬТР WHERE В САМ CTE, А ЛУЧШЕ НАПИСАТЬ ВСЕ В РАМКАХ ОДНОГО ЗАПРОСА В ДАННОМ СЛУЧАЕ SELECT customer_name, gender, min(order_date) AS first_order, max(order_date) AS last_order, count(DISTINCT id) AS order_count, sum(amount * quantity * (1 - (coalesce(discount::numeric,0) / 100))) AS overall_order_amount FROM public.orders LEFT JOIN public.customers USING (customer_id) WHERE order_type = 'paid' AND order_date >= '2026-01-01' GROUP BY 1,2 ;

Такой запрос в любой ситуации будет более оптимален, чем первый, потому что фильрация в явном виде будет выполняться раньше чем JOIN и другие преобразования.

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

НО часто вижу ситуации, когда пишут лишние подзапросы и CTE, в которых нет необходимости, т.к. их логику можно сразу учесть в рамках одного запроса.

Такой пример мы уже увидели в рамках рассмотрения предыдущего правила, но давай покажу еще пример. Напишем запрос, который собирает выборку клиентов с их контактными данными, у которых есть больше 1 заказа.

-- преобразуем данные клиентов WITH customer AS ( SELECT customer_id, split_part(customer_name, ' ', 1) AS first_name, split_part(customer_name, ' ', 2) AS last_name, CASE WHEN gender = 'm' THEN 'муж.' WHEN gender = 'f' THEN 'жен.' ELSE 'не определен' END AS gender, customer_phone, lower(customer_email) AS customer_email FROM public.customers ), -- считаем кол-во заказов по клиентам orders_agg AS ( SELECT customer_id, count(id) AS orders_count FROM public.orders GROUP BY customer_id ) -- соединяем данные клиентов с кол-вом заказов и оставляем только тех, у кого больше 1 заказа SELECT * FROM customer LEFT JOIN orders_agg USING (customer_id) WHERE orders_count > 1 ;

В данном случае 2 табличных выражения:

customer - в нем делается только преобразование полей

orders_agg - считаем кол-во заказов на клиента

Посмотрим как можно оптимизировать данный запрос и убрать лишний CTE.

-- считаем кол-во заказов по клиентам и оставляем сразу только тех, у кого больше 1 заказа WITH orders_agg AS ( SELECT customer_id, count(id) AS orders_count FROM public.orders GROUP BY customer_id HAVING count(id) > 1 -- СРАЗУ ФИЛЬТРУЕМ ) -- соединяем данные клиентов с кол-вом заказов через INNER JOIN, чтобы остались сразу только клиенты у кого больше 1 заказа SELECT customer_id, split_part(customer_name, ' ', 1) AS first_name, split_part(customer_name, ' ', 2) AS last_name, CASE WHEN gender = 'm' THEN 'муж.' WHEN gender = 'f' THEN 'жен.' ELSE 'не определен' END AS gender, customer_phone, lower(customer_email) AS customer_email, orders_count FROM public.customers JOIN orders_agg -- ПРИМЕНЯЕМ ВНУТРЕННИЙ JOIN КАК ФИЛЬТР ПРИ СОЕДИНЕНИИ ТАБЛИЦ USING (customer_id)

Можно полностью убрать CTE customer и вынести его логику в основной запрос.

Обрати также внимание, что я сразу добавил фильтрацию на кол-во заказов по клиенту в HAVING, чтобы в CTE orders_agg оставить сразу только нужных нам клиентов с их кол-вом заказов. Дальше я использую INNER JOIN чтобы конечный запрос оставил только нужную выборку клиентов, которая есть в orders_agg.

Обычно такое встречается, когда хотят соединить таблицу только на определенную выборку данных из другой таблицы.

Например, давай соберем детали по первым и последним заказам каждого клиента.

-- С ПОМОЩЬЮ ОКОННЫХ ФУНКЦИЙ В CTE РАЗМЕТИМ ПЕРВЫЙ И ПОСЛЕДНИЙ ЗАКАЗ КАЖДОГО КЛИЕНТА WITH orders_window AS ( SELECT customer_id, id, order_date, amount, quantity, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS order_first, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_last FROM public.orders ) -- СОЕДИНЯЕМ ТАБЛИЦУ КЛИЕНТОВ СО СТРОКАМИ ПЕРВОГО И ПОСЛЕДНЕГО ЗАКАЗА SELECT customer_name, ofirst.id AS first_order_id, ofirst.order_date AS first_order_date, ofirst.amount AS first_order_amount, ofirst.quantity AS first_order_quantity, olast.id AS last_order_id, olast.order_date AS last_order_date, olast.amount AS last_order_amount, olast.quantity AS last_order_quantity FROM customers c LEFT JOIN (SELECT * FROM orders_window WHERE order_first = 1) ofirst ON c.customer_id = ofirst.customer_id LEFT JOIN (SELECT * FROM orders_window WHERE order_last = 1) olast ON c.customer_id = olast.customer_id ;

Здесь видно, что в соединении прописаны подзапросы, которые выбирают первый заказ и последний заказ в разных выборках, чтобы соединить их с таблицей клиентов.

Давай избавимся от подзапросов и перепишем соединение просто с еще одним условием в блоке ON

WITH orders_window AS ( SELECT customer_id, id, order_date, amount, quantity, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS order_first, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_last FROM public.orders ) SELECT customer_name, ofirst.id AS first_order_id, ofirst.order_date AS first_order_date, ofirst.amount AS first_order_amount, ofirst.quantity AS first_order_quantity, olast.id AS last_order_id, olast.order_date AS last_order_date, olast.amount AS last_order_amount, olast.quantity AS last_order_quantity FROM customers c LEFT JOIN orders_window ofirst ON c.customer_id = ofirst.customer_id AND ofirst.order_first = 1 LEFT JOIN orders_window olast ON c.customer_id = olast.customer_id AND olast.order_last = 1

Как можно увидеть мы убрали подзапросы и добавили дополнительно условие в соединение.

Применяй временные таблицы для промежуточного хранения результатов

Временная таблица - инструмент для сохранения результатов запроса и переиспользования этих данных дальше в рамках одной сессии подключения.

Когда следует применять временные таблицы:

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

Например, мы хоти собрать отчет в таблице по кол-ву продаж с разной агрегацией (день/месяц/год) в разбивке по интерфейсу в рамках разных столбцов. В таком запросе придется обращаться к одним и тем же данным заказов несколько раз, чтобы агрегировать их на разном уровне и объединять через UNION.

WITH paid_orders AS ( SELECT id, date_part('year', order_date) AS order_year, date_trunc('month', order_date)::date AS order_month, order_date::date AS order_day, customer_id, item_id, lower(coalesce(channel, 'not defined')) AS channel, lower(coalesce(order_type, 'not defined')) AS order_type, lower(coalesce(interface, 'not defined')) AS interface, amount * quantity * (1 - (coalesce(discount::numeric,0) / 100)) AS order_amount FROM orders WHERE lower(order_type) = 'paid' ) SELECT order_year::varchar, count(id) FILTER (WHERE interface = 'web') AS orders_web, count(id) FILTER (WHERE interface = 'app') AS orders_app, count(id) FILTER (WHERE interface = 'not defined') AS orders_nf FROM paid_orders GROUP BY 1 UNION ALL SELECT order_month::varchar, count(id) FILTER (WHERE interface = 'web') AS orders_web, count(id) FILTER (WHERE interface = 'app') AS orders_app, count(id) FILTER (WHERE interface = 'not defined') AS orders_nf FROM paid_orders GROUP BY 1 UNION ALL SELECT order_day::varchar, count(id) FILTER (WHERE interface = 'web') AS orders_web, count(id) FILTER (WHERE interface = 'app') AS orders_app, count(id) FILTER (WHERE interface = 'not defined') AS orders_nf FROM paid_orders GROUP BY 1

В таком запросе данные из табличного выражения paid_orders применяются несколько раз.

Давай сохраним эти данные во временную таблицу один раз, чтобы обращение шло к ней.

CREATE TEMP TABLE paid_orders AS SELECT id, date_part('year', order_date) AS order_year, date_trunc('month', order_date)::date AS order_month, order_date::date AS order_day, customer_id, item_id, lower(coalesce(channel, 'not defined')) AS channel, lower(coalesce(order_type, 'not defined')) AS order_type, lower(coalesce(interface, 'not defined')) AS interface, amount * quantity * (1 - (coalesce(discount::numeric,0) / 100)) AS order_amount FROM orders WHERE lower(order_type) = 'paid' ; SELECT order_year::varchar, count(id) FILTER (WHERE interface = 'web') AS orders_web, count(id) FILTER (WHERE interface = 'app') AS orders_app, count(id) FILTER (WHERE interface = 'not defined') AS orders_nf FROM paid_orders GROUP BY 1 UNION ALL SELECT order_month::varchar, count(id) FILTER (WHERE interface = 'web') AS orders_web, count(id) FILTER (WHERE interface = 'app') AS orders_app, count(id) FILTER (WHERE interface = 'not defined') AS orders_nf FROM paid_orders GROUP BY 1 UNION ALL SELECT order_day::varchar, count(id) FILTER (WHERE interface = 'web') AS orders_web, count(id) FILTER (WHERE interface = 'app') AS orders_app, count(id) FILTER (WHERE interface = 'not defined') AS orders_nf FROM paid_orders GROUP BY 1

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

Часто в начале какой-либо аналитической задачи тебе сначала нужно "покрутить" данные.
- посмотреть их структуру
- оценить качество
- продумать логику их обработки и обогащения
- продумать методологию расчета метрики

И для этого проще и быстрей будет взять небольшую выборку нужных тебе данных, чтобы не обращаться сразу ко всему объему. Так получится и ускорить время выполнения запросов для ускорения работы, а также не нагружать базу данных.

-- сохраняем выборку данных во временную таблицу, ограничивая LIMIT -- можно также ограничивать специально доп. условиями через WHERE, иногда это удобней и правильней для задачи CREATE TEMP TABLE customers_with_phone_sample AS SELECT * FROM customers WHERE customer_phone IS NOT NULL LIMIT 1000

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

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

-- в данном случае выборки заранее будут разные по первому полю, поэтому можно смело применять UNION ALL SELECT order_year::varchar, count(id) FILTER (WHERE interface = 'web') AS orders_web, count(id) FILTER (WHERE interface = 'app') AS orders_app, count(id) FILTER (WHERE interface = 'not defined') AS orders_nf FROM paid_orders GROUP BY 1 UNION ALL SELECT order_month::varchar, count(id) FILTER (WHERE interface = 'web') AS orders_web, count(id) FILTER (WHERE interface = 'app') AS orders_app, count(id) FILTER (WHERE interface = 'not defined') AS orders_nf FROM paid_orders GROUP BY 1

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

-- пишем без DISTINCT если точно знаем, что значения столбца уникальны например при применении CONSTRAINT типа UNIQUE или PRIMARY KEY для этого поля SELECT COUNT(id) FROM orders

Твои запросы в песочнице выполняются довольно быстро за миллисекунды. Но в данном случае у нас

не так много преобразований данных, достаточно простые запросы
небольшие объёмы данных
база данных не загружена другими SELECT запросами и операциями INSERT/UPDATE/DELETE

На боевой базе с большими объёмами данных и комплексными SQL запросами на сотни и тысячи строк кода оптимизация является критически важным навыком.

Здесь мы разобрали правила оптимального формирования комплексных SELECT запросов, которые нужно применять каждому аналитику или специалисту, работающему с базами и хранилищами данных. Однако оптимизация на этом не заканчивается.

Что еще влияет на оптимизацию работы с данными в базе

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

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

🔹 обсудим как усилить твой навык SQL
🔹 найдем шаги развития в профессии конкретно для тебя
🔹 покажу материалы обучения моего курса Продвинутый SQL и автоматизация витрин данных
🔹 отвечу на любые вопросы в сфере аналитики/инженерии, обучения и карьеры

ЗАПИСАТЬСЯ НА КОНСУЛЬТАЦИЮ

Оптимизируем аналитические SQL запросы

Песочница базы данных для твоей практики

Почему важно оптимизировать SQL запросы

1. Скорость выполнения запроса

2. Затраты ресурсов базы

3. Чистота кода

4. Качество данных

Правила оптимизации SQL

Выбирай только нужные данные

Ранняя фильтрация - фильтруй данные в начале запроса, а не в конце

Избавляйся от лишних подзапросов и CTE

Избавляйся от подзапросов в JOIN когда это возможно

Для тестов выборку данных можешь ограничивать через LIMIT

Используй UNION ALL вместо UNION

Используй DISTINCT выборочно

Выводы