Оконные функции с «форточкой» или как пользоваться фреймом

2020-02-28 в 10:39, admin, рубрики: frame, Microsoft SQL Server, OVER, range, ROWS, sql, аналитические функции, Блог компании OTUS. Онлайн-образование, оконные функции, фрейм

Всем привет. 26 февраля в OTUS стартовали занятия в новой группе по курсу «MS SQL Server разработчик». В связи с этим я хочу поделиться с вами своей публикацией про оконные функции. Кстати, в ближайшую неделю еще можно записаться в группу ;-).

Оконные функции с «форточкой» или как пользоваться фреймом - 1

Оконные функции прочно вошли в нашу практику, но мало кто знает как работают фреймы RANGE и ROWS.

Возможно поэтому они несколько реже встречается. Цель этой статьи привести примеры использования, чтобы у вас точно не осталось вопросов “Кто есть кто?” и “Как это применять?”. Вопрос “Зачем?” в статье останется не освещенным.

Давайте разберемся что такое фрейм, и как схожего эффекта достичь с помощью ORDER By в предложении OVER().

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

Таблица

Create table sales 
(sales_id INT PRIMARY KEY, sales_dt DATETIME2 DEFAULT GETUTCDATE(),  customer_id INT, item_id INT, cnt INT, price_per_item DECIMAL(19,4));

INSERT INTO sales
(sales_id, sales_dt, customer_id, item_id, cnt, price_per_item)
VALUES
(1, '2020-01-10T10:00:00', 100, 200, 2, 30.15),
(2, '2020-01-11T11:00:00', 100, 311, 1, 5.00),
(3, '2020-01-12T14:00:00', 100, 400, 1, 50.00),
(4, '2020-01-12T20:00:00', 100, 311, 5, 5.00),
(5, '2020-01-13T10:00:00', 150, 311, 1, 5.00),
(6, '2020-01-13T11:00:00', 100, 315, 1, 17.00),
(7, '2020-01-14T10:00:00', 150, 200, 2, 30.15),
(8, '2020-01-14T15:00:00', 100, 380, 1, 8.00),
(9, '2020-01-14T18:00:00', 170, 380, 3, 8.00),
(10, '2020-01-15T09:30:00', 100, 311, 1, 5.00),
(11, '2020-01-15T12:45:00', 150, 311, 5, 5.00),
(12, '2020-01-15T21:30:00', 170, 200, 1, 30.15);

Давайте начнем с простого момента — разницы функции SUM с указанием сортировки и без нее

SELECT sales_id, customer_id, count, 
SUM(count) OVER () as total,
SUM(count) OVER (ORDER BY customer_id) AS cum,
SUM(count) OVER (ORDER BY customer_id, sales_id) AS cum_uniq
FROM sales
ORDER BY customer_id, sales_id;

Оконные функции с «форточкой» или как пользоваться фреймом - 2

Давайте посмотрим на первые незаметно лежащие грабли, как вы считаете сколько разработчиков в процессе чтения кода подумают, что cum и cum_uniq совпадают? Думаете немного? Возможно, но потому то тут это очевидно, а так ли очевидно это при чтении кода в приложении, да еще при не столь очевидной неуникальности поля сортировки.

Теперь откроем нашу замечательную форточку.

Форточка, а точнее фрейм бывает 2х видов ROWS и RANGE, познакомимся сначала с ROWS.
Варианты ограничения фрейма:

Все, что до текущей строки/диапазона и само значение текущей строки
BETWEEN UNBOUNDED PRECEDING
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Текущая строка/диапазон и все, что после нее
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
С конкретным указазнием сколько строк до и после включать (не поддерживается для RANGE)
BETWEEN N Preceding AND N Following
BETWEEN CURRENT ROW AND N Following
BETWEEN N Preceding AND CURRENT ROW

Но давайте посмотрим фрейм в действии.

Открываем “форточку” на текущую строку и все предыдущие, для функции SUM как видите это совпадает с сортировкой ASC

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id) AS cum_uniq,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_and_all_before2
FROM sales
ORDER BY customer_id, sales_id;

Оконные функции с «форточкой» или как пользоваться фреймом - 3

При этом хочу напомнить, что порядок сортировке в окне (в предложении OVER()) не связан с порядком сортировки в самом запрос, в примере он совпадает для того, чтобы упростить подсчет, если вы решите проверить расчет и свое понимание того, как работает функция

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id) AS cum_uniq,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_and_all_before2
FROM sales
ORDER BY cnt;

Оконные функции с «форточкой» или как пользоваться фреймом - 4

Теперь давайте посмотрим на функционал фрейма, когда мы включаем все последующие строки.

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_frame,
SUM(cnt) OVER (ORDER BY customer_id DESC, sales_id DESC) AS current_and_all_order_desc
FROM sales
ORDER BY customer_id, sales_id;

Оконные функции с «форточкой» или как пользоваться фреймом - 5

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

И наконец вариант, который уже не сымитируешь сортировками, когда мы указываем конкретное количество строк, которые должны войти во фрейм

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS before_and_current,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS current_and_1_next,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS before2_and_2_next
FROM sales
ORDER BY customer_id, sales_id;

Оконные функции с «форточкой» или как пользоваться фреймом - 6

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

Разница ROWS и RANGE

Разница заключается в том, что ROWS оперирует строкой, а RANGE диапазоном. Правда это очевидно из названия, но мало что объясняет на практике?

Давайте посмотрим на картинке (источник внизу статьи)

Оконные функции с «форточкой» или как пользоваться фреймом - 7

Оконные функции с «форточкой» или как пользоваться фреймом - 8

Теперь, если мы внимательно посмотрим, то станет очевидно, что диапазоном называются строки с одинаковым значением параметра сортировки.

Как уже было сказано выше ROWS ограничивается именно строкой, в то время как RANGE захватывает весь диапазон совпадающих значений, которые у вас указаны в ORDER BY оконной функции.

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id RANGE UNBOUNDED PRECEDING) AS current_and_all_before2
FROM sales
ORDER BY 2, sales_id;

Оконные функции с «форточкой» или как пользоваться фреймом - 9

ROWS — всегда оперирует конкретной строкой, даже если сортировка не уникальна, а вот RANGE как раз объединяет в диапазоны периоды, с совпадающими значениями полей сортировки. В этом смысле функциональность очень похожа на поведение функции SUM() с сортировкой по неуникальному полю. Давайте посмотрим еще один пример.

SELECT sales_id, customer_id, price_per_item, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, price_per_item) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item RANGE UNBOUNDED PRECEDING) AS current_and_all_before2
FROM sales
ORDER BY 2, price_per_item;

Оконные функции с «форточкой» или как пользоваться фреймом - 10

Тут уже 2 поля и range определяется диапазоном с совпадающими значениями по обоим полям

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

SELECT sales_id, customer_id, price_per_item, cnt, 
SUM(cnt) OVER (ORDER BY customer_id DESC, price_per_item DESC) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_before2
FROM sales
ORDER BY 2, price_per_item, sales_id desc;

Оконные функции с «форточкой» или как пользоваться фреймом - 11

Как видно RANGE опять же захватывает весь диапазон совпадающих пар.

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

Источник иллюстрации про разницу RANGE и ROWS
Window functions with SQL Server 2016, Mark Tabladillo

Успеть на курс

Автор: KristinaMyLife

Источник