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( )
 LEADLAG(
sql_exp1[, diff[, sql_exp2]] ) ...
Variants
1. ... AVG( ... )  ...  COUNT(*)
2. ... ROW_NUMBER( )
3. ... RANK(
)
4. ... DENSE_RANK( )
5. ... LEADLAG( 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( )
, andDENSE_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 gapfree. DENSE_RANK
can be used to remove gaps.
Executable Example
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
Variant 5
... LEADLAG( 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 bysql_exp1
. 
If
sql_exp2
is specified, the results ofsql_exp1
andsql_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
orLAG
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
orLAG
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.