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 BYrule
[,rule
...] | ORDER OFtable_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 FOLLOWINGpartitioning
::= PARTITION BY (value
[,value
...])ordering
::= {ORDER BYrule
[,rule
...] | ORDER OFtable_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}
Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.