Neoview SQL Reference Manual (R2.3)

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 COUNT of the
expression. The default option is ALL, which causes duplicate values to be included.
DISTINCT is not supported for windows functions.
expression
specifies a value expression that is to be counted. See “Expressions” (page 218).
inline-window-specification
specifies the window over which the COUNT 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 COUNT is computed.
Examples of COUNT Window Function
• Return the running count of the SALARY column:
SELECT empnum, COUNT(salary)
OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
• Return the running count of the SALARY column within each department:
SELECT deptnum, empnum, COUNT(salary)
OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
• Return the moving count of salary within each department over a window of the last 4 rows:
SELECT deptnum, empnum, COUNT(salary)
OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING)
FROM persnl.employee;
• Return the running count of employees within each department:
SELECT deptnum, empnum, COUNT(*)
OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
DENSE_RANK Window Funtion
DENSE_RANK is a window function that returns the ranking of each row of the current partition
specified by the inline-window-specification. The ranking is relative to the ordering specified in
the inline-window-specification. The return value of DENSE_RANK starts at 1 for the first row
of the window. Values of the given expression that are equal have the same rank. The value of
DENSE_RANK advances 1 when the value of the given expression changes.
DENSE_RANK() OVER (inline-window-specification)
inline-window-specification is:
[PARTITION BY expression [, expression]...]
[ORDER BY {colname} [ASC[ENDING] | DESC[ENDING]]
[,{colname} [ASC[ENDING] | DESC[ENDING]]]...]
inline-window-specification
specifies the window over which the DENSE_RANK is computed. The
inline-window-specification can contain an optional PARTITION BY clause and an
DENSE_RANK Window Funtion 449