diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/func.sgml | 22 | ||||
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 79 | ||||
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 59 |
3 files changed, 112 insertions, 48 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index fed003c4d01..a4ea1462a8b 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.501 2010/02/07 20:48:09 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.502 2010/02/12 17:33:19 tgl Exp $ --> <chapter id="functions"> <title>Functions and Operators</title> @@ -10559,21 +10559,23 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; <function>nth_value</> consider only the rows within the <quote>window frame</>, which by default contains the rows from the start of the partition through the last peer of the current row. This is - likely to give unhelpful results for <function>nth_value</> and - particularly <function>last_value</>. You can redefine the frame as - being the whole partition by adding <literal>ROWS BETWEEN UNBOUNDED - PRECEDING AND UNBOUNDED FOLLOWING</> to the <literal>OVER</> clause. - See <xref linkend="syntax-window-functions"> for more information. + likely to give unhelpful results for <function>last_value</> and + sometimes also <function>nth_value</>. You can redefine the frame by + adding a suitable frame specification (<literal>RANGE</> or + <literal>ROWS</>) to the <literal>OVER</> clause. + See <xref linkend="syntax-window-functions"> for more information + about frame specifications. </para> <para> When an aggregate function is used as a window function, it aggregates - over the rows within the current row's window frame. To obtain - aggregation over the whole partition, omit <literal>ORDER BY</> or use - <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>. + over the rows within the current row's window frame. An aggregate used with <literal>ORDER BY</> and the default window frame definition produces a <quote>running sum</> type of behavior, which may or - may not be what's wanted. + may not be what's wanted. To obtain + aggregation over the whole partition, omit <literal>ORDER BY</> or use + <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>. + Other frame specifications can be used to obtain other effects. </para> <note> diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 804a697e496..395ca79604c 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.128 2009/10/28 14:55:37 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.129 2010/02/12 17:33:19 tgl Exp $ PostgreSQL documentation --> @@ -616,27 +616,66 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl <para> The optional <replaceable class="parameter">frame_clause</> defines the <firstterm>window frame</> for window functions that depend on the - frame (not all do). It can be one of + frame (not all do). The window frame is a set of related rows for + each row of the query (called the <firstterm>current row</>). + The <replaceable class="parameter">frame_clause</> can be one of + +<synopsis> +[ RANGE | ROWS ] <replaceable>frame_start</> +[ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</> +</synopsis> + + where <replaceable>frame_start</> and <replaceable>frame_end</> 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 +UNBOUNDED PRECEDING +<replaceable>value</replaceable> PRECEDING +CURRENT ROW +<replaceable>value</replaceable> FOLLOWING +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. + + If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT + ROW</>. Restrictions are that + <replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>, + <replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>, + and the <replaceable>frame_end</> choice cannot appear earlier in the + above list than the <replaceable>frame_start</> choice — for example + <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</> + PRECEDING</literal> is not allowed. + </para> + + <para> + The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>, + which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND + CURRENT ROW</>; it sets 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</>). + In general, <literal>UNBOUNDED PRECEDING</> means that the frame + starts with the first row of the partition, and similarly + <literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last + row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</> + mode). In <literal>ROWS</> mode, <literal>CURRENT ROW</> + means that the frame starts or ends with the current row; but in + <literal>RANGE</> mode it means that the frame starts or ends with + the current row's first or last peer in the <literal>ORDER BY</> ordering. + The <replaceable>value</> <literal>PRECEDING</> and + <replaceable>value</> <literal>FOLLOWING</> cases are currently only + allowed in <literal>ROWS</> mode. They indicate that the frame starts + or ends with the row that many rows before or after the current row. + <replaceable>value</replaceable> must be an integer expression not + containing any variables, aggregate functions, or window functions. + The value must not be null or negative; but it can be zero, which + selects the current row itself. + </para> + + <para> + Beware that the <literal>ROWS</> options can produce unpredictable + results if the <literal>ORDER BY</> ordering does not order the rows + uniquely. The <literal>RANGE</> options are designed to ensure that + rows that are peers in the <literal>ORDER BY</> ordering are treated + alike; any two peer rows will be both in or both not in the frame. </para> <para> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 64aec4f6843..83ecb8653c6 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.141 2010/02/04 00:19:28 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.142 2010/02/12 17:33:19 tgl Exp $ --> <chapter id="sql-syntax"> <title>SQL Syntax</title> @@ -1667,14 +1667,21 @@ SELECT array_agg(a ORDER BY b DESC) FROM table; and the optional <replaceable class="parameter">frame_clause</replaceable> 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 +[ RANGE | ROWS ] <replaceable>frame_start</> +[ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</> </synopsis> + where <replaceable>frame_start</> and <replaceable>frame_end</> can be + one of +<synopsis> +UNBOUNDED PRECEDING +<replaceable>value</replaceable> PRECEDING +CURRENT ROW +<replaceable>value</replaceable> FOLLOWING +UNBOUNDED FOLLOWING +</synopsis> + </para> + <para> Here, <replaceable>expression</replaceable> represents any value expression that does not itself contain window function calls. The <literal>PARTITION BY</> and <literal>ORDER BY</> lists have @@ -1699,19 +1706,35 @@ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING The <replaceable class="parameter">frame_clause</replaceable> specifies the set of rows constituting the <firstterm>window frame</>, for those window functions that act on the frame instead of the whole partition. + If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT + ROW</>. Restrictions are that + <replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>, + <replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>, + and the <replaceable>frame_end</> choice cannot appear earlier in the + above list than the <replaceable>frame_start</> choice — for example + <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</> + PRECEDING</literal> is not allowed. The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>, which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND - CURRENT ROW</>; it selects rows 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. + CURRENT ROW</>; it sets 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</>). + In general, <literal>UNBOUNDED PRECEDING</> means that the frame + starts with the first row of the partition, and similarly + <literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last + row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</> + mode). In <literal>ROWS</> mode, <literal>CURRENT ROW</> + means that the frame starts or ends with the current row; but in + <literal>RANGE</> mode it means that the frame starts or ends with + the current row's first or last peer in the <literal>ORDER BY</> ordering. + The <replaceable>value</> <literal>PRECEDING</> and + <replaceable>value</> <literal>FOLLOWING</> cases are currently only + allowed in <literal>ROWS</> mode. They indicate that the frame starts + or ends with the row that many rows before or after the current row. + <replaceable>value</replaceable> must be an integer expression not + containing any variables, aggregate functions, or window functions. + The value must not be null or negative; but it can be zero, which + selects the current row itself. </para> <para> |