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

This example demonstrates simple window expressions.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA(out) = cl_demo_output=>new( ).

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

    out->display( windowed ).

Description

The example demonstrates how different window functions are applied to windows of a results set of a query defined using PARTITION. The content of the columns CHAR1 and CHAR2 is used as a window criterion. All rows that have identical content in these columns form a window. The result of a window function for a row is determined from all rows of the window this row is a part of. The final column, perc, demonstrates how a window function can be used as arguments of an SQL expression and hence produce row-specific results, in this case the percentage of the value of the column NUM1 as part of the full window.