Перейти к содержимому
Leonidov.DEV

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

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

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

Задача

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

  1. sales - продажи:
    • sale_id - уникальный идентификатор продажи;
    • product_id - идентификатор товара;
    • sale_date - дата продажи;
    • amount - сумма продажи.
  2. products - каталог:
    • product_id - идентификатор товара;
    • category - категория;
    • name - название товара;
    • price - цена.

Для каждой категории за последние шесть месяцев нужно вернуть:

  • общее количество проданных единиц;
  • суммарную сумму продаж;
  • средний чек (среднюю сумму одной продажи);
  • название самого продаваемого товара по количеству единиц.

Сам SQL-запрос

WITH product_sales AS (
    SELECT
        p.category,
        p.product_id,
        p.name,
        SUM(s.amount) AS total_sales,
        COUNT(*) AS total_transactions,
        ROW_NUMBER() OVER (
            PARTITION BY p.category
            ORDER BY COUNT(*) 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, p.product_id, p.name
)
SELECT
    ps.category,
    SUM(ps.total_transactions) AS items_sold,
    SUM(ps.total_sales) AS total_category_sales,
    ROUND(SUM(ps.total_sales) / NULLIF(SUM(ps.total_transactions), 0), 2) AS avg_check,
    MAX(CASE WHEN ps.sales_rank = 1 THEN ps.name END) AS best_selling_product
FROM product_sales ps
GROUP BY ps.category;

Как это работает

Синтаксис оконных функций разобран в документации PostgreSQL; та же идея есть в MySQL 8, SQL Server и Oracle.

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

Одна оговорка про ничью

Ранжирование по числу продаж может дать ничью: два товара с одинаковым количеством получат ранги 1 и 2 произвольно. Добавьте разрешение ничьей - ORDER BY COUNT(*) DESC, p.name - прежде чем отдавать отчёт.

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

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

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

Вопросы про SQL

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

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

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

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

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

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

Перед выкладкой смотрю план запроса через EXPLAIN и сверяю число строк: если оптимизатор читает всю таблицу, индекс или фильтр ещё не на месте. Если план изменился после правки, сравниваю два вывода рядом.

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