SAP SQL WIN FUNC
Get Example source ABAP code based on a different SAP table
• MAX ABAP_OSQL_WIN_FUNC
• MIN ABAP_OSQL_WIN_FUNC
• SUM ABAP_OSQL_WIN_FUNC
• MEDIAN ABAP_OSQL_WIN_FUNC
• Standard SAP Help forDEV ABAP_OSQL_WIN_FUNC
• VAR ABAP_OSQL_WIN_FUNC
• CORR ABAP_OSQL_WIN_FUNC
• CORRSPEARMAN ABAP_OSQL_WIN_FUNC
• COUNT ABAP_OSQL_WIN_FUNC
• ROW_NUMBER ABAP_OSQL_WIN_FUNC
• RANK ABAP_OSQL_WIN_FUNC
• DENSE_RANK ABAP_OSQL_WIN_FUNC
• LEAD ABAP_OSQL_WIN_FUNC
• LAG ABAP_OSQL_WIN_FUNC
• NTILE ABAP_OSQL_WIN_FUNC
• FIRST_VALUE ABAP_OSQL_WIN_FUNC
• LAST_VALUE ABAP_OSQL_WIN_FUNC
ABAP_SQLWIN - win_func ABAP_SYNTAX ... AVG( col $[AS dtype $] )
$| MEDIAN( sql_exp )
$| MAX( sql_exp )
$| MIN( sql_exp )
$| SUM( sql_exp )
$| Standard SAP Help forDEV( sql_exp )
$| VAR( sql_exp )
$| CORR( sql_exp1, sql_exp2 )
$| CORR_SPEARMAN( sql_exp,sql_exp2 )
$| COUNT( sql_exp )
$| COUNT( * )
$| COUNT(*)
$| ROW_NUMBER( )
$| RANK( )
$| DENSE_RANK( )
$| NTILE( n )
$| LEAD$|LAG( sql_exp1$[, diff$[, sql_exp2$]$] )
$| FIRST_VALUE$|LAST_VALUE( col$| sql_null ) ...
ABAP_VARIANTS:
1 ... AVG( ... ) $| ... $| COUNT(*)
2 ... ROW_NUMBER( )
3 ... RANK( )
4 ... DENSE_RANK( )
5 ... NTILE( n )
6 ... LEAD$|LAG( sql_exp1$[, diff$[, sql_exp2$]$] )
7 ... FIRST_VALUE$|LAST_VALUE( col|sql_null )
What does it do?
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( ), DENSE_RANK( ), and NTILE (n) that can only be used in window expressions.
Value functions LEAD( ... ) , LAG( ... ), FIRST_VALUE ( ... ), LAST_VALUE ( ... ) that can be used only in window expressions.
ABAP_VARIANT_1 ... AVG( ... ) $| ... $| COUNT(*)
What does it do?
Specifies one of the aggregate functions AVG , MEDIAN, MAX , MIN , Standard SAP Help forDEV, VAR, CORR, CORR_SPEARMAN, 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.
When used in window expressions, Standard SAP Help forDEV and VAR can have only data type FLTP as argument.
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 result set are then defined and the aggregate expressions allowed 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 there. A window function then determines its result from the aggregated values of the rows of the current window.
Latest notes:
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, but not rows with different results of sql_exp .
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_ABEXA
Window Expressions with Grouping
ABAP_EXAMPLE_END
ABAP_VARIANT_2 ... ROW_NUMBER( )
What does it do?
Specifies the ranking function ROW_NUMBER as a window function. This ranking function assigns 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.
Latest notes:
If ORDER BY is not specified after OVER , ROW_NUMBER still assigns a unique row number, but these numbers are not sorted.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLES_ABEXA
Examples of Window Expressions
ABAP_EXAMPLE_END
ABAP_VARIANT_3 ... RANK( )
What does it do?
Specifies the ranking function RANK as a window function. This ranking function assigns 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.
Latest notes:
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.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_ABEXA
Window Expressions with Sort
ABAP_EXAMPLE_END
ABAP_VARIANT_4 ... DENSE_RANK( )
What does it do?
Specifies the ranking function DENSE_RANK as a window function. This ranking function assigns 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.
Latest notes:
If a window does not contain any multiple rows with respect to the sort criterion, DENSE_RANK produces the same result as RANK.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_ABEXA
Window Expressions with Sort
ABAP_EXAMPLE_END
ABAP_VARIANT_5 ... NTILE( n ) OVER( $[PARTITION BY sql_exp1$]
ORDER BY col $[ASCENDING$|DESCENDING$]) ...
What does it do?
Specifies the ranking function NTILE as a window function. This window function divides the rows of a window into n buckets. The goal is to fill all buckets with the same number of rows by following the rule specified after ORDER BY.
If the number of rows of the window m cannot be distributed equally between the number of buckets n, the remainder r is distributed in such a way that the first (m MOD n) buckets each contain one element more. The buckets are numbered starting with the start value 1 and the result of the NTILE function is the number of the bucket a particular row belongs to.
n must be a host variable, a host expression, or a literal of type b, s, i, or int8 which represents a positive integer. The OVER clause including ORDER BY is mandatory.
If n is negative, for literals and host constants, a syntax error occurs. If n is a variable or an expression, instead of a syntax error, a database error and its respective exception CX_SY_OPEN_SQL_DB can occur. The result of the NTILE function is always of type INT8.
Latest notes:
Since the maximum number of rows in a bucket can vary by 1, rows with the same value can also be in different buckets.
NON_V5_HINTS
ABAP_HINT_END
ABAP_EXAMPLE_VX5
Sorting of all employees listed in table DEMO_EMPLOYEES by their salary and distributes them into five salary groups. Group 1 has one entry more, as the number of employees (11) cannot be distributed into five groups of equal size.
ABEXA 01422
ABAP_EXAMPLE_END
ABAP_EXAMPLE_ABEXA
Window Function NTILE
ABAP_EXAMPLE_END
ABAP_VARIANT_6 ... LEAD$|LAG( sql_exp1$[, diff$[, sql_exp2$]$]
What does it do?
Specifies one of 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 defined by the addition diff or the box defined by the addition ORDER BY after OVER. For diff , a literal or a host constant with the ABAP type b, s, i, int8 can be specified, whose value is a positive number other than 0.
For the function LEAD, diff determines the row positioned in a corresponding distance after the current row.
For the function LAG, diff determines the row positioned in a corresponding distance in front of the current row.
If diff is not specified, the value 1 is used implicitly. 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 for 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 an expression must represent the value of the other expression. The result has the dictionary type of the expression with the largest value range.
Latest notes:
The window functions LEAD or LAG are suitable for calculations, such as determining the difference between values in the current row and values of the preceding or following rows.
NON_V5_HINTS
If the window functions LEAD or LAG are used, the syntax check is performed in ABAP_STRICT_777 strict mode from ABAP_RELEASE ABAP_777 / .
ABAP_HINT_END
ABAP_EXAMPLE_VX5
SELECT statement with the window functions LEAD and LAG as operands of an arithmetic expression . The addition PARTITION BY is not specified, which means there is only one window with all rows of the result 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. The latter are defined using a null indicator. The class CL_DEMO_SELECT_OVER_LEAD_LAG_D uses this SELECT statement and displays the result.
ABEXA 01291
ABAP_EXAMPLE_END
ABAP_EXAMPLE_ABEXA
Window Functions LEAD and LAG
ABAP_EXAMPLE_END
ABAP_VARIANT_7 ... FIRST_VALUE$|LAST_VALUE( col$|sql_null )
What does it do?
Specifies one of the value functions FIRST_VALUE or LAST_VALUE as a window function. The FIRST_VALUE function returns the first value of a sorted set of values, the LAST_VALUE function returns the last value of a sorted set of values.
If the value is null or if the expression is empty, null is returned (see example, row H).
OVER and ORDER BY are mandatory. PARTITION BY is optional. If a window is divided into partitions, the FIRST_VALUE /LAST_VALUE function returns a result for each partition (see example). If there is no PARTITON BY clause, the functions work on the entire window.
With the LAST_VALUE function, framing is an important aspect to consider. The default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the LAST_VALUE function always returns the value from the current row. To find the last value for a partition or a window, the correct frame has to be specified explicitly: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. 