Coding Interview Prep · Урок

Скользящие средние в скользящем окне

Рассчитывайте скользящие средние за N периодов с BETWEEN PRECEDING и CURRENT ROW.

«Скользящие средние в скользящем окне» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Вопрос о скользящем среднем

Аналитикам постоянно задают такой вопрос: «Вычислите скользящее среднее выручки за 7 дней». Скользящее среднее сглаживает зашумлённые ежедневные данные, усредняя каждую точку с её ближайшими соседями.

Ответ на уровне собеседования — оконная функция AVG с явно заданной скользящей рамкой. Главный навык здесь — выбрать границы рамки так, чтобы окно правильно перемещалось вдоль порядка.

Основной шаблон

Скользящее среднее задаётся так: AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW). Рамка перемещается: в каждой строке она охватывает текущую строку и предыдущие n строк.

Для окна в 7 дней по ежедневным строкам нужно вернуться на 6 строк и добавить текущую, получив всего 7 строк. Ошибка на единицу — самая распространённая ошибка, которую выявляют интервьюеры.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

Правильный подсчёт размера окна

Размер окна равен preceding + 1 (единица добавляется за текущую строку). Поэтому:

окно из 3 строк: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
окно из 7 строк: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
окно из 30 строк: ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

На собеседовании проговорите эту арифметику вслух, чтобы показать комиссии, что вы действуете осознанно, а не угадываете.

Центрированные и концевые окна

Пример выше — это концевое среднее: оно смотрит только назад, поэтому подходит для причинно-следственного анализа и безопасно для панелей мониторинга прогнозов.

Центрированное среднее смотрит в обе стороны, например ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING для центрированного окна из 7 строк. Центрированные окна сглаживают данные более симметрично, но для самых свежих строк их нельзя вычислить в реальном времени. Укажите, какой вариант нужен для конкретного сценария.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7
FROM daily_sales;

Эффект на границе окна

В самом начале данных полного окна ещё нет. Поэтому для первой строки семидневного концевого среднего доступна только одна строка, и AVG усредняет лишь это значение.

Это означает, что в первых строках отображается среднее «периода разгона», рассчитанное по меньшему числу строк. На собеседовании могут спросить, как с этим поступить. Есть два варианта: принять неполное окно или скрыть ранние строки, требуя полного количества строк.

Скрытие неполных окон

Если по требованиям бизнеса нужно получать NULL, пока полное окно недоступно, используйте COUNT(*) с той же рамкой и заменяйте короткие окна пустым значением с помощью выражения CASE.

Это демонстрирует аккуратный подход и показывает, что вы подумали о корректности на границах — деталь, которую многие кандидаты упускают.

SELECT
  sale_date,
  CASE WHEN COUNT(*) OVER w = 7
       THEN AVG(amount) OVER w
  END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Повторное использование рамки с WINDOW

В предыдущем запросе использовалась именованная конструкция WINDOW. Если одна и та же рамка встречается несколько раз, её определение один раз как WINDOW w AS (...) и обращение к ней через OVER w сохраняют запрос DRY и делают его более понятным.

PostgreSQL, MySQL 8 и сервер SQL поддерживают именованные окна. Использование такого окна на собеседовании показывает свободное владение оконными функциями, а не простое копирование готового кода.

SELECT
  sale_date,
  AVG(amount) OVER w AS avg_7d,
  SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Проблема календарных дней и строк

Есть тонкая ловушка: если в таблице отсутствуют некоторые дни, ROWS 6 PRECEDING охватывает последние 7 зафиксированных строк, которые могут соответствовать периоду длиннее 7 календарных дней.

Для настоящего среднего за 7 календарных дней, учитывающего пропуски, используйте RANGE с интервальным смещением или сначала соедините данные с полным рядом дат, чтобы для каждого дня была строка. Упоминание этого различия — именно тот вывод о ROWS и RANGE, который был сделан на предыдущем уроке.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS calendar_avg_7d
FROM daily_sales;

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

Как и текущие суммы, скользящие средние обычно нужно начинать заново для каждой сущности. Добавьте PARTITION BY, чтобы для каждого товара или магазина формировалось собственное скользящее окно, не переходящее между группами.

Рамка и порядок вычисляются независимо внутри каждого раздела, поэтому первые строки каждой группы правильно начинают собственный период разгона.

SELECT
  product_id,
  sale_date,
  AVG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS product_ma_7d
FROM daily_sales;

Сглаживание в действии

Зачем это нужно? Ежедневная выручка скачкообразна, а скользящее среднее показывает тенденцию. Аналитики часто выводят исходное значение вместе со сглаженной линией, чтобы на панели мониторинга были видны и сигнал, и шум.

Также можно сравнить короткое и длинное скользящие средние (например, за 7 и 30 дней), чтобы обнаружить изменение динамики — SQL-аналог пересечения скользящих средних.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma_7,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;

Контрольный список для собеседования

Чтобы хорошо ответить на вопрос о скользящем среднем, упомяните:

AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW).
Размер окна = preceding + 1.
Выбор между концевым и центрированным окном.
Период разгона неполного окна и способ его скрытия.
Разницу между ROWS и RANGE при отсутствии некоторых дней.
PARTITION BY для сброса окна по каждой сущности.

Быстрая проверка

Выберите рамку для концевого скользящего среднего за 7 дней по ежедневным строкам.

Итоги: скользящие средние

Скользящее среднее задаётся так: AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW), где размер окна равен числу предшествующих строк плюс один. Выбирайте концевое или центрированное окно в зависимости от сценария, обрабатывайте строки периода разгона с помощью проверки COUNT и переходите к интервальному RANGE, когда важны пропуски в календаре.

Сбрасывайте окно для каждой сущности с помощью PARTITION BY и повторно используйте рамки через именованный WINDOW. Далее мы превратим накопительные суммы в проценты с помощью текущей доли от общего итога.

Часто задаваемые вопросы

Урок «Скользящие средние в скользящем окне» бесплатный?

Да — полный текст урока «Скользящие средние в скользящем окне» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Скользящие средние в скользящем окне»?

Рассчитывайте скользящие средние за N периодов с BETWEEN PRECEDING и CURRENT ROW. Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Скользящие средние в скользящем окне»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Накопительные суммы с оконными рамками
Рамки ROWS и RANGE
Скользящие средние в скользящем окне
Накопительное распределение и доля от общего

← Назад к Coding Interview Prep