🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
Фреймы оконных функций в PL/SQL
Ранее в разборе скрипта я упомянул запись ROWS UNBOUNDED PRECEDING. Подобные записи называются фреймами. Рассмотрим подробнее, какие фреймы вообще бывают и для чего они нужны.
Фреймы используются, когда нужно немного изменить поведение оконной функции, чтобы ее логика расчета отличалась от логики по умолчанию.
Все фреймы в PL/SQL имеют строго регламентированный вид записи и пишутся только лишь в контексте оконных функций, то есть внутри OVER().
Шаблон фреймов таков: сначала задается метод (ROWS или RANGE), а затем диапазон использования. То есть они имеют такой общий вид:
{ ROWS | RANGE }
[ BETWEEN
{ UNBOUNDED PRECEDING | N PRECEDING | CURRENT ROW }
AND
{ UNBOUNDED FOLLOWING | N FOLLOWING | CURRENT ROW }
]
🟠Как уже упомянул ранее, сначала выбирается ROWS или RANGE. Отличие между ними в том, что ROWS считает по каждой строке, а RANGE - по каждому уникальному значению.
Если очень грубо, то для аналогии можно привести в пример ранжирующие функции: ROWS работает как row_number(), а RANGE как rank().
🟠Затем мы устанавливаем диапазон (границы), в рамках которого будет считаться оконная функция. Всегда обязательно задавать после ROWS | RANGE верхнюю границу диапазона, которая может принимать только лишь 1 из 3 значений:
▶️ UNBOUNDED PRECEDING - от самой первой строки в выборке, то есть оконная функция идет с самого начала, постепенно накапливая строки. Особенно популярно в расчете накопленной суммы.
▶️ N PRECEDING - оконная функция затрагивает строки, которые находятся в N позиций ДО текущей (включительно). Обработка идет со смещением. Наиболее часто используется при расчете скользящих средних.
▶️ CURRENT ROW - по сути то же самое, что и N PRECEDING, только N = 0 всегда. Функция просто возвращает текущую строку
Идем дальше. Задать только лишь нижнюю границу нельзя.
Если мы задаём только верхнюю границу, то нижняя по умолчанию ставится как CURRENT ROW.
BETWEEN используется только лишь в тех ситуациях, когда мы явно задаём и верхнюю, и нижнюю границу.
🟠Нижняя граница может принимать только лишь 1 из 3 значений:
▶️ UNBOUNDED FOLLOWING
▶️ CURRENT ROW
▶️ N FOLLOWING
Тут всё по аналогии с верхней границей, с той лишь разницей, что это - нижняя граница.
🟠Теперь на примере. Допустим, есть такая оконная функция:
SUM(Sales) OVER (ORDER BY Date)
В данном случае фрейм тут не указывается, и PL/SQL по умолчанию использует
RANGE UNBOUNDED PRECEDING
Но это используется только когда в OVER есть ORDER BY. В остальных случаях фреймы по умолчанию не применяются, что эквивалентно записи
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
🟠Вообще самых разных фреймов можно составить не так уж и много, но наиболее часто используемые из них:
SUM(Sales) OVER (ORDER BY Date
ROWS UNBOUNDED PRECEDING)
Используется в основном тогда, когда мы хотим посчитать накопленную сумму правильно, то есть учитывая каждую строку (ROWS), без пропуска одинаковых значений (RANGE).
Если вдруг захочется посчитать накопленную сумму только за последние N строк и потом ее постоянно обновлять (скользящая сумма), то запишем:
SUM(Sales) OVER (ORDER BY Date
ROWS N PRECEDING)
В случае, если считаем по дням, то просто лучше заменить ROW на RANGE, т.к. продаж может быть больше 1 за день и они все будут суммироваться внутри одной даты. И результат будет выводиться уже за последние N дней, а не N строк.
Но вообще это часто используется при расчете скользящих средних.
Если же цель - посчитать накопленную сумму наоборот, то есть чтобы она не возрастала, а уменьшалась, оконная функция будет выглядеть так:
SUM(Sales) OVER (ORDER BY Date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Используется в основном при расчете каких-нибудь остатков.