Neoview SQL Reference Manual (R2.3)

7 OLAP Functions
This section describes the syntax and semantics of the On Line Analytical Process (OLAP) window
functions. The OLAP window functions are ANSI compliant.
Considerations for Window Functions
These considerations apply to all window functions.
inline-window-specification
The window defined by the inline-window-specification consists of the rows specified by
the window-frame-clause, bounded by the current partition. If no PARTITION BY clause is
specified, the partition is defined to be all the rows of the intermediate result. If a PARTITION
BY clause is specified, the partition is the set of rows which have the same values for the
expressions specified in the PARTITION clause.
window-frame-clause
DISTINCT is not supported for Window Functions.
Use of a FOLLOWING term is not supported. Using a FOLLOWING term results in an error.
If no window-frame-clause is specified, “ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING” is assumed. This is not supported since this involves
a FOLLOWING term and will result in an error.
“ROWS CURRENT ROW” is equivalent to “ROWS BETWEEN CURRENT ROW AND
CURRENT ROW”.
“ROWS preceding-row” is equivalent to “ROWS BETWEEN preceding-row AND
CURRENT ROW”.
Nulls
All nulls are eliminated before the function is applied to the set of values. If the window contains
all NULL values, the result of the window function is NULL.
If the specified window for a particular row consists of rows that are all before the first row of
the partition (no rows in the window), the result of the window function is NULL.
AVG Window Function
AVG is a window function that returns the average of nonnull values of the given expression
for the current window specified by the inline-window specification.
AVG ([ALL | DISTINCT] expression) OVER (inline-window-specification)
inline-window-specification is:
[PARTITION BY expression [, expression]...]
[ORDER BY {colname} [ASC[ENDING] | DESC[ENDING]]
[,{colname} [ASC[ENDING] | DESC[ENDING]]]...]
[ window-frame-clause ]
window-frame-clause is:
ROWS CURRENT ROW
| ROWS preceding-row
| ROWS BETWEEN preceding-row AND preceding-row
| ROWS BETWEEN preceding-row AND CURRENT ROW
| ROWS BETWEEN preceding-row AND following-row
| ROWS BETWEEN CURRENT ROW AND CURRENT ROW
| ROWS BETWEEN CURRENT ROW AND following-row
| ROWS BETWEEN following-row AND following-row
preceding-row is:
UNBOUNDED PRECEDING
Considerations for Window Functions 447