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)
See WL#9236
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.