WL#9603: Add remaining non-aggregate window functions
Affects: Server-8.0
—
Status: Complete
This is a followup of the work done in WL#9236 Add SQL window functions.
Please refer that that WL for a general background and specifications.
Under this work log we will add the non-aggregate window functions missing from
that WL:
- PERCENT_RANK
- CUME_DIST
- NTH_VALUE
- LEAD
- LAG
Syntax (culled from total syntax descrition in WL#9236):
window_func_call:
:
| CUME_DIST '(' ')' windowing_clause
| PERCENT_RANK '(' ')' windowing_clause
:
| LEAD '(' expr-1 opt_lead_lag_info ')'
opt_null_treatment windowing_clause
| LAG '(' expr-1 opt_lead_lag_info ')'
opt_null_treatment windowing_clause
:
| NTH_VALUE '(' expr ',' expr-1 ')' opt_from_first_last
opt_null_treatment windowing_clause
expr-1 must be a constant positive integer.
opt_lead_lag_info:
/* Nothing */
| ',' NUM_literal opt_ll_default
NUM_literal must be an integer larger than or equal to zero.
opt_ll_default:
/* Nothing */
| ',' expr
opt_null_treatment:
/* Nothing */
| RESPECT NULLS
| IGNORE NULLS
Only RESPECT NULLS is supported currently (also the default).
opt_from_first_last:
/* Nothing */
| FROM FIRST
| FROM LAST
Only FROM FIRST is supported (default).
For a description of windowing_clause, cf. WL#9236.
Standard SQL semantics, except we do not require ordering: if none, all rows in
a partition are peers.
See WL#9236
- NTH_VALUE(, ) [FROM LAST] [{IGNORE | RESPECT} NULLS]
We will not support "FROM LAST". (Neither does PostgreSQL)
We will not support "IGNORE NULLS". (Neither does PostgreSQL)
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.