Вы наверняка писали много бэкенд-кода только чтобы агрегировать данные, хотя сложный SQL запрос делает это в одном месте: загрузить строки, сгруппировать в памяти, отсортировать, выбрать верхний элемент. Часто правильный SQL-запрос делает всё это быстрее и в одном месте. Сегодня покажу задачу, которая сначала кажется запутанной, а в SQL решается изящно.
Задача
У интернет-магазина две таблицы:
- sales - продажи:
sale_id- уникальный идентификатор продажи;product_id- идентификатор товара;sale_date- дата продажи;amount- сумма продажи.
- products - каталог:
product_id- идентификатор товара;category- категория;price- цена.
Для каждой категории за последние шесть месяцев нужно вернуть:
- общее количество проданных единиц;
- суммарную сумму продаж;
- средний чек (среднюю сумму одной продажи);
- название самого продаваемого товара по количеству единиц.
Сам SQL-запрос
WITH product_sales AS (
SELECT
p.category,
s.product_id,
SUM(s.amount) AS total_sales,
COUNT(s.sale_id) AS total_transactions,
ROW_NUMBER() OVER (
PARTITION BY p.category
ORDER BY SUM(s.amount) DESC
) AS sales_rank
FROM sales s
JOIN products p ON s.product_id = p.product_id
WHERE s.sale_date >= NOW() - INTERVAL '6 months'
GROUP BY p.category, s.product_id
)
SELECT
ps.category,
SUM(ps.total_sales) AS total_category_sales,
SUM(ps.total_transactions) AS total_transactions,
ROUND(AVG(ps.total_sales::numeric), 2) AS avg_sale_per_product,
(
SELECT product_id
FROM product_sales
WHERE sales_rank = 1 AND category = ps.category
) AS most_popular_product
FROM product_sales ps
GROUP BY ps.category;Как это работает
Синтаксис оконных функций разобран в документации PostgreSQL; та же идея есть в MySQL 8, SQL Server и Oracle.
- WITH product_sales собирает временный набор: сумму продаж и число транзакций по каждому товару, а также ранг внутри категории через оконную функцию
ROW_NUMBER(). - Основной запрос агрегирует этот набор по категориям: общая сумма, общее число транзакций, среднее по товару и товар с первым рангом из коррелированного подзапроса.
Две честные оговорки
Запрос близок к исходному примеру, и у него есть две шероховатости, о которых стоит знать:
most_popular_productвозвращает ID товара, а не название - в реальном отчёте понадобится ещё одно соединение сproducts.AVG(ps.total_sales)- среднее по товарам, а не средний чек по транзакции. Если нужен настоящий средний чек, разделите сумму категории на число транзакций.
Обе правки - в одну строку, и они показывают, зачем изящный запрос стоит перечитать перед выкаткой.
Почему SQL лучше бэкенд-циклов
- Производительность. Базы данных оптимизированы для агрегации, фильтрации и ранжирования больших объёмов на уровне движка. Бэкенд-код редко это повторяет.
- Меньше кода. Загрузка, фильтрация, группировка, ранжирование - один запрос вместо десятков строк.
- Читаемость.
JOIN,GROUP BY, оконные функции и подзапросы выражают логику компактно. - Масштабируемость. Нужно двенадцать месяцев вместо шести или дополнительный фильтр? Меняется одно условие, остальной конвейер остаётся.
SQL - не устаревший навык. Это по-прежнему кратчайший путь от вопроса к данным до правильного ответа.
Вопросы про SQL
Когда SQL не тот инструмент? Когда правило требует внешнего сервиса, сессии пользователя или долгой задачи. Агрегацию держите в базе, решения - в коде.
Индексы всё ещё важны? Больше, чем раньше. Оконная функция по таблице без индекса - это медленная сортировка с лишними шагами.
Запрос переносим? Идея да, синтаксис нет. Оконные функции есть в MySQL 8, SQL Server и Oracle, но рамки и порядок null отличаются.
Когда сложный SQL запрос - лишний
Сложный SQL запрос выигрывает, когда данные уже в базе, а форма результата стабильна. Он проигрывает, когда правила меняются каждую неделю или дежурный не читает запрос в два ночи. Справочник MySQL по CTE - честная отправная точка.
Запрос, который никто не может прочитать, живёт до первого дежурства.
Ещё по теме: Архитектура ПО: шесть паттернов и как выбрать и Важность правильного нейминга в коде.