From 0a459cec96d3856f476c2db298c6b52f592894e8 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 7 Feb 2018 00:06:50 -0500 Subject: Support all SQL:2011 options for window frame clauses. This patch adds the ability to use "RANGE offset PRECEDING/FOLLOWING" frame boundaries in window functions. We'd punted on that back in the original patch to add window functions, because it was not clear how to do it in a reasonably data-type-extensible fashion. That problem is resolved here by adding the ability for btree operator classes to provide an "in_range" support function that defines how to add or subtract the RANGE offset value. Factoring it this way also allows the operator class to avoid overflow problems near the ends of the datatype's range, if it wishes to expend effort on that. (In the committed patch, the integer opclasses handle that issue, but it did not seem worth the trouble to avoid overflow failures for datetime types.) The patch includes in_range support for the integer_ops opfamily (int2/int4/int8) as well as the standard datetime types. Support for other numeric types has been requested, but that seems like suitable material for a follow-on patch. In addition, the patch adds GROUPS mode which counts the offset in ORDER-BY peer groups rather than rows, and it adds the frame_exclusion options specified by SQL:2011. As far as I can see, we are now fully up to spec on window framing options. Existing behaviors remain unchanged, except that I changed the errcode for a couple of existing error reports to meet the SQL spec's expectation that negative "offset" values should be reported as SQLSTATE 22013. Internally and in relevant parts of the documentation, we now consistently use the terminology "offset PRECEDING/FOLLOWING" rather than "value PRECEDING/FOLLOWING", since the term "value" is confusingly vague. Oliver Ford, reviewed and whacked around some by me Discussion: https://postgr.es/m/CAGMVOdu9sivPAxbNN0X+q19Sfv9edEPv=HibOJhB14TJv_RCQg@mail.gmail.com --- doc/src/sgml/ref/select.sgml | 123 ++++++++++++++++++++++++++++--------------- 1 file changed, 81 insertions(+), 42 deletions(-) (limited to 'doc/src/sgml/ref') diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 8a3e86b6db6..b5d3d3a071e 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -859,19 +859,28 @@ WINDOW window_name AS ( frame_clause can be one of -{ RANGE | ROWS } frame_start -{ RANGE | ROWS } BETWEEN frame_start AND frame_end +{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ] +{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ] - where frame_start and frame_end can be - one of + where frame_start + and frame_end can be one of UNBOUNDED PRECEDING -value PRECEDING +offset PRECEDING CURRENT ROW -value FOLLOWING +offset FOLLOWING UNBOUNDED FOLLOWING + + + and frame_exclusion can be one of + + +EXCLUDE CURRENT ROW +EXCLUDE GROUP +EXCLUDE TIES +EXCLUDE NO OTHERS If frame_end is omitted it defaults to CURRENT @@ -879,8 +888,10 @@ UNBOUNDED FOLLOWING 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 + above list of frame_start + and frame_end options than + the frame_start choice does — for example + RANGE BETWEEN CURRENT ROW AND offset PRECEDING is not allowed. @@ -888,33 +899,72 @@ UNBOUNDED FOLLOWING 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 (a row that ORDER - BY considers equivalent to the current row, or all rows if there - is no ORDER BY). + up through the current row's last peer (a row + that the window's ORDER BY clause considers + equivalent to the current row), or 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 + row of the partition, regardless + of RANGE, ROWS + or GROUPS mode. + In ROWS mode, CURRENT ROW means + that the frame starts or ends with the current row; but + in RANGE or GROUPS mode it means + that the frame starts or ends with the current row's first or last peer + in the ORDER BY ordering. + The offset PRECEDING and + offset FOLLOWING options + vary in meaning depending on the frame mode. + In ROWS mode, the offset + is an integer indicating that the frame starts or ends that many rows + before or after the current row. + In GROUPS mode, the offset + is an integer indicating that the frame starts or ends that many peer + groups before or after the current row's peer group, where + a peer group is a group of rows that are + equivalent according to ORDER BY. + In RANGE mode, use of + an offset option requires that there be + exactly one ORDER BY column in the window definition. + Then the frame contains those rows whose ordering column value is no + more than offset less than + (for PRECEDING) or more than + (for FOLLOWING) the current row's ordering column + value. In these cases the data type of + the offset expression depends on the data + type of the ordering column. For numeric ordering columns it is + typically of the same type as the ordering column, but for datetime + ordering columns it is an interval. + In all these cases, the value of the offset + must be non-null and non-negative. Also, while + the offset does not have to be a simple + constant, it cannot contain variables, aggregate functions, or window + functions. + + + + The frame_exclusion option allows rows around + the current row to be excluded from the frame, even if they would be + included according to the frame start and frame end options. + EXCLUDE CURRENT ROW excludes the current row from the + frame. + EXCLUDE GROUP excludes the current row and its + ordering peers from the frame. + EXCLUDE TIES excludes any peers of the current + row from the frame, but not the current row itself. + EXCLUDE NO OTHERS simply specifies explicitly the + default behavior of not excluding the current row or its peers. + + + + Beware that the ROWS mode 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; all peer rows will be in the same frame. + uniquely. The RANGE and GROUPS + modes are designed to ensure that rows that are peers in + the ORDER BY ordering are treated alike: all rows of + a given peer group will be in the frame or excluded from it. @@ -1981,17 +2031,6 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; - - <literal>WINDOW</literal> Clause Restrictions - - - The SQL standard provides additional options for the window - frame_clause. - PostgreSQL currently supports only the - options listed above. - - - <literal>LIMIT</literal> and <literal>OFFSET</literal> -- cgit v1.2.3