Add GROUPS option to the Window Functions
Started by Oliver Fordabout 8 years ago1 messages
Adds the GROUPS option to the window framing clause. This further
resolves TODO list item "Implement full support for window framing
clauses" and implements SQL:2011 T620. No other mainstream db has this
feature.
Apply this on top of my previous patch available here:
/messages/by-id/CAGMVOdvETRCKpeT06Uoq5RsNUOdH7d1iYy7C1Pze=L5=gBzs-Q@mail.gmail.com
== Specification ==
The GROUPS option is defined in SQL:2011 in addition to ROWS and
RANGE. Where ROWS calculate frame bounds by the number of rows before
and after the current row, and RANGE by the values of an ORDER BY
column, GROUPS calculates frame bounds by the number of changes to the
values of the ORDER BY columns.
GROUPS behaves similar to RANGE in that if two rows are peers, they
are both included in the frame. A row is out of frame if it is both
not a peer of the current row and also outside of the bounds specified
by start_value and end_value. Note that if neither start_value or
end_value are specified, then GROUPS will always produce the same
results as RANGE. So UNBOUNDED PRECEDING AND CURRENT ROW, or CURRENT
ROW AND UNBOUNDED FOLLOWING produce the same results in GROUPS and
RANGE mode (the syntax is slightly confusing as CURRENT ROW in these
modes includes peers of the actual current row).
The standard also defines an EXCLUDE GROUP option which excludes the
current row and any peers from the frame. This can be used in all
three modes, and is included in the patch.
== Performance Considerations ==
The code calculates the size of each window group for every partition
and stores this in a dynamic array. I chose 16 as the initial capacity
of the array, which doubles as needed. Real-world testing may show
that a lower or higher initial capacity is preferable for the majority
of use cases. The code also calls pfree on this array at the end of
each partition, to avoid memory hogging if there are many partitions.
== Testing ==
Tested on Windows with MinGW. All existing regression tests pass. New
tests and updated documentation is included. Tests show the results of
the GROUPS option and the EXCLUDE GROUP option also working in RANGE
and ROWS mode.
Attachments:
0001-window-groups-v1.patchapplication/octet-stream; name=0001-window-groups-v1.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4dd9d029e6..d61769da75 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14721,8 +14721,8 @@ 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 40c146ca40..26ff2f59fa 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -859,8 +859,8 @@ 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> [ <replaceable class="parameter">frame_exclusion_clause</replaceable> ]
-{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable class="parameter">frame_exclusion_clause</replaceable> ]
+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable class="parameter">frame_exclusion_clause</replaceable> ]
+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable class="parameter">frame_exclusion_clause</replaceable> ]
</synopsis>
where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be
@@ -879,6 +879,7 @@ UNBOUNDED FOLLOWING
<synopsis>
EXCLUDE CURRENT ROW
+EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS
</synopsis>
@@ -903,19 +904,20 @@ EXCLUDE NO OTHERS
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>
+ 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> mode it means that the frame starts or ends with
+ <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>value</replaceable> <literal>PRECEDING</literal> and
<replaceable>value</replaceable> <literal>FOLLOWING</literal> cases differ depending on
- whether the frame clause is in <literal>ROWS</literal> or <literal>RANGE</literal> mode. In
+ whether the frame clause is in <literal>ROWS</literal>, <literal>RANGE</literal>or <literal>GROUPS</literal> mode. 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. In <literal>RANGE</literal> mode, they indicate that
the frame starts or ends when the ORDER BY column's value for each row is within the bounds
- specified by <replaceable>value</replaceable> for both the start and the end of the frame.
- In <literal>ROWS</literal> mode, <replaceable>value</replaceable> must be an integer expression not
+ specified by <replaceable>value</replaceable> for both the start and the end of the frame. In <literal>GROUPS</literal> mode,
+ they indicate the number of changes to the value of the ORDER BY columns (i.e., groups of peers).
+ In <literal>ROWS</literal> or <literal>GROUPS</literal> mode, <replaceable>value</replaceable> must be an integer expression not
containing any variables, aggregate functions, or window functions.In <literal>RANGE</literal> mode,
there must be exactly one ORDER BY column and if the column is an integer column,
then <replaceable>value</replaceable> must be an integer. If it is a date/time column, then
@@ -926,14 +928,15 @@ EXCLUDE NO OTHERS
<para>
For the <replaceable class="parameter">frame_exclusion_clause</replaceable>, <literal>EXCLUDE CURRENT ROW</literal>
excludes the current row from the frame. <literal>EXCLUDE TIES</literal> excludes any peers of the current row from the
- frame. <literal>EXCLUDE NO OTHERS</literal> does nothing, but is provided in order to optionally document the intention
+ frame. <literal>EXCLUDE GROUP</literal> excludes both the current row and any peers of the current row from the frame.
+ <literal>EXCLUDE NO OTHERS</literal> does nothing, but is provided in order to optionally document the intention
not to exclude any other rows.
</para>
<para>
Beware that the <literal>ROWS</literal> options 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
+ uniquely. The <literal>RANGE</literal> and <literal>GROUPS</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.
</para>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index f711c99987..87905ef2e2 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1805,8 +1805,8 @@ FROM generate_series(1,10) AS s(i);
and the optional <replaceable class="parameter">frame_clause</replaceable>
can be one of
<synopsis>
-{ RANGE | ROWS } <replaceable>frame_start</replaceable> [ <replaceable class="parameter">frame_exclusion_clause</replaceable> ]
-{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable class="parameter">frame_exclusion_clause</replaceable> ]
+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable class="parameter">frame_exclusion_clause</replaceable> ]
+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable class="parameter">frame_exclusion_clause</replaceable> ]
</synopsis>
where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be
one of
@@ -1820,6 +1820,7 @@ UNBOUNDED FOLLOWING
where the optional <replaceable>frame_exclusion_clause</replaceable> can be one of
<synopsis>
EXCLUDE CURRENT ROW
+EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS
</synopsis>
@@ -1863,8 +1864,8 @@ EXCLUDE NO OTHERS
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
+ either <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,
it defaults to <literal>CURRENT ROW</literal>.
</para>
@@ -1877,7 +1878,7 @@ EXCLUDE NO OTHERS
</para>
<para>
- In <literal>RANGE</literal> mode, a <replaceable>frame_start</replaceable> of
+ 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 <literal>ORDER BY</literal> considers
equivalent to the current row), while a <replaceable>frame_end</replaceable> of
@@ -1903,10 +1904,19 @@ EXCLUDE NO OTHERS
</para>
<para>
+ In <literal>GROUPS</literal> mode, <replaceable>value</replaceable> <literal>PRECEDING</literal> and
+ <replaceable>value</replaceable> <literal>FOLLOWING</literal> cases indicate that the frame starts or ends the specified
+ number of <literal>window framing groups</literal> before or after the current <literal>window framing group</literal>.
+ Two rows are in the same <literal>window framing group</literal> if they are peers, (i.e., their ORDER BY column values
+ match). This mode allows the selection of a frame by the number of changes to the ORDER BY columns.
+ </para>
+
+ <para>
For the <replaceable class="parameter">frame_exclusion_clause</replaceable>, <literal>EXCLUDE CURRENT ROW</literal>
excludes the current row from the frame. <literal>EXCLUDE TIES</literal> excludes any peers of the current row from the
- frame. <literal>EXCLUDE NO OTHERS</literal> does nothing, but is provided in order to optionally document the intention
- not to exclude any other rows.
+ frame. <literal>EXCLUDE GROUP</literal> excludes both the current row and any peers of the current row from the frame.
+ <literal>EXCLUDE NO OTHERS</literal> does nothing, but is provided in order to optionally document the intention not to
+ exclude any other rows.
</para>
<para>
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 8e746f36d4..20d61f3780 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/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 4c6199fbe4..4e5b98f847 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -68,6 +68,8 @@ typedef struct WindowObjectData
int readptr; /* tuplestore read pointer for this fn */
int64 markpos; /* row that markptr is positioned on */
int64 seekpos; /* row that readptr is positioned on */
+ int64 winGroupsCount; /* number of window groups, used for GROUPS with values */
+ int64 *winGroupLen; /* length of each window group */
} WindowObjectData;
/*
@@ -181,6 +183,8 @@ 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_group(WindowObject winobj, int64 currpos, int64 slotpos,
+ int64 offset, bool preceding, bool end);
static bool row_is_in_range(Oid sortColOid, Datum currval,
Datum slotval, Datum offset, bool preceding, bool end);
static int row_is_in_frame(WindowAggState *winstate, int64 pos,
@@ -686,9 +690,6 @@ 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
* partition, and rows only enter the frame, never exit it, as the current
@@ -740,16 +741,17 @@ 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, 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
+ * FOLLOWING or CURRENT ROW, no exclusion clause is specified, we are not
+ * in GROUPS BETWEEN with values mode, 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->frameOptions & FRAMEOPTION_GROUPS_BETWEEN) &&
winstate->aggregatedbase <= winstate->currentpos &&
winstate->aggregatedupto > winstate->currentpos)
{
@@ -771,6 +773,7 @@ eval_windowaggregates(WindowAggState *winstate)
* - if the frame's head moved and we cannot use an inverse
* transition function, or
* - we are in RANGE BETWEEN with values mode, or
+ * - we are in GROUPS BETWEEN with values mode, or
* - we are in EXCLUDE CURRENT ROW/EXCLUDE TIES mode, or
* - if the new frame doesn't overlap the old one
*
@@ -787,6 +790,7 @@ eval_windowaggregates(WindowAggState *winstate)
(winstate->aggregatedbase != winstate->frameheadpos &&
!OidIsValid(peraggstate->invtransfn_oid)) ||
winstate->frameOptions & FRAMEOPTION_RANGE_BETWEEN ||
+ winstate->frameOptions & FRAMEOPTION_GROUPS_BETWEEN ||
winstate->frameOptions & FRAMEOPTION_EXCLUSION ||
winstate->aggregatedupto <= winstate->frameheadpos)
{
@@ -1132,6 +1136,7 @@ begin_partition(WindowAggState *winstate)
/* If the frame head is potentially movable ... */
if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) ||
winstate->frameOptions & (FRAMEOPTION_RANGE_BETWEEN) ||
+ winstate->frameOptions & (FRAMEOPTION_GROUPS_BETWEEN) ||
winstate->frameOptions & (FRAMEOPTION_EXCLUSION))
{
/* ... create a mark pointer to track the frame head */
@@ -1174,6 +1179,51 @@ begin_partition(WindowAggState *winstate)
*/
tuplestore_puttupleslot(winstate->buffer, winstate->first_part_slot);
winstate->spooled_rows++;
+
+ /*
+ * In GROUPS BETWEEN with values mode, we pre-compute the lengths of each
+ * window group by loading the partition and checking if each row is a peer
+ * of the succeeding row. The number of groups is stored in winobj->winGroupsCount
+ * and the number of rows in each group is stored in winobj->winGroupLen.
+ */
+ if (winstate->frameOptions & FRAMEOPTION_GROUPS_BETWEEN)
+ {
+ int64 i,
+ partitionSize;
+ int64 *lenptr;
+ int64 groupLenSize = 16;
+ bool peers;
+
+ winstate->agg_winobj->winGroupsCount = 1;
+ winstate->agg_winobj->winGroupLen = palloc0(sizeof(int64) * groupLenSize);
+ lenptr = winstate->agg_winobj->winGroupLen;
+ *lenptr = 1;
+ partitionSize = WinGetPartitionRowCount(winstate->agg_winobj);
+
+ for (i = 0; i < partitionSize - 1; i++)
+ {
+ peers = WinRowsArePeers(winstate->agg_winobj, i, i+1);
+ if (peers)
+ (*lenptr)++;
+ else
+ {
+ winstate->agg_winobj->winGroupsCount++;
+ if (winstate->agg_winobj->winGroupsCount > groupLenSize)
+ {
+ int64 prevSize = groupLenSize;
+
+ groupLenSize *= 2;
+ winstate->agg_winobj->winGroupLen = repalloc(winstate->agg_winobj->winGroupLen, sizeof(int64) * groupLenSize);
+ lenptr = winstate->agg_winobj->winGroupLen;
+ lenptr += prevSize;
+ }
+ else
+ lenptr++;
+
+ *lenptr = 1;
+ }
+ }
+ }
}
/*
@@ -1284,6 +1334,71 @@ release_partition(WindowAggState *winstate)
tuplestore_end(winstate->buffer);
winstate->buffer = NULL;
winstate->partition_spooled = false;
+
+ if (winstate->frameOptions & FRAMEOPTION_GROUPS_BETWEEN &&
+ winstate->agg_winobj->winGroupsCount > 0)
+ {
+ pfree(winstate->agg_winobj->winGroupLen);
+ winstate->agg_winobj->winGroupsCount = 0;
+ }
+}
+
+/*
+ * row_is_in_group
+ * Determine whether a row is in range when in GROUPS BETWEEN with values
+ * mode.
+ *
+ * Compares the current position to the slot position and checks if they are
+ * within the specified window group offset.
+ */
+static bool row_is_in_group(WindowObject winobj, int64 currpos, int64 slotpos,
+ int64 offset, bool preceding, bool end)
+{
+ int64 i,
+ len = 0,
+ currGroup = 0,
+ slotGroup = 0;
+ int64 *lenptr = winobj->winGroupLen;
+
+ if (preceding)
+ offset = -offset;
+ /*
+ * Calculate the currpos window group, then the slotpos window group. If
+ * the slotpos group is outside of the offset bounds, return false.
+ */
+ for (i = 0; i < winobj->winGroupsCount; i++, lenptr++)
+ {
+ len += *lenptr;
+ if (len > currpos)
+ {
+ currGroup = i;
+ break;
+ }
+ }
+ lenptr = winobj->winGroupLen;
+ len = 0;
+ for (i = 0; i < winobj->winGroupsCount; i++, lenptr++)
+ {
+ len += *lenptr;
+ if (len > slotpos)
+ {
+ slotGroup = i;
+ break;
+ }
+ }
+
+ currGroup += offset;
+ if (end)
+ {
+ if (slotGroup > currGroup)
+ return false;
+ }
+ else
+ {
+ if (slotGroup < currGroup)
+ return false;
+ }
+ return true;
}
/*
@@ -1445,7 +1560,7 @@ row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
if (pos < winstate->currentpos)
return -1;
}
- else if (frameOptions & FRAMEOPTION_RANGE)
+ else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS))
{
/* preceding row that is not peer is out of frame */
if (pos < winstate->currentpos &&
@@ -1483,6 +1598,15 @@ row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
slotval, winstate->startOffsetValue, preceding, false))
return 0;
}
+ else if (frameOptions & FRAMEOPTION_GROUPS)
+ {
+ bool preceding;
+
+ preceding = (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) != 0 ? true : false;
+ if (!row_is_in_group(winstate->agg_winobj,
+ winstate->currentpos, pos, offset, preceding, false))
+ return 0;
+ }
else
Assert(false);
}
@@ -1496,7 +1620,7 @@ row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
if (pos > winstate->currentpos)
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 &&
@@ -1533,6 +1657,15 @@ row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
slotval, winstate->endOffsetValue, preceding, true))
return -1;
}
+ else if (frameOptions & FRAMEOPTION_GROUPS)
+ {
+ bool preceding;
+
+ preceding = (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) != 0 ? true : false;
+ if (!row_is_in_group(winstate->agg_winobj,
+ winstate->currentpos, pos, offset, preceding, true))
+ return -1;
+ }
else
Assert(false);
}
@@ -1542,7 +1675,12 @@ row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
{
if (pos == winstate->currentpos)
return 0;
- } else if (frameOptions & FRAMEOPTION_EXCLUDE_TIES)
+ } else if (frameOptions & FRAMEOPTION_EXCLUDE_GROUP)
+ {
+ if (are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
+ return 0;
+ }
+ else if (frameOptions & FRAMEOPTION_EXCLUDE_TIES)
{
if ((pos != winstate->currentpos) &&
are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
@@ -1586,7 +1724,7 @@ 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;
@@ -1646,7 +1784,7 @@ update_frameheadpos(WindowObject winobj, TupleTableSlot *slot)
}
winstate->framehead_valid = true;
}
- else if (frameOptions & FRAMEOPTION_RANGE)
+ else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS))
{
winstate->frameheadpos = 0;
winstate->framehead_valid = true;
@@ -1692,7 +1830,7 @@ update_frametailpos(WindowObject winobj, TupleTableSlot *slot)
winstate->frametailpos = winstate->currentpos;
winstate->frametail_valid = true;
}
- else if (frameOptions & FRAMEOPTION_RANGE)
+ else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS))
{
int64 ftnext;
@@ -1748,7 +1886,7 @@ update_frametailpos(WindowObject winobj, TupleTableSlot *slot)
}
winstate->frametail_valid = true;
}
- else if (frameOptions & FRAMEOPTION_RANGE)
+ else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS))
{
winstate->frametailpos = -1;
winstate->frametail_valid = true;
@@ -1809,7 +1947,7 @@ 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);
@@ -1834,7 +1972,7 @@ 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);
@@ -1852,6 +1990,7 @@ ExecWindowAgg(PlanState *pstate)
{
/* Initialize for first partition and set current row = 0 */
begin_partition(winstate);
+
/* If there are no input rows, we'll detect that and exit below */
}
else
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d54fbff545..917341f1c5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -632,7 +632,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
@@ -725,8 +725,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* 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;
+ * RANGE, ROWS, or GROUPS to support opt_existing_window_name; and for RANGE, ROWS
+ * or 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 +746,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 '*' '/' '%'
@@ -13981,7 +13981,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
@@ -14001,9 +14001,6 @@ 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:2011 frame_clause grammar.
- * We don't support GROUPS yet.
*/
opt_frame_clause:
RANGE frame_extent opt_window_exclusion_clause
@@ -14029,6 +14026,20 @@ opt_frame_clause:
n->frameOptions |= n2->frameOptions;
$$ = n;
}
+ | GROUPS frame_extent opt_window_exclusion_clause
+ {
+ WindowDef *n = $2;
+ WindowDef *n2 = $3;
+ n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_GROUPS;
+ if ((n->frameOptions & (FRAMEOPTION_START_VALUE_PRECEDING |
+ FRAMEOPTION_END_VALUE_PRECEDING)) ||
+ (n->frameOptions & (FRAMEOPTION_START_VALUE_FOLLOWING |
+ FRAMEOPTION_END_VALUE_FOLLOWING)))
+ n->frameOptions |= FRAMEOPTION_GROUPS_BETWEEN;
+ if (n2 != NULL)
+ n->frameOptions |= n2->frameOptions;
+ $$ = n;
+ }
| /*EMPTY*/
{
WindowDef *n = makeNode(WindowDef);
@@ -14150,6 +14161,12 @@ opt_window_exclusion_clause:
n->frameOptions = FRAMEOPTION_EXCLUDE_CURRENT;
$$ = n;
}
+ | EXCLUDE GROUP_P
+ {
+ WindowDef *n = makeNode(WindowDef);
+ n->frameOptions = FRAMEOPTION_EXCLUDE_GROUP;
+ $$ = n;
+ }
| EXCLUDE TIES
{
WindowDef *n = makeNode(WindowDef);
@@ -15026,6 +15043,7 @@ unreserved_keyword:
| GENERATED
| GLOBAL
| GRANTED
+ | GROUPS
| HANDLER
| HEADER_P
| HOLD
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 4c4f4cdc3d..0922a9a7c3 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 73c80bbf1c..7d92a8edfd 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -3588,6 +3588,17 @@ transformFrameOffset(ParseState *pstate, int frameOptions, Node *clause)
else
node = coerce_to_specific_type(pstate, node, INT8OID, constructName);
}
+ 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
{
Assert(false);
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 29f9da796f..a1f44b0e7a 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 2f20516e76..9b8413bf26 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2218,6 +2218,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/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 867a4a26a8..85dc7d654c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5874,6 +5874,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)
@@ -5916,6 +5918,8 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
}
if (wc->frameOptions & FRAMEOPTION_EXCLUDE_CURRENT)
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 ");
else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_NO_OTHERS)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5cfce277cc..626c1864a9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -499,26 +499,28 @@ 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_RANGE_BETWEEN 0x04000 /* RANGE BETWEEN with values */
-#define FRAMEOPTION_EXCLUDE_CURRENT 0x08000 /* exclude current row */
-#define FRAMEOPTION_EXCLUDE_TIES 0x10000 /* exclude ties */
-#define FRAMEOPTION_EXCLUDE_NO_OTHERS 0x20000 /* exclude no others */
+#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_VALUE_PRECEDING 0x00800 /* start is V. P. */
+#define FRAMEOPTION_END_VALUE_PRECEDING 0x01000 /* end is V. P. */
+#define FRAMEOPTION_START_VALUE_FOLLOWING 0x02000 /* start is V. F. */
+#define FRAMEOPTION_END_VALUE_FOLLOWING 0x04000 /* end is V. F. */
+#define FRAMEOPTION_RANGE_BETWEEN 0x08000 /* RANGE BETWEEN with values */
+#define FRAMEOPTION_GROUPS_BETWEEN 0x010000 /* GROUPS BETWEEN with values */
+#define FRAMEOPTION_EXCLUDE_CURRENT 0x020000 /* exclude current row */
+#define FRAMEOPTION_EXCLUDE_TIES 0x040000 /* exclude ties */
+#define FRAMEOPTION_EXCLUDE_NO_OTHERS 0x080000 /* exclude no others */
+#define FRAMEOPTION_EXCLUDE_GROUP 0x100000 /* exclude group */
#define FRAMEOPTION_START_VALUE \
(FRAMEOPTION_START_VALUE_PRECEDING | FRAMEOPTION_START_VALUE_FOLLOWING)
@@ -530,7 +532,8 @@ typedef struct WindowDef
FRAMEOPTION_END_CURRENT_ROW)
#define FRAMEOPTION_EXCLUSION \
- (FRAMEOPTION_EXCLUDE_CURRENT | FRAMEOPTION_EXCLUDE_TIES)
+ (FRAMEOPTION_EXCLUDE_CURRENT | FRAMEOPTION_EXCLUDE_TIES | \
+ FRAMEOPTION_EXCLUDE_GROUP)
/*
* RangeSubselect - subquery appearing in a FROM clause
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 87c4cffba5..b51f86fd28 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)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 565bb3dc6c..1141d8aa2f 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 */
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index d8ae3807a2..b8220d44d2 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -858,6 +858,23 @@ FROM tenk1 WHERE unique1 < 10;
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;
@@ -960,6 +977,23 @@ FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
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);
@@ -1069,6 +1103,32 @@ 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;
+ 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
@@ -1119,6 +1179,32 @@ 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 groups 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 GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE NO OTHERS) AS sum_rows+
+ FROM generate_series(1, 10) i(i);
+(1 row)
+
-- RANGE BETWEEN with values tests
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding),
unique1, four
@@ -1171,6 +1257,23 @@ FROM tenk1 WHERE unique1 < 10;
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;
@@ -1205,6 +1308,23 @@ FROM tenk1 WHERE unique1 < 10;
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;
@@ -1272,6 +1392,22 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
(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
-------+--------+-------------
@@ -1320,6 +1456,22 @@ select sum(salary) over (order by enroll_time range between '1 hour'::interval p
(10 rows)
select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following
+ exclude group), salary, enroll_time from empsalary;
+ sum | salary | enroll_time
+-------+--------+-------------
+ 8500 | 5200 | 11:00:00
+ 13500 | 5000 | 12:00:00
+ 17900 | 3500 | 13:00:00
+ 11600 | 4800 | 14:00:00
+ 9300 | 3900 | 15:00:00
+ 9300 | 4200 | 15:00:00
+ 10800 | 4500 | 17:00:00
+ 15700 | 4800 | 18:00:00
+ 10000 | 6000 | 19:00:00
+ 6000 | 5200 | 20:00:00
+(10 rows)
+
+select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following
exclude ties), salary, enroll_time from empsalary;
sum | salary | enroll_time
-------+--------+-------------
@@ -1368,6 +1520,22 @@ select sum(salary) over (order by enroll_timetz range between '1 hour'::interval
(10 rows)
select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following
+ exclude group), salary, enroll_timetz from empsalary;
+ sum | salary | enroll_timetz
+-------+--------+---------------
+ 8500 | 5200 | 11:00:00+01
+ 13500 | 5000 | 12:00:00+01
+ 17900 | 3500 | 13:00:00+01
+ 11600 | 4800 | 14:00:00+01
+ 9300 | 3900 | 15:00:00+01
+ 9300 | 4200 | 15:00:00+01
+ 10800 | 4500 | 17:00:00+01
+ 15700 | 4800 | 18:00:00+01
+ 10000 | 6000 | 19:00:00+01
+ 6000 | 5200 | 20:00:00+01
+(10 rows)
+
+select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following
exclude ties), salary, enroll_timetz from empsalary;
sum | salary | enroll_timetz
-------+--------+---------------
@@ -1416,6 +1584,22 @@ select sum(salary) over (order by enroll_interval range between '1 year'::interv
(10 rows)
select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following
+ exclude group), salary, enroll_interval from empsalary;
+ sum | salary | enroll_interval
+-------+--------+-----------------
+ 8500 | 5200 | @ 1 year
+ 13500 | 5000 | @ 2 years
+ 17900 | 3500 | @ 3 years
+ 11600 | 4800 | @ 4 years
+ 9300 | 3900 | @ 5 years
+ 9300 | 4200 | @ 5 years
+ 10800 | 4500 | @ 7 years
+ 15700 | 4800 | @ 8 years
+ 10000 | 6000 | @ 9 years
+ 6000 | 5200 | @ 10 years
+(10 rows)
+
+select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_interval from empsalary;
sum | salary | enroll_interval
-------+--------+-----------------
@@ -1464,6 +1648,22 @@ select sum(salary) over (order by enroll_timestamptz range between '1 year'::int
(10 rows)
select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following
+ exclude group), salary, enroll_timestamptz from empsalary;
+ sum | salary | enroll_timestamptz
+-------+--------+------------------------------
+ 13300 | 5200 | Thu Oct 19 10:23:54 2000 PDT
+ 13900 | 5000 | Fri Oct 19 10:23:54 2001 PDT
+ 13900 | 3500 | Fri Oct 19 10:23:54 2001 PDT
+ 16600 | 4800 | Sat Oct 19 10:23:54 2002 PDT
+ 13500 | 3900 | Sun Oct 19 10:23:54 2003 PDT
+ 13200 | 4200 | Tue Oct 19 10:23:54 2004 PDT
+ 15000 | 4500 | Wed Oct 19 10:23:54 2005 PDT
+ 15700 | 4800 | Thu Oct 19 10:23:54 2006 PDT
+ 10000 | 6000 | Fri Oct 19 10:23:54 2007 PDT
+ 6000 | 5200 | Sun Oct 19 10:23:54 2008 PDT
+(10 rows)
+
+select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_timestamptz from empsalary;
sum | salary | enroll_timestamptz
-------+--------+------------------------------
@@ -1512,6 +1712,22 @@ select sum(salary) over (order by enroll_timestamp range between '1 year'::inter
(10 rows)
select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following
+ exclude group), salary, enroll_timestamp from empsalary;
+ sum | salary | enroll_timestamp
+-------+--------+--------------------------
+ 13300 | 5200 | Thu Oct 19 10:23:54 2000
+ 13900 | 5000 | Fri Oct 19 10:23:54 2001
+ 13900 | 3500 | Fri Oct 19 10:23:54 2001
+ 16600 | 4800 | Sat Oct 19 10:23:54 2002
+ 13500 | 3900 | Sun Oct 19 10:23:54 2003
+ 13200 | 4200 | Tue Oct 19 10:23:54 2004
+ 15000 | 4500 | Wed Oct 19 10:23:54 2005
+ 15700 | 4800 | Thu Oct 19 10:23:54 2006
+ 10000 | 6000 | Fri Oct 19 10:23:54 2007
+ 6000 | 5200 | Sun Oct 19 10:23:54 2008
+(10 rows)
+
+select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_timestamp from empsalary;
sum | salary | enroll_timestamp
-------+--------+--------------------------
@@ -1592,6 +1808,22 @@ select sum(salary) over(order by enroll_date range between unbounded preceding a
(10 rows)
select sum(salary) over(order by enroll_date range between unbounded 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
+ 43600 | 3500 | 12-10-2007
+ 38400 | 4500 | 01-01-2008
+ 38400 | 4200 | 01-01-2008
+(10 rows)
+
+select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude ties), salary, enroll_date from empsalary;
sum | salary | enroll_date
-------+--------+-------------
@@ -1637,6 +1869,475 @@ select max(enroll_date) over (order by salary range between '1 year'::interval p
ERROR: Offsets must be an integral
LINE 1: select max(enroll_date) over (order by salary range between ...
^
+-- 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)
+
+-- Show differences in values mode 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 cd25200501..87346fcf0f 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -202,6 +202,10 @@ SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude curre
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;
@@ -226,6 +230,10 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
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 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);
@@ -260,6 +268,14 @@ 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;
@@ -274,6 +290,14 @@ 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
+ exclude no others) as sum_rows FROM generate_series(1, 10) i;
+
+SELECT * FROM v_window;
+
+SELECT pg_get_viewdef('v_window');
+
-- RANGE BETWEEN with values tests
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding),
unique1, four
@@ -287,6 +311,10 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
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;
@@ -295,6 +323,10 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::i
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;
@@ -310,6 +342,9 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
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 sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following),
@@ -319,6 +354,9 @@ select sum(salary) over (order by enroll_time range between '1 hour'::interval p
exclude current row), salary, enroll_time from empsalary;
select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following
+ exclude group), salary, enroll_time from empsalary;
+
+select sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following
exclude ties), salary, enroll_time from empsalary;
select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following),
@@ -328,6 +366,9 @@ select sum(salary) over (order by enroll_timetz range between '1 hour'::interval
exclude current row), salary, enroll_timetz from empsalary;
select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following
+ exclude group), salary, enroll_timetz from empsalary;
+
+select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following
exclude ties), salary, enroll_timetz from empsalary;
select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following),
@@ -337,6 +378,9 @@ select sum(salary) over (order by enroll_interval range between '1 year'::interv
exclude current row), salary, enroll_interval from empsalary;
select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following
+ exclude group), salary, enroll_interval from empsalary;
+
+select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_interval from empsalary;
select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following),
@@ -346,6 +390,9 @@ select sum(salary) over (order by enroll_timestamptz range between '1 year'::int
exclude current row), salary, enroll_timestamptz from empsalary;
select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following
+ exclude group), salary, enroll_timestamptz from empsalary;
+
+select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_timestamptz from empsalary;
select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following),
@@ -355,6 +402,9 @@ select sum(salary) over (order by enroll_timestamp range between '1 year'::inter
exclude current row), salary, enroll_timestamp from empsalary;
select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following
+ exclude group), salary, enroll_timestamp from empsalary;
+
+select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_timestamp from empsalary;
select sum(salary) over (order by enroll_timestamp range between current row and '2 years'::interval following),
@@ -370,6 +420,9 @@ select sum(salary) over(order by enroll_date range between unbounded preceding a
exclude current row), salary, enroll_date from empsalary;
select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
+ exclude group), salary, enroll_date from empsalary;
+
+select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
exclude ties), salary, enroll_date from empsalary;
-- RANGE BETWEEN with values negative tests
@@ -394,6 +447,118 @@ select max(enroll_date) over (order by salary range between 1 preceding and -2 f
select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_timestamp 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;
+
+-- Show differences in values mode 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;