From ec4be2ee6827b6bd85e0813c7a8993cfbb0e6fa7 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 12 Feb 2010 17:33:21 +0000 Subject: Extend the set of frame options supported for window functions. This patch allows the frame to start from CURRENT ROW (in either RANGE or ROWS mode), and it also adds support for ROWS n PRECEDING and ROWS n FOLLOWING start and end points. (RANGE value PRECEDING/FOLLOWING isn't there yet --- the grammar works, but that's all.) Hitoshi Harada, reviewed by Pavel Stehule --- doc/src/sgml/func.sgml | 22 ++++++------ doc/src/sgml/ref/select.sgml | 79 +++++++++++++++++++++++++++++++++----------- doc/src/sgml/syntax.sgml | 59 +++++++++++++++++++++++---------- 3 files changed, 112 insertions(+), 48 deletions(-) (limited to 'doc/src') 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 @@ - + Functions and Operators @@ -10559,21 +10559,23 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; nth_value consider only the rows within the 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 nth_value and - particularly last_value. You can redefine the frame as - being the whole partition by adding ROWS BETWEEN UNBOUNDED - PRECEDING AND UNBOUNDED FOLLOWING to the OVER clause. - See for more information. + likely to give unhelpful results for last_value and + sometimes also nth_value. You can redefine the frame by + adding a suitable frame specification (RANGE or + ROWS) to the OVER clause. + See for more information + about frame specifications. 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 ORDER BY or use - ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. + over the rows within the current row's window frame. An aggregate used with ORDER BY and the default window frame definition produces a 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 ORDER BY or use + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. + Other frame specifications can be used to obtain other effects. 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 @@ @@ -616,27 +616,66 @@ WINDOW window_name AS ( The optional frame_clause defines the 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 current row). + The frame_clause can be one of + + +[ RANGE | ROWS ] frame_start +[ RANGE | ROWS ] BETWEEN frame_start AND frame_end + + + where frame_start and frame_end can be + one of + -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 +value PRECEDING +CURRENT ROW +value FOLLOWING +UNBOUNDED FOLLOWING - 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 ORDER BY ordering (which means all rows if - there is no ORDER BY). The options - RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and - ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - are also equivalent: they always select all rows in the partition. - Lastly, ROWS UNBOUNDED PRECEDING or its verbose equivalent - 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 ORDER BY ordering does not order the rows uniquely. + + If frame_end is omitted it defaults to CURRENT + ROW. Restrictions are that + frame_start cannot be UNBOUNDED FOLLOWING, + frame_end cannot be UNBOUNDED PRECEDING, + and the frame_end choice cannot appear earlier in the + above list than the frame_start choice — for example + RANGE BETWEEN CURRENT ROW AND value + PRECEDING is not allowed. + + + + The default framing option is RANGE UNBOUNDED PRECEDING, + which is the same as 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 ORDER BY + ordering (which means all rows if there is no ORDER BY). + In general, UNBOUNDED PRECEDING means that the frame + starts with the first row of the partition, and similarly + UNBOUNDED FOLLOWING means that the frame ends with the last + row of the partition (regardless of RANGE or ROWS + mode). In ROWS mode, CURRENT ROW + means that the frame starts or ends with the current row; but in + RANGE mode it means that the frame starts or ends with + the current row's first or last peer in the ORDER BY ordering. + The value PRECEDING and + value FOLLOWING cases are currently only + allowed in ROWS mode. They indicate that the frame starts + or ends with the row that many rows before or after the current row. + value 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. + + + + Beware that the ROWS options can produce unpredictable + results if the ORDER BY ordering does not order the rows + uniquely. The RANGE options are designed to ensure that + rows that are peers in the ORDER BY ordering are treated + alike; any two peer rows will be both in or both not in the frame. 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 @@ - + SQL Syntax @@ -1667,14 +1667,21 @@ SELECT array_agg(a ORDER BY b DESC) FROM table; and the optional frame_clause can be one of -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 ] frame_start +[ RANGE | ROWS ] BETWEEN frame_start AND frame_end + where frame_start and frame_end can be + one of + +UNBOUNDED PRECEDING +value PRECEDING +CURRENT ROW +value FOLLOWING +UNBOUNDED FOLLOWING + + + Here, expression represents any value expression that does not itself contain window function calls. The PARTITION BY and ORDER BY lists have @@ -1699,19 +1706,35 @@ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING The frame_clause specifies the set of rows constituting the window frame, for those window functions that act on the frame instead of the whole partition. + If frame_end is omitted it defaults to CURRENT + ROW. Restrictions are that + frame_start cannot be UNBOUNDED FOLLOWING, + frame_end cannot be UNBOUNDED PRECEDING, + and the frame_end choice cannot appear earlier in the + above list than the frame_start choice — for example + RANGE BETWEEN CURRENT ROW AND value + PRECEDING is not allowed. The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND - CURRENT ROW; it selects rows up through the current row's last - peer in the ORDER BY ordering (which means all rows if - there is no ORDER BY). The options - RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and - ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - are also equivalent: they always select all rows in the partition. - Lastly, ROWS UNBOUNDED PRECEDING or its verbose equivalent - 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 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 ORDER BY + ordering (which means all rows if there is no ORDER BY). + In general, UNBOUNDED PRECEDING means that the frame + starts with the first row of the partition, and similarly + UNBOUNDED FOLLOWING means that the frame ends with the last + row of the partition (regardless of RANGE or ROWS + mode). In ROWS mode, CURRENT ROW + means that the frame starts or ends with the current row; but in + RANGE mode it means that the frame starts or ends with + the current row's first or last peer in the ORDER BY ordering. + The value PRECEDING and + value FOLLOWING cases are currently only + allowed in ROWS mode. They indicate that the frame starts + or ends with the row that many rows before or after the current row. + value 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. -- cgit v1.2.3