Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ANSI SQL2003 Window Functions [CORE1688] #2113

Open
firebird-automations opened this issue Jan 12, 2008 · 22 comments
Open

ANSI SQL2003 Window Functions [CORE1688] #2113

firebird-automations opened this issue Jan 12, 2008 · 22 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 13816 ] => Firebird [ 14123 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Volker Rehn (vr2_s18)

Adriano has asked what to implement first since Philippe's list is fairly big. Please see this link on how it is done in Postgres

http://umitanuki.net/pgsql/wfv03/design.html

and this one for a good introduction and summary on this complex topic

http://wwwdvs.informatik.uni-kl.de/courses/NEDM/WS0607/Vorlesungsunterlagen/NEDM.Chapter.06.Windows_and_Query_Functions_in_SQL.pdf

this ticket deserves a higher priority. Even if only partially implemented those features would significantly enhance the toolkit of OLAP users of Firebird.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

In my opinion, the priority is to set Window functions (RANK(), ROW_NUMBER())

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

My intention is to implement early a way for current GROUP BY functions work with OVER ([PARTITION BY ...]) clause. Later, some internal refactoring would be desired to make easy creation of new functions.

Functions as RANK and ROW_NUMBER also requires ORDER BY clause, so they probably will not be the first ones.

@firebird-automations
Copy link
Collaborator Author

Commented by: Volker Rehn (vr2_s18)

the current window function postgres link is

http://umitanuki.net/pgsql/wfv05/design.html

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue replaces CORE2689 [ CORE2689 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I'm resolving this because there is no immediate plan to implement more subtasks.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Adriano, did you implemented :

percent_rank() relative rank of the current row: (rank - 1) / (total rows - 1)

cume_dist() relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)

ntile(num_buckets integer) integer ranging from 1 to the argument value, dividing the partition as equally as possible

lag(value any [, offset integer [, default any ]]) returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null

lead(value any [, offset integer [, default any ]]) returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null

first_value(value any) return value evaluated at the row that is the first row of the window frame

last_value(value any) returns value evaluated at the row that is the last row of the window frame

nth_value(value any, nth integer) returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row

for ref : http://www.postgresql.org/docs/9.1/static/functions-window.html#FUNCTIONS-WINDOW-TABLE

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Philippe, of these only LAG and LEAD are already implemented.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

ok so I reopen this one
and please add sub tasks for
percent_rank()
cume_dist()
ntile(num_buckets integer)
first_value(value any)
last_value(value any)
nth_value(value any, nth integer)

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: 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.

=>

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Sub-tasks created

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

seems that :
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}

are not implemeted ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Philippe, there was no indication that the whole specification would be implemented at once :-) I recall a kind of agreement with Adriano that framing could be postponed.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

framing could be postponed, but it have to be indicated somewhere or another ticket or sub-ticket have to be set for that, just to be clear about what we have or not

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

Standard say :
OVER {window_name|(window_specification)}
window_specification ::= [window_name][partitioning][ordering][framing]

so, according to the standard, you can write :
select emp_no, dept_no, salary, avg(salary) over (partition by dept_no)
from employee;

or you can use an explicit WINDOW clause

select emp_no, dept_no, salary, avg(salary) over w
from employee
WINDOW w as (partition by dept_no);

but now it returns :
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 50
-w

advantage of explicit WINDOW clause : window has a name, which can be used by multiple window table function
invocations in the SELECT clause

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

Please add a window frame clause to Firebird 4.

@dyemanov
Copy link
Member

As far as I see, only sub-task #2058 (aka CORE-5338) remains not implemented. Do I get it right? Any plans about it?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants