Вычисление скользящего среднего с помощью оконных функций

Вычисление скользящего среднего (или скользящего среднего) с помощью оконных функций позволяет анализировать данные, сглаживая их и убирая шум, что может быть полезно для выявления тенденций и паттернов. Скользящее среднее представляет собой среднее значение данных в определенном окне (периоде), которое перемещается по набору данных.

Синтаксис для вычисления скользящего среднего

Чтобы вычислить скользящее среднее с помощью оконных функций, обычно используется функция AVG() с определением размера окна через ROWS или RANGE. Вот общий синтаксис:

SELECT column1, column2, ...,
       AVG(column_to_average) OVER (
           PARTITION BY partition_column
           ORDER BY order_column
           ROWS BETWEEN N PRECEDING AND CURRENT ROW
       ) AS moving_avg
FROM table_name;

• PARTITION BY partition_column: Разделяет данные на группы (если необходимо).

• ORDER BY order_column: Упорядочивает строки внутри каждого окна.

• ROWS BETWEEN N PRECEDING AND CURRENT ROW: Определяет размер окна, включая N предыдущих строк и текущую строку.

Примеры использования

Пример 1: Скользящее среднее по зарплатам сотрудников

Рассмотрим таблицу employees с полями employee_id, department, и salary, и мы хотим вычислить скользящее среднее зарплат сотрудников в пределах их отдела.

SELECT employee_id, department, salary,
       AVG(salary) OVER (
           PARTITION BY department
           ORDER BY salary
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg
FROM employees;

• PARTITION BY department: Делит данные по отделам.

• ORDER BY salary: Упорядочивает строки по зарплате.

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: Учитывает текущую строку и две предыдущие строки.

Этот запрос вычислит среднее значение зарплаты для текущего сотрудника, включая его и две предыдущие зарплаты в пределах того же отдела.

Пример 2: Скользящее среднее по продажам

Предположим, у нас есть таблица sales с полями sale_date и amount, и нам нужно вычислить скользящее среднее продаж за последние 7 дней.

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

• ORDER BY sale_date: Упорядочивает строки по дате продажи.

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: Учитывает текущую строку и предыдущие 6 строк (всего 7 дней).

Этот запрос вычислит скользящее среднее продаж для каждой даты, включая данные за последние 6 дней и текущую дату.

Пример 3: Скользящее среднее с использованием временных интервалов

Если вам нужно вычислить скользящее среднее на основе временных интервалов, можно использовать RANGE вместо ROWS, что позволяет задавать интервал времени.

SELECT sale_date, amount,
       AVG(amount) OVER (
           ORDER BY sale_date
           RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW
       ) AS moving_avg
FROM sales;

• ORDER BY sale_date: Упорядочивает строки по дате продажи.

• RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW: Учитывает текущую строку и данные за предыдущие 30 дней.

Этот запрос вычислит среднее значение продаж за последние 30 дней для каждой даты продажи.

Заключение

Скользящее среднее — полезный инструмент для анализа временных рядов и трендов в данных. Оконные функции SQL позволяют легко вычислять скользящее среднее, предоставляя гибкие средства для определения размера окна и выбора интервала времени. Это помогает сглаживать данные и выявлять основные тренды, что может быть полезно в различных аналитических задачах.