Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Як порахувати ковзне середнє за останні 7 днів віконною функцією?

Рамка вікна визначає, які рядки враховує агрегат для поточного рядка. Є два способи її задати, і для часових даних різниця принципова.

ROWS - за кількістю рядків:

SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_rows
FROM daily_revenue;

Бере 7 рядків. Якщо в даних є пропущені дні, «7 рядків» охоплять більше ніж 7 днів - і середнє буде неправильним.

RANGE з інтервалом - за значенням колонки сортування:

SELECT day, revenue,
       AVG(revenue) OVER (
           ORDER BY day
           RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
       ) AS avg_7_days
FROM daily_revenue;

Бере всі рядки, у яких day в межах 6 днів до поточного - незалежно від того, скільки рядків туди потрапило. Пропущені дні не «розтягують» вікно. MySQL підтримує RANGE з INTERVAL для дат і часу.

Що ще враховувати:

  • Пропущені дні в самих даних: RANGE правильно визначить межі, але AVG порахує середнє лише по наявних днях. Якщо день без продажів має рахуватися як 0 - спершу доповнити дані календарем (рекурсивний CTE).
  • Початок ряду: для перших днів у вікні менше 7 значень. Якщо це спотворює графік, - показувати середнє лише з 7-го дня (COUNT(*) OVER (...) для перевірки кількості).
  • Однакові значення сортування: з RANGE рядки з однаковим day потрапляють у рамку разом (вони «рівні»). З ROWS - ні. Для щоденних агрегатів це не проблема, для сирих подій з однаковими мітками часу - важливо.
  • Іменовані вікна зменшують повтори, коли кілька функцій мають однакове вікно:
SELECT day,
       AVG(revenue) OVER w AS avg_7,
       SUM(revenue) OVER w AS sum_7
FROM daily_revenue
WINDOW w AS (ORDER BY day RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW);

Швидкодія: для мільйонів рядків віконні функції з рамкою дорогі. Для дашбордів агрегати рахують заздалегідь у таблицю денних підсумків, а вікно застосовують уже до неї.

Докладніше в документації: Рамки віконних функцій

1

Перевір себе

20 випадкових питань за спробу, після завершення - розбір кожної помилки

Схожі питання