7-дневное скользящее среднее регистраций

Решение

Задача требует вычисления 7-дневного скользящего среднего (moving average) для ежедневных регистраций с использованием оконных функций SQL. Скользящее среднее — это среднее значение за последние N дней, включая текущий день.

Подход к решению

Оконная функция AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) вычисляет среднее за 7 дней (текущий + 6 предыдущих).

SQL-запрос

SELECT 
  date,
  sign_ups,
  ROUND(
    AVG(sign_ups) OVER (
      ORDER BY date 
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )::NUMERIC,
    2
  ) AS moving_avg_7d
FROM signups
ORDER BY date ASC;

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

1. Оконная функция AVG()

• Считает среднее значение sign_ups за последние 7 дней
• OVER (ORDER BY date ...) — определяет окно функции

2. Фрейм окна

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — включает 7 строк (текущая + 6 предыдущих)
• 6 PRECEDING — 6 строк перед текущей
• CURRENT ROW — текущая строка
• Итого = 7 дней

3. Обработка первых дней

• Для 2024-01-01 (день 1): берём только 1 доступный день
• Для 2024-01-02 (день 2): берём 2 доступных дня
• Для 2024-01-07 (день 7): берём 7 дней (полное окно)
• Это автоматически работает при наличии ROWS BETWEEN 6 PRECEDING

4. Округление

• ::NUMERIC — приводим к числовому типу для корректного деления
• ROUND(..., 2) — округляем до 2 знаков

5. Сортировка

• ORDER BY date ASC — выводим в хронологическом порядке

Пример расчёта

Предположим данные:

| date | sign_ups |
|------|----------|
| 2024-01-01 | 10 | → moving_avg = 10 (1 день)
| 2024-01-02 | 15 | → moving_avg = (10+15)/2 = 12.50 (2 дня)
| 2024-01-03 | 12 | → moving_avg = (10+15+12)/3 = 12.33 (3 дня)
| 2024-01-04 | 18 | → moving_avg = (10+15+12+18)/4 = 13.75 (4 дня)
| 2024-01-05 | 20 | → moving_avg = (10+15+12+18+20)/5 = 15.00 (5 дней)
| 2024-01-06 | 22 | → moving_avg = (10+15+12+18+20+22)/6 = 16.17 (6 дней)
| 2024-01-07 | 20 | → moving_avg = (10+15+12+18+20+22+20)/7 = 16.71 (7 дней - полное окно)
| 2024-01-08 | 25 | → moving_avg = (15+12+18+20+22+20+25)/7 = 18.86 (последние 7 дней)

Заметьте: с 2024-01-08 окно стало скользящим по полным 7 дням.

Альтернативные варианты

Вариант 1: С партиционированием (если есть категория)

SELECT 
  date,
  category,
  sign_ups,
  ROUND(
    AVG(sign_ups) OVER (
      PARTITION BY category
      ORDER BY date 
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )::NUMERIC,
    2
  ) AS moving_avg_7d
FROM signups
ORDER BY category, date;

Это полезно, если регистрации разбиты по источникам, каналам или регионам.

Вариант 2: С явным началом окна

SELECT 
  date,
  sign_ups,
  ROUND(
    AVG(sign_ups) OVER (
      ORDER BY date 
      ROWS BETWEEN 
        CASE WHEN ROW_NUMBER() OVER (ORDER BY date) < 7 
          THEN ROW_NUMBER() OVER (ORDER BY date) - 1 
          ELSE 6 
        END PRECEDING
      AND CURRENT ROW
    )::NUMERIC,
    2
  ) AS moving_avg_7d
FROM signups
ORDER BY date;

Этот вариант более сложный, но позволяет явно контролировать размер окна.

Важные моменты

✓ ROWS BETWEEN 6 PRECEDING — задаёт ровно 7 дней (текущий + 6 предыдущих)

✓ Обработка недостаточных дней — автоматически берёт доступные дни в начале периода

✓ ::NUMERIC в PostgreSQL — необходим для корректного деления, иначе может быть целочисленная арифметика

✓ Производительность — оконные функции оптимизированы для работы с большими объёмами данных

✓ Сравнение с GROUP BY — в отличие от GROUP BY, оконные функции сохраняют все строки исходной таблицы

✓ Пропущенные дни — если в данных есть пропуски (например, выходные), скользящее среднее будет вычисляться только по доступным дням в окне