Глубина SQL, или вместо горы кода на бэкенде…

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

Задача: «Отслеживание продаж и популярности товаров с временными ограничениями»

Представьте, что у нас есть интернет-магазин с двумя таблицами:

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

Задача: Нужно составить SQL-запрос, который вернёт для каждой категории товаров следующую информацию за последние 6 месяцев:

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

Решение

Вот наш 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_transaction,
    (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;

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

WITH product_sales — временная таблица, которая собирает данные по категориям и товарам. Она считает сумму продаж и количество транзакций для каждого товара. В дополнение, с помощью оконной функции ROW_NUMBER(), мы ранжируем товары внутри каждой категории по объему продаж.
Основной запрос — извлекает обобщенные данные для каждой категории:
- Суммарная сумма продаж для категории.
- Общее количество транзакций для категории.
- Средний чек, рассчитанный как среднее значение суммы каждой продажи.
- Самый популярный товар (с помощью подзапроса, который находит товар с наибольшими продажами).

Почему это лучше, чем писать много кода на бэкенде?

Производительность: Базы данных SQL специально оптимизированы для работы с большими объемами данных. Они эффективно агрегируют, фильтруют и ранжируют данные на уровне ядра базы. Бэкенд, написанный на любом языке программирования, не всегда сможет работать с той же скоростью. В SQL запросы и операции проходят через тщательно оптимизированные алгоритмы, что сводит к минимуму задержки.
Меньше кода на бэкенде: Представьте себе, сколько кода пришлось бы писать для решения этой задачи на бэкенде! Надо было бы сначала загрузить данные, потом фильтровать, группировать, агрегировать и, наконец, ранжировать результаты. В SQL же всё это делается одним элегантным запросом.
Читаемость: Правильно написанный SQL-запрос говорит сам за себя. С помощью различных операций вроде JOIN, GROUP BY, оконных функций и подзапросов можно выразить сложную логику в сжатом и ясном виде. А вот в бэкенд-коде такая логика может занимать десятки строк, что усложняет сопровождение и отладку.
Масштабируемость: SQL-запросы легко адаптировать к изменениям в данных. Нужно увеличить период анализа до 12 месяцев или добавить новые условия фильтрации? Внесли одно изменение в запрос — и готово! А в коде это может повлечь за собой изменение логики работы с массивами, оптимизацию циклов, работу с коллекциями и прочие дополнительные задачи.