Оконные функции SQL: скользящие агрегаты

Это фрагмент моего курса Оконные функции SQL с пошаговым изложением, наглядными иллюстрациями и практическими упражнениями.

На предыдущих уроках мы разобрали оконные функции ранжирования, смещения и агрегации.

Скользящие агрегаты — это те же сумма, среднее и прочие сводные показатели. Только рассчитывают их не по всем элементам набора, а более хитрым способом.

Разберемся на примерах. Здесь возьмем таблицу expenses, которая содержит месячные доходы и расходы компании, где трудятся наши сотрудники. Для краткости будем рассматривать только первые девять месяцев 2020 года:

select
  year, month, income, expense
from expenses
where year = 2020 and month <= 9
order by month;

┌──────┬───────┬────────┬─────────┐
│ year │ month │ income │ expense │
├──────┼───────┼────────┼─────────┤
│ 2020 │ 1     │ 94     │ 82      │
│ 2020 │ 2     │ 94     │ 75      │
│ 2020 │ 3     │ 94     │ 104     │
│ 2020 │ 4     │ 100    │ 94      │
│ 2020 │ 5     │ 100    │ 99      │
│ 2020 │ 6     │ 100    │ 105     │
│ 2020 │ 7     │ 100    │ 95      │
│ 2020 │ 8     │ 100    │ 110     │
│ 2020 │ 9     │ 104    │ 104     │
└──────┴───────┴────────┴─────────┘

песочница • скачать

Содержание:

• Скользящее среднее
• Фрейм
• Сумма нарастающим итогом
• Фрейм по умолчанию
• Функции для скользящих агрегатов
• Так держать

Скользящее среднее

Судя по данным, доходы компании растут: 94К ₽ в январе → 104К ₽ в сентябре. А вот растут ли расходы? Сходу сложно сказать, месяц на месяц не приходится. Чтобы сгладить эти скачки, используют «скользящее среднее» — для каждого месяца рассчитывают средний расход с учетом предыдущего и следующего месяца:

• скользящее среднее за февраль = (январь + февраль + март) / 3;
• за март = (февраль + март + апрель) / 3;
• за апрель = (март + апрель + май) / 3;
• и так далее.

Рассчитаем скользящее среднее по всем месяцам:

было

стало

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

Как перейти от «было» к «стало»?

Отсортируем таблицу по месяцам:

select
  year, month, expense,
  null as roll_avg
from expenses
where year = 2020 and month <= 9
order by year, month;

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать среднее по предыдущему, текущему и следующему значению из столбца expense:

➀

➁

➂

➃

➄

и так далее...

Одной гифкой:

По-хорошему, стоило бы учесть декабрь 2019 при расчете среднего за январь 2020, а октябрь 2020 — при расчете среднего за сентябрь 2020. Но для простоты не будем этого делать.

Рамка на каждом шаге сдвигается вниз, скользит — так и получается скользящее среднее. Чтобы описать на SQL, придется вспомнить концепцию фреймов, с которой мы познакомились в главе «Смещение»:

1. Окно состоит из одной или нескольких секций (в нашем случае секция одна — все записи expenses).
2. Внутри секции записи упорядочены по конкретным столбцам (order by year, month).
3. У каждой записи свой фрейм.

Фрейм на каждом шаге охватывает три записи — текущую, предыдущую и следующую:

expenses

expenses

Вот как записать это на SQL:

window w as (
  order by year, month
  rows between 1 preceding and 1 following
)

Строчка rows — это определение фрейма. Она говорит:

Выбрать строки от 1 предыдущей до 1 следующей»

На следующем шаге разберемся с фреймами подробно, а пока закончим с нашим запросом.

Считаем среднее по расходам — это функция avg():

avg(expense) over w

Добавим округление и сведем все вместе:

select
  year, month, expense,
  round(avg(expense) over w) as roll_avg
from expenses
where year = 2020 and month <= 9
window w as (
  order by year, month
  rows between 1 preceding and 1 following
)
order by year, month;

Скользящее среднее по расходам готово!

Фрейм

В общем случае определение фрейма выглядит так:

rows between X preceding and Y following

Где X — количество строк перед текущей, а Y — количество строк после текущей:

Если указать вместо X или Y значение unbounded — это значит «от/до границы секции»:

Если указать вместо X preceding или Y following значение current row — это значит «текущая запись»:

Фрейм никогда не выходит за границы секции, если столкнулся с ней — обрезается:

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

Сумма нарастающим итогом

Благодаря скользящему среднему, мы выяснили, что доходы и расходы компании растут. А как они соотносятся друг с другом? Хочется понять, находится ли компания «в плюсе» или «в минусе» с учетом всех заработанных и потраченных денег.

Причем важно понимать не на конец периода, а на каждый месяц. Потому что если в сентябре у компании все ОК, а в июне она ушла в минус — это потенциальная проблема (такую ситуацию называют «кассовым разрывом»).

Поэтому посчитаем доходы и расходы по месяцам нарастающим итогом (кумулятивно):

• кумулятивный доход за январь = январь;
• за февраль = январь + февраль;
• за март = январь + февраль + март;
• за апрель = январь + февраль + март + апрель;
• и так далее.

Столбцы t_ показывают значения нарастающим итогом:

• доходы (t_income),
• расходы (t_expense),
• прибыль (t_profit).

t_profit = t_income - t_expense

Как рассчитать кумулятивные показатели?

Отсортируем таблицу по месяцам:

select
  year, month, income, expense,
  null as t_income,
  null as t_expense,
  null as t_profit
from expenses
where year = 2020 and month <= 9
order by year, month;

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать суммарные показатели от начала таблицы до текущей строки:

➀

➁

➂

➃

➄

и так далее...

Одной гифкой:

Рамка на каждом шаге охватывает строки от начала таблицы до текущей записи. Мы уже знаем, как сформулировать подходящий фрейм:

window w as (
  order by year, month
  rows between unbounded preceding and current row
)

Считаем сумму по доходам и расходам — это функция sum():

sum(income) over w as t_income,
sum(expense) over w as t_expense,

Прибыль считаем как разницу между доходами и расходами:

(sum(income) over w) - (sum(expense) over w) as t_profit

Все вместе:

select
  year, month, income, expense,
  sum(income) over w as t_income,
  sum(expense) over w as t_expense,
  (sum(income) over w) - (sum(expense) over w) as t_profit
from expenses
where year = 2020 and month <= 9
window w as (
  order by year, month
  rows between unbounded preceding and current row
)
order by year, month;

По t_profit видно, что дела у компании идут неплохо. В некоторых месяцах расходы превышают доходы, но благодаря накопленной «денежной подушке» кассового разрыва не происходит.

Фрейм по умолчанию

Возьмем запрос, который считает расходы нарастающим итогом:

select
  year, month, expense,
  sum(expense) over w as total
from expenses
where year = 2020 and month <= 9
window w as (
  order by year, month
  rows between unbounded preceding and current row
)
order by year, month;

И уберем из него определение фрейма:

select
  year, month, expense,
  sum(expense) over w as total
from expenses
where year = 2020 and month <= 9
window w as (
  order by year, month
)
order by year, month;

Ожидаем, что в total теперь всегда будет одно и то же число — суммарные расходы за 9 месяцев. А вместо этого видим вот что:

┌───────┬─────────┬───────┐
│ month │ expense │ total │
├───────┼─────────┼───────┤
│ 1     │ 82      │ 868   │
│ 2     │ 75      │ 868   │
│ 3     │ 104     │ 868   │
│ 4     │ 94      │ 868   │
│ 5     │ 99      │ 868   │
│ 6     │ 105     │ 868   │
│ 7     │ 95      │ 868   │
│ 8     │ 110     │ 868   │
│ 9     │ 104     │ 868   │
└───────┴─────────┴───────┘

┌───────┬─────────┬───────┐
│ month │ expense │ total │
├───────┼─────────┼───────┤
│ 1     │ 82      │ 82    │
│ 2     │ 75      │ 157   │
│ 3     │ 104     │ 261   │
│ 4     │ 94      │ 355   │
│ 5     │ 99      │ 454   │
│ 6     │ 105     │ 559   │
│ 7     │ 95      │ 654   │
│ 8     │ 110     │ 764   │
│ 9     │ 104     │ 868   │
└───────┴─────────┴───────┘

Запрос без фрейма все равно посчитал кумулятивные расходы — в точности как запрос с фреймом. Как так?

Все дело в наличии сортировки в окне (order by year, month). Правило такое:

• если в окне есть order by,
• и используется функция агрегации,
• и не указано определение фрейма,
• то используется фрейм по умолчанию.

Фрейм по умолчанию в нашем запросе распространяется от первой до текущей записи. Поэтому результаты совпадают с запросом, где фрейм был указан явно:

rows between unbounded preceding and current row

Но так бывает не всегда. Поэтому я рекомендую указывать фрейм явно — по крайней мере пока не разберетесь досконально в разновидностях фреймов. Добавили в окно order by — добавьте сразу и фрейм.

Если убрать из окна order by, агрегат из скользящего превратится в обычный:

select
  year, month, expense,
  sum(expense) over () as total
from expenses
where year = 2020 and month <= 9
order by year, month;

Здесь без неожиданностей.

Функции для скользящих агрегатов

Скользящие агрегаты используют те же самые функции, что и агрегаты обычные:

• min() и max(),
• count(), avg() и sum(),
• group_concat().

Разница только в наличии фрейма у скользящих агрегатов.

Так держать

Мы рассмотрели четыре класса задач, которые решаются с помощью оконных функций в SQL:

• Ранжирование (всевозможные рейтинги).
• Сравнение со смещением (соседние элементы и границы).
• Агрегация (количество, сумма и среднее).
• Скользящие агрегаты (сумма и среднее в динамике).

Теперь попробуйте применить «окошки» на практике!

Чтобы узнать больше об оконных функциях или потренироваться — записывайтесь на курс или читайте книгу.

Записаться на курс  или купить книгу (скоро)