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

sql_exp - win_func

Other versions: ~~7.31~~ | ~~7.40~~ | 7.54

Syntax


... AVG(
col [AS dtype] ) 

  | MAX( sql_exp ) 

  | MIN( sql_exp ) 

  | SUM( sql_exp ) 

  | COUNT( sql_exp ) 

  | COUNT( * ) 
  | COUNT(*) 

 
  | ROW_NUMBER( ) 
  | RANK( ) 
  | DENSE_RANK( ) 

 
  | LEAD|LAG(
sql_exp1[, diff[, sql_exp2]] ) ...

Variants

1. ... AVG( ... ) | ... | COUNT(*)

2. ... ROW_NUMBER( )

3. ... RANK( )
4. ... DENSE_RANK( )

5. ... LEAD|LAG( sql_exp1[, diff[, sql_exp2]] )

Effect

Window function in a window expression. Window functions are:

Aggregate functions AVG( ... ), ..., COUNT(*) that can also be used in aggregate expressions.
Ranking functions ROW_NUMBER( ), RANK( ), and DENSE_RANK( ) that can only be used in window expressions.
Value functions LEAD( ... ), LAG( ... ) that can be used only in window expressions.

Variant 1

... AVG( ... ) | ... | COUNT(*)

Effect

Specifies one of the aggregate functions MAX, MIN, SUM, COUNT, or COUNT(*) as a window function. The aggregate functions evaluate the rows of the current window or of the frame defined by an addition ORDER BY after OVER. The aggregate functions are applied as in the general description, with the following differences:

The addition DISTINCT is not allowed in a window expression.
The result of the function COUNT has the data type INT8 and not INT4.

The same applies to the arguments of aggregate functions as in the general description, with the difference that the argument of an aggregate function in a window expression can itself, as a window function, be an aggregate function. This is the precise case when a grouping is made using the GROUP BY clause in the current query. The windows on the combined results set are then defined and the aggregate expressions permitted as specified columns of the current SELECT list can be used either as standalone expressions or as part of an SQL expression (as an argument of window functions of the window expressions in question). A window function then determines its result from the aggregated values of the rows of the current window.

Note

The addition DISTINCT cannot be specified, which means that COUNT( sql_exp ) can only be used to count rows that do not contain a null value and cannot be used to count rows with different results of sql_exp.

Executable Example

Window Expressions with Grouping

Variant 2

... ROW_NUMBER( )

Effect

Specifies the ranking function ROW_NUMBER as a window function. This ranking function grants each row a row number of the data type INT8 and does not have an argument. The rows of each window are numbered starting with 1. This numbering takes place in the order in which the rows of a window are processed. The order is either undefined or can be defined by specifying the addition ORDER BY after OVER.

Note

If ORDER BY is not specified after OVER, ROW_NUMBER still grants a unique row number, but these numbers are not ordered.

Executable Examples

Examples of Window Expressions

Variant 3

... RANK( )

Effect

Specifies the ranking function RANK as a window function. This ranking function grants each row a rank of the data type INT8 and does not have an argument. It can only be specified together with ORDER BY after OVER.

The rank of a row is the position of this row in the ranking defined by the addition ORDER BY after OVER and is defined as follows:

All rows that occur more than once (with respect to the sort criterion) have the same rank. This rank is the lowest row number in this group, as determined by the function ROW_NUMBER.
The first group of each window starts with the value 1.

Note

If a window does not contain any multiple rows (with respect to the sort criterion), RANK produces the same result as ROW_NUMBER. If any other cases, a ranking determined by RANK is not gap-free. DENSE_RANK can be used to remove gaps.

Executable Example

Window Expressions with Sort

Variant 4

... DENSE_RANK( )

Effect

Specifies the ranking function DENSE_RANK as a window function. This ranking function grants each row a rank of the data type INT8 and does not have an argument. It can only be specified together with ORDER BY after OVER.

DENSE_RANK works in largely the same way as RANK, but counts without any gaps, starting from the first group, and does not determine the rank using the lowest row number of groups of identical values (with respect to the sort criterion).

Note

If a window does not contain any multiple rows (with respect to the sort criterion), DENSE_RANK produces the same result as RANK.

Executable Example

Window Expressions with Sort

Variant 5

... LEAD|LAG( sql_exp1[, diff[, sql_exp2]]

Effect

Specifies the value functions LEAD or LAG as a window function. They can only be specified together with ORDER BY after OVER.

The result of the functions is the value of the SQL expression sql_exp1 for the row of the current window or for the row of the box defined by an addition ORDER BY after OVER. This row is itself defined by the addition diff. diff can be specified as a literal or a host constant with the ABAP type b, s, i, int8, whose value is a positive number other than 0.

For the function LEAD, diff determines the row positioned the distance in question after the current row.
For the function LAG, diff determines the row positioned the distance in question in front of the current row.

If diff is not specified, the value 1 is used. In the case of LEAD, this is the row that follows directly and in the case of LAG, the directly preceding row. If the row determined by diff is not in the current window, the result is the null value by default. If the optional SQL expression sql_exp2 is specified, it is evaluated and returned as the current row in cases where the row does not exist.

The result of the functions LEAD and LAG has the following data type:

If sql_exp2 is not specified, the data type is determined by sql_exp1.
If sql_exp2 is specified, the results of sql_exp1 and sql_exp2 must match in a way that a common result type can be determined: The data types must either be the same or the data type of one expression must represent the value of the other expression. The result has the dictionary type of the expression with the largest value range.

Notes

The window functions LEAD or LAG are suited to calculations such as, for example, determining the difference between values in the current row and values of the preceding or following rows.
If the window functions LEAD or LAG are used, the syntax check is applied in strict mode from Release 7.54.

Example

SELECT statement with the window functions LEAD and LAG as operands of an arithmetic expression. The addition PARTITION is not specified, which means there is only one window containing all rows of the results set. Both LEAD and LAG have only one argument each, which means that the difference between the values of the column NUM1 is calculated using the directly following or preceding row, and any nonexistent rows produce null values. These values are defined using a null indicator. The program DEMO_SELECT_OVER_LEAD_LAG_DIFF uses this SELECT statement and, when executed, the program displays the result.

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.

Executable Example

Window Functions LEAD and LAG