Глубина SQL: один запрос вместо бэкенд-сервиса

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

Задача

У интернет-магазина две таблицы:

1. sales - продажи:
   • sale_id - уникальный идентификатор продажи;
   • product_id - идентификатор товара;
   • sale_date - дата продажи;
   • amount - сумма продажи.
2. 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.

1. WITH product_sales собирает временный набор: сумму продаж и число транзакций по каждому товару, а также ранг внутри категории через оконную функцию ROW_NUMBER().
2. Основной запрос агрегирует этот набор по категориям: общая сумма, общее число транзакций, среднее по товару и товар с первым рангом из коррелированного подзапроса.

Две честные оговорки

Запрос близок к исходному примеру, и у него есть две шероховатости, о которых стоит знать:

• most_popular_product возвращает ID товара, а не название - в реальном отчёте понадобится ещё одно соединение с products.
• AVG(ps.total_sales) - среднее по товарам, а не средний чек по транзакции. Если нужен настоящий средний чек, разделите сумму категории на число транзакций.

Обе правки - в одну строку, и они показывают, зачем изящный запрос стоит перечитать перед выкаткой.

Почему SQL лучше бэкенд-циклов

1. Производительность. Базы данных оптимизированы для агрегации, фильтрации и ранжирования больших объёмов на уровне движка. Бэкенд-код редко это повторяет.
2. Меньше кода. Загрузка, фильтрация, группировка, ранжирование - один запрос вместо десятков строк.
3. Читаемость. JOIN, GROUP BY, оконные функции и подзапросы выражают логику компактно.
4. Масштабируемость. Нужно двенадцать месяцев вместо шести или дополнительный фильтр? Меняется одно условие, остальной конвейер остаётся.

SQL - не устаревший навык. Это по-прежнему кратчайший путь от вопроса к данным до правильного ответа.

Вопросы про SQL

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

Индексы всё ещё важны? Больше, чем раньше. Оконная функция по таблице без индекса - это медленная сортировка с лишними шагами.

Запрос переносим? Идея да, синтаксис нет. Оконные функции есть в MySQL 8, SQL Server и Oracle, но рамки и порядок null отличаются.

Когда сложный SQL запрос - лишний

Сложный SQL запрос выигрывает, когда данные уже в базе, а форма результата стабильна. Он проигрывает, когда правила меняются каждую неделю или дежурный не читает запрос в два ночи. Справочник MySQL по CTE - честная отправная точка.

Запрос, который никто не может прочитать, живёт до первого дежурства.

Ещё по теме: Архитектура ПО: шесть паттернов и как выбрать и Важность правильного нейминга в коде.