Перейти до вмісту
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 і звіряю кількість рядків: якщо оптимізатор читає всю таблицю, індекс або фільтр ще не на місці. Якщо план змінився після правки, порівнюю два виводи поруч і записую різницю. Це займає хвилину.

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