Neoview SQL Reference Manual (R2.3)

optional ORDER BY clause. The PARTITION BY clause specifies how the intermediate result
is partitioned and the ORDER BY clause specifies how the rows are ordered within each
partition.
window-frame-clause
specifies the window within the partition over which the ROW_NUMBER is computed.
Examples of ROW_NUMBER Window Function
• Return the row number for each row of the employee table:
SELECT ROW_NUMBER () OVER(ORDER BY empnum), *
FROM persnl.employee;
• Return the row number for each row within each department:
SELECT ROW_NUMBER () OVER(PARTITION BY deptnum ORDER BY empnum), *
FROM persnl.employee;
STDDEV Window Function
STDDEV is a window function that returns the standard deviation of non null values of the given
expression for the current window specified by the inline-window-specification..
STDDEV ([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
| unsigned-integer PRECEDING
following-row is:
UNBOUNDED FOLLOWING
| unsigned-integer FOLLOWING
ALL | DISTINCT
specifies whether duplicate values are included in the computation of the STDDEV of the
expression. The default option is ALL, which causes duplicate values to be included.
DISTINCT is not supported for window functions.
expression
specifies a numeric or interval value expression that determines the values over which
STDDEV is computed.
inline-window-specification
specifies the window over which the STDDEV is computed. The
inline-window-specification can contain an optional PARTITION BY clause, an
optional ORDER BY clause and an optional window frame clause. The PARTITION BY clause
specifies how the intermediate result is partitioned and the ORDER BY clause specifies how
the rows are ordered within each partition.
window-frame-clause
specifies the window within the partition over which the STDDEV is computed.
STDDEV Window Function 453