PrepBro
← Назад к вопросам

SQL: Скользящее среднее за 7 дней

2.0 Middle🔥 251 комментариев
#SQL и базы данных

Условие

У вас есть таблица daily_metrics:

  • date (date)
  • revenue (decimal)
  • orders_count (integer)

Посчитайте скользящее среднее выручки за последние 7 дней для каждой даты.

Ожидаемый результат:

Таблица с колонками:

  • date
  • revenue (выручка за день)
  • moving_avg_7d (скользящее среднее за 7 дней)

Подсказка:

Используйте оконные функции с ROWS BETWEEN.

Источник: типовая задача на собеседованиях аналитиков

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение: Скользящее среднее за 7 дней

SQL-запрос (базовый вариант)

SELECT
  date,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY date 
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2
  ) as moving_avg_7d
FROM daily_metrics
ORDER BY date;

Объяснение

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

AVG(revenue) OVER (
  ORDER BY date                          -- Сортируем по дате
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- Берём текущую и 6 предыдущих строк
)

Как это работает:

  • ORDER BY date — задаёт порядок обхода строк
  • ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — окно включает:
    • 6 предыдущих дней (PRECEDING)
    • Текущий день (CURRENT ROW)
    • Итого: 7 дней

Почему "6 PRECEDING"?

  • 6 дней до текущего дня
    • 1 текущий день
  • = 7 дней всего

Пример

Исходные данные:

date       | revenue | moving_avg_7d
-----------|---------|---------------
2024-01-01 | 1000    | 1000.00       (только 1 день)
2024-01-02 | 1200    | 1100.00       (2 дня)
2024-01-03 | 1100    | 1100.00       (3 дня)
2024-01-04 | 1300    | 1150.00       (4 дня)
2024-01-05 | 1400    | 1200.00       (5 дней)
2024-01-06 | 1350    | 1225.00       (6 дней)
2024-01-07 | 1500    | 1264.29       (7 дней) = (1000+1200+1100+1300+1400+1350+1500) / 7
2024-01-08 | 1450    | 1350.00       (7 дней) = (1200+1100+1300+1400+1350+1500+1450) / 7
2024-01-09 | 1600    | 1414.29       (7 дней) = (1100+1300+1400+1350+1500+1450+1600) / 7

Альтернативный синтаксис (дни, а не строки)

SELECT
  date,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY date 
      RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ), 2
  ) as moving_avg_7d
FROM daily_metrics
ORDER BY date;

Разница:

  • ROWS BETWEEN — берёт фиксированное количество строк (6 предыдущих + текущая)
  • RANGE BETWEEN — берёт все строки в диапазоне дат (для пропущенных дней)

Когда какой использовать:

  • Если в таблице есть данные для КАЖДОГО дня → используй ROWS
  • Если могут быть пропуски в датах → используй RANGE

Вариант с явным указанием границ окна

SELECT
  date,
  revenue,
  -- Текущее значение
  revenue as current_day,
  -- Скользящее среднее
  ROUND(
    AVG(revenue) OVER (
      ORDER BY date 
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2
  ) as moving_avg_7d,
  -- Минимум за 7 дней
  MIN(revenue) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as min_7d,
  -- Максимум за 7 дней
  MAX(revenue) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as max_7d
FROM daily_metrics
ORDER BY date;

Скользящее среднее по другим окнам

3-дневное:

AVG(revenue) OVER (
  ORDER BY date 
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

30-дневное:

AVG(revenue) OVER (
  ORDER BY date 
  ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
)

Бесконечное (от начала):

AVG(revenue) OVER (
  ORDER BY date 
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

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

SELECT
  date,
  revenue,
  orders_count,
  ROUND(AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) as moving_avg_7d_revenue,
  ROUND(AVG(orders_count) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) as moving_avg_7d_orders,
  -- Среднее значение заказа
  ROUND(
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) /
    NULLIF(AVG(orders_count) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 0)
  , 2) as avg_order_value_7d
FROM daily_metrics
ORDER BY date;

Оптимизация для больших таблиц

SELECT
  date,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY date NULLS LAST
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2
  ) as moving_avg_7d
FROM daily_metrics
WHERE date >= CURRENT_DATE - INTERVAL '1 year'  -- Последний год
ORDER BY date DESC;  -- Новые даты в начале

Распространённые ошибки

Ошибка 1: Забыли ORDER BY

-- ❌ Неправильно
AVG(revenue) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
-- Результат: непредсказуемо (порядок строк случайный)

-- ✅ Правильно
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Ошибка 2: 7 PRECEDING вместо 6

-- ❌ Неправильно (8 дней вместо 7)
ROWS BETWEEN 7 PRECEDING AND CURRENT ROW

-- ✅ Правильно
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

Ошибка 3: Забыли ROUND

-- ❌ Много знаков после запятой
AVG(revenue) OVER (...)

-- ✅ Читаемо
ROUND(AVG(revenue) OVER (...), 2)

Производительность

Для таблиц с миллионами строк:

-- Добавьте индекс
CREATE INDEX idx_daily_metrics_date ON daily_metrics(date);

-- Оконные функции работают быстро, так как база может использовать индекс

Оконные функции в большинстве случаев быстрее GROUP BY с JOIN, потому что не требуют повторного сканирования таблицы.

SQL: Скользящее среднее за 7 дней | PrepBro