diff options
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 68 |
1 files changed, 41 insertions, 27 deletions
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 479adc950a8..f496fa88004 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1742,11 +1742,16 @@ UNBOUNDED FOLLOWING <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 - essentially the same syntax and semantics as <literal>GROUP BY</> - and <literal>ORDER BY</> clauses of the whole query, except that their + <literal>PARTITION BY</> behaves like a <literal>GROUP + BY</> clause by grouping rows into partitions, except its expressions are always just expressions and cannot be output-column - names or numbers. + names or numbers. <literal>ORDER BY</> behaves similar to a + query-level <literal>ORDER BY</> clause, except it controls the + order rows are supplied to the aggregate function within the window + frame, and with the same expression restrictions. + </para> + + <para> <replaceable>window_name</replaceable> is a reference to a named window specification defined in the query's <literal>WINDOW</literal> clause. Named window specifications are usually referenced with just @@ -1761,38 +1766,47 @@ UNBOUNDED FOLLOWING </para> <para> + In <literal>RANGE</> mode, <literal>CURRENT ROW</> starts with + the current row's first peer that <literal>ORDER BY</> + considers equivalent, and ends with its last equivalent peer. In + <literal>ROWS</> mode, <literal>CURRENT ROW</> simply starts and ends + with the current row. + </para> + + <para> + <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. + 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 the specified number of 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 + just selects the current row. + </para> + + <para> 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 + The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>, + which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND + CURRENT ROW</>. This sets the frame to be all rows from the partition start + up through the current row's last peer as specified by <literal>ORDER BY</>, + or all partition rows if there is no <literal>ORDER BY</>. + </para> + + <para> + 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 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> |