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