Neoview SQL Reference Manual (R2.4)

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 clause is not supported because it
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.
ORDER BY Clause Supports Expressions For OLAP Functions
The ORDER BY clause of the OLAP functions now supports expressions. However, use of multiple
OLAP functions with different expressions in the same query is not supported. The following
examples show how expressions may be used in the ORDER BY clause.
SELECT -1 * annualsalary neg_total,
RANK() OVER (ORDER BY -1 * annualsalary) olap_rank
FROM employee;
Using an aggregate in the ORDER BY clause:
SELECT num,
RANK() OVER (ORDER BY SUM(annualsalary)) olap_rank
FROM employee
GROUP BY num;
Using multiple functions with the same expression in the ORDER BY clause:
SELECT num, workgroupnum,
RANK() OVER (ORDER BY SUM (annualsalary)*num) olap_rank,
DENSE_RANK() OVER (ORDER BY SUM (annualsalary)*num) olap_drank
ROW_NUMBER() OVER (ORDER BY SUM (annualsalary)*num) olap_mum
FROM employee
GROUP BY num, workgroupnum, annualsalary;
Using more functions with the same expression in the ORDER BY clause:
Considerations for Window Functions 481