SQL in a Nutshell, 2nd Edition

DB2’s Window Syntax

DB2’s syntax is similar to Oracle’s. For OLAP, ranking, and numbering functions, DB2 allows the following syntax:

FUNCTION_NAME(expr) OVER (window_clause)

window_clause ::= [partitioning] [ordering]

partitioning ::= PARTITION BY (value [, value...])

ordering ::= {ORDER BY rule [, rule...] | ORDER OF table_name}

rule ::= {value|position|alias} [ASC|DESC [NULLS {FIRST|LAST}]]

When aggregate functions (e.g. AVG) are used as window functions, DB2 allows the addition of a framing clause:

FUNCTION_NAME(expr) OVER (window_clause)

window_clause ::= [partitioning] [ordering [framing]] [all|framing]

all ::= RANGE UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

partitioning ::= PARTITION BY (value [, value...])

ordering ::= {ORDER BY rule [, rule...] | ORDER OF table_name}

rule ::= {value|position|alias} [ASC|DESC [NULLS {FIRST|LAST}]]

framing ::= {ROWS|RANGE} {group_start|group_between|group_end}

group_start ::= {UNBOUNDED PRECEDING|unsigned-integer PRECEDING
                |CURRENT ROW}

group_between ::= BETWEEN {UNBOUNDED PRECEDING|unsigned_integer PRECEDING
                          |unsigned_integer FOLLOWING|CURRENT ROW}
                  AND {UNBOUNDED FOLLOWING|unsigned_integer PRECEDING
                          |unsigned_integer FOLLOWING|CURRENT ROW}

group_end ::= UNBOUNDED FOLLOWING|unsigned-integer FOLLOWING}