SQL Базовый №15. Оконные функции: скользящая агрегация

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

С помощью скользящих агрегатов можно:

• Вычислить скользящее среднее
• Вычислить нарастающий итог

Чтобы вычислить скользящий агрегат нужно добавить определение фрейма в оконную функцию. Фрейм — это диапазон от начала секции до текущей строки.

Примеры определения фрейма:

• rows between 2 preceding and 1 following — между вторым предыдущим и одним следующим включительно
• rows between unbound preceding and current row — между самым первым в секции и текущим включительно
• rows between unbound preceding and unbound following — расширяем фрейм до размера секции

На картинке ниже зеленым цветом обозначен фрейм для вычисления среднего с определением 2 preceding and current row. Текущая строка выделена оранжевым цветом. То есть во фрейм входит 3 значения: 2 предыдущих и текущее.

На следующей картинке изображено то же самое для фрейма 2 preceding and 1 following. Этот фрейм состоит уже из 4 значений: 2 предыдущих, 1 текущее и 1 следующее.

Ближе с фреймами вы можете познакомиться посмотрев видео-урок.

Вычисление скользящего среднего

Воспользуемся датасетом usdcad. Вычислим 10-периодное скользящее среднее.

-- Скользящее среднее последних 10 значений
select
  *
  ,avg(p_close)
     over(order by q_date
     rows between 10 preceding and 1 preceding)
from usdcad_small

Вычислим скользящее среднее с начала месяца по текущий день.

select
  *
  ,avg(p_close)
     over(partition by date_trunc('month', q_date) 
       	  order by q_date
          rows between unbounded preceding and current row)
     as moving_avg
from usdcad_small

Вычисление нарастающего итога

На основе датасета sport_goods_sales создадим таблицу sport_goods_sales_small.

-- Создать таблицу
create table sport_goods_sales_small (
  date_week_start date,
  region text,
  sales integer
)

-- Добавляем данные
insert into sport_goods_sales_small
select
  date_trunc('week', invoice_date)::date
  ,region
  ,sum(total_sales) as sales
from sport_goods_sales
group by 1, 2

-- Посмотреть таблицу
select *
from sport_goods_sales_small

Вычислим нарастающий итог с начала года.

-- Нарастающий итог по регионам с начала года
select
  *
  ,sum(sales)
     over(partition by region, extract(year from date_week_start)
  		  order by date_week_start
  		  rows between unbounded preceding and current row)
     as running_total
from sport_goods_sales_small