Ansicht
Dokumentation

ABENSQL_WIN_FUNC - SQL WIN FUNC

BAL Application Log Documentation   PERFORM Short Reference  
This documentation is copyright by SAP AG.

- win_func

... AVG( col $[AS dtype$] )
  $| MEDIAN( sql_exp )
  $| MAX( sql_exp )
  $| MIN( sql_exp )
  $| SUM( sql_exp )
  $| STDDEV( 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 ) ...

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 )

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

Variant 1

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

Effect

Specifies one of the aggregate functions AVG, MEDIAN, MAX, MIN, STDDEV, 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, STDDEV 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.

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, but not rows with different results of sql_exp.

Window Expressions with Grouping

Variant 2

... ROW_NUMBER( )

Effect

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.

Note

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

Examples of Window Expressions

Variant 3

... RANK( )

Effect

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.

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.

Window Expressions with Sort

Variant 4

... DENSE_RANK( )

Effect

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.

Note

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

Window Expressions with Sort

Variant 5

... NTILE( n ) OVER( $[PARTITION BY sql_exp1$]
               ORDER BY col $[ASCENDING$|DESCENDING$]) ...

Effect

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

Note

Since the maximum number of rows in a bucket can vary by 1, rows with the same value can also be in different buckets.

Example

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.

Window Function NTILE.

Variant 6

... 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 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 the corresponding distance after the current row.

• For the function LAG, diff determines the row positioned the 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.

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.

• If the window functions LEAD or LAG are used, the syntax check is performed in strict mode from Release .

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 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 program DEMO_SELECT_OVER_LEAD_LAG_DIFF uses this SELECT statement and, when executed, the program displays the result.

Window Functions LEAD and LAG

Variant 7

... FIRST_VALUE$|LAST_VALUE( col )

Effect

Specifies the value functions FIRST_VALUE and 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's 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.

Example

The program DEMO_SELECT_FIRST_LAST divides the rows from the DEMO_UPDATE table into three partitions, depending on their value in COL1. Within the partitions, the rows are ordered by their value in COL3.

The column FIRST_VALUE returns the first value of COL2 for each partition.

The column LAST_VALUE does not return the last value. As described above, the default frame is from the first row to the current row. If COL3 contains duplicate values, the rows are considered equal and the last value from the group of equals is returned. To get the last value of COL2 of the partition, the frame size has to be specified explicitly, as demonstrated in LAST_VALUE_CORRECT.

In this example, COL3 has multiple duplicate values. The key field - here the field ID - is used to sort rows with the same value.

IMAGE @@ABDOC_FIRST_LAST_VALUE.png@@487@@410@@

Vendor Master (General Section)   SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up  
This documentation is copyright by SAP AG.

Length: 21386 Date: 20240424 Time: 082135     sap01-206 ( 306 ms )