> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] 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
