diff options
Diffstat (limited to 'doc/src/sgml/ref/select.sgml')
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 60 |
1 files changed, 45 insertions, 15 deletions
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 3a4e718fbf..ba8bff0e52 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.114 2008/12/29 18:23:53 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.115 2008/12/31 00:08:35 tgl Exp $ PostgreSQL documentation --> @@ -583,7 +583,21 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl [ <replaceable class="parameter">existing_window_name</replaceable> ] [ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ] [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] +[ <replaceable class="parameter">framing_clause</replaceable> ] </synopsis> + </para> + + <para> + If an <replaceable class="parameter">existing_window_name</replaceable> + is specified it must refer to an earlier entry in the <literal>WINDOW</> + list; the new window copies its partitioning clause from that entry, + as well as its ordering clause if any. In this case the new window cannot + specify its own <literal>PARTITION BY</> clause, and it can specify + <literal>ORDER BY</> only if the copied window does not have one. + The framing clause is never copied from the existing window. + </para> + + <para> The elements of the <literal>PARTITION BY</> list are interpreted in the same fashion as elements of a <xref linkend="sql-groupby" endterm="sql-groupby-title">, and @@ -597,12 +611,29 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl </para> <para> - If an <replaceable class="parameter">existing_window_name</replaceable> - is specified it must refer to an earlier entry in the <literal>WINDOW</> - list; the new window copies its partitioning clause from that entry, - as well as its ordering clause if any. In this case the new window cannot - specify its own <literal>PARTITION BY</> clause, and it can specify - <literal>ORDER BY</> only if the copied window does not have one. + The optional <replaceable class="parameter">framing_clause</> defines + the <firstterm>window frame</> for window functions that depend on the + frame (not all do). It can be one of +<synopsis> +RANGE UNBOUNDED PRECEDING +RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW +RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING +ROWS UNBOUNDED PRECEDING +ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW +ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING +</synopsis> + The first two are equivalent and are also the default: they set the + frame to be all rows from the partition start up through the current row's + last peer in the <literal>ORDER BY</> ordering (which means all rows if + there is no <literal>ORDER BY</>). The options + <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> and + <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> + are also equivalent: they always select all rows in the partition. + Lastly, <literal>ROWS UNBOUNDED PRECEDING</> or its verbose equivalent + <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</> select + all rows up through the current row (regardless of duplicates). + Beware that this option can produce implementation-dependent results + if the <literal>ORDER BY</> ordering does not order the rows uniquely. </para> <para> @@ -623,8 +654,9 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl <para> Window functions are described in detail in - <xref linkend="tutorial-window"> and - <xref linkend="syntax-window-functions">. + <xref linkend="tutorial-window">, + <xref linkend="syntax-window-functions">, and + <xref linkend="queries-window">. </para> </refsect2> @@ -1453,12 +1485,10 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; <title><literal>WINDOW</literal> Clause Restrictions</title> <para> - The SQL standard provides for an optional <quote>framing clause</>, - introduced by the key word <literal>RANGE</> or <literal>ROWS</>, - in window definitions. <productname>PostgreSQL</productname> does - not yet implement framing clauses, and always follows the - default framing behavior, which is equivalent to the framing clause - <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</>. + The SQL standard provides additional options for the window + <replaceable class="parameter">framing_clause</>. + <productname>PostgreSQL</productname> currently supports only the + options listed above. </para> </refsect2> |