Neoview SQL Reference Manual (R2.3)

| unsigned-integer PRECEDING
following-row is:
UNBOUNDED FOLLOWING
| unsigned-integer FOLLOWING
ALL | DISTINCT
specifies whether duplicate values are included in the computation of the AVG 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 numeric or interval value expression that determines the values to average.
See “Numeric Value Expressions” (page 227) and “Interval Value Expressions” (page 223).
inline-window-specification
specifies the window over which the AVG 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 AVG is computed.
Examples of AVG Window Function
• Return the running average value of the SALARY column:
SELECT empnum, AVG(salary)
OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
• Return the running average value of the SALARY column within each department:
SELECT deptnum, empnum, AVG(salary)
OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
• Return the moving average of salary within each department over a window of the last 4
rows:
SELECT deptnum, empnum, AVG(SALARY)
OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING)
FROM persnl.employee;
COUNT Window Function
COUNT is a window function that returns the count of the non null values of the given expression
for the current window specified by the inline-window-specification.
COUNT {(*) | ([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:
ROW CURRENT ROW
| ROW preceding-row
| ROW BETWEEN preceding-row AND preceding-row
| ROW BETWEEN preceding-row AND CURRENT ROW
| ROW BETWEEN preceding-row AND following-row | ROW BETWEEN CURRENT ROW AND CURRENT ROW
| ROW BETWEEN CURRENT ROW AND following-row
| ROW BETWEEN following-row AND following-row
preceding-row is:
448 OLAP Functions