Оконные функции простым языком — Фреймы

Привет всем!

Это вторая часть к продолжению статьи «Оконные функции простым языком с примерами«. Рекомендую ознакомиться сначала с ней, а потом вернуться к прочтению данной статьи, чтобы полностью понимать синтаксис и применение оконных функций. В этой статье будет разобрано на примерах такое понятие как «фрейм» оконных функций, который расширяет возможности оконок для решения более сложных аналитических задач.

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

P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи))) 

Будем разбирать примеры на такой небольшой таблице, где указана прибыль (net_profit) компании на каждый месяц в рамках одного года.

Давайте сформулируем довольно распространенную и классическую аналитическую задачу:

У нас есть фактические данные прибыли на каждый месяц. Перед компанией стояло плановое значение по достижению прибыли 700 условных единиц на конец года. Достигла ли компания этого планового показателя и если достигла, то на какой месяц по счету?

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

Из примера выше мы видим, что достижение планового показателя 700 по прибыли произошло в сентябре, где фактическое значение прибыли за все предыдущие месяцы суммарно достигло 737.

Пример простой — задача тоже довольно простая и как уже сказал распространенная.

Как это посчитать технически с помощью SQL?

На помощь для решения подобных задач приходят фреймы оконных функций. Давайте разберем теорию.

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

Определение довольно сложно воспринять без примеров и картинок, давайте разберем синтаксис фрейма, а потом перейдем к картинкам с примерами.

Синтаксис оконной функции с фреймом

Синтаксис оконных функций с фреймом выглядит следующим образом:
название_оконной_функции OVER (описание_окна описание_фрейма)

На картинке ниже вы увидите синтаксис со всеми SQL командами для создания оконки с фреймом

Описание фрейма состоит из следующего синтаксиса:

тип_фрейма BETWEEN начальная_граница_фрейма AND конечная_граница_фрейма

Как можно понять из синтаксиса есть несколько типов фреймов и границы фреймов

Границы фреймов

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

Давайте рассмотрим на примерах ниже виды границ фреймов

Пример 1

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

Пример 2

В данном примере строки, входящие во фрейм, также зависят от текущей строки расчета оконной функции.
На шаге 1 — фрейм учитывает текущую строку и 2 после нее по условию границ, а вот 4 строки до текущей взять неоткуда, т.к. мы находимся вверху таблицы.
На шаге 2 — фрейм учитывает текущую строку и 2 после нее по условию границ, 4 строки до текущей взять опять не можем, можем взять только одну со значением понедельника
На шаге 3 — тоже правило работает с другой стороны о текущей строки, в условии фрейма указано чтобы взять 2 строки после текущей, но мы приблизились к границе окна/таблицы и можем взять во фрейм только 1 строку после текущей. А вот до текущей возьмем все 4 строки, чтобы удовлетворять условию фрейма.

Пример 3

Это и есть тот самый фрейм с границами, который позволяет нам решать задачу из начала статьи с кумулятивной суммой. На каждом последующем шаге фрейм будет учитывать значения текущей строки и все значения до текущей строки от начала окна/таблицы.
Применив такой синтаксис для первой задачи мы получим ее решение ниже

Теперь давайте разберем виды фреймов

Виды фреймов

Примеры с фреймами типа ROWS мы уже рассмотрели — это самый часто применяемый вид. Про остальные виды важно знать, но на практике как правило вы редко с ними столкнетесь.

Фрейм RANGE тоже достаточно интересный, он ориентируется на сами значения данных, а не на количество строк, указанных для фрейма.

Из примера выше такой фрейм для записи со значением product_price = X будет включать все строки по продуктом с диапазоном цены от X 10 до X+10.

Несколько правил для применение фреймов оконных функций

1. Не для всех оконных функций существуют фреймы. Фреймы можно применять с функциями агрегации (со всеми) и с некоторыми функциям смещения (first_value, last_value), а вот для ранжирующих функций фреймов нет и логически их даже представить в применении нельзя

2. Если задано окно (PARTITION BY), то фрейм будет работать внутри каждого окна отдельно

3. Для фреймов по логике задач важна сортировка данных внутри окна (ORDER BY). Без явной сортировки данных вы можете получить неинформативный и неправильный результат расчетов.

Надеюсь эта статья была для вас полезной, делитесь ей с другими 🙂

Telegram канал про аналитику и инженерию данных