diff options
Diffstat (limited to 'doc/src/sgml/ref/select.sgml')
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 79 |
1 files changed, 59 insertions, 20 deletions
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> |