In ABAP SQL, A window expression uses a window function to determine an individual value from the rows of a window of the result set of a query. The term window describes the set of rows on which the function operates. In other words, a window function uses values from the rows in a window to calculate the returned values.
A window expression can be specified as an SQL expression or as an argument of any SQL expressions, except for aggregate and defining a column of the result set in the SELECT list of a query. In each row of the result set, the result of the calculation is placed in the column defined in this way.
Basic Windowing Syntax
The window functions of a window expression work with the rows of a virtual table, which is defined by the specifications after addition OVER ( … ) where in the parentheses the window on the result set is defined for whose rows the window function is evaluated. It is done using the following additions:
◉ PARTITION BY sql_exp1, sql_exp2 …
If you’d like to narrow the window from the entire dataset to individual groups within the dataset, you can use PARTITION BY to do so. This optional addition defines the windows using a comma-separated list of SQL expressions sql_exp1, sql_exp2, … . A window is constructed of the rows of the result set for which all SQL expressions have the same result. The window function is calculated for the rows of the respective current window. If PARTITION is not specified, a single window is constructed that comprises all rows of the result set.
◉ ORDER BY
The optional addition ORDER BY introduces both an order and a frame within the current window, which further restricts the rows for which the window function is calculated. It is also a prerequisite for certain ranking functions.
The ORDER and PARTITION define what is referred to as the “window”, the ordered subset of data over which calculations are made. The addition PARTITION works in a similar way as in the GROUP BY clause in an aggregate expression. However, unlike aggregate expressions, there is no aggregation of the rows defined using PARTITION. Instead, these are retained and are all assigned the value calculated using the window expression.
This means, use of a window function does not cause rows to become grouped into a single output row, the rows retain their separate identities. The addition ORDER BY of the SELECT statement has no effect on the result of a window expression. Hence, the rows of the result set can be sorted according to the results of window expressions by using their alias name.
A window expression can only be specified in the result set in the SELECT list of a query. It cannot be used like an aggregate expression in the GROUP BY or HAVING clause. If a window expression is used in the SELECT list of a SELECT statement with GROUP BY clause, the windows are defined in the merged result list and aggregate expressions can be used as arguments of the window functions. Each column that is specified in any position in the window expression must also be specified in the GROUP BY clause.
window expressions can be used in other SQL expressions as well. However, the calculations combine values of the current row with the results of window expressions, for example, the percentage of a column in the current window, or the distance to the minimum or maximum value of the current window.
Example:
This example demonstrates simple window expressions.
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).
The example demonstrates how different window functions are applied to windows of a result 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-dependent results, in this case the percentage of the value of the column NUM1 as part of the entire window.
Optional Window Frame Specification
The window frame specification is an optional addition within the OVER( … ORDER BY … ) clause. It allows the definition of a subset of rows inside a window, which is also referred to as a frame. Frames are determined with respect to the current row, which enables the frame to move within a window. Some of these optional frames are as follows.
… ORDER BY col1 [ASCENDING|DESCENDING], col2[ASCENDING|DESCENDING], …
The definition of the window, the processing sequence, and the frame by ORDER BY following OVER is totally independent of the addition ORDER BY clause of the SELECT statement. The optional addition ORDER BY, which can be specified independently of PARTITION, defines an order in the current window and an evaluation framework for the window function. ORDER BY is followed by a comma-separated list of columns of the data sources of the current query. These columns are used to sort the rows of the window. A column can only be specified directly using the column name col1, col2 … . Alias names defined using AS cannot be specified. The additions ASCENDING and DESCENDING determine whether the rows are sorted in ascending or descending order by the column in question. The default is ASCENDING.
Specifying the order using ORDER BY causes the following:
◉ The rows of the window are processed by the window function in the order defined by the sort order. The order of the processing of rows that appear more than once regarding the sort criterion is not defined. If the addition ORDER BY is not specified, this applies to all rows of the window.
◉ The rows processed by the window function are additionally restricted by a frame. Only the rows of the window that are in front of the current row in the sorting, or that have the same values in the columns of the sort criterion are respected.
While the restricted frame mainly affects the results of aggregate functions specified as a window function, the processing sequence primarily affects the ranking functions. The addition ORDER BY must be specified for the ranking functions RANK and DENSE_RANK.
… ROWS BETWEEN …
A frame is defined by a starting frame boundary and an ending frame boundary. There are three options for the starting and ending frame boundaries:
◉ {UNBOUNDED PRECEDING}/{UNBOUNDED FOLLOWING}
UNBOUNDED PRECEDING as starting frame boundary specifies that the frame starts at the first row of the window. UNBOUNDED FOLLOWING as ending frame boundary specifies that the frame ends at the last row of the partition.
◉ CURRENT ROW can be used as both starting and ending frame boundary. It specifies that the window starts or ends at the current row, including the current row in the frame.
◉ {(n) PRECEDING}/{(n) FOLLOWING}
(n) PRECEDING can be used as both starting and ending frame boundary. It specifies that the frame starts or ends n rows above the current row.
(n) FOLLOWING can be used as both starting and ending frame boundary. It specifies that the frame starts or ends n rows beneath the current row.
n must be 0, a positive integer literal, or a host expression that can be resolved into a constant of type b, s,i, or int8.
ORDER BY is mandatory. Keep in mind that the ending frame boundary cannot be smaller than the starting frame boundary. If no window frame is used, the window function computes all rows up to the current row. As a result, the function returns cumulative values.
Example
The example demonstrates how different window functions are applied to frames inside a window.
◉ count sorts the rows by column ID and counts the number of rows from the first row of the window to the current row. The result is the same as that returned by the ROW_NUMBER( ) function.
◉ count_reverse sorts the rows by column ID and counts the number of rows from the current row to the last row of the window. The result is reverse numbering.
◉ average sorts the rows by column ID and calculates the rolling averages of a subset of rows from column col1. The subset consists of the current row plus one preceding and one following row. With this function, it is possible, for example, to calculate the 3-day-average temperature for every day from a list of temperature data.
◉ accumulate sorts the rows by column ID and accumulates the values from the first row of the window up to the current row, thus computing the running total of column col1.
SELECT id, col1, col2,
COUNT( * ) OVER( ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW )
AS count,
COUNT( * ) OVER( ORDER BY id ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING )
AS count_reverse,
AVG( col1 ) OVER( ORDER BY id ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING )
AS average,
SUM( col1 ) OVER( ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW )
AS accumulate
FROM demo_update
INTO TABLE @DATA(result).
source: sap.com
No comments:
Post a Comment