sql_exp - sql_win
7.31 | 7.40 | 7.54
... win_func OVER( [PARTITION sql_exp1, sql_exp2 ...]
[ORDER BY col1 [ASCENDING|DESCENDING],
col2 [ASCENDING|DESCENDING], ...] ) ...
Window expression in ABAP SQL. A window expression uses a
window function to determine an individual value from the rows of a
window of the results set of a
query. A window expression can be specified as an
SQL expression or as an argument of any SQL expressions, except for
aggregate and window expressions for defining a
of the result set in the
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.
A window expression consists of a window function
win_func followed by the addition
OVER( ... ) in whose parentheses the
window on the results set is defined for whose rows the window function is evaluated:
PARTITIONdefines the windows using a comma-separated list of SQL expressions
sql_exp2, ... . A window is formed by the rows of the result set for which all SQL expressions have the same result. All SQL expressions except for aggregate expressions and window expressions can be used. The window function is calculated for the rows of the respective current window. If
PARTITIONis not specified, a single window is formed, which comprises all rows of the result set.
ORDER BYintroduces 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.
If a window expression is used in the
SELECT list of a
clause, the windows are defined in the consolidated result list and aggregate expressions can be used
as arguments of the window features. Each column that is specified in any position in the window expression must also be specified in the
GROUP BY clause.
- A window expression works in a similar way to an
aggregate expression, whereby the addition
PARTITION assumes the role of the GROUP
BY clause. In contrast with the aggregate expressions, however, there is no aggregation of
the rows defined via
PARTITION, rather these are retained and are all assigned the value calculated via the window expression.
- The window features of a window expression work, as it were, with the rows of a virtual table, which
is defined by the specifications after
OVER. Using the addition
ORDER BYof the
SELECTstatement does not influence the result of a window expression. However, the rows of the result set can be sorted according to the results of window expressions through using their alias name.
- A window expression can only be specified in the result set in the
SELECTlist of a query. It cannot be used like an aggregate expression in the
- By using window expressions in other SQL expressions, calculations can be performed, which 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.
- Window expressions are not supported by all databases. In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports the use of window expressions. This requires the constant WINDOWING of this class to be passed to the method in an internal table.
... ORDER BY col1 [ASCENDING|DESCENDING], col2 [ASCENDING|DESCENDING], ...
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
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
Alias names defined using
cannot be specified. The additions
determine whether the rows are sorted in ascending or descending order by the column in question. The default is
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 sequence.
The order of the processing of rows that appear more than once regarding the sort criterion is not defined.
If the addition
ORDER BYis 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 taken into account.
While the restricted frame mainly affects the results of
aggregate functions specified as a
window function, the processing sequence primarily affects the
ORDER BY must be specified for the ranking functions
The definition of the window, processing sequence, and the frame by
OVER is totally independent of the addition
ORDER BY clause of the