Re: SQL:2003 Window Functions for postgresql 8.3?

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of AgentM
> Sent: Thursday, August 24, 2006 11:27 AM
> To: PostgreSQL General ML
> Subject: Re: [GENERAL] SQL:2003 Window Functions for postgresql 8.3?
>
>
> On Aug 24, 2006, at 14:11 , Alvaro Herrera wrote:
>
> > Karen Hill wrote:
> >
> >> It would be really great if PostgreSQL supported SQL:2003 Window
> >> functions. I know that oracle and sql server have them already,
> >> so it
> >> would make postgres competitive in that area. I know there is a
> >> feature freeze for 8.2, is it doable for 8.3?
> >
> > The sooner you start writing a patch, the sooner you will be done
;-)
> >
> > I agree it would be nice to have them, but currently I don't think
> > there's anyone working on'em.
>
> Could someone elaborate on the window functions? This page http://
> en.wikipedia.org/wiki/SELECT has some examples but they make it seem
> like the functions are an overly-verbose LIMIT statement. So what's
> the benefit?
>
> -M

Window functions: SQL 2003 defines aggregates computed over a window
with ROW_NUMBER function, rank functions (i.e., RANK, DENSE_RANK,
PERCENT_RANK, CUME_DIST), and aggregate functions (e.g., inverse
distribution, hypothetical set function)

From:
http://savage.net.au/SQL/sql-2003-2.bnf.html
6.10 <window function> (p193)
<window function> ::= <window function type> OVER <window name or
specification>

<window function type> ::=
<rank function type> <left paren> <right paren>
| ROW_NUMBER <left paren> <right paren>
| <aggregate function>

<rank function type> ::= RANK | DENSE_RANK | PERCENT_RANK |
CUME_DIST

<window name or specification> ::= <window name> | <in-line
window specification>

<in-line window specification> ::= <window specification>

7.11 <window clause> (p331)
Specify one or more window definitions.

<window clause> ::= WINDOW <window definition list>

<window definition list> ::= <window definition> [ { <comma>
<window definition> }... ]

<window definition> ::= <new window name> AS <window
specification>

<new window name> ::= <window name>

<window specification> ::= <left paren> <window specification
details> <right paren>

<window specification details> ::=
[ <existing window name> ] [ <window partition clause> ] [
<window order clause> ] [ <window frame clause> ]

<existing window name> ::= <window name>

<window partition clause> ::= PARTITION BY <window partition
column reference list>

<window partition column reference list> ::= <window partition
column reference> [ { <comma> <window partition column reference> }... ]

<window partition column reference> ::= <column reference> [
<collate clause> ]

<window order clause> ::= ORDER BY <sort specification list>

<window frame clause> ::= <window frame units> <window frame
extent> [ <window frame exclusion> ]

<window frame units> ::= ROWS | RANGE

<window frame extent> ::= <window frame start> | <window frame
between>

<window frame start> ::= UNBOUNDED PRECEDING | <window frame
preceding> | CURRENT ROW

<window frame preceding> ::= <unsigned value specification>
PRECEDING

<window frame between> ::= BETWEEN <window frame bound 1> AND
<window frame bound 2>

<window frame bound 1> ::= <window frame bound>

<window frame bound 2> ::= <window frame bound>

<window frame bound> ::=
<window frame start>
| UNBOUNDED FOLLOWING
| <window frame following>

<window frame following> ::= <unsigned value specification>
FOLLOWING

<window frame exclusion> ::=
EXCLUDE CURRENT ROW
| EXCLUDE GROUP
| EXCLUDE TIES
| EXCLUDE NO OTHERS

> ---------------------------(end of
broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq