Як порахувати ковзне середнє за останні 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);

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

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