diff options
38 files changed, 4300 insertions, 395 deletions
diff --git a/doc/src/sgml/btree.sgml b/doc/src/sgml/btree.sgml index 9f39edc742d..10abf90189e 100644 --- a/doc/src/sgml/btree.sgml +++ b/doc/src/sgml/btree.sgml @@ -207,7 +207,7 @@ <para> As shown in <xref linkend="xindex-btree-support-table"/>, btree defines - one required and one optional support function. + one required and two optional support functions. </para> <para> @@ -252,6 +252,185 @@ <filename>src/include/utils/sortsupport.h</filename>. </para> + <indexterm> + <primary>in_range support functions</primary> + </indexterm> + + <indexterm> + <primary>support functions</primary> + <secondary>in_range</secondary> + </indexterm> + + <para> + Optionally, a btree operator family may + provide <firstterm>in_range</firstterm> support function(s), registered + under support function number 3. These are not used during btree index + operations; rather, they extend the semantics of the operator family so + that it can support window clauses containing + the <literal>RANGE</literal> <replaceable>offset</replaceable> + <literal>PRECEDING</literal> + and <literal>RANGE</literal> <replaceable>offset</replaceable> + <literal>FOLLOWING</literal> frame bound types (see + <xref linkend="syntax-window-functions"/>). Fundamentally, the extra + information provided is how to add or subtract + an <replaceable>offset</replaceable> value in a way that is compatible + with the family's data ordering. + </para> + + <para> + An <function>in_range</function> function must have the signature +<synopsis> +in_range(<replaceable>val</replaceable> type1, <replaceable>base</replaceable> type1, <replaceable>offset</replaceable> type2, <replaceable>sub</replaceable> bool, <replaceable>less</replaceable> bool) +returns bool +</synopsis> + <replaceable>val</replaceable> and <replaceable>base</replaceable> must be + of the same type, which is one of the types supported by the operator + family (i.e., a type for which it provides an ordering). + However, <replaceable>offset</replaceable> could be of a different type, + which might be one otherwise unsupported by the family. An example is + that the built-in <literal>time_ops</literal> family provides + an <function>in_range</function> function that + has <replaceable>offset</replaceable> of type <type>interval</type>. + A family can provide <function>in_range</function> functions for any of + its supported types and one or more <replaceable>offset</replaceable> + types. Each <function>in_range</function> function should be entered + in <structname>pg_amproc</structname> + with <structfield>amproclefttype</structfield> equal to <type>type1</type> + and <structfield>amprocrighttype</structfield> equal to <type>type2</type>. + </para> + + <para> + The essential semantics of an <function>in_range</function> function + depend on the two boolean flag parameters. It should add or + subtract <replaceable>base</replaceable> + and <replaceable>offset</replaceable>, then + compare <replaceable>val</replaceable> to the result, as follows: + <itemizedlist> + <listitem> + <para> + if <literal>!</literal><replaceable>sub</replaceable> and + <literal>!</literal><replaceable>less</replaceable>, + return <replaceable>val</replaceable> <literal>>=</literal> + (<replaceable>base</replaceable> <literal>+</literal> + <replaceable>offset</replaceable>) + </para> + </listitem> + <listitem> + <para> + if <literal>!</literal><replaceable>sub</replaceable> + and <replaceable>less</replaceable>, + return <replaceable>val</replaceable> <literal><=</literal> + (<replaceable>base</replaceable> <literal>+</literal> + <replaceable>offset</replaceable>) + </para> + </listitem> + <listitem> + <para> + if <replaceable>sub</replaceable> + and <literal>!</literal><replaceable>less</replaceable>, + return <replaceable>val</replaceable> <literal>>=</literal> + (<replaceable>base</replaceable> <literal>-</literal> + <replaceable>offset</replaceable>) + </para> + </listitem> + <listitem> + <para> + if <replaceable>sub</replaceable> and <replaceable>less</replaceable>, + return <replaceable>val</replaceable> <literal><=</literal> + (<replaceable>base</replaceable> <literal>-</literal> + <replaceable>offset</replaceable>) + </para> + </listitem> + </itemizedlist> + Before doing so, the function should check the sign + of <replaceable>offset</replaceable>: if it is less than zero, raise + error <literal>ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE</literal> (22013) + with error text like <quote>invalid preceding or following size in window + function</quote>. (This is required by the SQL standard, although + nonstandard operator families might perhaps choose to ignore this + restriction, since there seems to be little semantic necessity for it.) + This requirement is delegated to the <function>in_range</function> + function so that the core code needn't understand what <quote>less than + zero</quote> means for a particular data type. + </para> + + <para> + An additional expectation is that <function>in_range</function> functions + should, if practical, avoid throwing an error + if <replaceable>base</replaceable> <literal>+</literal> + <replaceable>offset</replaceable> + or <replaceable>base</replaceable> <literal>-</literal> + <replaceable>offset</replaceable> would overflow. + The correct comparison result can be determined even if that value would + be out of the data type's range. Note that if the data type includes + concepts such as <quote>infinity</quote> or <quote>NaN</quote>, extra care + may be needed to ensure that <function>in_range</function>'s results agree + with the normal sort order of the operator family. + </para> + + <para> + The results of the <function>in_range</function> function must be + consistent with the sort ordering imposed by the operator family. + To be precise, given any fixed values of <replaceable>offset</replaceable> + and <replaceable>sub</replaceable>, then: + <itemizedlist> + <listitem> + <para> + If <function>in_range</function> with <replaceable>less</replaceable> = + true is true for some <replaceable>val1</replaceable> + and <replaceable>base</replaceable>, it must be true for + every <replaceable>val2</replaceable> <literal><=</literal> + <replaceable>val1</replaceable> with the + same <replaceable>base</replaceable>. + </para> + </listitem> + <listitem> + <para> + If <function>in_range</function> with <replaceable>less</replaceable> = + true is false for some <replaceable>val1</replaceable> + and <replaceable>base</replaceable>, it must be false for + every <replaceable>val2</replaceable> <literal>>=</literal> + <replaceable>val1</replaceable> with the + same <replaceable>base</replaceable>. + </para> + </listitem> + <listitem> + <para> + If <function>in_range</function> with <replaceable>less</replaceable> = + true is true for some <replaceable>val</replaceable> + and <replaceable>base1</replaceable>, it must be true for + every <replaceable>base2</replaceable> <literal>>=</literal> + <replaceable>base1</replaceable> with the + same <replaceable>val</replaceable>. + </para> + </listitem> + <listitem> + <para> + If <function>in_range</function> with <replaceable>less</replaceable> = + true is false for some <replaceable>val</replaceable> + and <replaceable>base1</replaceable>, it must be false for + every <replaceable>base2</replaceable> <literal><=</literal> + <replaceable>base1</replaceable> with the + same <replaceable>val</replaceable>. + </para> + </listitem> + </itemizedlist> + Analogous statements with inverted conditions hold + when <replaceable>less</replaceable> = false. + </para> + + <para> + If the type being ordered (<type>type1</type>) is collatable, + the appropriate collation OID will be passed to + the <function>in_range</function> function, using the standard + PG_GET_COLLATION() mechanism. + </para> + + <para> + <function>in_range</function> functions need not handle NULL inputs, and + typically will be marked strict. + </para> + </sect1> <sect1 id="btree-implementation"> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 487c7ff7507..640ff09a7b9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14729,8 +14729,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; partition through the last peer of the current row. This is likely to give unhelpful results for <function>last_value</function> and sometimes also <function>nth_value</function>. You can redefine the frame by - adding a suitable frame specification (<literal>RANGE</literal> or - <literal>ROWS</literal>) to the <literal>OVER</literal> clause. + adding a suitable frame specification (<literal>RANGE</literal>, + <literal>ROWS</literal> or <literal>GROUPS</literal>) to + the <literal>OVER</literal> clause. See <xref linkend="syntax-window-functions"/> for more information about frame specifications. </para> 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,28 +859,39 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl The <replaceable class="parameter">frame_clause</replaceable> can be one of <synopsis> -{ RANGE | ROWS } <replaceable>frame_start</replaceable> -{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> +{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] +{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] </synopsis> - where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be - one of + where <replaceable>frame_start</replaceable> + and <replaceable>frame_end</replaceable> can be one of <synopsis> UNBOUNDED PRECEDING -<replaceable>value</replaceable> PRECEDING +<replaceable>offset</replaceable> PRECEDING CURRENT ROW -<replaceable>value</replaceable> FOLLOWING +<replaceable>offset</replaceable> FOLLOWING UNBOUNDED FOLLOWING </synopsis> + and <replaceable>frame_exclusion</replaceable> can be one of + +<synopsis> +EXCLUDE CURRENT ROW +EXCLUDE GROUP +EXCLUDE TIES +EXCLUDE NO OTHERS +</synopsis> + If <replaceable>frame_end</replaceable> is omitted it defaults to <literal>CURRENT ROW</literal>. Restrictions are that <replaceable>frame_start</replaceable> cannot be <literal>UNBOUNDED FOLLOWING</literal>, <replaceable>frame_end</replaceable> cannot be <literal>UNBOUNDED PRECEDING</literal>, and the <replaceable>frame_end</replaceable> choice cannot appear earlier in the - above list than the <replaceable>frame_start</replaceable> choice — for example - <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</replaceable> + above list of <replaceable>frame_start</replaceable> + and <replaceable>frame_end</replaceable> options than + the <replaceable>frame_start</replaceable> choice does — for example + <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>offset</replaceable> PRECEDING</literal> is not allowed. </para> @@ -888,33 +899,72 @@ UNBOUNDED FOLLOWING The default framing option is <literal>RANGE UNBOUNDED PRECEDING</literal>, which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</literal>; it sets the frame to be all rows from the partition start - up through the current row's last peer (a row that <literal>ORDER - BY</literal> considers equivalent to the current row, or all rows if there - is no <literal>ORDER BY</literal>). + up through the current row's last <firstterm>peer</firstterm> (a row + that the window's <literal>ORDER BY</literal> clause considers + equivalent to the current row), or all rows if there + is no <literal>ORDER BY</literal>. In general, <literal>UNBOUNDED PRECEDING</literal> means that the frame starts with the first row of the partition, and similarly <literal>UNBOUNDED FOLLOWING</literal> means that the frame ends with the last - row of the partition (regardless of <literal>RANGE</literal> or <literal>ROWS</literal> - mode). In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> - means that the frame starts or ends with the current row; but in - <literal>RANGE</literal> mode it means that the frame starts or ends with - the current row's first or last peer in the <literal>ORDER BY</literal> ordering. - The <replaceable>value</replaceable> <literal>PRECEDING</literal> and - <replaceable>value</replaceable> <literal>FOLLOWING</literal> cases are currently only - allowed in <literal>ROWS</literal> 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</literal> options can produce unpredictable + row of the partition, regardless + of <literal>RANGE</literal>, <literal>ROWS</literal> + or <literal>GROUPS</literal> mode. + In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> means + that the frame starts or ends with the current row; but + in <literal>RANGE</literal> or <literal>GROUPS</literal> mode it means + that the frame starts or ends with the current row's first or last peer + in the <literal>ORDER BY</literal> ordering. + The <replaceable>offset</replaceable> <literal>PRECEDING</literal> and + <replaceable>offset</replaceable> <literal>FOLLOWING</literal> options + vary in meaning depending on the frame mode. + In <literal>ROWS</literal> mode, the <replaceable>offset</replaceable> + is an integer indicating that the frame starts or ends that many rows + before or after the current row. + In <literal>GROUPS</literal> mode, the <replaceable>offset</replaceable> + 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 <firstterm>peer group</firstterm> is a group of rows that are + equivalent according to <literal>ORDER BY</literal>. + In <literal>RANGE</literal> mode, use of + an <replaceable>offset</replaceable> option requires that there be + exactly one <literal>ORDER BY</literal> column in the window definition. + Then the frame contains those rows whose ordering column value is no + more than <replaceable>offset</replaceable> less than + (for <literal>PRECEDING</literal>) or more than + (for <literal>FOLLOWING</literal>) the current row's ordering column + value. In these cases the data type of + the <replaceable>offset</replaceable> 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 <type>interval</type>. + In all these cases, the value of the <replaceable>offset</replaceable> + must be non-null and non-negative. Also, while + the <replaceable>offset</replaceable> does not have to be a simple + constant, it cannot contain variables, aggregate functions, or window + functions. + </para> + + <para> + The <replaceable>frame_exclusion</replaceable> 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. + <literal>EXCLUDE CURRENT ROW</literal> excludes the current row from the + frame. + <literal>EXCLUDE GROUP</literal> excludes the current row and its + ordering peers from the frame. + <literal>EXCLUDE TIES</literal> excludes any peers of the current + row from the frame, but not the current row itself. + <literal>EXCLUDE NO OTHERS</literal> simply specifies explicitly the + default behavior of not excluding the current row or its peers. + </para> + + <para> + Beware that the <literal>ROWS</literal> mode can produce unpredictable results if the <literal>ORDER BY</literal> ordering does not order the rows - uniquely. The <literal>RANGE</literal> options are designed to ensure that - rows that are peers in the <literal>ORDER BY</literal> ordering are treated - alike; all peer rows will be in the same frame. + uniquely. The <literal>RANGE</literal> and <literal>GROUPS</literal> + modes are designed to ensure that rows that are peers in + the <literal>ORDER BY</literal> ordering are treated alike: all rows of + a given peer group will be in the frame or excluded from it. </para> <para> @@ -1982,17 +2032,6 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; </refsect2> <refsect2> - <title><literal>WINDOW</literal> Clause Restrictions</title> - - <para> - The SQL standard provides additional options for the window - <replaceable class="parameter">frame_clause</replaceable>. - <productname>PostgreSQL</productname> currently supports only the - options listed above. - </para> - </refsect2> - - <refsect2> <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title> <para> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index a938a21334b..f9905fb447b 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1802,21 +1802,28 @@ FROM generate_series(1,10) AS s(i); [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] [ <replaceable class="parameter">frame_clause</replaceable> ] </synopsis> - and the optional <replaceable class="parameter">frame_clause</replaceable> + The optional <replaceable class="parameter">frame_clause</replaceable> can be one of <synopsis> -{ RANGE | ROWS } <replaceable>frame_start</replaceable> -{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> +{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] +{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] </synopsis> - where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be - one of + where <replaceable>frame_start</replaceable> + and <replaceable>frame_end</replaceable> can be one of <synopsis> UNBOUNDED PRECEDING -<replaceable>value</replaceable> PRECEDING +<replaceable>offset</replaceable> PRECEDING CURRENT ROW -<replaceable>value</replaceable> FOLLOWING +<replaceable>offset</replaceable> FOLLOWING UNBOUNDED FOLLOWING </synopsis> + and <replaceable>frame_exclusion</replaceable> can be one of +<synopsis> +EXCLUDE CURRENT ROW +EXCLUDE GROUP +EXCLUDE TIES +EXCLUDE NO OTHERS +</synopsis> </para> <para> @@ -1856,11 +1863,14 @@ UNBOUNDED FOLLOWING The <replaceable class="parameter">frame_clause</replaceable> specifies the set of rows constituting the <firstterm>window frame</firstterm>, which is a subset of the current partition, for those window functions that act on - the frame instead of the whole partition. The frame can be specified in - either <literal>RANGE</literal> or <literal>ROWS</literal> mode; in either case, it - runs from the <replaceable>frame_start</replaceable> to the - <replaceable>frame_end</replaceable>. If <replaceable>frame_end</replaceable> is omitted, - it defaults to <literal>CURRENT ROW</literal>. + the frame instead of the whole partition. The set of rows in the frame + can vary depending on which row is the current row. The frame can be + specified in <literal>RANGE</literal>, <literal>ROWS</literal> + or <literal>GROUPS</literal> mode; in each case, it runs from + the <replaceable>frame_start</replaceable> to + the <replaceable>frame_end</replaceable>. + If <replaceable>frame_end</replaceable> is omitted, the end defaults + to <literal>CURRENT ROW</literal>. </para> <para> @@ -1871,24 +1881,91 @@ UNBOUNDED FOLLOWING </para> <para> - In <literal>RANGE</literal> mode, a <replaceable>frame_start</replaceable> of - <literal>CURRENT ROW</literal> means the frame starts with the current row's - first <firstterm>peer</firstterm> row (a row that <literal>ORDER BY</literal> considers - equivalent to the current row), while a <replaceable>frame_end</replaceable> of - <literal>CURRENT ROW</literal> means the frame ends with the last equivalent - <literal>ORDER BY</literal> peer. In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> simply means - the current row. + In <literal>RANGE</literal> or <literal>GROUPS</literal> mode, + a <replaceable>frame_start</replaceable> of + <literal>CURRENT ROW</literal> means the frame starts with the current + row's first <firstterm>peer</firstterm> row (a row that the + window's <literal>ORDER BY</literal> clause sorts as equivalent to the + current row), while a <replaceable>frame_end</replaceable> of + <literal>CURRENT ROW</literal> means the frame ends with the current + row's last peer row. + In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> simply + means the current row. </para> <para> - The <replaceable>value</replaceable> <literal>PRECEDING</literal> and - <replaceable>value</replaceable> <literal>FOLLOWING</literal> cases are currently only - allowed in <literal>ROWS</literal> 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 + In the <replaceable>offset</replaceable> <literal>PRECEDING</literal> + and <replaceable>offset</replaceable> <literal>FOLLOWING</literal> frame + options, the <replaceable>offset</replaceable> must be an 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. + The meaning of the <replaceable>offset</replaceable> depends on the + frame mode: + <itemizedlist> + <listitem> + <para> + In <literal>ROWS</literal> mode, + the <replaceable>offset</replaceable> must yield a non-null, + non-negative integer, and the option means that the frame starts or + ends the specified number of rows before or after the current row. + </para> + </listitem> + <listitem> + <para> + In <literal>GROUPS</literal> mode, + the <replaceable>offset</replaceable> again must yield a non-null, + non-negative integer, and the option means that the frame starts or + ends the specified number of <firstterm>peer groups</firstterm> + before or after the current row's peer group, where a peer group is a + set of rows that are equivalent in the <literal>ORDER BY</literal> + ordering. (If there is no <literal>ORDER BY</literal>, the whole + partition is one peer group.) + </para> + </listitem> + <listitem> + <para> + In <literal>RANGE</literal> mode, these options require that + the <literal>ORDER BY</literal> clause specify exactly one column. + The <replaceable>offset</replaceable> specifies the maximum + difference between the value of that column in the current row and + its value in preceding or following rows of the frame. The data type + of the <replaceable>offset</replaceable> expression varies depending + 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 <type>interval</type>. + For example, if the ordering column is of type <type>date</type> + or <type>timestamp</type>, one could write <literal>RANGE BETWEEN + '1 day' PRECEDING AND '10 days' FOLLOWING</literal>. + The <replaceable>offset</replaceable> is still required to be + non-null and non-negative, though the meaning + of <quote>non-negative</quote> depends on its data type. + </para> + </listitem> + </itemizedlist> + In any case, the distance to the end of the frame is limited by the + distance to the end of the partition, so that for rows near the partition + ends the frame might contain fewer rows than elsewhere. + </para> + + <para> + Notice that in both <literal>ROWS</literal> and <literal>GROUPS</literal> + mode, <literal>0 PRECEDING</literal> and <literal>0 FOLLOWING</literal> + are equivalent to <literal>CURRENT ROW</literal>. This normally holds + in <literal>RANGE</literal> mode as well, for an appropriate + data-type-specific meaning of <quote>zero</quote>. + </para> + + <para> + The <replaceable>frame_exclusion</replaceable> 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. + <literal>EXCLUDE CURRENT ROW</literal> excludes the current row from the + frame. + <literal>EXCLUDE GROUP</literal> excludes the current row and its + ordering peers from the frame. + <literal>EXCLUDE TIES</literal> excludes any peers of the current + row from the frame, but not the current row itself. + <literal>EXCLUDE NO OTHERS</literal> simply specifies explicitly the + default behavior of not excluding the current row or its peers. </para> <para> @@ -1896,9 +1973,9 @@ UNBOUNDED FOLLOWING which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</literal>. With <literal>ORDER BY</literal>, this sets the frame to be all rows from the partition start up through the current row's last - <literal>ORDER BY</literal> peer. Without <literal>ORDER BY</literal>, all rows of the partition are - included in the window frame, since all rows become peers of the current - row. + <literal>ORDER BY</literal> peer. Without <literal>ORDER BY</literal>, + this means all rows of the partition are included in the window frame, + since all rows become peers of the current row. </para> <para> @@ -1906,9 +1983,14 @@ UNBOUNDED FOLLOWING <replaceable>frame_start</replaceable> cannot be <literal>UNBOUNDED FOLLOWING</literal>, <replaceable>frame_end</replaceable> cannot be <literal>UNBOUNDED PRECEDING</literal>, and the <replaceable>frame_end</replaceable> choice cannot appear earlier in the - above list than the <replaceable>frame_start</replaceable> choice — for example - <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</replaceable> + above list of <replaceable>frame_start</replaceable> + and <replaceable>frame_end</replaceable> options than + the <replaceable>frame_start</replaceable> choice does — for example + <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>offset</replaceable> PRECEDING</literal> is not allowed. + But, for example, <literal>ROWS BETWEEN 7 PRECEDING AND 8 + PRECEDING</literal> is allowed, even though it would never select any + rows. </para> <para> diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml index e40131473fe..9f5c0c3fb2c 100644 --- a/doc/src/sgml/xindex.sgml +++ b/doc/src/sgml/xindex.sgml @@ -401,7 +401,8 @@ </para> <para> - B-trees require a single support function, and allow a second one to be + B-trees require a comparison support function, + and allow two additional support functions to be supplied at the operator class author's option, as shown in <xref linkend="xindex-btree-support-table"/>. The requirements for these support functions are explained further in @@ -433,6 +434,13 @@ </entry> <entry>2</entry> </row> + <row> + <entry> + Compare a test value to a base value plus/minus an offset, and return + true or false according to the comparison result (optional) + </entry> + <entry>3</entry> + </row> </tbody> </tgroup> </table> @@ -971,7 +979,8 @@ DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint8cmp(int8, int8) , - FUNCTION 2 btint8sortsupport(internal) ; + FUNCTION 2 btint8sortsupport(internal) , + FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) ; CREATE OPERATOR CLASS int4_ops DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS @@ -982,7 +991,8 @@ DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint4cmp(int4, int4) , - FUNCTION 2 btint4sortsupport(internal) ; + FUNCTION 2 btint4sortsupport(internal) , + FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) ; CREATE OPERATOR CLASS int2_ops DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS @@ -993,7 +1003,8 @@ DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint2cmp(int2, int2) , - FUNCTION 2 btint2sortsupport(internal) ; + FUNCTION 2 btint2sortsupport(internal) , + FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) ; ALTER OPERATOR FAMILY integer_ops USING btree ADD -- cross-type comparisons int8 vs int2 @@ -1042,7 +1053,13 @@ ALTER OPERATOR FAMILY integer_ops USING btree ADD OPERATOR 3 = (int2, int4) , OPERATOR 4 >= (int2, int4) , OPERATOR 5 > (int2, int4) , - FUNCTION 1 btint24cmp(int2, int4) ; + FUNCTION 1 btint24cmp(int2, int4) , + + -- cross-type in_range functions + FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) , + FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) , + FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) , + FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ; ]]> </programlisting> @@ -1181,6 +1198,39 @@ SELECT * FROM mytable ORDER BY somecol USING ~<~; </para> <para> + Another SQL feature that requires even more data-type-specific knowledge + is the <literal>RANGE</literal> <replaceable>offset</replaceable> + <literal>PRECEDING</literal>/<literal>FOLLOWING</literal> framing option + for window functions (see <xref linkend="syntax-window-functions"/>). + For a query such as +<programlisting> +SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING) + FROM mytable; +</programlisting> + it is not sufficient to know how to order by <literal>x</literal>; + the database must also understand how to <quote>subtract 5</quote> or + <quote>add 10</quote> to the current row's value of <literal>x</literal> + to identify the bounds of the current window frame. Comparing the + resulting bounds to other rows' values of <literal>x</literal> is + possible using the comparison operators provided by the B-tree operator + class that defines the <literal>ORDER BY</literal> ordering — but + addition and subtraction operators are not part of the operator class, so + which ones should be used? Hard-wiring that choice would be undesirable, + because different sort orders (different B-tree operator classes) might + need different behavior. Therefore, a B-tree operator class can specify + an <firstterm>in_range</firstterm> support function that encapsulates the + addition and subtraction behaviors that make sense for its sort order. + It can even provide more than one in_range support function, in case + there is more than one data type that makes sense to use as the offset + in <literal>RANGE</literal> clauses. + If the B-tree operator class associated with the window's <literal>ORDER + BY</literal> clause does not have a matching in_range support function, + the <literal>RANGE</literal> <replaceable>offset</replaceable> + <literal>PRECEDING</literal>/<literal>FOLLOWING</literal> + option is not supported. + </para> + + <para> Another important point is that an equality operator that appears in a hash operator family is a candidate for hash joins, hash aggregation, and related optimizations. The hash operator family diff --git a/src/backend/access/nbtree/nbtvalidate.c b/src/backend/access/nbtree/nbtvalidate.c index 8f4ccc87c0c..f24091c0ada 100644 --- a/src/backend/access/nbtree/nbtvalidate.c +++ b/src/backend/access/nbtree/nbtvalidate.c @@ -51,6 +51,7 @@ btvalidate(Oid opclassoid) List *grouplist; OpFamilyOpFuncGroup *opclassgroup; List *familytypes; + int usefulgroups; int i; ListCell *lc; @@ -95,6 +96,14 @@ btvalidate(Oid opclassoid) ok = check_amproc_signature(procform->amproc, VOIDOID, true, 1, 1, INTERNALOID); break; + case BTINRANGE_PROC: + ok = check_amproc_signature(procform->amproc, BOOLOID, true, + 5, 5, + procform->amproclefttype, + procform->amproclefttype, + procform->amprocrighttype, + BOOLOID, BOOLOID); + break; default: ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), @@ -165,12 +174,28 @@ btvalidate(Oid opclassoid) /* Now check for inconsistent groups of operators/functions */ grouplist = identify_opfamily_groups(oprlist, proclist); + usefulgroups = 0; opclassgroup = NULL; familytypes = NIL; foreach(lc, grouplist) { OpFamilyOpFuncGroup *thisgroup = (OpFamilyOpFuncGroup *) lfirst(lc); + /* + * It is possible for an in_range support function to have a RHS type + * that is otherwise irrelevant to the opfamily --- for instance, SQL + * requires the datetime_ops opclass to have range support with an + * interval offset. So, if this group appears to contain only an + * in_range function, ignore it: it doesn't represent a pair of + * supported types. + */ + if (thisgroup->operatorset == 0 && + thisgroup->functionset == (1 << BTINRANGE_PROC)) + continue; + + /* Else count it as a relevant group */ + usefulgroups++; + /* Remember the group exactly matching the test opclass */ if (thisgroup->lefttype == opcintype && thisgroup->righttype == opcintype) @@ -186,8 +211,8 @@ btvalidate(Oid opclassoid) /* * Complain if there seems to be an incomplete set of either operators - * or support functions for this datatype pair. The only thing that - * is considered optional is the sortsupport function. + * or support functions for this datatype pair. The only things + * considered optional are the sortsupport and in_range functions. */ if (thisgroup->operatorset != ((1 << BTLessStrategyNumber) | @@ -234,8 +259,7 @@ btvalidate(Oid opclassoid) * additional qual clauses from equivalence classes, so it seems * reasonable to insist that all built-in btree opfamilies be complete. */ - if (list_length(grouplist) != - list_length(familytypes) * list_length(familytypes)) + if (usefulgroups != (list_length(familytypes) * list_length(familytypes))) { ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index be60270ea5f..b7e39af7a24 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -1883,6 +1883,22 @@ find_expr_references_walker(Node *node, context->addrs); return false; } + else if (IsA(node, WindowClause)) + { + WindowClause *wc = (WindowClause *) node; + + if (OidIsValid(wc->startInRangeFunc)) + add_object_address(OCLASS_PROC, wc->startInRangeFunc, 0, + context->addrs); + if (OidIsValid(wc->endInRangeFunc)) + add_object_address(OCLASS_PROC, wc->endInRangeFunc, 0, + context->addrs); + if (OidIsValid(wc->inRangeColl) && + wc->inRangeColl != DEFAULT_COLLATION_OID) + add_object_address(OCLASS_COLLATION, wc->inRangeColl, 0, + context->addrs); + /* fall through to examine substructure */ + } else if (IsA(node, Query)) { /* Recurse into RTE subquery or not-yet-planned sublink subquery */ diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 8e746f36d4e..20d61f37803 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -498,7 +498,7 @@ T616 Null treatment option for LEAD and LAG functions NO T617 FIRST_VALUE and LAST_VALUE function YES T618 NTH_VALUE function NO function exists, but some options missing T619 Nested window functions NO -T620 WINDOW clause: GROUPS option NO +T620 WINDOW clause: GROUPS option YES T621 Enhanced numeric functions YES T631 IN predicate with one list element YES T641 Multiple column assignment NO only some syntax variants supported diff --git a/src/backend/commands/opclasscmds.c b/src/backend/commands/opclasscmds.c index 1768140a830..e4b1369f193 100644 --- a/src/backend/commands/opclasscmds.c +++ b/src/backend/commands/opclasscmds.c @@ -1128,10 +1128,11 @@ assignProcTypes(OpFamilyMember *member, Oid amoid, Oid typeoid) procform = (Form_pg_proc) GETSTRUCT(proctup); /* - * btree comparison procs must be 2-arg procs returning int4, while btree - * sortsupport procs must take internal and return void. hash support - * proc 1 must be a 1-arg proc returning int4, while proc 2 must be a - * 2-arg proc returning int8. Otherwise we don't know. + * btree comparison procs must be 2-arg procs returning int4. btree + * sortsupport procs must take internal and return void. btree in_range + * procs must be 5-arg procs returning bool. hash support proc 1 must be + * a 1-arg proc returning int4, while proc 2 must be a 2-arg proc + * returning int8. Otherwise we don't know. */ if (amoid == BTREE_AM_OID) { @@ -1171,6 +1172,26 @@ assignProcTypes(OpFamilyMember *member, Oid amoid, Oid typeoid) * Can't infer lefttype/righttype from proc, so use default rule */ } + else if (member->number == BTINRANGE_PROC) + { + if (procform->pronargs != 5) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("btree in_range procedures must have five arguments"))); + if (procform->prorettype != BOOLOID) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("btree in_range procedures must return boolean"))); + + /* + * If lefttype/righttype isn't specified, use the proc's input + * types (we look at the test-value and offset arguments) + */ + if (!OidIsValid(member->lefttype)) + member->lefttype = procform->proargtypes.values[0]; + if (!OidIsValid(member->righttype)) + member->righttype = procform->proargtypes.values[2]; + } } else if (amoid == HASH_AM_OID) { diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 0afb1c83d38..f6412576f40 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -180,10 +180,11 @@ static void begin_partition(WindowAggState *winstate); static void spool_tuples(WindowAggState *winstate, int64 pos); static void release_partition(WindowAggState *winstate); -static bool row_is_in_frame(WindowAggState *winstate, int64 pos, +static int row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot); -static void update_frameheadpos(WindowObject winobj, TupleTableSlot *slot); -static void update_frametailpos(WindowObject winobj, TupleTableSlot *slot); +static void update_frameheadpos(WindowAggState *winstate); +static void update_frametailpos(WindowAggState *winstate); +static void update_grouptailpos(WindowAggState *winstate); static WindowStatePerAggData *initialize_peragg(WindowAggState *winstate, WindowFunc *wfunc, @@ -683,11 +684,9 @@ eval_windowaggregates(WindowAggState *winstate) temp_slot = winstate->temp_slot_1; /* - * Currently, we support only a subset of the SQL-standard window framing - * rules. - * - * If the frame start is UNBOUNDED_PRECEDING, the window frame consists of - * a contiguous group of rows extending forward from the start of the + * If the window's frame start clause is UNBOUNDED_PRECEDING and no + * exclusion clause is specified, then the window frame consists of a + * contiguous group of rows extending forward from the start of the * partition, and rows only enter the frame, never exit it, as the current * row advances forward. This makes it possible to use an incremental * strategy for evaluating aggregates: we run the transition function for @@ -710,6 +709,11 @@ eval_windowaggregates(WindowAggState *winstate) * must perform the aggregation all over again for all tuples within the * new frame boundaries. * + * If there's any exclusion clause, then we may have to aggregate over a + * non-contiguous set of rows, so we punt and recalculate for every row. + * (For some frame end choices, it might be that the frame is always + * contiguous anyway, but that's an optimization to investigate later.) + * * In many common cases, multiple rows share the same frame and hence the * same aggregate value. (In particular, if there's no ORDER BY in a RANGE * window, then all rows are peers and so they all have window frame equal @@ -728,7 +732,7 @@ eval_windowaggregates(WindowAggState *winstate) * The frame head should never move backwards, and the code below wouldn't * cope if it did, so for safety we complain if it does. */ - update_frameheadpos(agg_winobj, temp_slot); + update_frameheadpos(winstate); if (winstate->frameheadpos < winstate->aggregatedbase) elog(ERROR, "window frame head moved backward"); @@ -737,15 +741,16 @@ eval_windowaggregates(WindowAggState *winstate) * the result values that were previously saved at the bottom of this * function. Since we don't know the current frame's end yet, this is not * possible to check for fully. But if the frame end mode is UNBOUNDED - * FOLLOWING or CURRENT ROW, and the current row lies within the previous - * row's frame, then the two frames' ends must coincide. Note that on the - * first row aggregatedbase == aggregatedupto, meaning this test must - * fail, so we don't need to check the "there was no previous row" case - * explicitly here. + * FOLLOWING or CURRENT ROW, no exclusion clause is specified, and the + * current row lies within the previous row's frame, then the two frames' + * ends must coincide. Note that on the first row aggregatedbase == + * aggregatedupto, meaning this test must fail, so we don't need to check + * the "there was no previous row" case explicitly here. */ if (winstate->aggregatedbase == winstate->frameheadpos && (winstate->frameOptions & (FRAMEOPTION_END_UNBOUNDED_FOLLOWING | FRAMEOPTION_END_CURRENT_ROW)) && + !(winstate->frameOptions & FRAMEOPTION_EXCLUSION) && winstate->aggregatedbase <= winstate->currentpos && winstate->aggregatedupto > winstate->currentpos) { @@ -766,6 +771,7 @@ eval_windowaggregates(WindowAggState *winstate) * - if we're processing the first row in the partition, or * - if the frame's head moved and we cannot use an inverse * transition function, or + * - we have an EXCLUSION clause, or * - if the new frame doesn't overlap the old one * * Note that we don't strictly need to restart in the last case, but if @@ -780,6 +786,7 @@ eval_windowaggregates(WindowAggState *winstate) if (winstate->currentpos == 0 || (winstate->aggregatedbase != winstate->frameheadpos && !OidIsValid(peraggstate->invtransfn_oid)) || + (winstate->frameOptions & FRAMEOPTION_EXCLUSION) || winstate->aggregatedupto <= winstate->frameheadpos) { peraggstate->restart = true; @@ -920,6 +927,8 @@ eval_windowaggregates(WindowAggState *winstate) */ for (;;) { + int ret; + /* Fetch next row if we didn't already */ if (TupIsNull(agg_row_slot)) { @@ -928,9 +937,15 @@ eval_windowaggregates(WindowAggState *winstate) break; /* must be end of partition */ } - /* Exit loop (for now) if not in frame */ - if (!row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot)) + /* + * Exit loop if no more rows can be in frame. Skip aggregation if + * current row is not in frame but there might be more in the frame. + */ + ret = row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot); + if (ret < 0) break; + if (ret == 0) + goto next_tuple; /* Set tuple context for evaluation of aggregate arguments */ winstate->tmpcontext->ecxt_outertuple = agg_row_slot; @@ -951,6 +966,7 @@ eval_windowaggregates(WindowAggState *winstate) peraggstate); } +next_tuple: /* Reset per-input-tuple context after each tuple */ ResetExprContext(winstate->tmpcontext); @@ -1061,6 +1077,7 @@ eval_windowfunction(WindowAggState *winstate, WindowStatePerFunc perfuncstate, static void begin_partition(WindowAggState *winstate) { + WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; PlanState *outerPlan = outerPlanState(winstate); int numfuncs = winstate->numfuncs; int i; @@ -1068,11 +1085,21 @@ begin_partition(WindowAggState *winstate) winstate->partition_spooled = false; winstate->framehead_valid = false; winstate->frametail_valid = false; + winstate->grouptail_valid = false; winstate->spooled_rows = 0; winstate->currentpos = 0; winstate->frameheadpos = 0; - winstate->frametailpos = -1; + winstate->frametailpos = 0; + winstate->currentgroup = 0; + winstate->frameheadgroup = 0; + winstate->frametailgroup = 0; + winstate->groupheadpos = 0; + winstate->grouptailpos = -1; /* see update_grouptailpos */ ExecClearTuple(winstate->agg_row_slot); + if (winstate->framehead_slot) + ExecClearTuple(winstate->framehead_slot); + if (winstate->frametail_slot) + ExecClearTuple(winstate->frametail_slot); /* * If this is the very first partition, we need to fetch the first input @@ -1099,7 +1126,7 @@ begin_partition(WindowAggState *winstate) /* * Set up read pointers for the tuplestore. The current pointer doesn't * need BACKWARD capability, but the per-window-function read pointers do, - * and the aggregate pointer does if frame start is movable. + * and the aggregate pointer does if we might need to restart aggregation. */ winstate->current_ptr = 0; /* read pointer 0 is pre-allocated */ @@ -1112,10 +1139,14 @@ begin_partition(WindowAggState *winstate) WindowObject agg_winobj = winstate->agg_winobj; int readptr_flags = 0; - /* If the frame head is potentially movable ... */ - if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) + /* + * If the frame head is potentially movable, or we have an EXCLUSION + * clause, we might need to restart aggregation ... + */ + if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) || + (winstate->frameOptions & FRAMEOPTION_EXCLUSION)) { - /* ... create a mark pointer to track the frame head */ + /* ... so create a mark pointer to track the frame head */ agg_winobj->markptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); /* and the read pointer will need BACKWARD capability */ readptr_flags |= EXEC_FLAG_BACKWARD; @@ -1150,6 +1181,44 @@ begin_partition(WindowAggState *winstate) } /* + * If we are in RANGE or GROUPS mode, then determining frame boundaries + * requires physical access to the frame endpoint rows, except in + * degenerate cases. We create read pointers to point to those rows, to + * simplify access and ensure that the tuplestore doesn't discard the + * endpoint rows prematurely. (Must match logic in update_frameheadpos + * and update_frametailpos.) + */ + winstate->framehead_ptr = winstate->frametail_ptr = -1; /* if not used */ + + if ((winstate->frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) && + node->ordNumCols != 0) + { + if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) + winstate->framehead_ptr = + tuplestore_alloc_read_pointer(winstate->buffer, 0); + if (!(winstate->frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)) + winstate->frametail_ptr = + tuplestore_alloc_read_pointer(winstate->buffer, 0); + } + + /* + * If we have an exclusion clause that requires knowing the boundaries of + * the current row's peer group, we create a read pointer to track the + * tail position of the peer group (i.e., first row of the next peer + * group). The head position does not require its own pointer because we + * maintain that as a side effect of advancing the current row. + */ + winstate->grouptail_ptr = -1; + + if ((winstate->frameOptions & (FRAMEOPTION_EXCLUDE_GROUP | + FRAMEOPTION_EXCLUDE_TIES)) && + node->ordNumCols != 0) + { + winstate->grouptail_ptr = + tuplestore_alloc_read_pointer(winstate->buffer, 0); + } + + /* * Store the first tuple into the tuplestore (it's always available now; * we either read it above, or saved it at the end of previous partition) */ @@ -1275,119 +1344,127 @@ release_partition(WindowAggState *winstate) * The caller must have already determined that the row is in the partition * and fetched it into a slot. This function just encapsulates the framing * rules. + * + * Returns: + * -1, if the row is out of frame and no succeeding rows can be in frame + * 0, if the row is out of frame but succeeding rows might be in frame + * 1, if the row is in frame + * + * May clobber winstate->temp_slot_2. */ -static bool +static int row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot) { int frameOptions = winstate->frameOptions; Assert(pos >= 0); /* else caller error */ - /* First, check frame starting conditions */ - if (frameOptions & FRAMEOPTION_START_CURRENT_ROW) - { - if (frameOptions & FRAMEOPTION_ROWS) - { - /* rows before current row are out of frame */ - if (pos < winstate->currentpos) - return false; - } - else if (frameOptions & FRAMEOPTION_RANGE) - { - /* preceding row that is not peer is out of frame */ - if (pos < winstate->currentpos && - !are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) - return false; - } - else - Assert(false); - } - else if (frameOptions & FRAMEOPTION_START_VALUE) - { - if (frameOptions & FRAMEOPTION_ROWS) - { - int64 offset = DatumGetInt64(winstate->startOffsetValue); - - /* rows before current row + offset are out of frame */ - if (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) - offset = -offset; - - if (pos < winstate->currentpos + offset) - return false; - } - else if (frameOptions & FRAMEOPTION_RANGE) - { - /* parser should have rejected this */ - elog(ERROR, "window frame with value offset is not implemented"); - } - else - Assert(false); - } + /* + * First, check frame starting conditions. We might as well delegate this + * to update_frameheadpos always; it doesn't add any notable cost. + */ + update_frameheadpos(winstate); + if (pos < winstate->frameheadpos) + return 0; - /* Okay so far, now check frame ending conditions */ + /* + * Okay so far, now check frame ending conditions. Here, we avoid calling + * update_frametailpos in simple cases, so as not to spool tuples further + * ahead than necessary. + */ if (frameOptions & FRAMEOPTION_END_CURRENT_ROW) { if (frameOptions & FRAMEOPTION_ROWS) { /* rows after current row are out of frame */ if (pos > winstate->currentpos) - return false; + return -1; } - else if (frameOptions & FRAMEOPTION_RANGE) + else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) { /* following row that is not peer is out of frame */ if (pos > winstate->currentpos && !are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) - return false; + return -1; } else Assert(false); } - else if (frameOptions & FRAMEOPTION_END_VALUE) + else if (frameOptions & FRAMEOPTION_END_OFFSET) { if (frameOptions & FRAMEOPTION_ROWS) { int64 offset = DatumGetInt64(winstate->endOffsetValue); /* rows after current row + offset are out of frame */ - if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) + if (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING) offset = -offset; if (pos > winstate->currentpos + offset) - return false; + return -1; } - else if (frameOptions & FRAMEOPTION_RANGE) + else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) { - /* parser should have rejected this */ - elog(ERROR, "window frame with value offset is not implemented"); + /* hard cases, so delegate to update_frametailpos */ + update_frametailpos(winstate); + if (pos >= winstate->frametailpos) + return -1; } else Assert(false); } + /* Check exclusion clause */ + if (frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW) + { + if (pos == winstate->currentpos) + return 0; + } + else if ((frameOptions & FRAMEOPTION_EXCLUDE_GROUP) || + ((frameOptions & FRAMEOPTION_EXCLUDE_TIES) && + pos != winstate->currentpos)) + { + WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; + + /* If no ORDER BY, all rows are peers with each other */ + if (node->ordNumCols == 0) + return 0; + /* Otherwise, check the group boundaries */ + if (pos >= winstate->groupheadpos) + { + update_grouptailpos(winstate); + if (pos < winstate->grouptailpos) + return 0; + } + } + /* If we get here, it's in frame */ - return true; + return 1; } /* * update_frameheadpos * make frameheadpos valid for the current row * - * Uses the winobj's read pointer for any required fetches; hence, if the - * frame mode is one that requires row comparisons, the winobj's mark must - * not be past the currently known frame head. Also uses the specified slot - * for any required fetches. + * Note that frameheadpos is computed without regard for any window exclusion + * clause; the current row and/or its peers are considered part of the frame + * for this purpose even if they must be excluded later. + * + * May clobber winstate->temp_slot_2. */ static void -update_frameheadpos(WindowObject winobj, TupleTableSlot *slot) +update_frameheadpos(WindowAggState *winstate) { - WindowAggState *winstate = winobj->winstate; WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; int frameOptions = winstate->frameOptions; + MemoryContext oldcontext; if (winstate->framehead_valid) return; /* already known for current row */ + /* We may be called in a short-lived context */ + oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory); + if (frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) { /* In UNBOUNDED PRECEDING mode, frame head is always row 0 */ @@ -1402,58 +1479,67 @@ update_frameheadpos(WindowObject winobj, TupleTableSlot *slot) winstate->frameheadpos = winstate->currentpos; winstate->framehead_valid = true; } - else if (frameOptions & FRAMEOPTION_RANGE) + else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) { - int64 fhprev; - /* If no ORDER BY, all rows are peers with each other */ if (node->ordNumCols == 0) { winstate->frameheadpos = 0; winstate->framehead_valid = true; + MemoryContextSwitchTo(oldcontext); return; } /* - * In RANGE START_CURRENT mode, frame head is the first row that - * is a peer of current row. We search backwards from current, - * which could be a bit inefficient if peer sets are large. Might - * be better to have a separate read pointer that moves forward - * tracking the frame head. + * In RANGE or GROUPS START_CURRENT_ROW mode, frame head is the + * first row that is a peer of current row. We keep a copy of the + * last-known frame head row in framehead_slot, and advance as + * necessary. Note that if we reach end of partition, we will + * leave frameheadpos = end+1 and framehead_slot empty. */ - fhprev = winstate->currentpos - 1; - for (;;) + tuplestore_select_read_pointer(winstate->buffer, + winstate->framehead_ptr); + if (winstate->frameheadpos == 0 && + TupIsNull(winstate->framehead_slot)) { - /* assume the frame head can't go backwards */ - if (fhprev < winstate->frameheadpos) - break; - if (!window_gettupleslot(winobj, fhprev, slot)) - break; /* start of partition */ - if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) - break; /* not peer of current row */ - fhprev--; + /* fetch first row into framehead_slot, if we didn't already */ + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->framehead_slot)) + elog(ERROR, "unexpected end of tuplestore"); + } + + while (!TupIsNull(winstate->framehead_slot)) + { + if (are_peers(winstate, winstate->framehead_slot, + winstate->ss.ss_ScanTupleSlot)) + break; /* this row is the correct frame head */ + /* Note we advance frameheadpos even if the fetch fails */ + winstate->frameheadpos++; + spool_tuples(winstate, winstate->frameheadpos); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->framehead_slot)) + break; /* end of partition */ } - winstate->frameheadpos = fhprev + 1; winstate->framehead_valid = true; } else Assert(false); } - else if (frameOptions & FRAMEOPTION_START_VALUE) + else if (frameOptions & FRAMEOPTION_START_OFFSET) { if (frameOptions & FRAMEOPTION_ROWS) { /* In ROWS mode, bound is physically n before/after current */ int64 offset = DatumGetInt64(winstate->startOffsetValue); - if (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) + if (frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING) offset = -offset; winstate->frameheadpos = winstate->currentpos + offset; /* frame head can't go before first row */ if (winstate->frameheadpos < 0) winstate->frameheadpos = 0; - else if (winstate->frameheadpos > winstate->currentpos) + else if (winstate->frameheadpos > winstate->currentpos + 1) { /* make sure frameheadpos is not past end of partition */ spool_tuples(winstate, winstate->frameheadpos - 1); @@ -1464,40 +1550,172 @@ update_frameheadpos(WindowObject winobj, TupleTableSlot *slot) } else if (frameOptions & FRAMEOPTION_RANGE) { - /* parser should have rejected this */ - elog(ERROR, "window frame with value offset is not implemented"); + /* + * In RANGE START_OFFSET mode, frame head is the first row that + * satisfies the in_range constraint relative to the current row. + * We keep a copy of the last-known frame head row in + * framehead_slot, and advance as necessary. Note that if we + * reach end of partition, we will leave frameheadpos = end+1 and + * framehead_slot empty. + */ + bool sub, + less; + + /* Precompute flags for in_range checks */ + if (frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING) + sub = true; /* subtract startOffset from current row */ + else + sub = false; /* add it */ + less = false; /* normally, we want frame head >= sum */ + /* If sort order is descending, flip both flags */ + if (!winstate->inRangeAsc) + { + sub = !sub; + less = true; + } + + tuplestore_select_read_pointer(winstate->buffer, + winstate->framehead_ptr); + if (winstate->frameheadpos == 0 && + TupIsNull(winstate->framehead_slot)) + { + /* fetch first row into framehead_slot, if we didn't already */ + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->framehead_slot)) + elog(ERROR, "unexpected end of tuplestore"); + } + + while (!TupIsNull(winstate->framehead_slot)) + { + Datum headval, + currval; + bool headisnull, + currisnull; + + headval = slot_getattr(winstate->framehead_slot, 1, + &headisnull); + currval = slot_getattr(winstate->ss.ss_ScanTupleSlot, 1, + &currisnull); + if (headisnull || currisnull) + { + /* order of the rows depends only on nulls_first */ + if (winstate->inRangeNullsFirst) + { + /* advance head if head is null and curr is not */ + if (!headisnull || currisnull) + break; + } + else + { + /* advance head if head is not null and curr is null */ + if (headisnull || !currisnull) + break; + } + } + else + { + if (DatumGetBool(FunctionCall5Coll(&winstate->startInRangeFunc, + winstate->inRangeColl, + headval, + currval, + winstate->startOffsetValue, + BoolGetDatum(sub), + BoolGetDatum(less)))) + break; /* this row is the correct frame head */ + } + /* Note we advance frameheadpos even if the fetch fails */ + winstate->frameheadpos++; + spool_tuples(winstate, winstate->frameheadpos); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->framehead_slot)) + break; /* end of partition */ + } + winstate->framehead_valid = true; + } + else if (frameOptions & FRAMEOPTION_GROUPS) + { + /* + * In GROUPS START_OFFSET mode, frame head is the first row of the + * first peer group whose number satisfies the offset constraint. + * We keep a copy of the last-known frame head row in + * framehead_slot, and advance as necessary. Note that if we + * reach end of partition, we will leave frameheadpos = end+1 and + * framehead_slot empty. + */ + int64 offset = DatumGetInt64(winstate->startOffsetValue); + int64 minheadgroup; + + if (frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING) + minheadgroup = winstate->currentgroup - offset; + else + minheadgroup = winstate->currentgroup + offset; + + tuplestore_select_read_pointer(winstate->buffer, + winstate->framehead_ptr); + if (winstate->frameheadpos == 0 && + TupIsNull(winstate->framehead_slot)) + { + /* fetch first row into framehead_slot, if we didn't already */ + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->framehead_slot)) + elog(ERROR, "unexpected end of tuplestore"); + } + + while (!TupIsNull(winstate->framehead_slot)) + { + if (winstate->frameheadgroup >= minheadgroup) + break; /* this row is the correct frame head */ + ExecCopySlot(winstate->temp_slot_2, winstate->framehead_slot); + /* Note we advance frameheadpos even if the fetch fails */ + winstate->frameheadpos++; + spool_tuples(winstate, winstate->frameheadpos); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->framehead_slot)) + break; /* end of partition */ + if (!are_peers(winstate, winstate->temp_slot_2, + winstate->framehead_slot)) + winstate->frameheadgroup++; + } + ExecClearTuple(winstate->temp_slot_2); + winstate->framehead_valid = true; } else Assert(false); } else Assert(false); + + MemoryContextSwitchTo(oldcontext); } /* * update_frametailpos * make frametailpos valid for the current row * - * Uses the winobj's read pointer for any required fetches; hence, if the - * frame mode is one that requires row comparisons, the winobj's mark must - * not be past the currently known frame tail. Also uses the specified slot - * for any required fetches. + * Note that frametailpos is computed without regard for any window exclusion + * clause; the current row and/or its peers are considered part of the frame + * for this purpose even if they must be excluded later. + * + * May clobber winstate->temp_slot_2. */ static void -update_frametailpos(WindowObject winobj, TupleTableSlot *slot) +update_frametailpos(WindowAggState *winstate) { - WindowAggState *winstate = winobj->winstate; WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; int frameOptions = winstate->frameOptions; + MemoryContext oldcontext; if (winstate->frametail_valid) return; /* already known for current row */ + /* We may be called in a short-lived context */ + oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory); + if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) { /* In UNBOUNDED FOLLOWING mode, all partition rows are in frame */ spool_tuples(winstate, -1); - winstate->frametailpos = winstate->spooled_rows - 1; + winstate->frametailpos = winstate->spooled_rows; winstate->frametail_valid = true; } else if (frameOptions & FRAMEOPTION_END_CURRENT_ROW) @@ -1505,77 +1723,276 @@ update_frametailpos(WindowObject winobj, TupleTableSlot *slot) if (frameOptions & FRAMEOPTION_ROWS) { /* In ROWS mode, exactly the rows up to current are in frame */ - winstate->frametailpos = winstate->currentpos; + winstate->frametailpos = winstate->currentpos + 1; winstate->frametail_valid = true; } - else if (frameOptions & FRAMEOPTION_RANGE) + else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) { - int64 ftnext; - /* If no ORDER BY, all rows are peers with each other */ if (node->ordNumCols == 0) { spool_tuples(winstate, -1); - winstate->frametailpos = winstate->spooled_rows - 1; + winstate->frametailpos = winstate->spooled_rows; winstate->frametail_valid = true; + MemoryContextSwitchTo(oldcontext); return; } /* - * Else we have to search for the first non-peer of the current - * row. We assume the current value of frametailpos is a lower - * bound on the possible frame tail location, ie, frame tail never - * goes backward, and that currentpos is also a lower bound, ie, - * frame end always >= current row. + * In RANGE or GROUPS END_CURRENT_ROW mode, frame end is the last + * row that is a peer of current row, frame tail is the row after + * that (if any). We keep a copy of the last-known frame tail row + * in frametail_slot, and advance as necessary. Note that if we + * reach end of partition, we will leave frametailpos = end+1 and + * frametail_slot empty. */ - ftnext = Max(winstate->frametailpos, winstate->currentpos) + 1; - for (;;) + tuplestore_select_read_pointer(winstate->buffer, + winstate->frametail_ptr); + if (winstate->frametailpos == 0 && + TupIsNull(winstate->frametail_slot)) + { + /* fetch first row into frametail_slot, if we didn't already */ + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->frametail_slot)) + elog(ERROR, "unexpected end of tuplestore"); + } + + while (!TupIsNull(winstate->frametail_slot)) { - if (!window_gettupleslot(winobj, ftnext, slot)) + if (winstate->frametailpos > winstate->currentpos && + !are_peers(winstate, winstate->frametail_slot, + winstate->ss.ss_ScanTupleSlot)) + break; /* this row is the frame tail */ + /* Note we advance frametailpos even if the fetch fails */ + winstate->frametailpos++; + spool_tuples(winstate, winstate->frametailpos); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->frametail_slot)) break; /* end of partition */ - if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot)) - break; /* not peer of current row */ - ftnext++; } - winstate->frametailpos = ftnext - 1; winstate->frametail_valid = true; } else Assert(false); } - else if (frameOptions & FRAMEOPTION_END_VALUE) + else if (frameOptions & FRAMEOPTION_END_OFFSET) { if (frameOptions & FRAMEOPTION_ROWS) { /* In ROWS mode, bound is physically n before/after current */ int64 offset = DatumGetInt64(winstate->endOffsetValue); - if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) + if (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING) offset = -offset; - winstate->frametailpos = winstate->currentpos + offset; - /* smallest allowable value of frametailpos is -1 */ + winstate->frametailpos = winstate->currentpos + offset + 1; + /* smallest allowable value of frametailpos is 0 */ if (winstate->frametailpos < 0) - winstate->frametailpos = -1; - else if (winstate->frametailpos > winstate->currentpos) + winstate->frametailpos = 0; + else if (winstate->frametailpos > winstate->currentpos + 1) { - /* make sure frametailpos is not past last row of partition */ - spool_tuples(winstate, winstate->frametailpos); - if (winstate->frametailpos >= winstate->spooled_rows) - winstate->frametailpos = winstate->spooled_rows - 1; + /* make sure frametailpos is not past end of partition */ + spool_tuples(winstate, winstate->frametailpos - 1); + if (winstate->frametailpos > winstate->spooled_rows) + winstate->frametailpos = winstate->spooled_rows; } winstate->frametail_valid = true; } else if (frameOptions & FRAMEOPTION_RANGE) { - /* parser should have rejected this */ - elog(ERROR, "window frame with value offset is not implemented"); + /* + * In RANGE END_OFFSET mode, frame end is the last row that + * satisfies the in_range constraint relative to the current row, + * frame tail is the row after that (if any). We keep a copy of + * the last-known frame tail row in frametail_slot, and advance as + * necessary. Note that if we reach end of partition, we will + * leave frametailpos = end+1 and frametail_slot empty. + */ + bool sub, + less; + + /* Precompute flags for in_range checks */ + if (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING) + sub = true; /* subtract endOffset from current row */ + else + sub = false; /* add it */ + less = true; /* normally, we want frame tail <= sum */ + /* If sort order is descending, flip both flags */ + if (!winstate->inRangeAsc) + { + sub = !sub; + less = false; + } + + tuplestore_select_read_pointer(winstate->buffer, + winstate->frametail_ptr); + if (winstate->frametailpos == 0 && + TupIsNull(winstate->frametail_slot)) + { + /* fetch first row into frametail_slot, if we didn't already */ + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->frametail_slot)) + elog(ERROR, "unexpected end of tuplestore"); + } + + while (!TupIsNull(winstate->frametail_slot)) + { + Datum tailval, + currval; + bool tailisnull, + currisnull; + + tailval = slot_getattr(winstate->frametail_slot, 1, + &tailisnull); + currval = slot_getattr(winstate->ss.ss_ScanTupleSlot, 1, + &currisnull); + if (tailisnull || currisnull) + { + /* order of the rows depends only on nulls_first */ + if (winstate->inRangeNullsFirst) + { + /* advance tail if tail is null or curr is not */ + if (!tailisnull) + break; + } + else + { + /* advance tail if tail is not null or curr is null */ + if (!currisnull) + break; + } + } + else + { + if (!DatumGetBool(FunctionCall5Coll(&winstate->endInRangeFunc, + winstate->inRangeColl, + tailval, + currval, + winstate->endOffsetValue, + BoolGetDatum(sub), + BoolGetDatum(less)))) + break; /* this row is the correct frame tail */ + } + /* Note we advance frametailpos even if the fetch fails */ + winstate->frametailpos++; + spool_tuples(winstate, winstate->frametailpos); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->frametail_slot)) + break; /* end of partition */ + } + winstate->frametail_valid = true; + } + else if (frameOptions & FRAMEOPTION_GROUPS) + { + /* + * In GROUPS END_OFFSET mode, frame end is the last row of the + * last peer group whose number satisfies the offset constraint, + * and frame tail is the row after that (if any). We keep a copy + * of the last-known frame tail row in frametail_slot, and advance + * as necessary. Note that if we reach end of partition, we will + * leave frametailpos = end+1 and frametail_slot empty. + */ + int64 offset = DatumGetInt64(winstate->endOffsetValue); + int64 maxtailgroup; + + if (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING) + maxtailgroup = winstate->currentgroup - offset; + else + maxtailgroup = winstate->currentgroup + offset; + + tuplestore_select_read_pointer(winstate->buffer, + winstate->frametail_ptr); + if (winstate->frametailpos == 0 && + TupIsNull(winstate->frametail_slot)) + { + /* fetch first row into frametail_slot, if we didn't already */ + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->frametail_slot)) + elog(ERROR, "unexpected end of tuplestore"); + } + + while (!TupIsNull(winstate->frametail_slot)) + { + if (winstate->frametailgroup > maxtailgroup) + break; /* this row is the correct frame tail */ + ExecCopySlot(winstate->temp_slot_2, winstate->frametail_slot); + /* Note we advance frametailpos even if the fetch fails */ + winstate->frametailpos++; + spool_tuples(winstate, winstate->frametailpos); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->frametail_slot)) + break; /* end of partition */ + if (!are_peers(winstate, winstate->temp_slot_2, + winstate->frametail_slot)) + winstate->frametailgroup++; + } + ExecClearTuple(winstate->temp_slot_2); + winstate->frametail_valid = true; } else Assert(false); } else Assert(false); + + MemoryContextSwitchTo(oldcontext); +} + +/* + * update_grouptailpos + * make grouptailpos valid for the current row + * + * May clobber winstate->temp_slot_2. + */ +static void +update_grouptailpos(WindowAggState *winstate) +{ + WindowAgg *node = (WindowAgg *) winstate->ss.ps.plan; + MemoryContext oldcontext; + + if (winstate->grouptail_valid) + return; /* already known for current row */ + + /* We may be called in a short-lived context */ + oldcontext = MemoryContextSwitchTo(winstate->ss.ps.ps_ExprContext->ecxt_per_query_memory); + + /* If no ORDER BY, all rows are peers with each other */ + if (node->ordNumCols == 0) + { + spool_tuples(winstate, -1); + winstate->grouptailpos = winstate->spooled_rows; + winstate->grouptail_valid = true; + MemoryContextSwitchTo(oldcontext); + return; + } + + /* + * Because grouptail_valid is reset only when current row advances into a + * new peer group, we always reach here knowing that grouptailpos needs to + * be advanced by at least one row. Hence, unlike the otherwise similar + * case for frame tail tracking, we do not need persistent storage of the + * group tail row. + */ + Assert(winstate->grouptailpos <= winstate->currentpos); + tuplestore_select_read_pointer(winstate->buffer, + winstate->grouptail_ptr); + for (;;) + { + /* Note we advance grouptailpos even if the fetch fails */ + winstate->grouptailpos++; + spool_tuples(winstate, winstate->grouptailpos); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->temp_slot_2)) + break; /* end of partition */ + if (winstate->grouptailpos > winstate->currentpos && + !are_peers(winstate, winstate->temp_slot_2, + winstate->ss.ss_ScanTupleSlot)) + break; /* this row is the group tail */ + } + ExecClearTuple(winstate->temp_slot_2); + winstate->grouptail_valid = true; + + MemoryContextSwitchTo(oldcontext); } @@ -1602,7 +2019,9 @@ ExecWindowAgg(PlanState *pstate) return NULL; /* - * Compute frame offset values, if any, during first call. + * Compute frame offset values, if any, during first call (or after a + * rescan). These are assumed to hold constant throughout the scan; if + * user gives us a volatile expression, we'll only use its initial value. */ if (winstate->all_first) { @@ -1613,7 +2032,7 @@ ExecWindowAgg(PlanState *pstate) int16 len; bool byval; - if (frameOptions & FRAMEOPTION_START_VALUE) + if (frameOptions & FRAMEOPTION_START_OFFSET) { Assert(winstate->startOffset != NULL); value = ExecEvalExprSwitchContext(winstate->startOffset, @@ -1627,18 +2046,18 @@ ExecWindowAgg(PlanState *pstate) get_typlenbyval(exprType((Node *) winstate->startOffset->expr), &len, &byval); winstate->startOffsetValue = datumCopy(value, byval, len); - if (frameOptions & FRAMEOPTION_ROWS) + if (frameOptions & (FRAMEOPTION_ROWS | FRAMEOPTION_GROUPS)) { /* value is known to be int8 */ int64 offset = DatumGetInt64(value); if (offset < 0) ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), errmsg("frame starting offset must not be negative"))); } } - if (frameOptions & FRAMEOPTION_END_VALUE) + if (frameOptions & FRAMEOPTION_END_OFFSET) { Assert(winstate->endOffset != NULL); value = ExecEvalExprSwitchContext(winstate->endOffset, @@ -1652,14 +2071,14 @@ ExecWindowAgg(PlanState *pstate) get_typlenbyval(exprType((Node *) winstate->endOffset->expr), &len, &byval); winstate->endOffsetValue = datumCopy(value, byval, len); - if (frameOptions & FRAMEOPTION_ROWS) + if (frameOptions & (FRAMEOPTION_ROWS | FRAMEOPTION_GROUPS)) { /* value is known to be int8 */ int64 offset = DatumGetInt64(value); if (offset < 0) ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), errmsg("frame ending offset must not be negative"))); } } @@ -1679,6 +2098,7 @@ ExecWindowAgg(PlanState *pstate) /* This might mean that the frame moves, too */ winstate->framehead_valid = false; winstate->frametail_valid = false; + /* we don't need to invalidate grouptail here; see below */ } /* @@ -1718,12 +2138,38 @@ ExecWindowAgg(PlanState *pstate) * out of the tuplestore, since window function evaluation might cause the * tuplestore to dump its state to disk.) * + * In GROUPS mode, or when tracking a group-oriented exclusion clause, we + * must also detect entering a new peer group and update associated state + * when that happens. We use temp_slot_2 to temporarily hold the previous + * row for this purpose. + * * Current row must be in the tuplestore, since we spooled it above. */ tuplestore_select_read_pointer(winstate->buffer, winstate->current_ptr); - if (!tuplestore_gettupleslot(winstate->buffer, true, true, - winstate->ss.ss_ScanTupleSlot)) - elog(ERROR, "unexpected end of tuplestore"); + if ((winstate->frameOptions & (FRAMEOPTION_GROUPS | + FRAMEOPTION_EXCLUDE_GROUP | + FRAMEOPTION_EXCLUDE_TIES)) && + winstate->currentpos > 0) + { + ExecCopySlot(winstate->temp_slot_2, winstate->ss.ss_ScanTupleSlot); + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->ss.ss_ScanTupleSlot)) + elog(ERROR, "unexpected end of tuplestore"); + if (!are_peers(winstate, winstate->temp_slot_2, + winstate->ss.ss_ScanTupleSlot)) + { + winstate->currentgroup++; + winstate->groupheadpos = winstate->currentpos; + winstate->grouptail_valid = false; + } + ExecClearTuple(winstate->temp_slot_2); + } + else + { + if (!tuplestore_gettupleslot(winstate->buffer, true, true, + winstate->ss.ss_ScanTupleSlot)) + elog(ERROR, "unexpected end of tuplestore"); + } /* * Evaluate true window functions @@ -1747,6 +2193,23 @@ ExecWindowAgg(PlanState *pstate) eval_windowaggregates(winstate); /* + * If we have created auxiliary read pointers for the frame or group + * boundaries, force them to be kept up-to-date, because we don't know + * whether the window function(s) will do anything that requires that. + * Failing to advance the pointers would result in being unable to trim + * data from the tuplestore, which is bad. (If we could know in advance + * whether the window functions will use frame boundary info, we could + * skip creating these pointers in the first place ... but unfortunately + * the window function API doesn't require that.) + */ + if (winstate->framehead_ptr >= 0) + update_frameheadpos(winstate); + if (winstate->frametail_ptr >= 0) + update_frametailpos(winstate); + if (winstate->grouptail_ptr >= 0) + update_grouptailpos(winstate); + + /* * Truncate any no-longer-needed rows from the tuplestore. */ tuplestore_trim(winstate->buffer); @@ -1777,6 +2240,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) ExprContext *tmpcontext; WindowStatePerFunc perfunc; WindowStatePerAgg peragg; + int frameOptions = node->frameOptions; int numfuncs, wfuncno, numaggs, @@ -1832,6 +2296,20 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) winstate->temp_slot_2 = ExecInitExtraTupleSlot(estate); /* + * create frame head and tail slots only if needed (must match logic in + * update_frameheadpos and update_frametailpos) + */ + winstate->framehead_slot = winstate->frametail_slot = NULL; + + if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) + { + if (!(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) + winstate->framehead_slot = ExecInitExtraTupleSlot(estate); + if (!(frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING)) + winstate->frametail_slot = ExecInitExtraTupleSlot(estate); + } + + /* * WindowAgg nodes never have quals, since they can only occur at the * logical top level of a query (ie, after any WHERE or HAVING filters) */ @@ -1858,6 +2336,12 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); ExecSetSlotDescriptor(winstate->temp_slot_2, winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); + if (winstate->framehead_slot) + ExecSetSlotDescriptor(winstate->framehead_slot, + winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); + if (winstate->frametail_slot) + ExecSetSlotDescriptor(winstate->frametail_slot, + winstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); /* * Initialize result tuple type and projection info. @@ -1991,7 +2475,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) } /* copy frame options to state node for easy access */ - winstate->frameOptions = node->frameOptions; + winstate->frameOptions = frameOptions; /* initialize frame bound offset expressions */ winstate->startOffset = ExecInitExpr((Expr *) node->startOffset, @@ -1999,6 +2483,15 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) winstate->endOffset = ExecInitExpr((Expr *) node->endOffset, (PlanState *) winstate); + /* Lookup in_range support functions if needed */ + if (OidIsValid(node->startInRangeFunc)) + fmgr_info(node->startInRangeFunc, &winstate->startInRangeFunc); + if (OidIsValid(node->endInRangeFunc)) + fmgr_info(node->endInRangeFunc, &winstate->endInRangeFunc); + winstate->inRangeColl = node->inRangeColl; + winstate->inRangeAsc = node->inRangeAsc; + winstate->inRangeNullsFirst = node->inRangeNullsFirst; + winstate->all_first = true; winstate->partition_spooled = false; winstate->more_partitions = false; @@ -2023,6 +2516,10 @@ ExecEndWindowAgg(WindowAggState *node) ExecClearTuple(node->agg_row_slot); ExecClearTuple(node->temp_slot_1); ExecClearTuple(node->temp_slot_2); + if (node->framehead_slot) + ExecClearTuple(node->framehead_slot); + if (node->frametail_slot) + ExecClearTuple(node->frametail_slot); /* * Free both the expr contexts. @@ -2068,6 +2565,10 @@ ExecReScanWindowAgg(WindowAggState *node) ExecClearTuple(node->agg_row_slot); ExecClearTuple(node->temp_slot_1); ExecClearTuple(node->temp_slot_2); + if (node->framehead_slot) + ExecClearTuple(node->framehead_slot); + if (node->frametail_slot) + ExecClearTuple(node->frametail_slot); /* Forget current wfunc values */ MemSet(econtext->ecxt_aggvalues, 0, sizeof(Datum) * node->numfuncs); @@ -2574,7 +3075,7 @@ WinSetMarkPosition(WindowObject winobj, int64 markpos) /* * WinRowsArePeers - * Compare two rows (specified by absolute position in window) to see + * Compare two rows (specified by absolute position in partition) to see * if they are equal according to the ORDER BY clause. * * NB: this does not consider the window frame mode. @@ -2596,6 +3097,10 @@ WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2) if (node->ordNumCols == 0) return true; + /* + * Note: OK to use temp_slot_2 here because we aren't calling any + * frame-related functions (those tend to clobber temp_slot_2). + */ slot1 = winstate->temp_slot_1; slot2 = winstate->temp_slot_2; @@ -2680,30 +3185,7 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno, if (isout) *isout = false; if (set_mark) - { - int frameOptions = winstate->frameOptions; - int64 mark_pos = abs_pos; - - /* - * In RANGE mode with a moving frame head, we must not let the - * mark advance past frameheadpos, since that row has to be - * fetchable during future update_frameheadpos calls. - * - * XXX it is very ugly to pollute window functions' marks with - * this consideration; it could for instance mask a logic bug that - * lets a window function fetch rows before what it had claimed - * was its mark. Perhaps use a separate mark for frame head - * probes? - */ - if ((frameOptions & FRAMEOPTION_RANGE) && - !(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) - { - update_frameheadpos(winobj, winstate->temp_slot_2); - if (mark_pos > winstate->frameheadpos) - mark_pos = winstate->frameheadpos; - } - WinSetMarkPosition(winobj, mark_pos); - } + WinSetMarkPosition(winobj, abs_pos); econtext->ecxt_outertuple = slot; return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), econtext, isnull); @@ -2714,19 +3196,34 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno, * WinGetFuncArgInFrame * Evaluate a window function's argument expression on a specified * row of the window frame. The row is identified in lseek(2) style, - * i.e. relative to the current, first, or last row. + * i.e. relative to the first or last row of the frame. (We do not + * support WINDOW_SEEK_CURRENT here, because it's not very clear what + * that should mean if the current row isn't part of the frame.) * * argno: argument number to evaluate (counted from 0) * relpos: signed rowcount offset from the seek position - * seektype: WINDOW_SEEK_CURRENT, WINDOW_SEEK_HEAD, or WINDOW_SEEK_TAIL - * set_mark: If the row is found and set_mark is true, the mark is moved to - * the row as a side-effect. + * seektype: WINDOW_SEEK_HEAD or WINDOW_SEEK_TAIL + * set_mark: If the row is found/in frame and set_mark is true, the mark is + * moved to the row as a side-effect. * isnull: output argument, receives isnull status of result * isout: output argument, set to indicate whether target row position * is out of frame (can pass NULL if caller doesn't care about this) * - * Specifying a nonexistent row is not an error, it just causes a null result - * (plus setting *isout true, if isout isn't NULL). + * Specifying a nonexistent or not-in-frame row is not an error, it just + * causes a null result (plus setting *isout true, if isout isn't NULL). + * + * Note that some exclusion-clause options lead to situations where the + * rows that are in-frame are not consecutive in the partition. But we + * count only in-frame rows when measuring relpos. + * + * The set_mark flag is interpreted as meaning that the caller will specify + * a constant (or, perhaps, monotonically increasing) relpos in successive + * calls, so that *if there is no exclusion clause* there will be no need + * to fetch a row before the previously fetched row. But we do not expect + * the caller to know how to account for exclusion clauses. Therefore, + * if there is an exclusion clause we take responsibility for adjusting the + * mark request to something that will be safe given the above assumption + * about relpos. */ Datum WinGetFuncArgInFrame(WindowObject winobj, int argno, @@ -2736,8 +3233,8 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno, WindowAggState *winstate; ExprContext *econtext; TupleTableSlot *slot; - bool gottuple; int64 abs_pos; + int64 mark_pos; Assert(WindowObjectIsValid(winobj)); winstate = winobj->winstate; @@ -2747,66 +3244,167 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno, switch (seektype) { case WINDOW_SEEK_CURRENT: - abs_pos = winstate->currentpos + relpos; + elog(ERROR, "WINDOW_SEEK_CURRENT is not supported for WinGetFuncArgInFrame"); + abs_pos = mark_pos = 0; /* keep compiler quiet */ break; case WINDOW_SEEK_HEAD: - update_frameheadpos(winobj, slot); + /* rejecting relpos < 0 is easy and simplifies code below */ + if (relpos < 0) + goto out_of_frame; + update_frameheadpos(winstate); abs_pos = winstate->frameheadpos + relpos; + mark_pos = abs_pos; + + /* + * Account for exclusion option if one is active, but advance only + * abs_pos not mark_pos. This prevents changes of the current + * row's peer group from resulting in trying to fetch a row before + * some previous mark position. + * + * Note that in some corner cases such as current row being + * outside frame, these calculations are theoretically too simple, + * but it doesn't matter because we'll end up deciding the row is + * out of frame. We do not attempt to avoid fetching rows past + * end of frame; that would happen in some cases anyway. + */ + switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION) + { + case 0: + /* no adjustment needed */ + break; + case FRAMEOPTION_EXCLUDE_CURRENT_ROW: + if (abs_pos >= winstate->currentpos && + winstate->currentpos >= winstate->frameheadpos) + abs_pos++; + break; + case FRAMEOPTION_EXCLUDE_GROUP: + update_grouptailpos(winstate); + if (abs_pos >= winstate->groupheadpos && + winstate->grouptailpos > winstate->frameheadpos) + { + int64 overlapstart = Max(winstate->groupheadpos, + winstate->frameheadpos); + + abs_pos += winstate->grouptailpos - overlapstart; + } + break; + case FRAMEOPTION_EXCLUDE_TIES: + update_grouptailpos(winstate); + if (abs_pos >= winstate->groupheadpos && + winstate->grouptailpos > winstate->frameheadpos) + { + int64 overlapstart = Max(winstate->groupheadpos, + winstate->frameheadpos); + + if (abs_pos == overlapstart) + abs_pos = winstate->currentpos; + else + abs_pos += winstate->grouptailpos - overlapstart - 1; + } + break; + default: + elog(ERROR, "unrecognized frame option state: 0x%x", + winstate->frameOptions); + break; + } break; case WINDOW_SEEK_TAIL: - update_frametailpos(winobj, slot); - abs_pos = winstate->frametailpos + relpos; + /* rejecting relpos > 0 is easy and simplifies code below */ + if (relpos > 0) + goto out_of_frame; + update_frametailpos(winstate); + abs_pos = winstate->frametailpos - 1 + relpos; + + /* + * Account for exclusion option if one is active. If there is no + * exclusion, we can safely set the mark at the accessed row. But + * if there is, we can only mark the frame start, because we can't + * be sure how far back in the frame the exclusion might cause us + * to fetch in future. Furthermore, we have to actually check + * against frameheadpos here, since it's unsafe to try to fetch a + * row before frame start if the mark might be there already. + */ + switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION) + { + case 0: + /* no adjustment needed */ + mark_pos = abs_pos; + break; + case FRAMEOPTION_EXCLUDE_CURRENT_ROW: + if (abs_pos <= winstate->currentpos && + winstate->currentpos < winstate->frametailpos) + abs_pos--; + update_frameheadpos(winstate); + if (abs_pos < winstate->frameheadpos) + goto out_of_frame; + mark_pos = winstate->frameheadpos; + break; + case FRAMEOPTION_EXCLUDE_GROUP: + update_grouptailpos(winstate); + if (abs_pos < winstate->grouptailpos && + winstate->groupheadpos < winstate->frametailpos) + { + int64 overlapend = Min(winstate->grouptailpos, + winstate->frametailpos); + + abs_pos -= overlapend - winstate->groupheadpos; + } + update_frameheadpos(winstate); + if (abs_pos < winstate->frameheadpos) + goto out_of_frame; + mark_pos = winstate->frameheadpos; + break; + case FRAMEOPTION_EXCLUDE_TIES: + update_grouptailpos(winstate); + if (abs_pos < winstate->grouptailpos && + winstate->groupheadpos < winstate->frametailpos) + { + int64 overlapend = Min(winstate->grouptailpos, + winstate->frametailpos); + + if (abs_pos == overlapend - 1) + abs_pos = winstate->currentpos; + else + abs_pos -= overlapend - 1 - winstate->groupheadpos; + } + update_frameheadpos(winstate); + if (abs_pos < winstate->frameheadpos) + goto out_of_frame; + mark_pos = winstate->frameheadpos; + break; + default: + elog(ERROR, "unrecognized frame option state: 0x%x", + winstate->frameOptions); + mark_pos = 0; /* keep compiler quiet */ + break; + } break; default: elog(ERROR, "unrecognized window seek type: %d", seektype); - abs_pos = 0; /* keep compiler quiet */ + abs_pos = mark_pos = 0; /* keep compiler quiet */ break; } - gottuple = window_gettupleslot(winobj, abs_pos, slot); - if (gottuple) - gottuple = row_is_in_frame(winstate, abs_pos, slot); + if (!window_gettupleslot(winobj, abs_pos, slot)) + goto out_of_frame; - if (!gottuple) - { - if (isout) - *isout = true; - *isnull = true; - return (Datum) 0; - } - else - { - if (isout) - *isout = false; - if (set_mark) - { - int frameOptions = winstate->frameOptions; - int64 mark_pos = abs_pos; + /* The code above does not detect all out-of-frame cases, so check */ + if (row_is_in_frame(winstate, abs_pos, slot) <= 0) + goto out_of_frame; - /* - * In RANGE mode with a moving frame head, we must not let the - * mark advance past frameheadpos, since that row has to be - * fetchable during future update_frameheadpos calls. - * - * XXX it is very ugly to pollute window functions' marks with - * this consideration; it could for instance mask a logic bug that - * lets a window function fetch rows before what it had claimed - * was its mark. Perhaps use a separate mark for frame head - * probes? - */ - if ((frameOptions & FRAMEOPTION_RANGE) && - !(frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING)) - { - update_frameheadpos(winobj, winstate->temp_slot_2); - if (mark_pos > winstate->frameheadpos) - mark_pos = winstate->frameheadpos; - } - WinSetMarkPosition(winobj, mark_pos); - } - econtext->ecxt_outertuple = slot; - return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), - econtext, isnull); - } + if (isout) + *isout = false; + if (set_mark) + WinSetMarkPosition(winobj, mark_pos); + econtext->ecxt_outertuple = slot; + return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno), + econtext, isnull); + +out_of_frame: + if (isout) + *isout = true; + *isnull = true; + return (Datum) 0; } /* diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index bafe0d10715..82255b0d1da 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -1012,6 +1012,11 @@ _copyWindowAgg(const WindowAgg *from) COPY_SCALAR_FIELD(frameOptions); COPY_NODE_FIELD(startOffset); COPY_NODE_FIELD(endOffset); + COPY_SCALAR_FIELD(startInRangeFunc); + COPY_SCALAR_FIELD(endInRangeFunc); + COPY_SCALAR_FIELD(inRangeColl); + COPY_SCALAR_FIELD(inRangeAsc); + COPY_SCALAR_FIELD(inRangeNullsFirst); return newnode; } @@ -2412,6 +2417,11 @@ _copyWindowClause(const WindowClause *from) COPY_SCALAR_FIELD(frameOptions); COPY_NODE_FIELD(startOffset); COPY_NODE_FIELD(endOffset); + COPY_SCALAR_FIELD(startInRangeFunc); + COPY_SCALAR_FIELD(endInRangeFunc); + COPY_SCALAR_FIELD(inRangeColl); + COPY_SCALAR_FIELD(inRangeAsc); + COPY_SCALAR_FIELD(inRangeNullsFirst); COPY_SCALAR_FIELD(winref); COPY_SCALAR_FIELD(copiedOrder); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 02ca7d588ce..b9bc8e38d74 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2735,6 +2735,11 @@ _equalWindowClause(const WindowClause *a, const WindowClause *b) COMPARE_SCALAR_FIELD(frameOptions); COMPARE_NODE_FIELD(startOffset); COMPARE_NODE_FIELD(endOffset); + COMPARE_SCALAR_FIELD(startInRangeFunc); + COMPARE_SCALAR_FIELD(endInRangeFunc); + COMPARE_SCALAR_FIELD(inRangeColl); + COMPARE_SCALAR_FIELD(inRangeAsc); + COMPARE_SCALAR_FIELD(inRangeNullsFirst); COMPARE_SCALAR_FIELD(winref); COMPARE_SCALAR_FIELD(copiedOrder); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index e6ba0962571..011d2a3fa93 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -840,6 +840,11 @@ _outWindowAgg(StringInfo str, const WindowAgg *node) WRITE_INT_FIELD(frameOptions); WRITE_NODE_FIELD(startOffset); WRITE_NODE_FIELD(endOffset); + WRITE_OID_FIELD(startInRangeFunc); + WRITE_OID_FIELD(endInRangeFunc); + WRITE_OID_FIELD(inRangeColl); + WRITE_BOOL_FIELD(inRangeAsc); + WRITE_BOOL_FIELD(inRangeNullsFirst); } static void @@ -2985,6 +2990,11 @@ _outWindowClause(StringInfo str, const WindowClause *node) WRITE_INT_FIELD(frameOptions); WRITE_NODE_FIELD(startOffset); WRITE_NODE_FIELD(endOffset); + WRITE_OID_FIELD(startInRangeFunc); + WRITE_OID_FIELD(endInRangeFunc); + WRITE_OID_FIELD(inRangeColl); + WRITE_BOOL_FIELD(inRangeAsc); + WRITE_BOOL_FIELD(inRangeNullsFirst); WRITE_UINT_FIELD(winref); WRITE_BOOL_FIELD(copiedOrder); } diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 22d8b9d0d53..068db353d70 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -369,6 +369,11 @@ _readWindowClause(void) READ_INT_FIELD(frameOptions); READ_NODE_FIELD(startOffset); READ_NODE_FIELD(endOffset); + READ_OID_FIELD(startInRangeFunc); + READ_OID_FIELD(endInRangeFunc); + READ_OID_FIELD(inRangeColl); + READ_BOOL_FIELD(inRangeAsc); + READ_BOOL_FIELD(inRangeNullsFirst); READ_UINT_FIELD(winref); READ_BOOL_FIELD(copiedOrder); @@ -2139,6 +2144,11 @@ _readWindowAgg(void) READ_INT_FIELD(frameOptions); READ_NODE_FIELD(startOffset); READ_NODE_FIELD(endOffset); + READ_OID_FIELD(startInRangeFunc); + READ_OID_FIELD(endInRangeFunc); + READ_OID_FIELD(inRangeColl); + READ_BOOL_FIELD(inRangeAsc); + READ_BOOL_FIELD(inRangeNullsFirst); READ_DONE(); } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index c46e1318a68..da0cc7f266c 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -261,6 +261,8 @@ static WindowAgg *make_windowagg(List *tlist, Index winref, int partNumCols, AttrNumber *partColIdx, Oid *partOperators, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, int frameOptions, Node *startOffset, Node *endOffset, + Oid startInRangeFunc, Oid endInRangeFunc, + Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst, Plan *lefttree); static Group *make_group(List *tlist, List *qual, int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators, @@ -2123,6 +2125,11 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) wc->frameOptions, wc->startOffset, wc->endOffset, + wc->startInRangeFunc, + wc->endInRangeFunc, + wc->inRangeColl, + wc->inRangeAsc, + wc->inRangeNullsFirst, subplan); copy_generic_path_info(&plan->plan, (Path *) best_path); @@ -6080,6 +6087,8 @@ make_windowagg(List *tlist, Index winref, int partNumCols, AttrNumber *partColIdx, Oid *partOperators, int ordNumCols, AttrNumber *ordColIdx, Oid *ordOperators, int frameOptions, Node *startOffset, Node *endOffset, + Oid startInRangeFunc, Oid endInRangeFunc, + Oid inRangeColl, bool inRangeAsc, bool inRangeNullsFirst, Plan *lefttree) { WindowAgg *node = makeNode(WindowAgg); @@ -6095,6 +6104,11 @@ make_windowagg(List *tlist, Index winref, node->frameOptions = frameOptions; node->startOffset = startOffset; node->endOffset = endOffset; + node->startInRangeFunc = startInRangeFunc; + node->endInRangeFunc = endInRangeFunc; + node->inRangeColl = inRangeColl; + node->inRangeAsc = inRangeAsc; + node->inRangeNullsFirst = inRangeNullsFirst; plan->targetlist = tlist; plan->lefttree = lefttree; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 5329432f25c..d99f2be2c97 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -570,6 +570,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <list> window_clause window_definition_list opt_partition_clause %type <windef> window_definition over_clause window_specification opt_frame_clause frame_extent frame_bound +%type <ival> opt_window_exclusion_clause %type <str> opt_existing_window_name %type <boolean> opt_if_not_exists %type <ival> generated_when override_kind @@ -632,7 +633,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS - GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING + GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING GROUPS HANDLER HAVING HEADER_P HOLD HOUR_P @@ -656,7 +657,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR - ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER + ORDER ORDINALITY OTHERS OUT_P OUTER_P + OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY @@ -676,7 +678,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); SUBSCRIPTION SUBSTRING SYMMETRIC SYSID SYSTEM_P TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN - TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM TREAT TRIGGER TRIM TRUE_P + TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM + TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P TYPES_P UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED @@ -724,9 +727,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); * between POSTFIXOP and Op. We can safely assign the same priority to * various unreserved keywords as needed to resolve ambiguities (this can't * have any bad effects since obviously the keywords will still behave the - * same as if they weren't keywords). We need to do this for PARTITION, - * RANGE, ROWS to support opt_existing_window_name; and for RANGE, ROWS - * so that they can follow a_expr without creating postfix-operator problems; + * same as if they weren't keywords). We need to do this: + * for PARTITION, RANGE, ROWS, GROUPS to support opt_existing_window_name; + * for RANGE, ROWS, GROUPS so that they can follow a_expr without creating + * postfix-operator problems; * for GENERATED so that it can follow b_expr; * and for NULL so that it can follow b_expr in ColQualList without creating * postfix-operator problems. @@ -746,7 +750,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); * blame any funny behavior of UNBOUNDED on the SQL standard, though. */ %nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */ -%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP +%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' @@ -14003,7 +14007,7 @@ window_specification: '(' opt_existing_window_name opt_partition_clause ; /* - * If we see PARTITION, RANGE, or ROWS as the first token after the '(' + * If we see PARTITION, RANGE, ROWS or GROUPS as the first token after the '(' * of a window_specification, we want the assumption to be that there is * no existing_window_name; but those keywords are unreserved and so could * be ColIds. We fix this by making them have the same precedence as IDENT @@ -14023,33 +14027,27 @@ opt_partition_clause: PARTITION BY expr_list { $$ = $3; } /* * For frame clauses, we return a WindowDef, but only some fields are used: * frameOptions, startOffset, and endOffset. - * - * This is only a subset of the full SQL:2008 frame_clause grammar. - * We don't support <window frame exclusion> yet. */ opt_frame_clause: - RANGE frame_extent + RANGE frame_extent opt_window_exclusion_clause { WindowDef *n = $2; n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE; - if (n->frameOptions & (FRAMEOPTION_START_VALUE_PRECEDING | - FRAMEOPTION_END_VALUE_PRECEDING)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("RANGE PRECEDING is only supported with UNBOUNDED"), - parser_errposition(@1))); - if (n->frameOptions & (FRAMEOPTION_START_VALUE_FOLLOWING | - FRAMEOPTION_END_VALUE_FOLLOWING)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("RANGE FOLLOWING is only supported with UNBOUNDED"), - parser_errposition(@1))); + n->frameOptions |= $3; $$ = n; } - | ROWS frame_extent + | ROWS frame_extent opt_window_exclusion_clause { WindowDef *n = $2; n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_ROWS; + n->frameOptions |= $3; + $$ = n; + } + | GROUPS frame_extent opt_window_exclusion_clause + { + WindowDef *n = $2; + n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_GROUPS; + n->frameOptions |= $3; $$ = n; } | /*EMPTY*/ @@ -14071,7 +14069,7 @@ frame_extent: frame_bound (errcode(ERRCODE_WINDOWING_ERROR), errmsg("frame start cannot be UNBOUNDED FOLLOWING"), parser_errposition(@1))); - if (n->frameOptions & FRAMEOPTION_START_VALUE_FOLLOWING) + if (n->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), errmsg("frame starting from following row cannot end with current row"), @@ -14100,13 +14098,13 @@ frame_extent: frame_bound errmsg("frame end cannot be UNBOUNDED PRECEDING"), parser_errposition(@4))); if ((frameOptions & FRAMEOPTION_START_CURRENT_ROW) && - (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING)) + (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING)) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), errmsg("frame starting from current row cannot have preceding rows"), parser_errposition(@4))); - if ((frameOptions & FRAMEOPTION_START_VALUE_FOLLOWING) && - (frameOptions & (FRAMEOPTION_END_VALUE_PRECEDING | + if ((frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING) && + (frameOptions & (FRAMEOPTION_END_OFFSET_PRECEDING | FRAMEOPTION_END_CURRENT_ROW))) ereport(ERROR, (errcode(ERRCODE_WINDOWING_ERROR), @@ -14151,7 +14149,7 @@ frame_bound: | a_expr PRECEDING { WindowDef *n = makeNode(WindowDef); - n->frameOptions = FRAMEOPTION_START_VALUE_PRECEDING; + n->frameOptions = FRAMEOPTION_START_OFFSET_PRECEDING; n->startOffset = $1; n->endOffset = NULL; $$ = n; @@ -14159,13 +14157,21 @@ frame_bound: | a_expr FOLLOWING { WindowDef *n = makeNode(WindowDef); - n->frameOptions = FRAMEOPTION_START_VALUE_FOLLOWING; + n->frameOptions = FRAMEOPTION_START_OFFSET_FOLLOWING; n->startOffset = $1; n->endOffset = NULL; $$ = n; } ; +opt_window_exclusion_clause: + EXCLUDE CURRENT_P ROW { $$ = FRAMEOPTION_EXCLUDE_CURRENT_ROW; } + | EXCLUDE GROUP_P { $$ = FRAMEOPTION_EXCLUDE_GROUP; } + | EXCLUDE TIES { $$ = FRAMEOPTION_EXCLUDE_TIES; } + | EXCLUDE NO OTHERS { $$ = 0; } + | /*EMPTY*/ { $$ = 0; } + ; + /* * Supporting nonterminals for expressions. @@ -15027,6 +15033,7 @@ unreserved_keyword: | GENERATED | GLOBAL | GRANTED + | GROUPS | HANDLER | HEADER_P | HOLD @@ -15092,6 +15099,7 @@ unreserved_keyword: | OPTION | OPTIONS | ORDINALITY + | OTHERS | OVER | OVERRIDING | OWNED @@ -15182,6 +15190,7 @@ unreserved_keyword: | TEMPLATE | TEMPORARY | TEXT_P + | TIES | TRANSACTION | TRANSFORM | TRIGGER diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 6a9f1b02178..747139489a0 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -420,6 +420,13 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) err = _("grouping operations are not allowed in window ROWS"); break; + case EXPR_KIND_WINDOW_FRAME_GROUPS: + if (isAgg) + err = _("aggregate functions are not allowed in window GROUPS"); + else + err = _("grouping operations are not allowed in window GROUPS"); + + break; case EXPR_KIND_SELECT_TARGET: /* okay */ break; @@ -835,6 +842,7 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_WINDOW_ORDER: case EXPR_KIND_WINDOW_FRAME_RANGE: case EXPR_KIND_WINDOW_FRAME_ROWS: + case EXPR_KIND_WINDOW_FRAME_GROUPS: err = _("window functions are not allowed in window definitions"); break; case EXPR_KIND_SELECT_TARGET: diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 406cd1dad03..9bafc24083b 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -18,10 +18,13 @@ #include "miscadmin.h" #include "access/heapam.h" +#include "access/htup_details.h" +#include "access/nbtree.h" #include "access/tsmapi.h" #include "catalog/catalog.h" #include "catalog/heap.h" #include "catalog/pg_am.h" +#include "catalog/pg_amproc.h" #include "catalog/pg_collation.h" #include "catalog/pg_constraint_fn.h" #include "catalog/pg_type.h" @@ -43,8 +46,11 @@ #include "parser/parse_target.h" #include "parser/parse_type.h" #include "rewrite/rewriteManip.h" +#include "utils/builtins.h" #include "utils/guc.h" +#include "utils/catcache.h" #include "utils/lsyscache.h" +#include "utils/syscache.h" #include "utils/rel.h" @@ -95,6 +101,7 @@ static List *addTargetToGroupList(ParseState *pstate, TargetEntry *tle, List *grouplist, List *targetlist, int location); static WindowClause *findWindowClause(List *wclist, const char *name); static Node *transformFrameOffset(ParseState *pstate, int frameOptions, + Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc, Node *clause); @@ -2627,6 +2634,8 @@ transformWindowDefinitions(ParseState *pstate, WindowClause *refwc = NULL; List *partitionClause; List *orderClause; + Oid rangeopfamily = InvalidOid; + Oid rangeopcintype = InvalidOid; WindowClause *wc; winref++; @@ -2753,10 +2762,47 @@ transformWindowDefinitions(ParseState *pstate, parser_errposition(pstate, windef->location))); } wc->frameOptions = windef->frameOptions; + + /* + * RANGE offset PRECEDING/FOLLOWING requires exactly one ORDER BY + * column; check that and get its sort opfamily info. + */ + if ((wc->frameOptions & FRAMEOPTION_RANGE) && + (wc->frameOptions & (FRAMEOPTION_START_OFFSET | + FRAMEOPTION_END_OFFSET))) + { + SortGroupClause *sortcl; + Node *sortkey; + int16 rangestrategy; + + if (list_length(wc->orderClause) != 1) + ereport(ERROR, + (errcode(ERRCODE_WINDOWING_ERROR), + errmsg("RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column"), + parser_errposition(pstate, windef->location))); + sortcl = castNode(SortGroupClause, linitial(wc->orderClause)); + sortkey = get_sortgroupclause_expr(sortcl, *targetlist); + /* Find the sort operator in pg_amop */ + if (!get_ordering_op_properties(sortcl->sortop, + &rangeopfamily, + &rangeopcintype, + &rangestrategy)) + elog(ERROR, "operator %u is not a valid ordering operator", + sortcl->sortop); + /* Record properties of sort ordering */ + wc->inRangeColl = exprCollation(sortkey); + wc->inRangeAsc = (rangestrategy == BTLessStrategyNumber); + wc->inRangeNullsFirst = sortcl->nulls_first; + } + /* Process frame offset expressions */ wc->startOffset = transformFrameOffset(pstate, wc->frameOptions, + rangeopfamily, rangeopcintype, + &wc->startInRangeFunc, windef->startOffset); wc->endOffset = transformFrameOffset(pstate, wc->frameOptions, + rangeopfamily, rangeopcintype, + &wc->endInRangeFunc, windef->endOffset); wc->winref = winref; @@ -3489,13 +3535,24 @@ findWindowClause(List *wclist, const char *name) /* * transformFrameOffset * Process a window frame offset expression + * + * In RANGE mode, rangeopfamily is the sort opfamily for the input ORDER BY + * column, and rangeopcintype is the input data type the sort operator is + * registered with. We expect the in_range function to be registered with + * that same type. (In binary-compatible cases, it might be different from + * the input column's actual type, so we can't use that for the lookups.) + * We'll return the OID of the in_range function to *inRangeFunc. */ static Node * -transformFrameOffset(ParseState *pstate, int frameOptions, Node *clause) +transformFrameOffset(ParseState *pstate, int frameOptions, + Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc, + Node *clause) { const char *constructName = NULL; Node *node; + *inRangeFunc = InvalidOid; /* default result */ + /* Quick exit if no offset expression */ if (clause == NULL) return NULL; @@ -3513,16 +3570,105 @@ transformFrameOffset(ParseState *pstate, int frameOptions, Node *clause) } else if (frameOptions & FRAMEOPTION_RANGE) { + /* + * We must look up the in_range support function that's to be used, + * possibly choosing one of several, and coerce the "offset" value to + * the appropriate input type. + */ + Oid nodeType; + Oid preferredType; + int nfuncs = 0; + int nmatches = 0; + Oid selectedType = InvalidOid; + Oid selectedFunc = InvalidOid; + CatCList *proclist; + int i; + /* Transform the raw expression tree */ node = transformExpr(pstate, clause, EXPR_KIND_WINDOW_FRAME_RANGE); + nodeType = exprType(node); + + /* + * If there are multiple candidates, we'll prefer the one that exactly + * matches nodeType; or if nodeType is as yet unknown, prefer the one + * that exactly matches the sort column type. (The second rule is + * like what we do for "known_type operator unknown".) + */ + preferredType = (nodeType != UNKNOWNOID) ? nodeType : rangeopcintype; + + /* Find the in_range support functions applicable to this case */ + proclist = SearchSysCacheList2(AMPROCNUM, + ObjectIdGetDatum(rangeopfamily), + ObjectIdGetDatum(rangeopcintype)); + for (i = 0; i < proclist->n_members; i++) + { + HeapTuple proctup = &proclist->members[i]->tuple; + Form_pg_amproc procform = (Form_pg_amproc) GETSTRUCT(proctup); + + /* The search will find all support proc types; ignore others */ + if (procform->amprocnum != BTINRANGE_PROC) + continue; + nfuncs++; + + /* Ignore function if given value can't be coerced to that type */ + if (!can_coerce_type(1, &nodeType, &procform->amprocrighttype, + COERCION_IMPLICIT)) + continue; + nmatches++; + + /* Remember preferred match, or any match if didn't find that */ + if (selectedType != preferredType) + { + selectedType = procform->amprocrighttype; + selectedFunc = procform->amproc; + } + } + ReleaseCatCacheList(proclist); /* - * this needs a lot of thought to decide how to support in the context - * of Postgres' extensible datatype framework + * Throw error if needed. It seems worth taking the trouble to + * distinguish "no support at all" from "you didn't match any + * available offset type". */ + if (nfuncs == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("RANGE with offset PRECEDING/FOLLOWING is not supported for column type %s", + format_type_be(rangeopcintype)), + parser_errposition(pstate, exprLocation(node)))); + if (nmatches == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("RANGE with offset PRECEDING/FOLLOWING is not supported for column type %s and offset type %s", + format_type_be(rangeopcintype), + format_type_be(nodeType)), + errhint("Cast the offset value to an appropriate type."), + parser_errposition(pstate, exprLocation(node)))); + if (nmatches != 1 && selectedType != preferredType) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("RANGE with offset PRECEDING/FOLLOWING has multiple interpretations for column type %s and offset type %s", + format_type_be(rangeopcintype), + format_type_be(nodeType)), + errhint("Cast the offset value to the exact intended type."), + parser_errposition(pstate, exprLocation(node)))); + + /* OK, coerce the offset to the right type */ constructName = "RANGE"; - /* error was already thrown by gram.y, this is just a backstop */ - elog(ERROR, "window frame with value offset is not implemented"); + node = coerce_to_specific_type(pstate, node, + selectedType, constructName); + *inRangeFunc = selectedFunc; + } + else if (frameOptions & FRAMEOPTION_GROUPS) + { + /* Transform the raw expression tree */ + node = transformExpr(pstate, clause, EXPR_KIND_WINDOW_FRAME_GROUPS); + + /* + * Like LIMIT clause, simply coerce to int8 + */ + constructName = "GROUPS"; + node = coerce_to_specific_type(pstate, node, INT8OID, constructName); } else { diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index b2f5e46e3bb..d45926f27fd 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1805,6 +1805,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_WINDOW_ORDER: case EXPR_KIND_WINDOW_FRAME_RANGE: case EXPR_KIND_WINDOW_FRAME_ROWS: + case EXPR_KIND_WINDOW_FRAME_GROUPS: case EXPR_KIND_SELECT_TARGET: case EXPR_KIND_INSERT_TARGET: case EXPR_KIND_UPDATE_SOURCE: @@ -3428,6 +3429,8 @@ ParseExprKindName(ParseExprKind exprKind) return "window RANGE"; case EXPR_KIND_WINDOW_FRAME_ROWS: return "window ROWS"; + case EXPR_KIND_WINDOW_FRAME_GROUPS: + return "window GROUPS"; case EXPR_KIND_SELECT_TARGET: return "SELECT"; case EXPR_KIND_INSERT_TARGET: diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index ffae0f3cf31..4a7bc77c0f7 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2227,6 +2227,7 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) break; case EXPR_KIND_WINDOW_FRAME_RANGE: case EXPR_KIND_WINDOW_FRAME_ROWS: + case EXPR_KIND_WINDOW_FRAME_GROUPS: err = _("set-returning functions are not allowed in window definitions"); break; case EXPR_KIND_SELECT_TARGET: diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index 747ef497897..eea29044146 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -1011,6 +1011,34 @@ timestamptz_cmp_date(PG_FUNCTION_ARGS) PG_RETURN_INT32(timestamptz_cmp_internal(dt1, dt2)); } +/* + * in_range support function for date. + * + * We implement this by promoting the dates to timestamp (without time zone) + * and then using the timestamp-and-interval in_range function. + */ +Datum +in_range_date_interval(PG_FUNCTION_ARGS) +{ + DateADT val = PG_GETARG_DATEADT(0); + DateADT base = PG_GETARG_DATEADT(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + Timestamp valStamp; + Timestamp baseStamp; + + valStamp = date2timestamp(val); + baseStamp = date2timestamp(base); + + return DirectFunctionCall5(in_range_timestamp_interval, + TimestampGetDatum(valStamp), + TimestampGetDatum(baseStamp), + IntervalPGetDatum(offset), + BoolGetDatum(sub), + BoolGetDatum(less)); +} + /* Add an interval to a date, giving a new date. * Must handle both positive and negative intervals. @@ -1842,6 +1870,45 @@ time_mi_interval(PG_FUNCTION_ARGS) PG_RETURN_TIMEADT(result); } +/* + * in_range support function for time. + */ +Datum +in_range_time_interval(PG_FUNCTION_ARGS) +{ + TimeADT val = PG_GETARG_TIMEADT(0); + TimeADT base = PG_GETARG_TIMEADT(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + TimeADT sum; + + /* + * Like time_pl_interval/time_mi_interval, we disregard the month and day + * fields of the offset. So our test for negative should too. + */ + if (offset->time < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* + * We can't use time_pl_interval/time_mi_interval here, because their + * wraparound behavior would give wrong (or at least undesirable) answers. + * Fortunately the equivalent non-wrapping behavior is trivial, especially + * since we don't worry about integer overflow. + */ + if (sub) + sum = base - offset->time; + else + sum = base + offset->time; + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); +} + /* time_part() * Extract specified field from time type. @@ -2305,6 +2372,46 @@ timetz_mi_interval(PG_FUNCTION_ARGS) PG_RETURN_TIMETZADT_P(result); } +/* + * in_range support function for timetz. + */ +Datum +in_range_timetz_interval(PG_FUNCTION_ARGS) +{ + TimeTzADT *val = PG_GETARG_TIMETZADT_P(0); + TimeTzADT *base = PG_GETARG_TIMETZADT_P(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + TimeTzADT sum; + + /* + * Like timetz_pl_interval/timetz_mi_interval, we disregard the month and + * day fields of the offset. So our test for negative should too. + */ + if (offset->time < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* + * We can't use timetz_pl_interval/timetz_mi_interval here, because their + * wraparound behavior would give wrong (or at least undesirable) answers. + * Fortunately the equivalent non-wrapping behavior is trivial, especially + * since we don't worry about integer overflow. + */ + if (sub) + sum.time = base->time - offset->time; + else + sum.time = base->time + offset->time; + sum.zone = base->zone; + + if (less) + PG_RETURN_BOOL(timetz_cmp_internal(val, &sum) <= 0); + else + PG_RETURN_BOOL(timetz_cmp_internal(val, &sum) >= 0); +} + /* overlaps_timetz() --- implements the SQL OVERLAPS operator. * * Algorithm is per SQL spec. This is much harder than you'd think diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c index 73529083655..559c365fecd 100644 --- a/src/backend/utils/adt/int.c +++ b/src/backend/utils/adt/int.c @@ -585,6 +585,158 @@ int42ge(PG_FUNCTION_ARGS) PG_RETURN_BOOL(arg1 >= arg2); } + +/*---------------------------------------------------------- + * in_range functions for int4 and int2, + * including cross-data-type comparisons. + * + * Note: we provide separate intN_int8 functions for performance + * reasons. This forces also providing intN_int2, else cases with a + * smallint offset value would fail to resolve which function to use. + * But that's an unlikely situation, so don't duplicate code for it. + *---------------------------------------------------------*/ + +Datum +in_range_int4_int4(PG_FUNCTION_ARGS) +{ + int32 val = PG_GETARG_INT32(0); + int32 base = PG_GETARG_INT32(1); + int32 offset = PG_GETARG_INT32(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + int32 sum; + + if (offset < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + if (sub) + offset = -offset; /* cannot overflow */ + + if (unlikely(pg_add_s32_overflow(base, offset, &sum))) + { + /* + * If sub is false, the true sum is surely more than val, so correct + * answer is the same as "less". If sub is true, the true sum is + * surely less than val, so the answer is "!less". + */ + PG_RETURN_BOOL(sub ? !less : less); + } + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); +} + +Datum +in_range_int4_int2(PG_FUNCTION_ARGS) +{ + /* Doesn't seem worth duplicating code for, so just invoke int4_int4 */ + return DirectFunctionCall5(in_range_int4_int4, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1), + Int32GetDatum((int32) PG_GETARG_INT16(2)), + PG_GETARG_DATUM(3), + PG_GETARG_DATUM(4)); +} + +Datum +in_range_int4_int8(PG_FUNCTION_ARGS) +{ + /* We must do all the math in int64 */ + int64 val = (int64) PG_GETARG_INT32(0); + int64 base = (int64) PG_GETARG_INT32(1); + int64 offset = PG_GETARG_INT64(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + int64 sum; + + if (offset < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + if (sub) + offset = -offset; /* cannot overflow */ + + if (unlikely(pg_add_s64_overflow(base, offset, &sum))) + { + /* + * If sub is false, the true sum is surely more than val, so correct + * answer is the same as "less". If sub is true, the true sum is + * surely less than val, so the answer is "!less". + */ + PG_RETURN_BOOL(sub ? !less : less); + } + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); +} + +Datum +in_range_int2_int4(PG_FUNCTION_ARGS) +{ + /* We must do all the math in int32 */ + int32 val = (int32) PG_GETARG_INT16(0); + int32 base = (int32) PG_GETARG_INT16(1); + int32 offset = PG_GETARG_INT32(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + int32 sum; + + if (offset < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + if (sub) + offset = -offset; /* cannot overflow */ + + if (unlikely(pg_add_s32_overflow(base, offset, &sum))) + { + /* + * If sub is false, the true sum is surely more than val, so correct + * answer is the same as "less". If sub is true, the true sum is + * surely less than val, so the answer is "!less". + */ + PG_RETURN_BOOL(sub ? !less : less); + } + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); +} + +Datum +in_range_int2_int2(PG_FUNCTION_ARGS) +{ + /* Doesn't seem worth duplicating code for, so just invoke int2_int4 */ + return DirectFunctionCall5(in_range_int2_int4, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1), + Int32GetDatum((int32) PG_GETARG_INT16(2)), + PG_GETARG_DATUM(3), + PG_GETARG_DATUM(4)); +} + +Datum +in_range_int2_int8(PG_FUNCTION_ARGS) +{ + /* Doesn't seem worth duplicating code for, so just invoke int4_int8 */ + return DirectFunctionCall5(in_range_int4_int8, + Int32GetDatum((int32) PG_GETARG_INT16(0)), + Int32GetDatum((int32) PG_GETARG_INT16(1)), + PG_GETARG_DATUM(2), + PG_GETARG_DATUM(3), + PG_GETARG_DATUM(4)); +} + + /* * int[24]pl - returns arg1 + arg2 * int[24]mi - returns arg1 - arg2 diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c index ae6a4683d4d..e6bae6860da 100644 --- a/src/backend/utils/adt/int8.c +++ b/src/backend/utils/adt/int8.c @@ -14,7 +14,7 @@ #include "postgres.h" #include <ctype.h> -#include <float.h> /* for _isnan */ +#include <float.h> /* for _isnan */ #include <limits.h> #include <math.h> @@ -469,6 +469,46 @@ int28ge(PG_FUNCTION_ARGS) PG_RETURN_BOOL(val1 >= val2); } +/* + * in_range support function for int8. + * + * Note: we needn't supply int8_int4 or int8_int2 variants, as implicit + * coercion of the offset value takes care of those scenarios just as well. + */ +Datum +in_range_int8_int8(PG_FUNCTION_ARGS) +{ + int64 val = PG_GETARG_INT64(0); + int64 base = PG_GETARG_INT64(1); + int64 offset = PG_GETARG_INT64(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + int64 sum; + + if (offset < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + if (sub) + offset = -offset; /* cannot overflow */ + + if (unlikely(pg_add_s64_overflow(base, offset, &sum))) + { + /* + * If sub is false, the true sum is surely more than val, so correct + * answer is the same as "less". If sub is true, the true sum is + * surely less than val, so the answer is "!less". + */ + PG_RETURN_BOOL(sub ? !less : less); + } + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); +} + /*---------------------------------------------------------- * Arithmetic operators on 64-bit integers. diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index c5f5a1ca3f9..28767a129af 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5877,6 +5877,8 @@ get_rule_windowspec(WindowClause *wc, List *targetList, appendStringInfoString(buf, "RANGE "); else if (wc->frameOptions & FRAMEOPTION_ROWS) appendStringInfoString(buf, "ROWS "); + else if (wc->frameOptions & FRAMEOPTION_GROUPS) + appendStringInfoString(buf, "GROUPS "); else Assert(false); if (wc->frameOptions & FRAMEOPTION_BETWEEN) @@ -5885,12 +5887,12 @@ get_rule_windowspec(WindowClause *wc, List *targetList, appendStringInfoString(buf, "UNBOUNDED PRECEDING "); else if (wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) appendStringInfoString(buf, "CURRENT ROW "); - else if (wc->frameOptions & FRAMEOPTION_START_VALUE) + else if (wc->frameOptions & FRAMEOPTION_START_OFFSET) { get_rule_expr(wc->startOffset, context, false); - if (wc->frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) + if (wc->frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING) appendStringInfoString(buf, " PRECEDING "); - else if (wc->frameOptions & FRAMEOPTION_START_VALUE_FOLLOWING) + else if (wc->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING) appendStringInfoString(buf, " FOLLOWING "); else Assert(false); @@ -5904,12 +5906,12 @@ get_rule_windowspec(WindowClause *wc, List *targetList, appendStringInfoString(buf, "UNBOUNDED FOLLOWING "); else if (wc->frameOptions & FRAMEOPTION_END_CURRENT_ROW) appendStringInfoString(buf, "CURRENT ROW "); - else if (wc->frameOptions & FRAMEOPTION_END_VALUE) + else if (wc->frameOptions & FRAMEOPTION_END_OFFSET) { get_rule_expr(wc->endOffset, context, false); - if (wc->frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) + if (wc->frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING) appendStringInfoString(buf, " PRECEDING "); - else if (wc->frameOptions & FRAMEOPTION_END_VALUE_FOLLOWING) + else if (wc->frameOptions & FRAMEOPTION_END_OFFSET_FOLLOWING) appendStringInfoString(buf, " FOLLOWING "); else Assert(false); @@ -5917,6 +5919,12 @@ get_rule_windowspec(WindowClause *wc, List *targetList, else Assert(false); } + if (wc->frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW) + appendStringInfoString(buf, "EXCLUDE CURRENT ROW "); + else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_GROUP) + appendStringInfoString(buf, "EXCLUDE GROUP "); + else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES) + appendStringInfoString(buf, "EXCLUDE TIES "); /* we will now have a trailing space; remove it */ buf->len--; } diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index e6a1eed191e..103f91ae624 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -3258,6 +3258,110 @@ interval_div(PG_FUNCTION_ARGS) PG_RETURN_INTERVAL_P(result); } + +/* + * in_range support functions for timestamps and intervals. + * + * Per SQL spec, we support these with interval as the offset type. + * The spec's restriction that the offset not be negative is a bit hard to + * decipher for intervals, but we choose to interpret it the same as our + * interval comparison operators would. + */ + +Datum +in_range_timestamptz_interval(PG_FUNCTION_ARGS) +{ + TimestampTz val = PG_GETARG_TIMESTAMPTZ(0); + TimestampTz base = PG_GETARG_TIMESTAMPTZ(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + TimestampTz sum; + + if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* We don't currently bother to avoid overflow hazards here */ + if (sub) + sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_mi_interval, + TimestampTzGetDatum(base), + IntervalPGetDatum(offset))); + else + sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval, + TimestampTzGetDatum(base), + IntervalPGetDatum(offset))); + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); +} + +Datum +in_range_timestamp_interval(PG_FUNCTION_ARGS) +{ + Timestamp val = PG_GETARG_TIMESTAMP(0); + Timestamp base = PG_GETARG_TIMESTAMP(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + Timestamp sum; + + if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* We don't currently bother to avoid overflow hazards here */ + if (sub) + sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_mi_interval, + TimestampGetDatum(base), + IntervalPGetDatum(offset))); + else + sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_pl_interval, + TimestampGetDatum(base), + IntervalPGetDatum(offset))); + + if (less) + PG_RETURN_BOOL(val <= sum); + else + PG_RETURN_BOOL(val >= sum); +} + +Datum +in_range_interval_interval(PG_FUNCTION_ARGS) +{ + Interval *val = PG_GETARG_INTERVAL_P(0); + Interval *base = PG_GETARG_INTERVAL_P(1); + Interval *offset = PG_GETARG_INTERVAL_P(2); + bool sub = PG_GETARG_BOOL(3); + bool less = PG_GETARG_BOOL(4); + Interval *sum; + + if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE), + errmsg("invalid preceding or following size in window function"))); + + /* We don't currently bother to avoid overflow hazards here */ + if (sub) + sum = DatumGetIntervalP(DirectFunctionCall2(interval_mi, + IntervalPGetDatum(base), + IntervalPGetDatum(offset))); + else + sum = DatumGetIntervalP(DirectFunctionCall2(interval_pl, + IntervalPGetDatum(base), + IntervalPGetDatum(offset))); + + if (less) + PG_RETURN_BOOL(interval_cmp_internal(val, sum) <= 0); + else + PG_RETURN_BOOL(interval_cmp_internal(val, sum) >= 0); +} + + /* * interval_accum, interval_accum_inv, and interval_avg implement the * AVG(interval) aggregate. diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index 1475bfe3627..9871d1e7931 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -177,6 +177,7 @@ Section: Class 22 - Data Exception 22P06 E ERRCODE_NONSTANDARD_USE_OF_ESCAPE_CHARACTER nonstandard_use_of_escape_character 22010 E ERRCODE_INVALID_INDICATOR_PARAMETER_VALUE invalid_indicator_parameter_value 22023 E ERRCODE_INVALID_PARAMETER_VALUE invalid_parameter_value +22013 E ERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE invalid_preceding_following_size 2201B E ERRCODE_INVALID_REGULAR_EXPRESSION invalid_regular_expression 2201W E ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE invalid_row_count_in_limit_clause 2201X E ERRCODE_INVALID_ROW_COUNT_IN_RESULT_OFFSET_CLAUSE invalid_row_count_in_result_offset_clause diff --git a/src/include/access/nbtree.h b/src/include/access/nbtree.h index 0f6a40168ca..2b0b1da7636 100644 --- a/src/include/access/nbtree.h +++ b/src/include/access/nbtree.h @@ -225,11 +225,17 @@ typedef struct BTMetaPageData * To facilitate accelerated sorting, an operator class may choose to * offer a second procedure (BTSORTSUPPORT_PROC). For full details, see * src/include/utils/sortsupport.h. + * + * To support window frames defined by "RANGE offset PRECEDING/FOLLOWING", + * an operator class may choose to offer a third amproc procedure + * (BTINRANGE_PROC), independently of whether it offers sortsupport. + * For full details, see doc/src/sgml/btree.sgml. */ #define BTORDER_PROC 1 #define BTSORTSUPPORT_PROC 2 -#define BTNProcs 2 +#define BTINRANGE_PROC 3 +#define BTNProcs 3 /* * We need to be able to tell the difference between read and write diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index f1765af4ba5..433d6db4f60 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201712251 +#define CATALOG_VERSION_NO 201802061 #endif diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h index f545a0580d2..c3d0ff70e63 100644 --- a/src/include/catalog/pg_amproc.h +++ b/src/include/catalog/pg_amproc.h @@ -96,6 +96,9 @@ DATA(insert ( 434 1184 1184 1 1314 )); DATA(insert ( 434 1184 1184 2 3137 )); DATA(insert ( 434 1184 1082 1 2383 )); DATA(insert ( 434 1184 1114 1 2533 )); +DATA(insert ( 434 1082 1186 3 4133 )); +DATA(insert ( 434 1114 1186 3 4134 )); +DATA(insert ( 434 1184 1186 3 4135 )); DATA(insert ( 1970 700 700 1 354 )); DATA(insert ( 1970 700 700 2 3132 )); DATA(insert ( 1970 700 701 1 2194 )); @@ -107,15 +110,23 @@ DATA(insert ( 1976 21 21 1 350 )); DATA(insert ( 1976 21 21 2 3129 )); DATA(insert ( 1976 21 23 1 2190 )); DATA(insert ( 1976 21 20 1 2192 )); +DATA(insert ( 1976 21 20 3 4130 )); +DATA(insert ( 1976 21 23 3 4131 )); +DATA(insert ( 1976 21 21 3 4132 )); DATA(insert ( 1976 23 23 1 351 )); DATA(insert ( 1976 23 23 2 3130 )); DATA(insert ( 1976 23 20 1 2188 )); DATA(insert ( 1976 23 21 1 2191 )); +DATA(insert ( 1976 23 20 3 4127 )); +DATA(insert ( 1976 23 23 3 4128 )); +DATA(insert ( 1976 23 21 3 4129 )); DATA(insert ( 1976 20 20 1 842 )); DATA(insert ( 1976 20 20 2 3131 )); DATA(insert ( 1976 20 23 1 2189 )); DATA(insert ( 1976 20 21 1 2193 )); +DATA(insert ( 1976 20 20 3 4126 )); DATA(insert ( 1982 1186 1186 1 1315 )); +DATA(insert ( 1982 1186 1186 3 4136 )); DATA(insert ( 1984 829 829 1 836 )); DATA(insert ( 1984 829 829 2 3359 )); DATA(insert ( 1986 19 19 1 359 )); @@ -128,7 +139,9 @@ DATA(insert ( 1991 30 30 1 404 )); DATA(insert ( 1994 25 25 1 360 )); DATA(insert ( 1994 25 25 2 3255 )); DATA(insert ( 1996 1083 1083 1 1107 )); +DATA(insert ( 1996 1083 1186 3 4137 )); DATA(insert ( 2000 1266 1266 1 1358 )); +DATA(insert ( 2000 1266 1186 3 4138 )); DATA(insert ( 2002 1562 1562 1 1672 )); DATA(insert ( 2095 25 25 1 2166 )); DATA(insert ( 2095 25 25 2 3332 )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index f01648c9616..2a5321315a7 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -647,6 +647,20 @@ DATA(insert OID = 381 ( bttintervalcmp PGNSP PGUID 12 1 0 0 0 f f f f t f i DESCR("less-equal-greater"); DATA(insert OID = 382 ( btarraycmp PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 23 "2277 2277" _null_ _null_ _null_ _null_ _null_ btarraycmp _null_ _null_ _null_ )); DESCR("less-equal-greater"); +DATA(insert OID = 4126 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "20 20 20 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int8_int8 _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4127 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "23 23 20 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int4_int8 _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4128 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "23 23 23 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int4_int4 _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4129 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "23 23 21 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int4_int2 _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4130 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "21 21 20 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int2_int8 _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4131 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "21 21 23 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int2_int4 _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4132 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "21 21 21 16 16" _null_ _null_ _null_ _null_ _null_ in_range_int2_int2 _null_ _null_ _null_ )); +DESCR("window RANGE support"); DATA(insert OID = 361 ( lseg_distance PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 701 "601 601" _null_ _null_ _null_ _null_ _null_ lseg_distance _null_ _null_ _null_ )); DATA(insert OID = 362 ( lseg_interpt PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 600 "601 601" _null_ _null_ _null_ _null_ _null_ lseg_interpt _null_ _null_ _null_ )); @@ -1216,6 +1230,8 @@ DATA(insert OID = 1092 ( date_cmp PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 DESCR("less-equal-greater"); DATA(insert OID = 3136 ( date_sortsupport PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2278 "2281" _null_ _null_ _null_ _null_ _null_ date_sortsupport _null_ _null_ _null_ )); DESCR("sort support"); +DATA(insert OID = 4133 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1082 1082 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_date_interval _null_ _null_ _null_ )); +DESCR("window RANGE support"); /* OIDS 1100 - 1199 */ @@ -3141,6 +3157,18 @@ DATA(insert OID = 2045 ( timestamp_cmp PGNSP PGUID 12 1 0 0 0 f f f f t f i s DESCR("less-equal-greater"); DATA(insert OID = 3137 ( timestamp_sortsupport PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2278 "2281" _null_ _null_ _null_ _null_ _null_ timestamp_sortsupport _null_ _null_ _null_ )); DESCR("sort support"); + +DATA(insert OID = 4134 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1114 1114 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_timestamp_interval _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4135 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f s s 5 0 16 "1184 1184 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_timestamptz_interval _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4136 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1186 1186 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_interval_interval _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4137 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1083 1083 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_time_interval _null_ _null_ _null_ )); +DESCR("window RANGE support"); +DATA(insert OID = 4138 ( in_range PGNSP PGUID 12 1 0 0 0 f f f f t f i s 5 0 16 "1266 1266 1186 16 16" _null_ _null_ _null_ _null_ _null_ in_range_timetz_interval _null_ _null_ _null_ )); +DESCR("window RANGE support"); + DATA(insert OID = 2046 ( time PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 1083 "1266" _null_ _null_ _null_ _null_ _null_ timetz_time _null_ _null_ _null_ )); DESCR("convert time with time zone to time"); DATA(insert OID = 2047 ( timetz PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 1266 "1083" _null_ _null_ _null_ _null_ _null_ time_timetz _null_ _null_ _null_ )); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index a2a2a9f3d4d..54ce63f1477 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1885,11 +1885,14 @@ typedef struct WindowAggState FmgrInfo *partEqfunctions; /* equality funcs for partition columns */ FmgrInfo *ordEqfunctions; /* equality funcs for ordering columns */ Tuplestorestate *buffer; /* stores rows of current partition */ - int current_ptr; /* read pointer # for current */ + int current_ptr; /* read pointer # for current row */ + int framehead_ptr; /* read pointer # for frame head, if used */ + int frametail_ptr; /* read pointer # for frame tail, if used */ + int grouptail_ptr; /* read pointer # for group tail, if used */ int64 spooled_rows; /* total # of rows in buffer */ int64 currentpos; /* position of current row in partition */ int64 frameheadpos; /* current frame head position */ - int64 frametailpos; /* current frame tail position */ + int64 frametailpos; /* current frame tail position (frame end+1) */ /* use struct pointer to avoid including windowapi.h here */ struct WindowObjectData *agg_winobj; /* winobj for aggregate fetches */ int64 aggregatedbase; /* start row for current aggregates */ @@ -1901,6 +1904,20 @@ typedef struct WindowAggState Datum startOffsetValue; /* result of startOffset evaluation */ Datum endOffsetValue; /* result of endOffset evaluation */ + /* these fields are used with RANGE offset PRECEDING/FOLLOWING: */ + FmgrInfo startInRangeFunc; /* in_range function for startOffset */ + FmgrInfo endInRangeFunc; /* in_range function for endOffset */ + Oid inRangeColl; /* collation for in_range tests */ + bool inRangeAsc; /* use ASC sort order for in_range tests? */ + bool inRangeNullsFirst; /* nulls sort first for in_range tests? */ + + /* these fields are used in GROUPS mode: */ + int64 currentgroup; /* peer group # of current row in partition */ + int64 frameheadgroup; /* peer group # of frame head row */ + int64 frametailgroup; /* peer group # of frame tail row */ + int64 groupheadpos; /* current row's peer group head position */ + int64 grouptailpos; /* " " " " tail position (group end+1) */ + MemoryContext partcontext; /* context for partition-lifespan data */ MemoryContext aggcontext; /* shared context for aggregate working data */ MemoryContext curaggcontext; /* current aggregate's working data */ @@ -1916,9 +1933,13 @@ typedef struct WindowAggState * date for current row */ bool frametail_valid; /* true if frametailpos is known up to * date for current row */ + bool grouptail_valid; /* true if grouptailpos is known up to + * date for current row */ TupleTableSlot *first_part_slot; /* first tuple of current or next * partition */ + TupleTableSlot *framehead_slot; /* first tuple of current frame */ + TupleTableSlot *frametail_slot; /* first tuple after current frame */ /* temporary slots for tuples fetched back from tuplestore */ TupleTableSlot *agg_row_slot; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index a16de289ba8..c7a43b8933f 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -499,27 +499,33 @@ typedef struct WindowDef * which were defaulted; the correct behavioral bits must be set either way. * The START_foo and END_foo options must come in pairs of adjacent bits for * the convenience of gram.y, even though some of them are useless/invalid. - * We will need more bits (and fields) to cover the full SQL:2008 option set. */ #define FRAMEOPTION_NONDEFAULT 0x00001 /* any specified? */ #define FRAMEOPTION_RANGE 0x00002 /* RANGE behavior */ #define FRAMEOPTION_ROWS 0x00004 /* ROWS behavior */ -#define FRAMEOPTION_BETWEEN 0x00008 /* BETWEEN given? */ -#define FRAMEOPTION_START_UNBOUNDED_PRECEDING 0x00010 /* start is U. P. */ -#define FRAMEOPTION_END_UNBOUNDED_PRECEDING 0x00020 /* (disallowed) */ -#define FRAMEOPTION_START_UNBOUNDED_FOLLOWING 0x00040 /* (disallowed) */ -#define FRAMEOPTION_END_UNBOUNDED_FOLLOWING 0x00080 /* end is U. F. */ -#define FRAMEOPTION_START_CURRENT_ROW 0x00100 /* start is C. R. */ -#define FRAMEOPTION_END_CURRENT_ROW 0x00200 /* end is C. R. */ -#define FRAMEOPTION_START_VALUE_PRECEDING 0x00400 /* start is V. P. */ -#define FRAMEOPTION_END_VALUE_PRECEDING 0x00800 /* end is V. P. */ -#define FRAMEOPTION_START_VALUE_FOLLOWING 0x01000 /* start is V. F. */ -#define FRAMEOPTION_END_VALUE_FOLLOWING 0x02000 /* end is V. F. */ - -#define FRAMEOPTION_START_VALUE \ - (FRAMEOPTION_START_VALUE_PRECEDING | FRAMEOPTION_START_VALUE_FOLLOWING) -#define FRAMEOPTION_END_VALUE \ - (FRAMEOPTION_END_VALUE_PRECEDING | FRAMEOPTION_END_VALUE_FOLLOWING) +#define FRAMEOPTION_GROUPS 0x00008 /* GROUPS behavior */ +#define FRAMEOPTION_BETWEEN 0x00010 /* BETWEEN given? */ +#define FRAMEOPTION_START_UNBOUNDED_PRECEDING 0x00020 /* start is U. P. */ +#define FRAMEOPTION_END_UNBOUNDED_PRECEDING 0x00040 /* (disallowed) */ +#define FRAMEOPTION_START_UNBOUNDED_FOLLOWING 0x00080 /* (disallowed) */ +#define FRAMEOPTION_END_UNBOUNDED_FOLLOWING 0x00100 /* end is U. F. */ +#define FRAMEOPTION_START_CURRENT_ROW 0x00200 /* start is C. R. */ +#define FRAMEOPTION_END_CURRENT_ROW 0x00400 /* end is C. R. */ +#define FRAMEOPTION_START_OFFSET_PRECEDING 0x00800 /* start is O. P. */ +#define FRAMEOPTION_END_OFFSET_PRECEDING 0x01000 /* end is O. P. */ +#define FRAMEOPTION_START_OFFSET_FOLLOWING 0x02000 /* start is O. F. */ +#define FRAMEOPTION_END_OFFSET_FOLLOWING 0x04000 /* end is O. F. */ +#define FRAMEOPTION_EXCLUDE_CURRENT_ROW 0x08000 /* omit C.R. */ +#define FRAMEOPTION_EXCLUDE_GROUP 0x10000 /* omit C.R. & peers */ +#define FRAMEOPTION_EXCLUDE_TIES 0x20000 /* omit C.R.'s peers */ + +#define FRAMEOPTION_START_OFFSET \ + (FRAMEOPTION_START_OFFSET_PRECEDING | FRAMEOPTION_START_OFFSET_FOLLOWING) +#define FRAMEOPTION_END_OFFSET \ + (FRAMEOPTION_END_OFFSET_PRECEDING | FRAMEOPTION_END_OFFSET_FOLLOWING) +#define FRAMEOPTION_EXCLUSION \ + (FRAMEOPTION_EXCLUDE_CURRENT_ROW | FRAMEOPTION_EXCLUDE_GROUP | \ + FRAMEOPTION_EXCLUDE_TIES) #define FRAMEOPTION_DEFAULTS \ (FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \ @@ -1277,6 +1283,9 @@ typedef struct GroupingSet * if the clause originally came from WINDOW, and is NULL if it originally * was an OVER clause (but note that we collapse out duplicate OVERs). * partitionClause and orderClause are lists of SortGroupClause structs. + * If we have RANGE with offset PRECEDING/FOLLOWING, the semantics of that are + * specified by startInRangeFunc/inRangeColl/inRangeAsc/inRangeNullsFirst + * for the start offset, or endInRangeFunc/inRange* for the end offset. * winref is an ID number referenced by WindowFunc nodes; it must be unique * among the members of a Query's windowClause list. * When refname isn't null, the partitionClause is always copied from there; @@ -1293,6 +1302,11 @@ typedef struct WindowClause int frameOptions; /* frame_clause options, see WindowDef */ Node *startOffset; /* expression for starting bound, if any */ Node *endOffset; /* expression for ending bound, if any */ + Oid startInRangeFunc; /* in_range function for startOffset */ + Oid endInRangeFunc; /* in_range function for endOffset */ + Oid inRangeColl; /* collation for in_range tests */ + bool inRangeAsc; /* use ASC sort order for in_range tests? */ + bool inRangeNullsFirst; /* nulls sort first for in_range tests? */ Index winref; /* ID referenced by window functions */ bool copiedOrder; /* did we copy orderClause from refname? */ } WindowClause; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index baf3c074171..f2e19eae68f 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -811,6 +811,12 @@ typedef struct WindowAgg int frameOptions; /* frame_clause options, see WindowDef */ Node *startOffset; /* expression for starting bound, if any */ Node *endOffset; /* expression for ending bound, if any */ + /* these fields are used with RANGE offset PRECEDING/FOLLOWING: */ + Oid startInRangeFunc; /* in_range function for startOffset */ + Oid endInRangeFunc; /* in_range function for endOffset */ + Oid inRangeColl; /* collation for in_range tests */ + bool inRangeAsc; /* use ASC sort order for in_range tests? */ + bool inRangeNullsFirst; /* nulls sort first for in_range tests? */ } WindowAgg; /* ---------------- diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 26af944e03a..cf32197bc3e 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -182,6 +182,7 @@ PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD) PG_KEYWORD("greatest", GREATEST, COL_NAME_KEYWORD) PG_KEYWORD("group", GROUP_P, RESERVED_KEYWORD) PG_KEYWORD("grouping", GROUPING, COL_NAME_KEYWORD) +PG_KEYWORD("groups", GROUPS, UNRESERVED_KEYWORD) PG_KEYWORD("handler", HANDLER, UNRESERVED_KEYWORD) PG_KEYWORD("having", HAVING, RESERVED_KEYWORD) PG_KEYWORD("header", HEADER_P, UNRESERVED_KEYWORD) @@ -283,6 +284,7 @@ PG_KEYWORD("options", OPTIONS, UNRESERVED_KEYWORD) PG_KEYWORD("or", OR, RESERVED_KEYWORD) PG_KEYWORD("order", ORDER, RESERVED_KEYWORD) PG_KEYWORD("ordinality", ORDINALITY, UNRESERVED_KEYWORD) +PG_KEYWORD("others", OTHERS, UNRESERVED_KEYWORD) PG_KEYWORD("out", OUT_P, COL_NAME_KEYWORD) PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD) @@ -397,6 +399,7 @@ PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD) PG_KEYWORD("temporary", TEMPORARY, UNRESERVED_KEYWORD) PG_KEYWORD("text", TEXT_P, UNRESERVED_KEYWORD) PG_KEYWORD("then", THEN, RESERVED_KEYWORD) +PG_KEYWORD("ties", TIES, UNRESERVED_KEYWORD) PG_KEYWORD("time", TIME, COL_NAME_KEYWORD) PG_KEYWORD("timestamp", TIMESTAMP, COL_NAME_KEYWORD) PG_KEYWORD("to", TO, RESERVED_KEYWORD) diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 4e96fa79070..2e0792d60b0 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -45,6 +45,7 @@ typedef enum ParseExprKind EXPR_KIND_WINDOW_ORDER, /* window definition ORDER BY */ EXPR_KIND_WINDOW_FRAME_RANGE, /* window frame clause with RANGE */ EXPR_KIND_WINDOW_FRAME_ROWS, /* window frame clause with ROWS */ + EXPR_KIND_WINDOW_FRAME_GROUPS, /* window frame clause with GROUPS */ EXPR_KIND_SELECT_TARGET, /* SELECT target list item */ EXPR_KIND_INSERT_TARGET, /* INSERT target list item */ EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */ @@ -67,7 +68,7 @@ typedef enum ParseExprKind EXPR_KIND_EXECUTE_PARAMETER, /* parameter value in EXECUTE */ EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */ EXPR_KIND_POLICY, /* USING or WITH CHECK expr in policy */ - EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */ + EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */ EXPR_KIND_CALL /* CALL argument */ } ParseExprKind; diff --git a/src/test/regress/expected/alter_generic.out b/src/test/regress/expected/alter_generic.out index 200828aa995..44356dea0b7 100644 --- a/src/test/regress/expected/alter_generic.out +++ b/src/test/regress/expected/alter_generic.out @@ -354,9 +354,9 @@ ERROR: invalid operator number 0, must be between 1 and 5 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 1 < ; -- operator without argument types ERROR: operator argument types must be specified in ALTER OPERATOR FAMILY ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 0 btint42cmp(int4, int2); -- function number should be between 1 and 5 -ERROR: invalid procedure number 0, must be between 1 and 2 +ERROR: invalid procedure number 0, must be between 1 and 3 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 6 btint42cmp(int4, int2); -- function number should be between 1 and 5 -ERROR: invalid procedure number 6, must be between 1 and 2 +ERROR: invalid procedure number 6, must be between 1 and 3 ALTER OPERATOR FAMILY alt_opf4 USING btree ADD STORAGE invalid_storage; -- Ensure STORAGE is not a part of ALTER OPERATOR FAMILY ERROR: STORAGE cannot be specified in ALTER OPERATOR FAMILY DROP OPERATOR FAMILY alt_opf4 USING btree; diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 19f909f3d10..b675487729b 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -819,6 +819,176 @@ FROM tenk1 WHERE unique1 < 10; 10 | 0 | 0 (10 rows) +SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 7 | 4 | 0 + 13 | 2 | 2 + 22 | 1 | 1 + 26 | 6 | 2 + 29 | 9 | 1 + 31 | 8 | 0 + 32 | 5 | 1 + 23 | 3 | 3 + 15 | 7 | 3 + 10 | 0 | 0 +(10 rows) + +SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 3 | 4 | 0 + 11 | 2 | 2 + 21 | 1 | 1 + 20 | 6 | 2 + 20 | 9 | 1 + 23 | 8 | 0 + 27 | 5 | 1 + 20 | 3 | 3 + 8 | 7 | 3 + 10 | 0 | 0 +(10 rows) + +SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + | 4 | 0 + | 2 | 2 + | 1 | 1 + | 6 | 2 + | 9 | 1 + | 8 | 0 + | 5 | 1 + | 3 | 3 + | 7 | 3 + | 0 | 0 +(10 rows) + +SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 4 | 4 | 0 + 2 | 2 | 2 + 1 | 1 | 1 + 6 | 6 | 2 + 9 | 9 | 1 + 8 | 8 | 0 + 5 | 5 | 1 + 3 | 3 | 3 + 7 | 7 | 3 + 0 | 0 | 0 +(10 rows) + +SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + first_value | unique1 | four +-------------+---------+------ + 8 | 0 | 0 + 4 | 8 | 0 + 5 | 4 | 0 + 9 | 5 | 1 + 1 | 9 | 1 + 6 | 1 | 1 + 2 | 6 | 2 + 3 | 2 | 2 + 7 | 3 | 3 + | 7 | 3 +(10 rows) + +SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + first_value | unique1 | four +-------------+---------+------ + | 0 | 0 + 5 | 8 | 0 + 5 | 4 | 0 + | 5 | 1 + 6 | 9 | 1 + 6 | 1 | 1 + 3 | 6 | 2 + 3 | 2 | 2 + | 3 | 3 + | 7 | 3 +(10 rows) + +SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + first_value | unique1 | four +-------------+---------+------ + 0 | 0 | 0 + 8 | 8 | 0 + 4 | 4 | 0 + 5 | 5 | 1 + 9 | 9 | 1 + 1 | 1 | 1 + 6 | 6 | 2 + 2 | 2 | 2 + 3 | 3 | 3 + 7 | 7 | 3 +(10 rows) + +SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + last_value | unique1 | four +------------+---------+------ + 4 | 0 | 0 + 5 | 8 | 0 + 9 | 4 | 0 + 1 | 5 | 1 + 6 | 9 | 1 + 2 | 1 | 1 + 3 | 6 | 2 + 7 | 2 | 2 + 7 | 3 | 3 + | 7 | 3 +(10 rows) + +SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + last_value | unique1 | four +------------+---------+------ + | 0 | 0 + 5 | 8 | 0 + 9 | 4 | 0 + | 5 | 1 + 6 | 9 | 1 + 2 | 1 | 1 + 3 | 6 | 2 + 7 | 2 | 2 + | 3 | 3 + | 7 | 3 +(10 rows) + +SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + last_value | unique1 | four +------------+---------+------ + 0 | 0 | 0 + 5 | 8 | 0 + 9 | 4 | 0 + 5 | 5 | 1 + 6 | 9 | 1 + 2 | 1 | 1 + 3 | 6 | 2 + 7 | 2 | 2 + 3 | 3 | 3 + 7 | 7 | 3 +(10 rows) + SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), unique1, four FROM tenk1 WHERE unique1 < 10; @@ -887,13 +1057,57 @@ FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); 10 | 7 | 3 (10 rows) --- fail: not implemented yet -SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), +SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), unique1, four -FROM tenk1 WHERE unique1 < 10; -ERROR: RANGE PRECEDING is only supported with UNBOUNDED -LINE 1: SELECT sum(unique1) over (order by four range between 2::int... - ^ +FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); + sum | unique1 | four +-----+---------+------ + 12 | 0 | 0 + 4 | 8 | 0 + 8 | 4 | 0 + 22 | 5 | 1 + 18 | 9 | 1 + 26 | 1 | 1 + 29 | 6 | 2 + 33 | 2 | 2 + 42 | 3 | 3 + 38 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); + sum | unique1 | four +-----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 35 | 3 | 3 + 35 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); + sum | unique1 | four +-----+---------+------ + 0 | 0 | 0 + 8 | 8 | 0 + 4 | 4 | 0 + 17 | 5 | 1 + 21 | 9 | 1 + 13 | 1 | 1 + 33 | 6 | 2 + 29 | 2 | 2 + 38 | 3 | 3 + 42 | 7 | 3 +(10 rows) + SELECT first_value(unique1) over w, nth_value(unique1, 2) over w AS nth_2, last_value(unique1) over w, unique1, four @@ -958,6 +1172,1477 @@ SELECT pg_get_viewdef('v_window'); FROM generate_series(1, 10) i(i); (1 row) +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude current row) as sum_rows FROM generate_series(1, 10) i; +SELECT * FROM v_window; + i | sum_rows +----+---------- + 1 | 2 + 2 | 4 + 3 | 6 + 4 | 8 + 5 | 10 + 6 | 12 + 7 | 14 + 8 | 16 + 9 | 18 + 10 | 9 +(10 rows) + +SELECT pg_get_viewdef('v_window'); + pg_get_viewdef +----------------------------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS sum_rows+ + FROM generate_series(1, 10) i(i); +(1 row) + +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude group) as sum_rows FROM generate_series(1, 10) i; +SELECT * FROM v_window; + i | sum_rows +----+---------- + 1 | 2 + 2 | 4 + 3 | 6 + 4 | 8 + 5 | 10 + 6 | 12 + 7 | 14 + 8 | 16 + 9 | 18 + 10 | 9 +(10 rows) + +SELECT pg_get_viewdef('v_window'); + pg_get_viewdef +----------------------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS sum_rows+ + FROM generate_series(1, 10) i(i); +(1 row) + +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude ties) as sum_rows FROM generate_series(1, 10) i; +SELECT * FROM v_window; + i | sum_rows +----+---------- + 1 | 3 + 2 | 6 + 3 | 9 + 4 | 12 + 5 | 15 + 6 | 18 + 7 | 21 + 8 | 24 + 9 | 27 + 10 | 19 +(10 rows) + +SELECT pg_get_viewdef('v_window'); + pg_get_viewdef +---------------------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS sum_rows+ + FROM generate_series(1, 10) i(i); +(1 row) + +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude no others) as sum_rows FROM generate_series(1, 10) i; +SELECT * FROM v_window; + i | sum_rows +----+---------- + 1 | 3 + 2 | 6 + 3 | 9 + 4 | 12 + 5 | 15 + 6 | 18 + 7 | 21 + 8 | 24 + 9 | 27 + 10 | 19 +(10 rows) + +SELECT pg_get_viewdef('v_window'); + pg_get_viewdef +--------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ + FROM generate_series(1, 10) i(i); +(1 row) + +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following) as sum_rows FROM generate_series(1, 10) i; +SELECT * FROM v_window; + i | sum_rows +----+---------- + 1 | 3 + 2 | 6 + 3 | 9 + 4 | 12 + 5 | 15 + 6 | 18 + 7 | 21 + 8 | 24 + 9 | 27 + 10 | 19 +(10 rows) + +SELECT pg_get_viewdef('v_window'); + pg_get_viewdef +----------------------------------------------------------------------------------------- + SELECT i.i, + + sum(i.i) OVER (ORDER BY i.i GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ + FROM generate_series(1, 10) i(i); +(1 row) + +DROP VIEW v_window; +CREATE TEMP VIEW v_window AS + SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i + FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i; +SELECT pg_get_viewdef('v_window'); + pg_get_viewdef +--------------------------------------------------------------------------------------------------------------------------- + SELECT i.i, + + min(i.i) OVER (ORDER BY i.i RANGE BETWEEN '@ 1 day'::interval PRECEDING AND '@ 10 days'::interval FOLLOWING) AS min_i+ + FROM generate_series(now(), (now() + '@ 100 days'::interval), '@ 1 hour'::interval) i(i); +(1 row) + +-- RANGE offset PRECEDING/FOLLOWING tests +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + | 3 | 3 + | 7 | 3 + 10 | 6 | 2 + 10 | 2 | 2 + 18 | 9 | 1 + 18 | 5 | 1 + 18 | 1 | 1 + 23 | 0 | 0 + 23 | 8 | 0 + 23 | 4 | 0 +(10 rows) + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 33 | 0 | 0 + 41 | 8 | 0 + 37 | 4 | 0 + 35 | 5 | 1 + 39 | 9 | 1 + 31 | 1 | 1 + 43 | 6 | 2 + 39 | 2 | 2 + 26 | 3 | 3 + 30 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 33 | 0 | 0 + 33 | 8 | 0 + 33 | 4 | 0 + 30 | 5 | 1 + 30 | 9 | 1 + 30 | 1 | 1 + 37 | 6 | 2 + 37 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 4 | 0 | 0 + 12 | 4 | 0 + 12 | 8 | 0 + 6 | 1 | 1 + 15 | 5 | 1 + 14 | 9 | 1 + 8 | 2 | 2 + 8 | 6 | 2 + 10 | 3 | 3 + 10 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following + exclude current row),unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 4 | 0 | 0 + 8 | 4 | 0 + 4 | 8 | 0 + 5 | 1 | 1 + 10 | 5 | 1 + 5 | 9 | 1 + 6 | 2 | 2 + 2 | 6 | 2 + 7 | 3 | 3 + 3 | 7 | 3 +(10 rows) + +select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + sum | salary | enroll_date +-------+--------+------------- + 34900 | 5000 | 10-01-2006 + 34900 | 6000 | 10-01-2006 + 38400 | 3900 | 12-23-2006 + 47100 | 4800 | 08-01-2007 + 47100 | 5200 | 08-01-2007 + 47100 | 4800 | 08-08-2007 + 47100 | 5200 | 08-15-2007 + 36100 | 3500 | 12-10-2007 + 32200 | 4500 | 01-01-2008 + 32200 | 4200 | 01-01-2008 +(10 rows) + +select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + sum | salary | enroll_date +-------+--------+------------- + 32200 | 4200 | 01-01-2008 + 32200 | 4500 | 01-01-2008 + 36100 | 3500 | 12-10-2007 + 47100 | 5200 | 08-15-2007 + 47100 | 4800 | 08-08-2007 + 47100 | 4800 | 08-01-2007 + 47100 | 5200 | 08-01-2007 + 38400 | 3900 | 12-23-2006 + 34900 | 5000 | 10-01-2006 + 34900 | 6000 | 10-01-2006 +(10 rows) + +select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), + salary, enroll_date from empsalary; + sum | salary | enroll_date +-----+--------+------------- + | 4200 | 01-01-2008 + | 4500 | 01-01-2008 + | 3500 | 12-10-2007 + | 5200 | 08-15-2007 + | 4800 | 08-08-2007 + | 4800 | 08-01-2007 + | 5200 | 08-01-2007 + | 3900 | 12-23-2006 + | 5000 | 10-01-2006 + | 6000 | 10-01-2006 +(10 rows) + +select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude current row), salary, enroll_date from empsalary; + sum | salary | enroll_date +-------+--------+------------- + 29900 | 5000 | 10-01-2006 + 28900 | 6000 | 10-01-2006 + 34500 | 3900 | 12-23-2006 + 42300 | 4800 | 08-01-2007 + 41900 | 5200 | 08-01-2007 + 42300 | 4800 | 08-08-2007 + 41900 | 5200 | 08-15-2007 + 32600 | 3500 | 12-10-2007 + 27700 | 4500 | 01-01-2008 + 28000 | 4200 | 01-01-2008 +(10 rows) + +select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude group), salary, enroll_date from empsalary; + sum | salary | enroll_date +-------+--------+------------- + 23900 | 5000 | 10-01-2006 + 23900 | 6000 | 10-01-2006 + 34500 | 3900 | 12-23-2006 + 37100 | 4800 | 08-01-2007 + 37100 | 5200 | 08-01-2007 + 42300 | 4800 | 08-08-2007 + 41900 | 5200 | 08-15-2007 + 32600 | 3500 | 12-10-2007 + 23500 | 4500 | 01-01-2008 + 23500 | 4200 | 01-01-2008 +(10 rows) + +select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude ties), salary, enroll_date from empsalary; + sum | salary | enroll_date +-------+--------+------------- + 28900 | 5000 | 10-01-2006 + 29900 | 6000 | 10-01-2006 + 38400 | 3900 | 12-23-2006 + 41900 | 4800 | 08-01-2007 + 42300 | 5200 | 08-01-2007 + 47100 | 4800 | 08-08-2007 + 47100 | 5200 | 08-15-2007 + 36100 | 3500 | 12-10-2007 + 28000 | 4500 | 01-01-2008 + 27700 | 4200 | 01-01-2008 +(10 rows) + +select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), + lead(salary) over(order by salary range between 1000 preceding and 1000 following), + nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), + salary from empsalary; + first_value | lead | nth_value | salary +-------------+------+-----------+-------- + 3500 | 3900 | 3500 | 3500 + 3500 | 4200 | 3500 | 3900 + 3500 | 4500 | 3500 | 4200 + 3500 | 4800 | 3500 | 4500 + 3900 | 4800 | 3900 | 4800 + 3900 | 5000 | 3900 | 4800 + 4200 | 5200 | 4200 | 5000 + 4200 | 5200 | 4200 | 5200 + 4200 | 6000 | 4200 | 5200 + 5000 | | 5000 | 6000 +(10 rows) + +select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), + lag(salary) over(order by salary range between 1000 preceding and 1000 following), + salary from empsalary; + last_value | lag | salary +------------+------+-------- + 4500 | | 3500 + 4800 | 3500 | 3900 + 5200 | 3900 | 4200 + 5200 | 4200 | 4500 + 5200 | 4500 | 4800 + 5200 | 4800 | 4800 + 6000 | 4800 | 5000 + 6000 | 5000 | 5200 + 6000 | 5200 | 5200 + 6000 | 5200 | 6000 +(10 rows) + +select first_value(salary) over(order by salary range between 1000 following and 3000 following + exclude current row), + lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), + nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following + exclude ties), + salary from empsalary; + first_value | lead | nth_value | salary +-------------+------+-----------+-------- + 4500 | 3900 | 4500 | 3500 + 5000 | 4200 | 5000 | 3900 + 5200 | 4500 | 5200 | 4200 + 6000 | 4800 | 6000 | 4500 + 6000 | 4800 | 6000 | 4800 + 6000 | 5000 | 6000 | 4800 + 6000 | 5200 | 6000 | 5000 + | 5200 | | 5200 + | 6000 | | 5200 + | | | 6000 +(10 rows) + +select last_value(salary) over(order by salary range between 1000 following and 3000 following + exclude group), + lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), + salary from empsalary; + last_value | lag | salary +------------+------+-------- + 6000 | | 3500 + 6000 | 3500 | 3900 + 6000 | 3900 | 4200 + 6000 | 4200 | 4500 + 6000 | 4500 | 4800 + 6000 | 4800 | 4800 + 6000 | 4800 | 5000 + | 5000 | 5200 + | 5200 | 5200 + | 5200 | 6000 +(10 rows) + +select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + first_value | last_value | salary | enroll_date +-------------+------------+--------+------------- + 5000 | 5200 | 5000 | 10-01-2006 + 6000 | 5200 | 6000 | 10-01-2006 + 5000 | 3500 | 3900 | 12-23-2006 + 5000 | 4200 | 4800 | 08-01-2007 + 5000 | 4200 | 5200 | 08-01-2007 + 5000 | 4200 | 4800 | 08-08-2007 + 5000 | 4200 | 5200 | 08-15-2007 + 5000 | 4200 | 3500 | 12-10-2007 + 5000 | 4200 | 4500 | 01-01-2008 + 5000 | 4200 | 4200 | 01-01-2008 +(10 rows) + +select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + salary, enroll_date from empsalary; + first_value | last_value | salary | enroll_date +-------------+------------+--------+------------- + 5000 | 5200 | 5000 | 10-01-2006 + 6000 | 5200 | 6000 | 10-01-2006 + 5000 | 3500 | 3900 | 12-23-2006 + 5000 | 4200 | 4800 | 08-01-2007 + 5000 | 4200 | 5200 | 08-01-2007 + 5000 | 4200 | 4800 | 08-08-2007 + 5000 | 4200 | 5200 | 08-15-2007 + 5000 | 4200 | 3500 | 12-10-2007 + 5000 | 4500 | 4500 | 01-01-2008 + 5000 | 4200 | 4200 | 01-01-2008 +(10 rows) + +select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), + salary, enroll_date from empsalary; + first_value | last_value | salary | enroll_date +-------------+------------+--------+------------- + 3900 | 5200 | 5000 | 10-01-2006 + 3900 | 5200 | 6000 | 10-01-2006 + 5000 | 3500 | 3900 | 12-23-2006 + 5000 | 4200 | 4800 | 08-01-2007 + 5000 | 4200 | 5200 | 08-01-2007 + 5000 | 4200 | 4800 | 08-08-2007 + 5000 | 4200 | 5200 | 08-15-2007 + 5000 | 4200 | 3500 | 12-10-2007 + 5000 | 3500 | 4500 | 01-01-2008 + 5000 | 3500 | 4200 | 01-01-2008 +(10 rows) + +select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), + salary, enroll_date from empsalary; + first_value | last_value | salary | enroll_date +-------------+------------+--------+------------- + 6000 | 5200 | 5000 | 10-01-2006 + 5000 | 5200 | 6000 | 10-01-2006 + 5000 | 3500 | 3900 | 12-23-2006 + 5000 | 4200 | 4800 | 08-01-2007 + 5000 | 4200 | 5200 | 08-01-2007 + 5000 | 4200 | 4800 | 08-08-2007 + 5000 | 4200 | 5200 | 08-15-2007 + 5000 | 4200 | 3500 | 12-10-2007 + 5000 | 4200 | 4500 | 01-01-2008 + 5000 | 4500 | 4200 | 01-01-2008 +(10 rows) + +-- RANGE offset PRECEDING/FOLLOWING with null values +select x, y, + first_value(y) over w, + last_value(y) over w +from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss +window w as + (order by x asc nulls first range between 2 preceding and 2 following); + x | y | first_value | last_value +---+----+-------------+------------ + | 42 | 42 | 43 + | 43 | 42 | 43 + 1 | 1 | 1 | 3 + 2 | 2 | 1 | 4 + 3 | 3 | 1 | 5 + 4 | 4 | 2 | 5 + 5 | 5 | 3 | 5 +(7 rows) + +select x, y, + first_value(y) over w, + last_value(y) over w +from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss +window w as + (order by x asc nulls last range between 2 preceding and 2 following); + x | y | first_value | last_value +---+----+-------------+------------ + 1 | 1 | 1 | 3 + 2 | 2 | 1 | 4 + 3 | 3 | 1 | 5 + 4 | 4 | 2 | 5 + 5 | 5 | 3 | 5 + | 42 | 42 | 43 + | 43 | 42 | 43 +(7 rows) + +select x, y, + first_value(y) over w, + last_value(y) over w +from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss +window w as + (order by x desc nulls first range between 2 preceding and 2 following); + x | y | first_value | last_value +---+----+-------------+------------ + | 43 | 43 | 42 + | 42 | 43 | 42 + 5 | 5 | 5 | 3 + 4 | 4 | 5 | 2 + 3 | 3 | 5 | 1 + 2 | 2 | 4 | 1 + 1 | 1 | 3 | 1 +(7 rows) + +select x, y, + first_value(y) over w, + last_value(y) over w +from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss +window w as + (order by x desc nulls last range between 2 preceding and 2 following); + x | y | first_value | last_value +---+----+-------------+------------ + 5 | 5 | 5 | 3 + 4 | 4 | 5 | 2 + 3 | 3 | 5 | 1 + 2 | 2 | 4 | 1 + 1 | 1 | 3 | 1 + | 42 | 42 | 43 + | 43 | 42 | 43 +(7 rows) + +-- Check overflow behavior for various integer sizes +select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following) +from generate_series(32764, 32766) x; + x | last_value +-------+------------ + 32764 | 32766 + 32765 | 32766 + 32766 | 32766 +(3 rows) + +select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following) +from generate_series(-32766, -32764) x; + x | last_value +--------+------------ + -32764 | -32766 + -32765 | -32766 + -32766 | -32766 +(3 rows) + +select x, last_value(x) over (order by x range between current row and 4 following) +from generate_series(2147483644, 2147483646) x; + x | last_value +------------+------------ + 2147483644 | 2147483646 + 2147483645 | 2147483646 + 2147483646 | 2147483646 +(3 rows) + +select x, last_value(x) over (order by x desc range between current row and 5 following) +from generate_series(-2147483646, -2147483644) x; + x | last_value +-------------+------------- + -2147483644 | -2147483646 + -2147483645 | -2147483646 + -2147483646 | -2147483646 +(3 rows) + +select x, last_value(x) over (order by x range between current row and 4 following) +from generate_series(9223372036854775804, 9223372036854775806) x; + x | last_value +---------------------+--------------------- + 9223372036854775804 | 9223372036854775806 + 9223372036854775805 | 9223372036854775806 + 9223372036854775806 | 9223372036854775806 +(3 rows) + +select x, last_value(x) over (order by x desc range between current row and 5 following) +from generate_series(-9223372036854775806, -9223372036854775804) x; + x | last_value +----------------------+---------------------- + -9223372036854775804 | -9223372036854775806 + -9223372036854775805 | -9223372036854775806 + -9223372036854775806 | -9223372036854775806 +(3 rows) + +-- Test in_range for other datetime datatypes +create temp table datetimes( + id int, + f_time time, + f_timetz timetz, + f_interval interval, + f_timestamptz timestamptz, + f_timestamp timestamp +); +insert into datetimes values +(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'), +(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), +(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), +(4, '14:00', '14:00 BST', '4 years', '2002-10-19 10:23:54+01', '2002-10-19 10:23:54'), +(5, '15:00', '15:00 BST', '5 years', '2003-10-19 10:23:54+01', '2003-10-19 10:23:54'), +(6, '15:00', '15:00 BST', '5 years', '2004-10-19 10:23:54+01', '2004-10-19 10:23:54'), +(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'), +(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'), +(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'), +(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54'); +select id, f_time, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_time range between + '70 min'::interval preceding and '2 hours'::interval following); + id | f_time | first_value | last_value +----+----------+-------------+------------ + 1 | 11:00:00 | 1 | 3 + 2 | 12:00:00 | 1 | 4 + 3 | 13:00:00 | 2 | 6 + 4 | 14:00:00 | 3 | 6 + 5 | 15:00:00 | 4 | 7 + 6 | 15:00:00 | 4 | 7 + 7 | 17:00:00 | 7 | 9 + 8 | 18:00:00 | 7 | 10 + 9 | 19:00:00 | 8 | 10 + 10 | 20:00:00 | 9 | 10 +(10 rows) + +select id, f_time, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_time desc range between + '70 min' preceding and '2 hours' following); + id | f_time | first_value | last_value +----+----------+-------------+------------ + 10 | 20:00:00 | 10 | 8 + 9 | 19:00:00 | 10 | 7 + 8 | 18:00:00 | 9 | 7 + 7 | 17:00:00 | 8 | 5 + 6 | 15:00:00 | 6 | 3 + 5 | 15:00:00 | 6 | 3 + 4 | 14:00:00 | 6 | 2 + 3 | 13:00:00 | 4 | 1 + 2 | 12:00:00 | 3 | 1 + 1 | 11:00:00 | 2 | 1 +(10 rows) + +select id, f_timetz, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timetz range between + '70 min'::interval preceding and '2 hours'::interval following); + id | f_timetz | first_value | last_value +----+-------------+-------------+------------ + 1 | 11:00:00+01 | 1 | 3 + 2 | 12:00:00+01 | 1 | 4 + 3 | 13:00:00+01 | 2 | 6 + 4 | 14:00:00+01 | 3 | 6 + 5 | 15:00:00+01 | 4 | 7 + 6 | 15:00:00+01 | 4 | 7 + 7 | 17:00:00+01 | 7 | 9 + 8 | 18:00:00+01 | 7 | 10 + 9 | 19:00:00+01 | 8 | 10 + 10 | 20:00:00+01 | 9 | 10 +(10 rows) + +select id, f_timetz, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timetz desc range between + '70 min' preceding and '2 hours' following); + id | f_timetz | first_value | last_value +----+-------------+-------------+------------ + 10 | 20:00:00+01 | 10 | 8 + 9 | 19:00:00+01 | 10 | 7 + 8 | 18:00:00+01 | 9 | 7 + 7 | 17:00:00+01 | 8 | 5 + 6 | 15:00:00+01 | 6 | 3 + 5 | 15:00:00+01 | 6 | 3 + 4 | 14:00:00+01 | 6 | 2 + 3 | 13:00:00+01 | 4 | 1 + 2 | 12:00:00+01 | 3 | 1 + 1 | 11:00:00+01 | 2 | 1 +(10 rows) + +select id, f_interval, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_interval range between + '1 year'::interval preceding and '1 year'::interval following); + id | f_interval | first_value | last_value +----+------------+-------------+------------ + 1 | @ 1 year | 1 | 2 + 2 | @ 2 years | 1 | 3 + 3 | @ 3 years | 2 | 4 + 4 | @ 4 years | 3 | 6 + 5 | @ 5 years | 4 | 6 + 6 | @ 5 years | 4 | 6 + 7 | @ 7 years | 7 | 8 + 8 | @ 8 years | 7 | 9 + 9 | @ 9 years | 8 | 10 + 10 | @ 10 years | 9 | 10 +(10 rows) + +select id, f_interval, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_interval desc range between + '1 year' preceding and '1 year' following); + id | f_interval | first_value | last_value +----+------------+-------------+------------ + 10 | @ 10 years | 10 | 9 + 9 | @ 9 years | 10 | 8 + 8 | @ 8 years | 9 | 7 + 7 | @ 7 years | 8 | 7 + 6 | @ 5 years | 6 | 4 + 5 | @ 5 years | 6 | 4 + 4 | @ 4 years | 6 | 3 + 3 | @ 3 years | 4 | 2 + 2 | @ 2 years | 3 | 1 + 1 | @ 1 year | 2 | 1 +(10 rows) + +select id, f_timestamptz, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timestamptz range between + '1 year'::interval preceding and '1 year'::interval following); + id | f_timestamptz | first_value | last_value +----+------------------------------+-------------+------------ + 1 | Thu Oct 19 02:23:54 2000 PDT | 1 | 3 + 2 | Fri Oct 19 02:23:54 2001 PDT | 1 | 4 + 3 | Fri Oct 19 02:23:54 2001 PDT | 1 | 4 + 4 | Sat Oct 19 02:23:54 2002 PDT | 2 | 5 + 5 | Sun Oct 19 02:23:54 2003 PDT | 4 | 6 + 6 | Tue Oct 19 02:23:54 2004 PDT | 5 | 7 + 7 | Wed Oct 19 02:23:54 2005 PDT | 6 | 8 + 8 | Thu Oct 19 02:23:54 2006 PDT | 7 | 9 + 9 | Fri Oct 19 02:23:54 2007 PDT | 8 | 10 + 10 | Sun Oct 19 02:23:54 2008 PDT | 9 | 10 +(10 rows) + +select id, f_timestamptz, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timestamptz desc range between + '1 year' preceding and '1 year' following); + id | f_timestamptz | first_value | last_value +----+------------------------------+-------------+------------ + 10 | Sun Oct 19 02:23:54 2008 PDT | 10 | 9 + 9 | Fri Oct 19 02:23:54 2007 PDT | 10 | 8 + 8 | Thu Oct 19 02:23:54 2006 PDT | 9 | 7 + 7 | Wed Oct 19 02:23:54 2005 PDT | 8 | 6 + 6 | Tue Oct 19 02:23:54 2004 PDT | 7 | 5 + 5 | Sun Oct 19 02:23:54 2003 PDT | 6 | 4 + 4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 2 + 3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1 + 2 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1 + 1 | Thu Oct 19 02:23:54 2000 PDT | 3 | 1 +(10 rows) + +select id, f_timestamp, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timestamp range between + '1 year'::interval preceding and '1 year'::interval following); + id | f_timestamp | first_value | last_value +----+--------------------------+-------------+------------ + 1 | Thu Oct 19 10:23:54 2000 | 1 | 3 + 2 | Fri Oct 19 10:23:54 2001 | 1 | 4 + 3 | Fri Oct 19 10:23:54 2001 | 1 | 4 + 4 | Sat Oct 19 10:23:54 2002 | 2 | 5 + 5 | Sun Oct 19 10:23:54 2003 | 4 | 6 + 6 | Tue Oct 19 10:23:54 2004 | 5 | 7 + 7 | Wed Oct 19 10:23:54 2005 | 6 | 8 + 8 | Thu Oct 19 10:23:54 2006 | 7 | 9 + 9 | Fri Oct 19 10:23:54 2007 | 8 | 10 + 10 | Sun Oct 19 10:23:54 2008 | 9 | 10 +(10 rows) + +select id, f_timestamp, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timestamp desc range between + '1 year' preceding and '1 year' following); + id | f_timestamp | first_value | last_value +----+--------------------------+-------------+------------ + 10 | Sun Oct 19 10:23:54 2008 | 10 | 9 + 9 | Fri Oct 19 10:23:54 2007 | 10 | 8 + 8 | Thu Oct 19 10:23:54 2006 | 9 | 7 + 7 | Wed Oct 19 10:23:54 2005 | 8 | 6 + 6 | Tue Oct 19 10:23:54 2004 | 7 | 5 + 5 | Sun Oct 19 10:23:54 2003 | 6 | 4 + 4 | Sat Oct 19 10:23:54 2002 | 5 | 2 + 3 | Fri Oct 19 10:23:54 2001 | 4 | 1 + 2 | Fri Oct 19 10:23:54 2001 | 4 | 1 + 1 | Thu Oct 19 10:23:54 2000 | 3 | 1 +(10 rows) + +-- RANGE offset PRECEDING/FOLLOWING error cases +select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_date from empsalary; +ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column +LINE 1: select sum(salary) over (order by enroll_date, salary range ... + ^ +select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_date from empsalary; +ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column +LINE 1: select sum(salary) over (range between '1 year'::interval pr... + ^ +select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_date from empsalary; +ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type text +LINE 1: ... sum(salary) over (order by depname range between '1 year'::... + ^ +select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following + exclude ties), salary, enroll_date from empsalary; +ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type date and offset type integer +LINE 1: ...ll_date) over (order by enroll_date range between 1 precedin... + ^ +HINT: Cast the offset value to an appropriate type. +select max(enroll_date) over (order by salary range between -1 preceding and 2 following + exclude ties), salary, enroll_date from empsalary; +ERROR: invalid preceding or following size in window function +select max(enroll_date) over (order by salary range between 1 preceding and -2 following + exclude ties), salary, enroll_date from empsalary; +ERROR: invalid preceding or following size in window function +select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_date from empsalary; +ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type integer and offset type interval +LINE 1: ...(enroll_date) over (order by salary range between '1 year'::... + ^ +HINT: Cast the offset value to an appropriate type. +select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following + exclude ties), salary, enroll_date from empsalary; +ERROR: invalid preceding or following size in window function +-- GROUPS tests +SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 12 | 0 | 0 + 12 | 8 | 0 + 12 | 4 | 0 + 27 | 5 | 1 + 27 | 9 | 1 + 27 | 1 | 1 + 35 | 6 | 2 + 35 | 2 | 2 + 45 | 3 | 3 + 45 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 45 | 0 | 0 + 45 | 8 | 0 + 45 | 4 | 0 + 45 | 5 | 1 + 45 | 9 | 1 + 45 | 1 | 1 + 45 | 6 | 2 + 45 | 2 | 2 + 45 | 3 | 3 + 45 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between current row and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 45 | 0 | 0 + 45 | 8 | 0 + 45 | 4 | 0 + 33 | 5 | 1 + 33 | 9 | 1 + 33 | 1 | 1 + 18 | 6 | 2 + 18 | 2 | 2 + 10 | 3 | 3 + 10 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 45 | 0 | 0 + 45 | 8 | 0 + 45 | 4 | 0 + 45 | 5 | 1 + 45 | 9 | 1 + 45 | 1 | 1 + 33 | 6 | 2 + 33 | 2 | 2 + 18 | 3 | 3 + 18 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 33 | 0 | 0 + 33 | 8 | 0 + 33 | 4 | 0 + 18 | 5 | 1 + 18 | 9 | 1 + 18 | 1 | 1 + 10 | 6 | 2 + 10 | 2 | 2 + | 3 | 3 + | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 35 | 0 | 0 + 35 | 8 | 0 + 35 | 4 | 0 + 45 | 5 | 1 + 45 | 9 | 1 + 45 | 1 | 1 + 45 | 6 | 2 + 45 | 2 | 2 + 45 | 3 | 3 + 45 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + | 0 | 0 + | 8 | 0 + | 4 | 0 + 12 | 5 | 1 + 12 | 9 | 1 + 12 | 1 | 1 + 27 | 6 | 2 + 27 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 27 | 0 | 0 + 27 | 8 | 0 + 27 | 4 | 0 + 35 | 5 | 1 + 35 | 9 | 1 + 35 | 1 | 1 + 45 | 6 | 2 + 45 | 2 | 2 + 33 | 3 | 3 + 33 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 12 | 0 | 0 + 12 | 8 | 0 + 12 | 4 | 0 + 15 | 5 | 1 + 15 | 9 | 1 + 15 | 1 | 1 + 8 | 6 | 2 + 8 | 2 | 2 + 10 | 3 | 3 + 10 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude current row), unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 27 | 0 | 0 + 19 | 8 | 0 + 23 | 4 | 0 + 30 | 5 | 1 + 26 | 9 | 1 + 34 | 1 | 1 + 39 | 6 | 2 + 43 | 2 | 2 + 30 | 3 | 3 + 26 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude group), unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 15 | 0 | 0 + 15 | 8 | 0 + 15 | 4 | 0 + 20 | 5 | 1 + 20 | 9 | 1 + 20 | 1 | 1 + 37 | 6 | 2 + 37 | 2 | 2 + 23 | 3 | 3 + 23 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude ties), unique1, four +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four +-----+---------+------ + 15 | 0 | 0 + 23 | 8 | 0 + 19 | 4 | 0 + 25 | 5 | 1 + 29 | 9 | 1 + 21 | 1 | 1 + 43 | 6 | 2 + 39 | 2 | 2 + 26 | 3 | 3 + 30 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following),unique1, four, ten +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four | ten +-----+---------+------+----- + 0 | 0 | 0 | 0 + 1 | 1 | 1 | 1 + 2 | 2 | 2 | 2 + 3 | 3 | 3 | 3 + 4 | 4 | 0 | 4 + 5 | 5 | 1 | 5 + 6 | 6 | 2 | 6 + 7 | 7 | 3 | 7 + 8 | 8 | 0 | 8 + 9 | 9 | 1 | 9 +(10 rows) + +SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four | ten +-----+---------+------+----- + | 0 | 0 | 0 + | 1 | 1 | 1 + | 2 | 2 | 2 + | 3 | 3 | 3 + | 4 | 0 | 4 + | 5 | 1 | 5 + | 6 | 2 | 6 + | 7 | 3 | 7 + | 8 | 0 | 8 + | 9 | 1 | 9 +(10 rows) + +SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four | ten +-----+---------+------+----- + | 0 | 0 | 0 + | 1 | 1 | 1 + | 2 | 2 | 2 + | 3 | 3 | 3 + | 4 | 0 | 4 + | 5 | 1 | 5 + | 6 | 2 | 6 + | 7 | 3 | 7 + | 8 | 0 | 8 + | 9 | 1 | 9 +(10 rows) + +SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten +FROM tenk1 WHERE unique1 < 10; + sum | unique1 | four | ten +-----+---------+------+----- + 0 | 0 | 0 | 0 + 1 | 1 | 1 | 1 + 2 | 2 | 2 | 2 + 3 | 3 | 3 | 3 + 4 | 4 | 0 | 4 + 5 | 5 | 1 | 5 + 6 | 6 | 2 | 6 + 7 | 7 | 3 | 7 + 8 | 8 | 0 | 8 + 9 | 9 | 1 | 9 +(10 rows) + +select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), + lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), + nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), + salary, enroll_date from empsalary; + first_value | lead | nth_value | salary | enroll_date +-------------+------+-----------+--------+------------- + 5000 | 6000 | 5000 | 5000 | 10-01-2006 + 5000 | 3900 | 5000 | 6000 | 10-01-2006 + 5000 | 4800 | 5000 | 3900 | 12-23-2006 + 3900 | 5200 | 3900 | 4800 | 08-01-2007 + 3900 | 4800 | 3900 | 5200 | 08-01-2007 + 4800 | 5200 | 4800 | 4800 | 08-08-2007 + 4800 | 3500 | 4800 | 5200 | 08-15-2007 + 5200 | 4500 | 5200 | 3500 | 12-10-2007 + 3500 | 4200 | 3500 | 4500 | 01-01-2008 + 3500 | | 3500 | 4200 | 01-01-2008 +(10 rows) + +select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), + lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), + salary, enroll_date from empsalary; + last_value | lag | salary | enroll_date +------------+------+--------+------------- + 3900 | | 5000 | 10-01-2006 + 3900 | 5000 | 6000 | 10-01-2006 + 5200 | 6000 | 3900 | 12-23-2006 + 4800 | 3900 | 4800 | 08-01-2007 + 4800 | 4800 | 5200 | 08-01-2007 + 5200 | 5200 | 4800 | 08-08-2007 + 3500 | 4800 | 5200 | 08-15-2007 + 4200 | 5200 | 3500 | 12-10-2007 + 4200 | 3500 | 4500 | 01-01-2008 + 4200 | 4500 | 4200 | 01-01-2008 +(10 rows) + +select first_value(salary) over(order by enroll_date groups between 1 following and 3 following + exclude current row), + lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), + nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following + exclude ties), + salary, enroll_date from empsalary; + first_value | lead | nth_value | salary | enroll_date +-------------+------+-----------+--------+------------- + 3900 | 6000 | 3900 | 5000 | 10-01-2006 + 3900 | 3900 | 3900 | 6000 | 10-01-2006 + 4800 | 4800 | 4800 | 3900 | 12-23-2006 + 4800 | 5200 | 4800 | 4800 | 08-01-2007 + 4800 | 4800 | 4800 | 5200 | 08-01-2007 + 5200 | 5200 | 5200 | 4800 | 08-08-2007 + 3500 | 3500 | 3500 | 5200 | 08-15-2007 + 4500 | 4500 | 4500 | 3500 | 12-10-2007 + | 4200 | | 4500 | 01-01-2008 + | | | 4200 | 01-01-2008 +(10 rows) + +select last_value(salary) over(order by enroll_date groups between 1 following and 3 following + exclude group), + lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), + salary, enroll_date from empsalary; + last_value | lag | salary | enroll_date +------------+------+--------+------------- + 4800 | | 5000 | 10-01-2006 + 4800 | 5000 | 6000 | 10-01-2006 + 5200 | 6000 | 3900 | 12-23-2006 + 3500 | 3900 | 4800 | 08-01-2007 + 3500 | 4800 | 5200 | 08-01-2007 + 4200 | 5200 | 4800 | 08-08-2007 + 4200 | 4800 | 5200 | 08-15-2007 + 4200 | 5200 | 3500 | 12-10-2007 + | 3500 | 4500 | 01-01-2008 + | 4500 | 4200 | 01-01-2008 +(10 rows) + +-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS +WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); + x | sum +----+----- + 1 | 4 + 3 | 9 + 5 | 15 + 7 | 21 + 9 | 27 + 11 | 33 + 13 | 39 + 15 | 45 + 17 | 51 + 19 | 57 + 21 | 63 + 23 | 69 + 25 | 75 + 27 | 81 + 29 | 87 + 31 | 93 + 33 | 99 + 35 | 68 +(18 rows) + +WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); + x | sum +----+----- + 1 | 1 + 3 | 3 + 5 | 5 + 7 | 7 + 9 | 9 + 11 | 11 + 13 | 13 + 15 | 15 + 17 | 17 + 19 | 19 + 21 | 21 + 23 | 23 + 25 | 25 + 27 | 27 + 29 | 29 + 31 | 31 + 33 | 33 + 35 | 35 +(18 rows) + +WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); + x | sum +----+----- + 1 | 4 + 3 | 9 + 5 | 15 + 7 | 21 + 9 | 27 + 11 | 33 + 13 | 39 + 15 | 45 + 17 | 51 + 19 | 57 + 21 | 63 + 23 | 69 + 25 | 75 + 27 | 81 + 29 | 87 + 31 | 93 + 33 | 99 + 35 | 68 +(18 rows) + +WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); + x | sum +----+----- + 1 | 2 + 1 | 3 + 1 | 7 + 5 | 13 + 7 | 21 + 9 | 27 + 11 | 33 + 13 | 39 + 15 | 45 + 17 | 51 + 19 | 57 + 21 | 63 + 23 | 69 + 25 | 75 + 27 | 81 + 29 | 87 + 31 | 93 + 33 | 99 + 35 | 105 + 37 | 111 + 39 | 117 + 41 | 123 + 43 | 129 + 45 | 135 + 47 | 141 + 49 | 96 +(26 rows) + +WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); + x | sum +----+----- + 1 | 3 + 1 | 3 + 1 | 3 + 5 | 5 + 7 | 7 + 9 | 9 + 11 | 11 + 13 | 13 + 15 | 15 + 17 | 17 + 19 | 19 + 21 | 21 + 23 | 23 + 25 | 25 + 27 | 27 + 29 | 29 + 31 | 31 + 33 | 33 + 35 | 35 + 37 | 37 + 39 | 39 + 41 | 41 + 43 | 43 + 45 | 45 + 47 | 47 + 49 | 49 +(26 rows) + +WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); + x | sum +----+----- + 1 | 8 + 1 | 8 + 1 | 8 + 5 | 15 + 7 | 21 + 9 | 27 + 11 | 33 + 13 | 39 + 15 | 45 + 17 | 51 + 19 | 57 + 21 | 63 + 23 | 69 + 25 | 75 + 27 | 81 + 29 | 87 + 31 | 93 + 33 | 99 + 35 | 105 + 37 | 111 + 39 | 117 + 41 | 123 + 43 | 129 + 45 | 135 + 47 | 141 + 49 | 96 +(26 rows) + -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; count diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index e2a1a1cdd51..3320aa81f89 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -189,6 +189,46 @@ SELECT sum(unique1) over (rows between 2 preceding and 2 following), unique1, four FROM tenk1 WHERE unique1 < 10; +SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), unique1, four FROM tenk1 WHERE unique1 < 10; @@ -205,10 +245,17 @@ SELECT sum(unique1) over (w range between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); --- fail: not implemented yet -SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), +SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row), unique1, four -FROM tenk1 WHERE unique1 < 10; +FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); + +SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); + +SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four); SELECT first_value(unique1) over w, nth_value(unique1, 2) over w AS nth_2, @@ -230,6 +277,449 @@ SELECT * FROM v_window; SELECT pg_get_viewdef('v_window'); +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude current row) as sum_rows FROM generate_series(1, 10) i; + +SELECT * FROM v_window; + +SELECT pg_get_viewdef('v_window'); + +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude group) as sum_rows FROM generate_series(1, 10) i; + +SELECT * FROM v_window; + +SELECT pg_get_viewdef('v_window'); + +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude ties) as sum_rows FROM generate_series(1, 10) i; + +SELECT * FROM v_window; + +SELECT pg_get_viewdef('v_window'); + +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following + exclude no others) as sum_rows FROM generate_series(1, 10) i; + +SELECT * FROM v_window; + +SELECT pg_get_viewdef('v_window'); + +CREATE OR REPLACE TEMP VIEW v_window AS + SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following) as sum_rows FROM generate_series(1, 10) i; + +SELECT * FROM v_window; + +SELECT pg_get_viewdef('v_window'); + +DROP VIEW v_window; + +CREATE TEMP VIEW v_window AS + SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i + FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i; + +SELECT pg_get_viewdef('v_window'); + +-- RANGE offset PRECEDING/FOLLOWING tests + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following + exclude current row),unique1, four +FROM tenk1 WHERE unique1 < 10; + +select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + +select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + +select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following), + salary, enroll_date from empsalary; + +select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude current row), salary, enroll_date from empsalary; + +select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude group), salary, enroll_date from empsalary; + +select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following + exclude ties), salary, enroll_date from empsalary; + +select first_value(salary) over(order by salary range between 1000 preceding and 1000 following), + lead(salary) over(order by salary range between 1000 preceding and 1000 following), + nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following), + salary from empsalary; + +select last_value(salary) over(order by salary range between 1000 preceding and 1000 following), + lag(salary) over(order by salary range between 1000 preceding and 1000 following), + salary from empsalary; + +select first_value(salary) over(order by salary range between 1000 following and 3000 following + exclude current row), + lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties), + nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following + exclude ties), + salary from empsalary; + +select last_value(salary) over(order by salary range between 1000 following and 3000 following + exclude group), + lag(salary) over(order by salary range between 1000 following and 3000 following exclude group), + salary from empsalary; + +select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following), + salary, enroll_date from empsalary; + +select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude ties), + salary, enroll_date from empsalary; + +select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude group), + salary, enroll_date from empsalary; + +select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), + last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following + exclude current row), + salary, enroll_date from empsalary; + +-- RANGE offset PRECEDING/FOLLOWING with null values +select x, y, + first_value(y) over w, + last_value(y) over w +from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss +window w as + (order by x asc nulls first range between 2 preceding and 2 following); + +select x, y, + first_value(y) over w, + last_value(y) over w +from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss +window w as + (order by x asc nulls last range between 2 preceding and 2 following); + +select x, y, + first_value(y) over w, + last_value(y) over w +from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss +window w as + (order by x desc nulls first range between 2 preceding and 2 following); + +select x, y, + first_value(y) over w, + last_value(y) over w +from + (select x, x as y from generate_series(1,5) as x + union all select null, 42 + union all select null, 43) ss +window w as + (order by x desc nulls last range between 2 preceding and 2 following); + +-- Check overflow behavior for various integer sizes + +select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following) +from generate_series(32764, 32766) x; + +select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following) +from generate_series(-32766, -32764) x; + +select x, last_value(x) over (order by x range between current row and 4 following) +from generate_series(2147483644, 2147483646) x; + +select x, last_value(x) over (order by x desc range between current row and 5 following) +from generate_series(-2147483646, -2147483644) x; + +select x, last_value(x) over (order by x range between current row and 4 following) +from generate_series(9223372036854775804, 9223372036854775806) x; + +select x, last_value(x) over (order by x desc range between current row and 5 following) +from generate_series(-9223372036854775806, -9223372036854775804) x; + +-- Test in_range for other datetime datatypes + +create temp table datetimes( + id int, + f_time time, + f_timetz timetz, + f_interval interval, + f_timestamptz timestamptz, + f_timestamp timestamp +); + +insert into datetimes values +(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'), +(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), +(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'), +(4, '14:00', '14:00 BST', '4 years', '2002-10-19 10:23:54+01', '2002-10-19 10:23:54'), +(5, '15:00', '15:00 BST', '5 years', '2003-10-19 10:23:54+01', '2003-10-19 10:23:54'), +(6, '15:00', '15:00 BST', '5 years', '2004-10-19 10:23:54+01', '2004-10-19 10:23:54'), +(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'), +(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'), +(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'), +(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54'); + +select id, f_time, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_time range between + '70 min'::interval preceding and '2 hours'::interval following); + +select id, f_time, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_time desc range between + '70 min' preceding and '2 hours' following); + +select id, f_timetz, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timetz range between + '70 min'::interval preceding and '2 hours'::interval following); + +select id, f_timetz, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timetz desc range between + '70 min' preceding and '2 hours' following); + +select id, f_interval, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_interval range between + '1 year'::interval preceding and '1 year'::interval following); + +select id, f_interval, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_interval desc range between + '1 year' preceding and '1 year' following); + +select id, f_timestamptz, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timestamptz range between + '1 year'::interval preceding and '1 year'::interval following); + +select id, f_timestamptz, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timestamptz desc range between + '1 year' preceding and '1 year' following); + +select id, f_timestamp, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timestamp range between + '1 year'::interval preceding and '1 year'::interval following); + +select id, f_timestamp, first_value(id) over w, last_value(id) over w +from datetimes +window w as (order by f_timestamp desc range between + '1 year' preceding and '1 year' following); + +-- RANGE offset PRECEDING/FOLLOWING error cases +select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_date from empsalary; + +select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_date from empsalary; + +select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_date from empsalary; + +select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following + exclude ties), salary, enroll_date from empsalary; + +select max(enroll_date) over (order by salary range between -1 preceding and 2 following + exclude ties), salary, enroll_date from empsalary; + +select max(enroll_date) over (order by salary range between 1 preceding and -2 following + exclude ties), salary, enroll_date from empsalary; + +select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following + exclude ties), salary, enroll_date from empsalary; + +select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following + exclude ties), salary, enroll_date from empsalary; + +-- GROUPS tests + +SELECT sum(unique1) over (order by four groups between unbounded preceding and current row), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between current row and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between 1 following and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude current row), unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude group), unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following + exclude ties), unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following),unique1, four, ten +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (partition by ten + order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten +FROM tenk1 WHERE unique1 < 10; + +select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), + lead(salary) over(order by enroll_date groups between 1 preceding and 1 following), + nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following), + salary, enroll_date from empsalary; + +select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following), + lag(salary) over(order by enroll_date groups between 1 preceding and 1 following), + salary, enroll_date from empsalary; + +select first_value(salary) over(order by enroll_date groups between 1 following and 3 following + exclude current row), + lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties), + nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following + exclude ties), + salary, enroll_date from empsalary; + +select last_value(salary) over(order by enroll_date groups between 1 following and 3 following + exclude group), + lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group), + salary, enroll_date from empsalary; + +-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS +WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); + +WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); + +WITH cte (x) AS ( + SELECT * FROM generate_series(1, 35, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); + +WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following); + +WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x range between 1 preceding and 1 following); + +WITH cte (x) AS ( + select 1 union all select 1 union all select 1 union all + SELECT * FROM generate_series(5, 49, 2) +) +SELECT x, (sum(x) over w) +FROM cte +WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following); + -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; |