(ABAP 코드) WINDOW FUNCTION
** https://blogs.sap.com/2021/11/24/window-expressions-in-abap-sql/
** https://blogs.sap.com/2021/11/30/new-window-functions-in-abap-sql/
SELECT char1, char2, num1,
COUNT(*) OVER( PARTITION BY char1, char2 ) AS cnt,
MAX( num1 ) OVER( PARTITION BY char1, char2 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1, char2 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1, char2 ) AS sum,
RANK( ) OVER( PARTITION BY char1, char2 ORDER BY num1 ) AS rank, " Allow Duplicates
* rank( ) over( partition by char1, char2 order by num1 DESCENDING ) as rank " Don't allow Duplicates
DENSE_RANK( ) OVER( PARTITION BY char1, char2 ORDER BY num1 ) AS dens_rank
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @DATA(window_test_no_order).
**SELECT char1 && '_' && char2 AS group,
** num1,
** COUNT(*) OVER( PARTITION BY char1, char2 ) AS cnt,
** ROW_NUMBER( ) OVER( PARTITION BY char1, char2 ) AS rnum,
** MIN( num1 ) OVER( PARTITION BY char1, char2 ) AS min,
** MAX( num1 ) OVER( PARTITION BY char1, char2 ) AS max,
** SUM( num1 ) OVER( PARTITION BY char1, char2 ) AS sum,
** division( 100 * num1,
** SUM( num1 ) OVER( PARTITION BY char1, char2 ),
** 2 ) AS perc
** FROM demo_expressions
** ORDER BY group
** INTO TABLE @DATA(windowed).
*&---------------------------------------------------------------------*
*& .. 자주 쓰일 만한 것 정리
*&---------------------------------------------------------------------*
SELECT carrid, connid, price,
COUNT( * ) OVER( ORDER BY carrid, connid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
AS cnt_by_all,
COUNT( * ) OVER( PARTITION BY carrid, connid
ORDER BY carrid, connid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
AS cnt_by_flight,
COUNT( * ) OVER( ORDER BY carrid, connid
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
AS cnt_reverse_by_all,
COUNT( * ) OVER( PARTITION BY carrid ,connid
ORDER BY carrid, connid
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
AS cnt_reverse_by_flight,
AVG( price ) OVER( ORDER BY carrid, connid
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING )
AS average_by_all,
AVG( price ) OVER( PARTITION BY carrid, connid
ORDER BY carrid, connid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
AS average_by_flight,
SUM( price ) OVER( ORDER BY carrid, connid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
AS accumulate_by_all,
SUM( price ) OVER( PARTITION BY carrid, connid
ORDER BY carrid, connid, price
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
AS accumulate_by_flight
FROM sflight
ORDER BY carrid, connid, accumulate_by_flight DESCENDING
INTO TABLE @DATA(gt_flight).
DATA(gt_max) = gt_flight[].
CLEAR: gt_max[].
** .. 각 항공편 별 마지막 값 구하기
LOOP AT gt_flight INTO DATA(ls_flight)
GROUP BY ( carrid = ls_flight-carrid
connid = ls_flight-connid )
ASCENDING
* WITHOUT MEMBERS
ASSIGNING FIELD-SYMBOL(<group>).
APPEND INITIAL LINE TO gt_max ASSIGNING FIELD-SYMBOL(<gs_max>).
IF <gs_max> IS ASSIGNED.
LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<fs_group>).
IF <fs_group> IS ASSIGNED.
<gs_max> = <fs_group>.
UNASSIGN <fs_group>.
UNASSIGN <gs_max>.
EXIT.
ENDIF.
ENDLOOP.
ENDIF.
ENDLOOP.
*
*LOOP AT gt_flight ASSIGNING FIELD-SYMBOL(<gs_flight>).
* ON CHANGE OF <gs_flight>-connid.
* APPEND INITIAL LINE TO gt_max ASSIGNING FIELD-SYMBOL(<gs_max>).
* <gs_max> = <gs_flight>.
* UNASSIGN <gs_max>.
* ENDON.
*ENDLOOP.
*&---------------------------------------------------------------------*
**& .. LEAD, LAG
*&---------------------------------------------------------------------*
*https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/5932eebb6208406590071eb65c6caa83.html?locale=en-US&version=2.0.05
SELECT num1 AS number,
num1 - LEAD( num1 ) OVER( ORDER BY id ) AS diff_lead,
num1 - LAG( num1 ) OVER( ORDER BY id ) AS diff_lag
FROM demo_expressions
ORDER BY id
INTO TABLE @DATA(lead_lag_diffs)
INDICATORS NULL STRUCTURE null_ind.
SELECT id,
num1,
LEAD( num1 ) OVER( PARTITION BY id ORDER BY num1 ) AS lead,
LEAD( num1, 1, num1 ) OVER( PARTITION BY id ORDER BY num1 ) AS lead2,
LAG( num1 ) OVER( PARTITION BY id ORDER BY num1 ) AS lag,
LAG( num1, 2, num1 ) OVER( PARTITION BY id ORDER BY num1 ) AS lag2
FROM demo_expressions INTO TABLE @DATA(lead_lag).
*&---------------------------------------------------------------------*
**& .. FIRST_VALUE and LAST_VALUE
*&---------------------------------------------------------------------*
SELECT
id,
col1,
col2,
col3,
FIRST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3 )
AS first_value,
LAST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3 )
AS last_value,
LAST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING )
AS last_value_correct
FROM demo_update
INTO TABLE @DATA(result).
BREAK-POINT.
*&---------------------------------------------------------------------*
**& .. NTILE
*&---------------------------------------------------------------------*
CLASS demo DEFINITION.
PUBLIC SECTION.
CLASS-METHODS:
main,
class_constructor.
ENDCLASS.
CLASS demo IMPLEMENTATION.
METHOD main.
DATA(out) = cl_demo_output=>new( ).
SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1 ) AS rnum,
'-' AS rank,
'-' AS schlank,
MAX( num1 ) OVER( PARTITION BY char1 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1 ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @DATA(windowed_no_order).
out->write( windowed_no_order ).
SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1
ORDER BY char2 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS rnum,
RANK( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS rank,
DENSE_RANK( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS schlank,
MAX( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @DATA(windowed_order_ascending).
out->write( windowed_order_ascending ).
SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS rnum,
RANK( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS rank,
DENSE_RANK( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS schlank,
MAX( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS max,
MIN( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS min,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1 DESCENDING, char2 DESCENDING
INTO TABLE @DATA(windowed_order_descending).
out->write( windowed_order_descending ).
out->display( ).
ENDMETHOD.
METHOD class_constructor.
TYPES tab_type TYPE STANDARD TABLE OF
demo_expressions WITH EMPTY KEY.
DELETE FROM demo_expressions.
INSERT demo_expressions FROM TABLE @( REDUCE tab_type(
LET r1 = cl_abap_random_int=>create(
seed = CONV i( sy-uzeit ) min = 1 max = 10 )
r2 = cl_abap_random_int=>create(
seed = CONV i( r1->get_next( ) ) min = 0 max = 1 )
c = `AB` IN
INIT t TYPE tab_type
FOR i = 0 THEN i + 1 UNTIL i >= 25
NEXT t = VALUE #(
BASE t ( id = sy-abcde+i(1)
num1 = r1->get_next( )
char1 = substring( val = c
off = r2->get_next( )
len = 1 ) &&
substring( val = c
off = r2->get_next( )
len = 1 )
char2 = substring( val = c
off = r2->get_next( )
len = 1 ) &&
substring( val = c
off = r2->get_next( )
len = 1 ) ) ) ) ).
ENDMETHOD.
ENDCLASS.
START-OF-SELECTION.
demo=>main( ).