Начиная с версии ABAP 7.54 в ABAP SQL появились так называемые оконные выражения. Оконные выражения — SQL выражения определяемые с помощью дополнения OVER в запросе, позволяют определить окна, как подмножества итогового результата запроса и выполнять над ним оконные функции.
Оконные функции можно сравнить с агрегатными функциями, но, в отличие от обычной агрегатной функции, при использовании оконной функции несколько строк не группируются в одну, а продолжают существовать отдельно. При этом результаты работы оконных функций просто добавляются к результирующей выборке как еще одно поле.
Оконные функции являются частью стандарта SQL и даже если версия языка не позволит вам использовать их непосредственно в ABAP, всегда можно воспользоваться Native SQL (Так, по одной из задач, применение оконной функции внутри AMDP позволило ускорить работу запроса в десятки раз).
Далее на небольших примерах рассмотрим основные оконные функции и то, как с ними работать.
Синтаксис
Описание синтаксиса тут и далее взято из последней доступной версии ABAP — 7.55
Общий синтаксис оконного выражения выглядит следующим образом:
|
1 2 3 4 5 6 7 8 9 10 11 |
... win_func OVER( [PARTITION sql_exp1, sql_exp2 ...] [ORDER BY col1 [ASCENDING|DESCENDING], col2 [ASCENDING|DESCENDING] [ROWS BETWEEN {UNBOUNDED PRECEDING} |{CURRENT ROW} |{(n) PRECEDING} |{(n) FOLLOWING} AND {UNBOUNDED FOLLOWING} |{CURRENT ROW} |{(n) PRECEDING} |{(n) FOLLOWING}]] ) ... |
Оконное выражение состоит из оконной функции, за которой следует дополнение OVER, в круглых скобках которого определяется окно в общем наборе результатов (или подмножество), для которого отрабатывает оконная функция. Внутри окна опционально можно указать:
- PARTITION BY sql_exp1, sql_exp2 … Определяет перечень столбцов для группировки. Можно использовать все SQL выражения доступные в ABAP SQL за исключением оконных и агрегатных функций. Если оставить данный раздел пустым, будет обработан весь набор данных запроса.
- ORDER BY. Определяет как порядок сортировки, так и ограничения над набором данных в окне. Является обязательным для некоторых оконных функций ранжирования/смещения.
Если оконное выражение используется как поле выбора в запросе с группировкой GROUP BY, окна вычисляются над объединённым набором результатов, а результаты агрегатных функций этого запроса могут быть аргументами оконной функции.
Как и с общими табличными выражениями, они поддерживаются не на всех СУБД, проверку можно выполнить через класс CL_ABAP_DBFEATURES:
|
1 2 3 4 5 6 7 8 |
IF cl_abap_dbfeatures=>use_features( EXPORTING requested_features = VALUE #( ( cl_abap_dbfeatures=>windowing ) ) ). ... ELSE. ... ENDIF. |
Win_func определяет оконную функцию:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
... AVG( col [AS dtype] ) | MEDIAN( sql_exp ) | MAX( sql_exp ) | MIN( sql_exp ) | SUM( sql_exp ) | STDDEV( sql_exp ) | VAR( sql_exp ) | CORR( sql_exp1,sql_exp2 ) | CORR_SPEARMAN( sql_exp,sql_exp2 ) | COUNT( sql_exp ) | COUNT( * ) | COUNT(*) | ROW_NUMBER( ) | RANK( ) | DENSE_RANK( ) | NTILE( n ) | LEAD|LAG( sql_exp1[, diff[, sql_exp2]] ) | FIRST_VALUE|LAST_VALUE( col ) ... |
Оконные функции могут быть трёх видов:
- Агрегатные: AVG, MIN, MAX, SUM и др.,
- Функции ранжирования: ROW_NUMBER, RANK и др.,
- Функции смещения или функции значений (value functions): LEAD, LAG и др,
- Аналитические функции: PERCENT_RANK, CUME_DIST и др., пока не представлены в ABAP.
Данные для тестирования
В качестве тестовых данных будем рассматривать стандартную таблицу SFLIGHT и следующий набор данных:
Агрегатные функции
В следующем примере посчитаем максимальную сумму оплаты PAYMENTSUM в разрезе авиакомпаний и рейсов:
|
1 2 3 4 5 6 7 |
SELECT DISTINCT carrid, connid, MAX( paymentsum ) OVER( PARTITION BY carrid, connid ) AS max_sum FROM sflight ORDER BY carrid, connid INTO TABLE @DATA(lt_sflight). |
Результат:
Тут следует еще раз обратить внимание на то, что оконные функции не занимаются группировкой набора данных основного запроса, а формируют значение поля max_sum для каждой строки и чтобы взять только уникальные значения, мы задействовали в основном запросе DISTINCT.
В противном случае получили мы максимальную сумму по всем строкам основного запроса:
При выполнении оконной функции с помощью дополнения PARTITION BY мы определили группу, в рамках которой формируются окна, в рамках каждого из окна идёт расчёт максимального значения.
Аналогичным образом мы можем посчитать суммы по всем рейсам:
|
1 2 3 4 5 6 7 8 9 |
SELECT DISTINCT carrid, connid, sum( paymentsum ) OVER( PARTITION BY carrid, connid ) AS total_sum FROM sflight ORDER BY carrid, connid INTO TABLE @DATA(lt_sflight). |
Результат:
Используя агрегатные оконные функции, без указания ORDER BY подсчёт идёт один раз для всего окна (в функциях ранжирования выполняется расчёт для каждой строки, несмотря на пустой ORDER BY). Однако, если указать ORDER BY подсчёт в каждой строке будет осуществляться c учётом сортировки по каждой уникальной записи. Таким образом мы можем рассчитывать например какой-нибудь нарастающий итог.
Исходный набор данных по авиакомпании AZ:
Выполним по этой авиакомпании следующий запрос:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT carrid, fldate, sum( paymentsum ) OVER( PARTITION BY carrid ORDER BY carrid, fldate ) AS balance FROM sflight WHERE carrid = 'AZ' ORDER BY carrid, fldate INTO TABLE @DATA(lt_sflight). |
В данном примере мы сделали окно по авиакомпании, где для каждой новой даты рассчитали баланс по сумме — PAYMENTSUM, т.е. для каждой строки с уникальной датой посчитали итог от начала окна до текущей в обработке строки. Получили нарастающий итог по каждой дате.
Кстати, оконные функции можно использовать сразу по несколько штук и они не мешают выполнению друг друга:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT DISTINCT carrid, fldate, sum( paymentsum ) OVER( PARTITION BY carrid ORDER BY carrid, fldate ) AS balance, sum( paymentsum ) OVER( PARTITION BY carrid ) AS total FROM sflight ORDER BY carrid, fldate INTO TABLE @DATA(lt_sflight). |
В результате получим:
Ранжирующие функции
Ранжирующие функции – это функции, которые ранжируют значение для каждой строки в окне. Например, их можно использовать для того, чтобы присвоить порядковый номер строке или составить рейтинг.
- ROW_NUMBER – функция возвращает номер строки и используется для нумерации;
- RANK — функция возвращает ранг каждой строки. В данном случае значения уже анализируются и, в случае нахождения одинаковых, возвращает одинаковый ранг с пропуском следующего значения;
- DENSE_RANK — функция возвращает ранг каждой строки. Но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий;
В качестве самой простой задачи, мы можем выбрать все уникальные коды авиакомпаний и пронумеровать их по порядку:
|
1 2 3 4 5 6 |
SELECT carrid, ROW_NUMBER( ) OVER( ORDER BY carrid ) AS row_number FROM sflight GROUP BY carrid ORDER BY row_number INTO TABLE @DATA(lt_sflight). |
Результат:
Порядок нумерации может быть изменён как внутри окна, так и относительно результата оконной функции:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT carrid, ROW_NUMBER( ) OVER( ORDER BY carrid DESCENDING ) AS row_number FROM sflight GROUP BY carrid ORDER BY carrid INTO TABLE @DATA(lt_sflight). " Тоже самое что и: * SELECT carrid, * ROW_NUMBER( ) OVER( ORDER BY carrid ) AS row_number * FROM sflight * GROUP BY carrid * ORDER BY row_number DESCENDING * INTO TABLE @DATA(lt_sflight). |
Усложним пример, добавив в оконную функцию ограничение по коду авиакомпании:
|
1 2 3 4 5 6 7 8 9 |
SELECT carrid, connid, ROW_NUMBER( ) OVER( PARTITION BY carrid ) AS row_number FROM sflight GROUP BY carrid, connid ORDER BY carrid, row_number INTO TABLE @DATA(lt_sflight). |
Результат:
В данном примере мы добавили ограничение окна на поле CARRID, таким образом оконная функция подсчёта строк работает уже над тремя подмножествами запроса по трём имеющимся авиакомпаниям, где для каждой авиакомпании ведёт нумерацию рейсов.
Результат работы оконной функции невозможно отфильтровать в запросе с помощью WHERE, потому что оконные функции выполняются после всей фильтрации и группировки, т.е. с тем, что получилось. Соответственно нельзя добавить в WHERE row_number = 2 и выбрать только вторые строки. Но можно воспользоваться общими табличными выражениями:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
WITH +window_result AS ( SELECT carrid, connid, ROW_NUMBER( ) OVER( PARTITION BY carrid ORDER BY carrid, connid ) AS row_number FROM sflight GROUP BY carrid, connid ORDER BY carrid, connid ) SELECT * FROM +window_result WHERE row_number = 2 INTO TABLE @DATA(lt_sflight). |
В результате получим только строку где row_number = 2.
Прочие ранжирующие функции можно рассмотреть на примере:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT carrid, fldate, ROW_NUMBER( ) OVER( PARTITION BY carrid ORDER BY fldate ) AS row_number, RANK( ) OVER( PARTITION BY carrid ORDER BY fldate ) AS rank, DENSE_RANK( ) OVER( PARTITION BY carrid ORDER BY fldate ) AS dense_rank FROM sflight ORDER BY carrid, fldate INTO TABLE @DATA(lt_sflight). |
row_number — в рамках каждой авиакомпании выставляет номер строки, rank — выставляет ранг строки (для повторяющихся fldate он одинаковый) с увеличением счётчика, dense_rank — аналогичен rank( ) но без увеличения счётчика.
Функции смещения (функции значений)
Функции смещения – это функции, которые позволяют перемещаться и обращаться к разным строкам в окне, относительно текущей строки, а также обращаться к значениям в начале или в конце окна.
- LEAD – функция обращается к данным из следующей строки набора данных. Ее можно использовать, например, для того чтобы сравнить текущее значение строки со следующим.
- LAG – функция обращается к данным из предыдущей строки набора данных. В данном случае функцию можно использовать для того, чтобы сравнить текущее значение строки с предыдущим.
Рассмотрим простой пример:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT carrid, fldate, LAG( fldate ) OVER( PARTITION BY carrid ORDER BY fldate ) AS lag_date, LEAD( fldate, 2 ) OVER( PARTITION BY carrid ORDER BY fldate ) AS lead_date FROM sflight ORDER BY carrid, fldate INTO TABLE @DATA(lt_sflight). |
В результате получим:
Как видно из примера, для строк с функцией LAG предыдущая строка для первых строк в окне недоступна, значение будет NULL, по при преобразовании к ABAP типу вернётся начальное значение для даты.
Для функции LEAD мы использовали дополнение, указывающее на число строк, которые необходимо пропустить после текущей для получения значения (по умолчанию 1). Соответственно для двух последних строк в каждом окне значение даты определить не удалось.
Ограничения строк
Начиная с версии ABAP 7.55 появилась дополнительная возможность ограничивать строки обрабатываемые в рамках окна, задавая так называемый фрейм (или рамку). Фрейм определяется относительно текущей строки окна, что позволяет ему перемещаться одновременно с расчётом строк внутри окна. Каждый фрейм имеет начальную и конечную границу, существует три варианта определения границ фрейма:
- {UNBOUNDED PRECEDING}/{UNBOUNDED FOLLOWING}
- UNBOUNDED PRECEDING начальная граница фрейма определяется начальной строкой окна.
- UNBOUNDED FOLLOWING определяет конечную границу фрейма, как последнюю строку окна.
- CURRENT ROW может определять как конечную, так и начальную границу фрейма (в зависимости от комбинации другими ключевыми словами). .
- {(n) PRECEDING}/{(n) FOLLOWING}
- (n) PRECEDING может использоваться для определения начала и конца фрейма. Определяет что фрейм начинается или заканчивается до N строк относительно текущей строки.
- (n) FOLLOWING может использоваться для определения начала и конца фрейма. Определяет что фрейм начинается или заканчивается после N строк относительно текущей строки.
Т.к. версии 7.55 под рукой у меня нет (Docker образ ABAP Platform 2020 еще в работе :)), рассмотрим пример из сети:
|
1 2 3 4 5 |
SELECT Date, Medium, Conversions, SUM(Conversions) OVER( PARTITION BY Date ORDER BY Conversions ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) AS 'Sum' FROM Orders. |
Границы фрейма определены от текущей строки до следующей за ней.
В данном случае сумма рассчитывается по текущей и следующей ячейке в окне. А последняя строка в окне имеет то же значение, что и столбец «Conversions», потому что больше не с чем складывать. Комбинируя ключевые слова, вы можете подогнать диапазон работы оконной функции под вашу специфическую задачу.
Благодаря таким ограничениям можно легко находить скользящие средние значения, определяя сколько следующих строк фрейма следует учитывать в агрегатной функции AVG.
В стандарте SQL кроме выражения ROWS есть так же дополнение RANGE которое пока не представлено в ABAP. Предложение RANGE предназначено также для ограничения набора строк. В отличие от ROWS, оно работает не с физическими строками, а с диапазоном строк в предложении ORDER BY. Это означает, что одинаковые по рангу строки в контексте предложения ORDER BY будут считаться как одна текущая строка для функции CURRENT ROW. Более подробно можете ознакомиться в этом материале.