Skip to content

ANSI SQL2003 Window Functions [CORE1688] #2113

@firebird-automations

Description

@firebird-automations

Submitted by: @pmakowski

Jira_subtask_outward CORE2090
Jira_subtask_outward CORE2133
Replaces CORE2689
Jira_subtask_outward CORE2823
Jira_subtask_outward CORE2830
Jira_subtask_outward CORE2869
Jira_subtask_outward CORE3616
Jira_subtask_outward CORE3617
Jira_subtask_outward CORE3618
Jira_subtask_outward CORE3619
Jira_subtask_outward CORE3620
Jira_subtask_outward CORE3621
Jira_subtask_outward CORE3647
Jira_subtask_outward CORE5338
Jira_subtask_outward CORE5346

Votes: 16

After CTE, it would be nice to have ANSI SQL Window Functions

SQL2003 allows for a window_clause in aggregate function calls, the addition of which makes those functions into window functions.
SQL2003 specifies the following syntax for window functions:

FUNCTION_NAME(expr) OVER {window_name|(window_specification)}
window_specification ::= [window_name][partitioning][ordering][framing]
partitioning ::= PARTITION BY value [, value...] [COLLATE collation_name]
ordering ::= ORDER [SIBLINGS] BY rule [, rule...]
rule ::= {value|position|alias} [ASC|DESC] [NULLS {FIRST|LAST}]
framing ::= {ROWS|RANGE} {start|between} [exclusion]
start ::= {UNBOUNDED PRECEDING|unsigned-integer PRECEDING|CURRENT ROW}
between ::= BETWEEN bound AND bound
bound ::= {start|UNBOUNDED FOLLOWING|unsigned-integer FOLLOWING}
exclusion ::= {EXCLUDE CURRENT ROW|EXCLUDE GROUP
|EXCLUDE TIES|EXCLUDE NO OTHERS}

List of Window Functions
CUME_DIST( ) OVER {window_name|(window_specification)} : Calculates the cumulative distribution, or relative rank, of the current row to other rows in the same partition

DENSE_RANK( ) OVER {window_name|(window_specification)} : Assigns a rank to each row in a partition, which should be ordered in some manner. The rank for a given row is computed by counting the number of rows preceding the row in question, and then adding 1 to the result. Rows with duplicate ORDER BY values will rank the same. Unlike the case with RANK( ), gaps in rank numbers will not result from two rows sharing the same rank.

RANK( ) OVER {window_name|(window_specification)} : Assigns a rank to each row in a partition, which should be ordered in some manner. The rank for a given row is computed by counting the number of rows preceding the row in question, and then adding 1 to the result. Rows with duplicate ORDER BY values will rank the same, and will lead to subsequent gaps in rank numbers.

PERCENT_RANK( ) OVER ({window_name|(window_specification)} : Computes the relative rank of a row by dividing that row's rank less 1 by the number of rows in the partition, also less 1

ROW_NUMBER( ) OVER ({window_name|(window_specification)} : Assigns a unique number to each row in a partition.

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions