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

Скользящее Среднее за 3 месяца: Решение с Оконными Функциями

Это классическое задание по работе с временными рядами в SQL. Оконные функции (Window Functions) — идеальный инструмент для этого.

1. Базовое Скользящее Среднее за 3 месяца

SELECT
    DATE_TRUNC('month', date)::DATE AS month,
    SUM(income) AS monthly_income,
    AVG(SUM(income)) OVER (
        ORDER BY DATE_TRUNC('month', date)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM transactions
GROUP BY DATE_TRUNC('month', date)
ORDER BY month;

Результат:

month      | monthly_income | moving_avg_3m
2024-01-01 | 10000.00       | 10000.00
2024-02-01 | 15000.00       | 12500.00
2024-03-01 | 12000.00       | 12333.33
2024-04-01 | 18000.00       | 15000.00
2024-05-01 | 14000.00       | 14666.67

2. С Обработкой Граничных Случаев (NULLs для недостаточных данных)

SELECT
    DATE_TRUNC('month', date)::DATE AS month,
    SUM(income) AS monthly_income,
    CASE
        WHEN ROW_NUMBER() OVER (ORDER BY DATE_TRUNC('month', date)) < 3
        THEN NULL
        ELSE AVG(SUM(income)) OVER (
            ORDER BY DATE_TRUNC('month', date)
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        )
    END AS moving_avg_3m_strict
FROM transactions
GROUP BY DATE_TRUNC('month', date)
ORDER BY month;

Результат (только полные 3-месячные окна):

month      | monthly_income | moving_avg_3m_strict
2024-01-01 | 10000.00       | NULL
2024-02-01 | 15000.00       | NULL
2024-03-01 | 12000.00       | 12333.33
2024-04-01 | 18000.00       | 15000.00
2024-05-01 | 14000.00       | 14666.67

3. Альтернативный Подход с RANGE и INTERVAL

Для использования фактических дат вместо количества строк:

SELECT
    DATE_TRUNC('month', date)::DATE AS month,
    SUM(income) AS monthly_income,
    AVG(monthly_totals.income) AS moving_avg_3m
FROM (
    SELECT
        DATE_TRUNC('month', date)::DATE AS month,
        SUM(income) AS income
    FROM transactions
    GROUP BY DATE_TRUNC('month', date)
) AS monthly_totals
INNER JOIN (
    SELECT DATE_TRUNC('month', date)::DATE AS month
    FROM transactions
    GROUP BY DATE_TRUNC('month', date)
) AS months ON monthly_totals.month >= months.month - INTERVAL '2 months'
    AND monthly_totals.month <= months.month
GROUP BY months.month
ORDER BY months.month;

4. Нарастающий Итог (Cumulative Sum)

SELECT
    DATE_TRUNC('month', date)::DATE AS month,
    SUM(income) AS monthly_income,
    SUM(SUM(income)) OVER (
        ORDER BY DATE_TRUNC('month', date)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum
FROM transactions
GROUP BY DATE_TRUNC('month', date)
ORDER BY month;

Результат:

month      | monthly_income | cumulative_sum
2024-01-01 | 10000.00       | 10000.00
2024-02-01 | 15000.00       | 25000.00
2024-03-01 | 12000.00       | 37000.00
2024-04-01 | 18000.00       | 55000.00
2024-05-01 | 14000.00       | 69000.00

5. Полное Решение со Всеми Метриками

WITH monthly_data AS (
    SELECT
        DATE_TRUNC('month', date)::DATE AS month,
        SUM(income) AS monthly_income,
        ROW_NUMBER() OVER (ORDER BY DATE_TRUNC('month', date)) AS month_num
    FROM transactions
    GROUP BY DATE_TRUNC('month', date)
)
SELECT
    month,
    monthly_income,
    -- Скользящее среднее за 3 месяца (включая текущий)
    ROUND(
        AVG(monthly_income) OVER (
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        )::NUMERIC, 2
    ) AS moving_avg_3m,
    -- Скользящее среднее (только если есть 3 полных месяца)
    CASE
        WHEN month_num < 3 THEN NULL
        ELSE ROUND(
            AVG(monthly_income) OVER (
                ORDER BY month
                ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
            )::NUMERIC, 2
        )
    END AS moving_avg_3m_strict,
    -- Нарастающий итог
    ROUND(
        SUM(monthly_income) OVER (
            ORDER BY month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )::NUMERIC, 2
    ) AS cumulative_sum,
    -- Темп роста к предыдущему месяцу (%)
    ROUND(
        ((monthly_income - LAG(monthly_income) OVER (ORDER BY month))
            / LAG(monthly_income) OVER (ORDER BY month) * 100)::NUMERIC, 2
    ) AS growth_pct
FROM monthly_data
ORDER BY month;

6. Обработка Граничных Случаев

# Вариант 1: Использование NULLS для первых периодов
# CASE WHEN month_num >= 3 THEN moving_avg ELSE NULL END

# Вариант 2: Расширяющееся окно (начинается с 1 месяца)
# ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

# Вариант 3: Заполнение данных (forward fill / backward fill)
WITH monthly_data AS (...)
SELECT
    month,
    monthly_income,
    COALESCE(
        NULLIF(moving_avg_3m_strict, NULL),
        moving_avg_3m_with_available_months
    ) AS moving_avg_final
FROM ...

7. Оптимизация для Больших Данных

-- Если таблица очень большая, предварительно агрегируй
CREATE INDEX idx_transactions_date ON transactions(date);

WITH monthly_aggregated AS (
    SELECT
        DATE_TRUNC('month', date)::DATE AS month,
        SUM(income) AS total
    FROM transactions
    WHERE date >= CURRENT_DATE - INTERVAL '1 year'  -- Фильтруй если нужно
    GROUP BY 1
)
SELECT
    month,
    total,
    AVG(total) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM monthly_aggregated
ORDER BY month;

8. Практические Советы

Оконные функции для анализа временных рядов:

1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — последние 3 значения
2. RANGE BETWEEN INTERVAL '2 months' PRECEDING AND CURRENT ROW — последние 2 календарных месяца
3. UNBOUNDED PRECEDING — от начала таблицы (для cumulative sum)
4. LAG/LEAD — доступ к предыдущим/следующим значениям
5. ROW_NUMBER() / RANK() — нумерация строк для фильтрации

Типичные ошибки:

• Забыть GROUP BY перед оконной функцией
• Использовать ROWS вместо RANGE для дат
• Забыть ORDER BY в OVER()
• Включать NULL значения в SUM/AVG

Этот подход работает для любых временных рядов: доход, транзакции, метрики, события и т.д.