WINDOW_AVG
Description
WINDOW_AVG can be used to calculate the average over a user-defined window. It is possible to specify a column based ordering and partitioning.
The output type is FLOAT.
Syntax
WINDOW_AVG ( column, start, end [, ORDER BY ( sort_column [sorting], ... )] [, PARTITION BY ( partition_column, ... )] )
column: The input column where values should be averaged. Supported input types: INT, FLOAT
start: Amount of rows where the window should start, relative to the window. Must be of type INT.
end: Amount of rows where the window should end, relative to the window. Must be of type INT.
sort_column: Optional sorting column to specify an order.
sorting: Each of these columns can have an optional tag specifying the ordering of the column. Default is ascending:
ASC: Ascending order
DESC: Descending order
partition_column: Optional partition column to specify groups in which
WINDOW_AVG
should operate.
One or more columns can be given to specify an ordering. This tells the WINDOW_AVG
function what the preceding/following element actually is. Optionally every column can be tagged as ascending or descending. The partition columns specify groups. The WINDOW_AVG
function operates independently within every group. This means when an ordering is given it is applied within every group.
NULL handling
NULL values are ignored, meaning that even if they are inside the window, they do not influence the result of the average. If all values of a window are NULL, the result for this window is also NULL.
Examples
Basic usage
[1] WINDOW_AVG with INT values, taking the current and the row afterwards into consideration. | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
[2] WINDOW_AVG with INT values, taking the current and the previous row into consideration. | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
[3] WINDOW_AVG with negative and zero INT values. | ||||||||||||||||
| ||||||||||||||||
|
Advanced usage (e.g. showing NULL handling)
[4] WINDOW_AVG with FLOAT values and NULL occurrences. | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
[5] WINDOW_AVG with a leading NULL. | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
[6] WINDOW_AVG with NULL occurrences. | ||||||||||||||||||||
| ||||||||||||||||||||
|
Ordering, Partitioning
[7] WINDOW_AVG with an ORDER BY clause. | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
[8] WINDOW_AVG with a PARTITION BY clause. | ||||||||||||||||||||||
| ||||||||||||||||||||||
|
[9] WINDOW_AVG with multiple orders and partition clauses. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|