SQL: Нарастающий итог продаж по месяцам

Нарастающий итог продаж по месяцам

Условие задачи

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

Основной SQL запрос

SELECT 
    EXTRACT(YEAR FROM sale_date) AS year,
    EXTRACT(MONTH FROM sale_date) AS month,
    group_name,
    SUM(quantity) AS monthly_quantity,
    SUM(SUM(quantity)) OVER (
        PARTITION BY EXTRACT(YEAR FROM sale_date), group_name
        ORDER BY EXTRACT(MONTH FROM sale_date)
    ) AS cumulative_quantity
FROM sales
GROUP BY 
    EXTRACT(YEAR FROM sale_date),
    EXTRACT(MONTH FROM sale_date),
    group_name
ORDER BY year, group_name, month;

Пошаговое объяснение

1. Выделение года и месяца:

EXTRACT(YEAR FROM sale_date) AS year,
EXTRACT(MONTH FROM sale_date) AS month

2. Агрегирование по месяцам и группам:

GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date), group_name

Это дает нам сумму количества за каждый месяц для каждой группы товара.

3. Оконная функция для нарастающего итога:

SUM(SUM(quantity)) OVER (
    PARTITION BY EXTRACT(YEAR FROM sale_date), group_name
    ORDER BY EXTRACT(MONTH FROM sale_date)
) AS cumulative_quantity

• PARTITION BY разделяет данные по годам и группам (для каждой группы в каждом году свой отсчет)
• ORDER BY MONTH определяет порядок накопления
• SUM() OVER вычисляет нарастающую сумму

Альтернативный вариант с CTE

WITH monthly_sales AS (
    SELECT 
        EXTRACT(YEAR FROM sale_date) AS year,
        EXTRACT(MONTH FROM sale_date) AS month,
        group_name,
        SUM(quantity) AS monthly_qty
    FROM sales
    GROUP BY year, month, group_name
)
SELECT 
    year,
    month,
    group_name,
    monthly_qty,
    SUM(monthly_qty) OVER (
        PARTITION BY year, group_name
        ORDER BY month
    ) AS cumulative_qty
FROM monthly_sales
ORDER BY year, group_name, month;

Преимущества CTE:

• Код более читаемый
• Проще дебажить промежуточные результаты
• Лучше для сложных запросов

Пример результата

Ключевые навыки

• EXTRACT() — выделение компонентов даты
• GROUP BY — группировка по нескольким полям
• SUM(SUM()) — агрегирование результата агрегирования
• Window Function (OVER) — вычисление по упорядоченным строкам
• PARTITION BY — логическое разделение данных
• ORDER BY в OVER — направление накопления

Оптимизация

Для больших объемов данных:

CREATE INDEX idx_sales_date_group ON sales(sale_date, group_name);

Это значительно ускорит GROUP BY и PARTITION BY операции.