SQL: Скользящее среднее дохода за 3 месяца

Скользящее среднее дохода за 3 месяца

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

Рассчитать скользящее среднее (moving average) дохода за текущий месяц и два предыдущих месяца (окно размером 3 месяца). Это позволяет сгладить сезонные колебания и увидеть тренд.

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

SELECT 
    EXTRACT(YEAR FROM date) AS year,
    EXTRACT(MONTH FROM date) AS month,
    SUM(revenue) AS monthly_revenue,
    AVG(SUM(revenue)) OVER (
        ORDER BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3months
FROM transactions
GROUP BY 
    EXTRACT(YEAR FROM date),
    EXTRACT(MONTH FROM date)
ORDER BY year, month;

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

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

GROUP BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date)
SUM(revenue) AS monthly_revenue

Сначала вычисляем общий доход за каждый месяц.

2. Оконная функция AVG() OVER:

AVG(SUM(revenue)) OVER (
    ORDER BY year, month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3months

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — окно из 3 строк:

  • 2 месяца назад
  • 1 месяц назад
  • Текущий месяц

• ORDER BY year, month — упорядочивает строки по времени

• AVG() вычисляет среднее значение в окне

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

WITH monthly_revenue AS (
    SELECT 
        EXTRACT(YEAR FROM date) AS year,
        EXTRACT(MONTH FROM date) AS month,
        SUM(revenue) AS monthly_rev
    FROM transactions
    GROUP BY year, month
)
SELECT 
    year,
    month,
    monthly_rev,
    ROUND(
        AVG(monthly_rev) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_3months
FROM monthly_revenue
ORDER BY year, month;

Вариант без агрегирования (дневные данные)

Если нужно скользящее среднее по дням:

SELECT 
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3days
FROM transactions
ORDER BY date;

Вариант со всеми деталями

WITH monthly_data AS (
    SELECT 
        DATE_TRUNC(month, date)::date AS month_start,
        EXTRACT(YEAR FROM date) AS year,
        EXTRACT(MONTH FROM date) AS month,
        SUM(revenue) AS monthly_revenue,
        COUNT(*) AS transaction_count
    FROM transactions
    GROUP BY DATE_TRUNC(month, date), year, month
)
SELECT 
    month_start,
    year,
    month,
    monthly_revenue,
    transaction_count,
    ROUND(
        AVG(monthly_revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_3months,
    ROUND(
        (monthly_revenue - AVG(monthly_revenue) OVER (
            ORDER BY year, month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        )), 2
    ) AS deviation_from_avg
FROM monthly_data
ORDER BY year, month;

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

Интерпретация

• Месяц 1-2: неполное окно (меньше 3 месяцев доступно)
• Месяц 3+: полное окно из 3 месяцев
• Скользящее среднее сглаживает выбросы — помогает увидеть реальный тренд

Другие варианты окон

Последние 6 месяцев:

AVG(revenue) OVER (
    ORDER BY year, month
    ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
)

Предыдущие + текущий месяц (симметричное окно):

AVG(revenue) OVER (
    ORDER BY year, month
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)  -- окно из 3 строк, центрированное на текущей

Все предыдущие + текущий (нарастающее):

AVG(revenue) OVER (
    ORDER BY year, month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Оптимизация

Для больших таблиц:

CREATE INDEX idx_transactions_date ON transactions(date);

Это ускорит GROUP BY по датам.

Практическое применение

• Финансы: анализ трендов в доходе, выявление аномалий
• E-commerce: прогнозирование продаж
• Маркетинг: оценка эффективности кампаний
• Операции: планирование ресурсов