Issue Details (XML | Word | Printable)

Key: CORE-1688
Type: New Feature New Feature
Status: Open Open
Priority: Minor Minor
Assignee: Adriano dos Santos Fernandes
Reporter: Philippe Makowski
Votes: 4
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

ANSI SQL2003 Window Functions

Created: 12/Jan/08 12:34 PM   Updated: 19/Oct/09 05:09 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

Time Tracking:
Issue & Sub-Tasks
Issue Only
Not Specified

Issue Links:
Replace
 

Sub-Tasks  All   Open   

 Description  « Hide
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.


 All   Comments   Work Log   Change History   Version Control   FishEye      Sort Order: Ascending order - Click to sort in descending order
Volker Rehn added a comment - 18/Aug/08 02:46 PM
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.

Philippe Makowski added a comment - 18/Aug/08 04:01 PM
In my opinion, the priority is to set Window functions (RANK(), ROW_NUMBER())

Adriano dos Santos Fernandes added a comment - 18/Aug/08 04:51 PM
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.

Volker Rehn added a comment - 17/Sep/08 03:27 AM
the current window function postgres link is

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