Обчислення ковзаючого середнього — це поширена задача SQL, особливо в сценаріях, що передбачають роботу з даними часових рядів, таких як відслідковування цін на акції, продажів або показників ефективності. У цьому дописі ми розглянемо, як обчислити ковзаюче середнє за допомогою SQL.
Розуміння задачі
Дано таблицю з даними за часом, потрібно обчислити ковзаюче середнє за останні 3 дні.
Приклад таблиці: Sales
Найефективніший спосіб обчислення ковзаючого середнього в SQL — це використання віконних функцій.
Дозвольте пояснити це більш детально:
PARTITION BY product: Забезпечує, щоб ковзаюче середнє обчислювалося окремо для кожного продукту.ORDER BY date: Сортує рядки хронологічно для кожного продукту.ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: Визначає ковзаюче вікно, яке включає поточний рядок і два попередні (вікно за 3 дні).AVG(sales): Обчислює середнє значення продажів у межах зазначеного вікна.ROUND(..., 2): Округлює результат до двох знаків після коми для зручності читання, хоча це не обов’язково, але є гарною практикою для чистоти результатів.
Основні моменти для запам’ятовування
Коригування розміру вікна:
- Змініть
ROWS BETWEEN(для віконних функцій) або діапазон дат (для підзапитів), щоб змінити розмір ковзаючого вікна.
Обробка крайових випадків:
- Для перших кількох рядків, де доступно менше ніж 3 рядки, ковзаюче середнє обчислюється з використанням доступних даних.
Розповсюджені варіанти цього запитання
Кумулятивне ковзаюче середнє:
- Обчислює середнє значення для всіх рядків до поточного.
- Замініть
ROWS BETWEENнаROWS UNBOUNDED PRECEDING.
Ковзаюча сума:
- Замініть
AVG(sales)наSUM(sales), щоб обчислити загальну суму продажів за ковзаюче вікно.
Не пропустіть наступне SQL запитання 8/30 завтра!
Перекладено з: SQL Interview Question 7/30: Calculating the Moving Average