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

Курс профессиональной Power BI аналитики	Подробнее
Курс SQL-анализа данных с нуля	Подробнее

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

• Для спонсоров 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

SQL Базовый

Номер урока	Урок	Описание
1	SQL Базовый №1. Установка PostgreSQL, создание таблицы, импорт из CSV	Начинаем изучать SQL. Установим СУБД, создадим схему, таблицу и выполним импорт из CSV.
1.1	SQL Базовый №1.1. UI создание схемы, таблицы, импорт из CSV	Дополнение к уроку 1. Создадим схему, таблицу и выполним импорт данных из CSV с помощью пользовательского интерфейса.
2	SQL Базовый №2. Простые операции, SELECT	Научимся отбирать нужные нам столбцы и строки таблицы и делать сортировку.
3	SQL Базовый №3. Типы данных	Знакомство с основными типами данных в PostgreSQL: строковые, числовые, дата и время.
4	SQL Базовый №4. Импорт и экспорт данных	Научимся импортировать данные из CSV и экспортировать в CSV.
5	SQL Базовый №5. Группировка	Изучаем операцию группировки и основные агрегатные функции.
6	SQL Базовый №6. Математические операции	Сложение, вычитание, умножение, деление, остаток от деления, факториал, возведение в степень, корень.
7	SQL Базовый №7. CASE	CASE — это условный оператор, который позволяет осуществлять проверку и возвращать результат, который зависит от того, какое условие выполнено.
8	SQL Базовый №8. Дополнения к урокам 1-7	Значение NULL, запросы к таблицам из других схем, рекомендации по форматированию кода, порядок выполнения операций в SQL.
9	SQL Базовый №9. Повторение изученного	Повторим все, что изучили в уроках 1-8. Спонсоры также смогут получить домашнее задание.
10	SQL Базовый №10. Подзапросы	Подзапросы нужны, когда для решения задачи нужно сделать несколько действий. Подзапросы внутри WHERE, FROM, SELECT, HAVING.
11	SQL Базовый №11. CTE	CTE также используются для решения задач в несколько этапов.
12	SQL Базовый №12. Оконные функции. Ранг, ранжирование (ranking)	Как посчитать ранг для всей таблицы или для конкретной категории.
13	SQL Базовый №13. Оконные функции. Агрегаты (SUM, AVG, MIN, MAX, COUNT)	Получить агрегированную итоговую сумму для категории.
14	SQL Базовый №14. Смещение	Приемы из этого урока позволяют получить значение из другой строки таблицы.
15	SQL Базовый №15. Оконные функции. Скользящие агрегаты	Научимся вычислять скользящее среднее и нарастающий итог.
16	SQL Базовый №16. Операция JOIN	Какие бывают виды объединения по горизонтали. Чем они отличаются друг от друга. Практика и домашнее задание.
17	SQL Базовый №17. Практика	Повторим все изученное в предыдущих уроках. Создадим схему, несколько таблиц и выполним несколько запросов на закрепление изученного материала.
18	SQL Базовый №18. Объединение по вертикали (UNION, UNION ALL, INTERSECT, EXCEPT)	Научимся объединять таблицы по вертикали.
19	SQL Базовый №19. Устройство на работу 1	Тестовые задания при приеме на работу.
20	SQL Базовый №20. Сравнение с Excel, важные функции DBeaver	Важные функции DBeaver. Сравниваем SQL и Excel.
21	SQL Базовый №21. Устройство на работу 2	Решим еще один тест при устройстве на работу.
22	SQL Базовый №22. Краткий обзор основных DDL и DML команд	Познакомимся с командами, которые позволяют создавать таблицы, наполнять их данными, редактировать, удалять и т. д.
23	SQL Базовый №23. Устройство на работу 3	Еще одно тестовое задание по SQL при приеме на работу на позицию BI-аналитика.