Re: SQL:2003 Window Functions for postgresql 8.3? - Mailing list pgsql-general

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.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