ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Expressions sql_exp →  sql_exp - sql_win →  ABAP SQL - Examples of Window Expressions 

SQL Expressions, Window Expressions with Sort

This example demonstrates window expressions with a sort.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    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( ).

Description

The example compares SELECT statements with identical window expressions with and without the addition ORDER BY specified after OVER. The use of the ranking functions RANK and DENSE_RANK is only possible in combination with this addition.

• If the addition ORDER BY is not specified after OVER, the window functions are executed in an undefined order for all rows of the windows defined by the content of the column CHAR1:

• The aggregate functions COUNT, MAX, MIN, and SUM evaluate all rows of a window.

• The ranking function ROW_NUMBER grants a sequential row number within each window, which matches the undefined order of processing.

• When the addition ORDER BY is specified after OVER the window functions within the window are executed once in ascending order and once in descending order, with respect to the content of the column CHAR2. Only the rows of a frame are respected:

• Within a window (that is all rows with identical content in the column CHAR1), the aggregate functions COUNT, MAX, MIN, and SUM evaluate only those rows that have identical content in CHAR2 or that precede it in the sort.

• The ranking function ROW_NUMBER grants the row number in accordance with the sort order. The order is then only undefined in rows with identical content in the column CHAR2. The ranking functions RANK and DENSE_RANK return the rank with respect to the sort order within the current window. Here, identical content in the column CHAR2 has the same rank.

The addition ORDER BY of the SELECT statement is used only to highlight the output. It does not modify the results for the individual rows.