|
In my opinion, the priority is to set Window functions (RANK(), ROW_NUMBER())
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. I'm resolving this because there is no immediate plan to implement more subtasks.
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 Philippe, of these only LAG and LEAD are already implemented.
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) 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 ? 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.
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
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 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
New Feature
Reopened
Minor
ANSI SQL2003 Window Functions
Resolved
Open
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.