Глибина 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 - чесна відправна точка.

Ще за темою: Архітектура ПЗ: шість патернів і як обрати і Важливість правильного неймінгу в коді.