On Mon, Nov 27, 2017 at 04:55:17PM +0000, Oliver Ford wrote:
> On Mon, Nov 27, 2017 at 4:40 PM, Erik Rijkers <e...@xs4all.nl> wrote:
> > On 2017-11-27 17:34, Erik Rijkers wrote:
> >>
> >> On 2017-11-27 16:01, Oliver Ford wrote:
> >>>
> >>> Attached is it in bare diff form.
> >>
> >>
> >> [0001-window-frame-v3.patch]
> >>
> >> Thanks, that did indeed fix it:
> >>
> >> make && make check now ok.
> >>
> >> There were errors in the doc build (unmatched tags); I fixed them in
> >> the attached doc-patch (which should go on top of yours).
> >
> >
> > 0001-window-frame-v3-fixtags.diff
> >
> > now attached, I hope...
> >
>
> Cheers here's v4 with the correct docs.
I've taken the liberty of adding float8, somewhat mechanically. Do
the docs need some change, assuming that addition is useful?
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
>From 9144e33c2bb3c6acccfe27bfde8155850faacd87 Mon Sep 17 00:00:00 2001
From: David Fetter <da...@fetter.org>
Date: Mon, 27 Nov 2017 23:27:04 -0500
Subject: [PATCH] window frame v5
---
doc/src/sgml/ref/select.sgml | 39 +-
doc/src/sgml/syntax.sgml | 36 +-
src/backend/executor/nodeWindowAgg.c | 296 +++++++++++--
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 2 +
src/backend/nodes/readfuncs.c | 2 +
src/backend/optimizer/plan/createplan.c | 10 +-
src/backend/parser/gram.y | 61 ++-
src/backend/parser/parse_clause.c | 84 +++-
src/backend/utils/adt/ruleutils.c | 6 +
src/include/nodes/execnodes.h | 1 +
src/include/nodes/parsenodes.h | 9 +
src/include/nodes/plannodes.h | 1 +
src/include/parser/kwlist.h | 2 +
src/test/regress/expected/window.out | 713 +++++++++++++++++++++++++++++++-
src/test/regress/sql/window.sql | 192 ++++++++-
17 files changed, 1340 insertions(+), 117 deletions(-)
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 8a3e86b6db..40c146ca40 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>
-{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable>
+{ 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> ]
</synopsis>
where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be
@@ -874,6 +874,15 @@ CURRENT ROW
UNBOUNDED FOLLOWING
</synopsis>
+ and the optional <replaceable class="parameter">frame_exclusion_clause</replaceable> can be
+ one of
+
+<synopsis>
+EXCLUDE CURRENT ROW
+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>,
@@ -900,13 +909,25 @@ UNBOUNDED FOLLOWING
<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.
+ <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
+ <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
+ 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
+ <replaceable>value</replaceable> must be an interval. In both modes, the value must not be null or
+ negative; but it can be zero, which just selects the current row itself.
+ </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.
</para>
<para>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index a938a21334..f711c99987 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>
-{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable>
+{ 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> ]
</synopsis>
where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be
one of
@@ -1817,6 +1817,12 @@ CURRENT ROW
<replaceable>value</replaceable> FOLLOWING
UNBOUNDED FOLLOWING
</synopsis>
+ where the optional <replaceable>frame_exclusion_clause</replaceable> can be one of
+<synopsis>
+EXCLUDE CURRENT ROW
+EXCLUDE TIES
+EXCLUDE NO OTHERS
+</synopsis>
</para>
<para>
@@ -1882,13 +1888,25 @@ UNBOUNDED FOLLOWING
<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
- 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.
+ <replaceable>value</replaceable> <literal>FOLLOWING</literal> cases, when used
+ in <literal>ROWS</literal> mode, indicate that the frame starts or ends the specified
+ number of rows before or after the current row. In <literal>ROWS</literal> mode,
+ <replaceable>value</replaceable> must be an integer expression not containing any variables,
+ aggregate functions, or window functions.
+ When used in <literal>RANGE</literal> mode, they indicate that the frame starts or ends when the value of
+ each row's ORDER BY column is within the start value and end value bounds. In <literal>RANGE</literal> mode,
+ <replaceable>value</replaceable> can be either an integer expression or a date/time interval.
+ 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 <replaceable>value</replaceable> must be an interval. In both modes,
+ the value must not be null or negative; but it can be zero, which just selects the current row.
+ </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.
</para>
<para>
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 02868749f6..088b0c580d 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -37,6 +37,7 @@
#include "catalog/objectaccess.h"
#include "catalog/pg_aggregate.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_type.h"
#include "executor/executor.h"
#include "executor/nodeWindowAgg.h"
#include "miscadmin.h"
@@ -180,7 +181,9 @@ 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 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,
TupleTableSlot *slot);
static void update_frameheadpos(WindowObject winobj, TupleTableSlot *slot);
static void update_frametailpos(WindowObject winobj, TupleTableSlot *slot);
@@ -737,15 +740,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 +770,8 @@ 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 are in RANGE 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
*
* Note that we don't strictly need to restart in the last case, but if
@@ -780,6 +786,8 @@ eval_windowaggregates(WindowAggState *winstate)
if (winstate->currentpos == 0 ||
(winstate->aggregatedbase != winstate->frameheadpos &&
!OidIsValid(peraggstate->invtransfn_oid)) ||
+ winstate->frameOptions & FRAMEOPTION_RANGE_BETWEEN ||
+ winstate->frameOptions & FRAMEOPTION_EXCLUSION ||
winstate->aggregatedupto <= winstate->frameheadpos)
{
peraggstate->restart = true;
@@ -920,6 +928,8 @@ eval_windowaggregates(WindowAggState *winstate)
*/
for (;;)
{
+ int ret;
+
/* Fetch next row if we didn't already */
if (TupIsNull(agg_row_slot))
{
@@ -928,9 +938,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.
+ */
+ ret = row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot);
+ if (ret == -1)
break;
+ else if (ret == 0)
+ goto next_tuple;
/* Set tuple context for evaluation of aggregate arguments */
winstate->tmpcontext->ecxt_outertuple = agg_row_slot;
@@ -951,6 +967,7 @@ eval_windowaggregates(WindowAggState *winstate)
peraggstate);
}
+ next_tuple:
/* Reset per-input-tuple context after each tuple */
ResetExprContext(winstate->tmpcontext);
@@ -1113,7 +1130,9 @@ begin_partition(WindowAggState *winstate)
int readptr_flags = 0;
/* If the frame head is potentially movable ... */
- if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING))
+ if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) ||
+ winstate->frameOptions & (FRAMEOPTION_RANGE_BETWEEN) ||
+ winstate->frameOptions & (FRAMEOPTION_EXCLUSION))
{
/* ... create a mark pointer to track the frame head */
agg_winobj->markptr = tuplestore_alloc_read_pointer(winstate->buffer, 0);
@@ -1268,6 +1287,169 @@ release_partition(WindowAggState *winstate)
}
/*
+ * row_is_in_range
+ * Determine whether a row is in range when in RANGE BETWEEN with values
+ * mode.
+ *
+ * Performs either an integer or a date/time comparison.
+ */
+static bool
+row_is_in_range(Oid sortColOid, Datum currval, Datum slotval, Datum offset,
+ bool preceding, bool end)
+{
+ Oid intOid = INT8OID;
+ Oid floatOid = FLOAT8OID;
+
+ /* If ORDER BY IS an int, perform int calculation */
+ if (can_coerce_type(1, &sortColOid, &intOid, COERCION_IMPLICIT))
+ {
+ int64 currint,
+ slotint,
+ offsetint;
+ currint = DatumGetInt64(currval);
+ slotint = DatumGetInt64(slotval);
+ offsetint = DatumGetInt64(offset);
+
+ /*
+ * SQL:2011 defines the range offsets as unsigned, so reject values
+ * less than 0.
+ */
+ if (offsetint < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("RANGE offsets cannot be negative. invalid value %zd", offsetint)));
+ if (preceding)
+ offsetint = -offsetint;
+
+ if (end)
+ {
+ if (slotint > currint + offsetint)
+ return false;
+ }
+ else
+ {
+ if (slotint < currint + offsetint)
+ return false;
+ }
+ }
+ /* If ORDER BY IS a float, perform float calculation */
+ else if (can_coerce_type(1, &sortColOid, &floatOid, COERCION_IMPLICIT))
+ {
+ float8 currfloat,
+ slotfloat,
+ offsetfloat;
+ currfloat = DatumGetFloat8(currval);
+ slotfloat = DatumGetFloat8(slotval);
+ offsetfloat = DatumGetFloat8(offset);
+
+ /*
+ * SQL:2011 defines the range offsets as unsigned, so reject values
+ * less than 0.
+ */
+ if (offsetfloat < 0.0)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("RANGE offsets cannot be negative. invalid value %f", offsetfloat)));
+ if (preceding)
+ offsetfloat = -offsetfloat;
+
+ if (end)
+ {
+ if (slotfloat > currfloat + offsetfloat)
+ return false;
+ }
+ else
+ {
+ if (slotfloat < currfloat + offsetfloat)
+ return false;
+ }
+ }
+ else
+ {
+ /* ORDER BY is a date/time value, so perform interval calculation */
+ Datum val;
+ PGFunction intervalFunc,
+ comparatorFunc;
+
+ switch (sortColOid)
+ {
+ case TIMESTAMPOID:
+ if (preceding)
+ intervalFunc = timestamp_mi_interval;
+ else
+ intervalFunc = timestamp_pl_interval;
+ if (end)
+ comparatorFunc = timestamp_gt;
+ else
+ comparatorFunc = timestamp_lt;
+ break;
+ case TIMESTAMPTZOID:
+ if (preceding)
+ intervalFunc = timestamptz_mi_interval;
+ else
+ intervalFunc = timestamptz_pl_interval;
+ if (end)
+ comparatorFunc = timestamp_gt;
+ else
+ comparatorFunc = timestamp_lt;
+ break;
+ case INTERVALOID:
+ if (preceding)
+ intervalFunc = interval_mi;
+ else
+ intervalFunc = interval_pl;
+ if (end)
+ comparatorFunc = interval_gt;
+ else
+ comparatorFunc = interval_lt;
+ break;
+ case TIMETZOID:
+ if (preceding)
+ intervalFunc = timetz_mi_interval;
+ else
+ intervalFunc = timetz_pl_interval;
+ if (end)
+ comparatorFunc = timetz_gt;
+ else
+ comparatorFunc = timetz_lt;
+ break;
+ case DATEOID:
+ slotval = DirectFunctionCall1(date_timestamp, slotval);
+ if (preceding)
+ intervalFunc = date_mi_interval;
+ else
+ intervalFunc = date_pl_interval;
+ if (end)
+ comparatorFunc = timestamp_gt;
+ else
+ comparatorFunc = timestamp_lt;
+ break;
+ case TIMEOID:
+ currval = DirectFunctionCall1(time_interval, currval);
+ slotval = DirectFunctionCall1(time_interval, slotval);
+ if (preceding)
+ intervalFunc = interval_mi;
+ else
+ intervalFunc = interval_pl;
+ if (end)
+ comparatorFunc = interval_gt;
+ else
+ comparatorFunc = interval_lt;
+ break;
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("ORDER BY column in window function must be an integral or date/time")));
+ }
+ val = DirectFunctionCall2(intervalFunc, currval, offset);
+ if (DirectFunctionCall2(comparatorFunc, slotval, val))
+ return false;
+ }
+
+ return true;
+}
+
+/*
* row_is_in_frame
* Determine whether a row is in the current row's window frame according
* to our window framing rule
@@ -1275,12 +1457,16 @@ 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
*/
-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 */
@@ -1290,35 +1476,45 @@ row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
{
/* rows before current row are out of frame */
if (pos < winstate->currentpos)
- return false;
+ return -1;
}
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;
+ return -1;
}
else
Assert(false);
}
else if (frameOptions & FRAMEOPTION_START_VALUE)
{
+ int64 offset = DatumGetInt64(winstate->startOffsetValue);
+
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;
+ return 0;
}
else if (frameOptions & FRAMEOPTION_RANGE)
{
- /* parser should have rejected this */
- elog(ERROR, "window frame with value offset is not implemented");
+ bool isnull,
+ preceding;
+ Datum slotval,
+ currval;
+
+ slotval = slot_getattr(slot, 1, &isnull);
+ Assert(!isnull);
+ currval = slot_getattr(winstate->ss.ss_ScanTupleSlot, 1, &isnull);
+ Assert(!isnull);
+ preceding = (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING) != 0 ? true : false;
+ if (!row_is_in_range(winstate->firstSortColOid, currval,
+ slotval, winstate->startOffsetValue, preceding, false))
+ return 0;
}
else
Assert(false);
@@ -1331,42 +1527,63 @@ row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
{
/* rows after current row are out of frame */
if (pos > winstate->currentpos)
- return false;
+ return -1;
}
else if (frameOptions & FRAMEOPTION_RANGE)
{
/* 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)
{
+ int64 offset = DatumGetInt64(winstate->endOffsetValue);
if (frameOptions & FRAMEOPTION_ROWS)
{
- int64 offset = DatumGetInt64(winstate->endOffsetValue);
-
/* rows after current row + offset are out of frame */
if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING)
offset = -offset;
-
if (pos > winstate->currentpos + offset)
- return false;
+ return -1;
}
else if (frameOptions & FRAMEOPTION_RANGE)
{
- /* parser should have rejected this */
- elog(ERROR, "window frame with value offset is not implemented");
+ bool isnull,
+ preceding;
+ Datum slotval,
+ currval;
+
+ slotval = slot_getattr(slot, 1, &isnull);
+ Assert(!isnull);
+ currval = slot_getattr(winstate->ss.ss_ScanTupleSlot, 1, &isnull);
+ Assert(!isnull);
+ preceding = (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING) != 0 ? true : false;
+ if (!row_is_in_range(winstate->firstSortColOid, currval,
+ slotval, winstate->endOffsetValue, preceding, true))
+ return -1;
}
else
Assert(false);
}
+ /* Check exclusion clause */
+ if (frameOptions & FRAMEOPTION_EXCLUDE_CURRENT)
+ {
+ if (pos == winstate->currentpos)
+ return 0;
+ } else if (frameOptions & FRAMEOPTION_EXCLUDE_TIES)
+ {
+ if ((pos != winstate->currentpos) &&
+ are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
+ return 0;
+ }
+
/* If we get here, it's in frame */
- return true;
+ return 1;
}
/*
@@ -1464,8 +1681,8 @@ 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");
+ winstate->frameheadpos = 0;
+ winstate->framehead_valid = true;
}
else
Assert(false);
@@ -1545,14 +1762,12 @@ update_frametailpos(WindowObject winobj, TupleTableSlot *slot)
}
else if (frameOptions & FRAMEOPTION_END_VALUE)
{
+ /* In ROWS mode, bound is physically n before/after current */
+ int64 offset = DatumGetInt64(winstate->endOffsetValue);
+ if (frameOptions & FRAMEOPTION_END_VALUE_PRECEDING)
+ offset = -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)
- offset = -offset;
-
winstate->frametailpos = winstate->currentpos + offset;
/* smallest allowable value of frametailpos is -1 */
if (winstate->frametailpos < 0)
@@ -1568,8 +1783,8 @@ update_frametailpos(WindowObject winobj, TupleTableSlot *slot)
}
else if (frameOptions & FRAMEOPTION_RANGE)
{
- /* parser should have rejected this */
- elog(ERROR, "window frame with value offset is not implemented");
+ winstate->frametailpos = -1;
+ winstate->frametail_valid = true;
}
else
Assert(false);
@@ -1989,6 +2204,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
agg_winobj->readptr = -1;
winstate->agg_winobj = agg_winobj;
}
+ winstate->firstSortColOid = node->firstSortColOid;
/* copy frame options to state node for easy access */
winstate->frameOptions = node->frameOptions;
@@ -2736,7 +2952,7 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
WindowAggState *winstate;
ExprContext *econtext;
TupleTableSlot *slot;
- bool gottuple;
+ int gottuple;
int64 abs_pos;
Assert(WindowObjectIsValid(winobj));
@@ -2767,7 +2983,7 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
if (gottuple)
gottuple = row_is_in_frame(winstate, abs_pos, slot);
- if (!gottuple)
+ if (gottuple != 1)
{
if (isout)
*isout = true;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index d9ff8a7e51..59cfe0c767 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1007,6 +1007,7 @@ _copyWindowAgg(const WindowAgg *from)
COPY_POINTER_FIELD(ordColIdx, from->ordNumCols * sizeof(AttrNumber));
COPY_POINTER_FIELD(ordOperators, from->ordNumCols * sizeof(Oid));
}
+ COPY_SCALAR_FIELD(firstSortColOid);
COPY_SCALAR_FIELD(frameOptions);
COPY_NODE_FIELD(startOffset);
COPY_NODE_FIELD(endOffset);
@@ -2405,6 +2406,7 @@ _copyWindowClause(const WindowClause *from)
COPY_STRING_FIELD(refname);
COPY_NODE_FIELD(partitionClause);
COPY_NODE_FIELD(orderClause);
+ COPY_SCALAR_FIELD(firstSortColOid);
COPY_SCALAR_FIELD(frameOptions);
COPY_NODE_FIELD(startOffset);
COPY_NODE_FIELD(endOffset);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 2866fd7b4a..b0a453f958 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2720,6 +2720,7 @@ _equalWindowClause(const WindowClause *a, const WindowClause *b)
COMPARE_STRING_FIELD(refname);
COMPARE_NODE_FIELD(partitionClause);
COMPARE_NODE_FIELD(orderClause);
+ COMPARE_SCALAR_FIELD(firstSortColOid);
COMPARE_SCALAR_FIELD(frameOptions);
COMPARE_NODE_FIELD(startOffset);
COMPARE_NODE_FIELD(endOffset);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index c97ee24ade..1cee88001d 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -835,6 +835,7 @@ _outWindowAgg(StringInfo str, const WindowAgg *node)
for (i = 0; i < node->ordNumCols; i++)
appendStringInfo(str, " %u", node->ordOperators[i]);
+ WRITE_INT_FIELD(firstSortColOid);
WRITE_INT_FIELD(frameOptions);
WRITE_NODE_FIELD(startOffset);
WRITE_NODE_FIELD(endOffset);
@@ -2975,6 +2976,7 @@ _outWindowClause(StringInfo str, const WindowClause *node)
WRITE_STRING_FIELD(refname);
WRITE_NODE_FIELD(partitionClause);
WRITE_NODE_FIELD(orderClause);
+ WRITE_INT_FIELD(firstSortColOid);
WRITE_INT_FIELD(frameOptions);
WRITE_NODE_FIELD(startOffset);
WRITE_NODE_FIELD(endOffset);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 7eb67fc040..68ccbd6ff6 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -366,6 +366,7 @@ _readWindowClause(void)
READ_STRING_FIELD(refname);
READ_NODE_FIELD(partitionClause);
READ_NODE_FIELD(orderClause);
+ READ_INT_FIELD(firstSortColOid);
READ_INT_FIELD(frameOptions);
READ_NODE_FIELD(startOffset);
READ_NODE_FIELD(endOffset);
@@ -2134,6 +2135,7 @@ _readWindowAgg(void)
READ_INT_FIELD(ordNumCols);
READ_ATTRNUMBER_ARRAY(ordColIdx, local_node->ordNumCols);
READ_OID_ARRAY(ordOperators, local_node->ordNumCols);
+ READ_INT_FIELD(firstSortColOid);
READ_INT_FIELD(frameOptions);
READ_NODE_FIELD(startOffset);
READ_NODE_FIELD(endOffset);
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index d4454779ee..ca50ab8d43 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -259,8 +259,8 @@ static Material *make_material(Plan *lefttree);
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,
- Plan *lefttree);
+ Oid firstSortColOid, int frameOptions, Node *startOffset,
+ Node *endOffset, Plan *lefttree);
static Group *make_group(List *tlist, List *qual, int numGroupCols,
AttrNumber *grpColIdx, Oid *grpOperators,
Plan *lefttree);
@@ -2114,6 +2114,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
ordNumCols,
ordColIdx,
ordOperators,
+ wc->firstSortColOid,
wc->frameOptions,
wc->startOffset,
wc->endOffset,
@@ -6059,8 +6060,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,
- Plan *lefttree)
+ Oid firstSortColOid, int frameOptions, Node *startOffset,
+ Node *endOffset, Plan *lefttree)
{
WindowAgg *node = makeNode(WindowAgg);
Plan *plan = &node->plan;
@@ -6072,6 +6073,7 @@ make_windowagg(List *tlist, Index winref,
node->ordNumCols = ordNumCols;
node->ordColIdx = ordColIdx;
node->ordOperators = ordOperators;
+ node->firstSortColOid = firstSortColOid;
node->frameOptions = frameOptions;
node->startOffset = startOffset;
node->endOffset = endOffset;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c301ca465d..e3189b53c9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -569,7 +569,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> filter_clause
%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
+ opt_frame_clause frame_extent frame_bound opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
%type <ival> generated_when override_kind
@@ -656,7 +656,7 @@ 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 +676,7 @@ 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
@@ -13761,32 +13761,31 @@ 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.
+ * 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
+ RANGE frame_extent opt_window_exclusion_clause
{
WindowDef *n = $2;
+ WindowDef *n2 = $3;
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)));
+ if ((n->frameOptions & (FRAMEOPTION_START_VALUE_PRECEDING |
+ FRAMEOPTION_END_VALUE_PRECEDING)) ||
+ (n->frameOptions & (FRAMEOPTION_START_VALUE_FOLLOWING |
+ FRAMEOPTION_END_VALUE_FOLLOWING)))
+ n->frameOptions |= FRAMEOPTION_RANGE_BETWEEN;
+ if (n2 != NULL)
+ n->frameOptions |= n2->frameOptions;
$$ = n;
}
- | ROWS frame_extent
+ | ROWS frame_extent opt_window_exclusion_clause
{
WindowDef *n = $2;
+ WindowDef *n2 = $3;
n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_ROWS;
+ if (n2 != NULL)
+ n->frameOptions |= n2->frameOptions;
$$ = n;
}
| /*EMPTY*/
@@ -13903,6 +13902,28 @@ frame_bound:
}
;
+opt_window_exclusion_clause:
+ EXCLUDE CURRENT_P ROW
+ {
+ WindowDef *n = makeNode(WindowDef);
+ n->frameOptions = FRAMEOPTION_EXCLUDE_CURRENT;
+ $$ = n;
+ }
+ | EXCLUDE TIES
+ {
+ WindowDef *n = makeNode(WindowDef);
+ n->frameOptions = FRAMEOPTION_EXCLUDE_TIES;
+ $$ = n;
+ }
+ | EXCLUDE NO OTHERS
+ {
+ WindowDef *n = makeNode(WindowDef);
+ n->frameOptions = FRAMEOPTION_EXCLUDE_NO_OTHERS;
+ $$ = n;
+ }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
/*
* Supporting nonterminals for expressions.
@@ -14828,6 +14849,7 @@ unreserved_keyword:
| OPTION
| OPTIONS
| ORDINALITY
+ | OTHERS
| OVER
| OVERRIDING
| OWNED
@@ -14915,6 +14937,7 @@ unreserved_keyword:
| TEMPLATE
| TEMPORARY
| TEXT_P
+ | TIES
| TRANSACTION
| TRANSFORM
| TRIGGER
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 2828bbf796..a39851fc40 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -96,6 +96,8 @@ static List *addTargetToGroupList(ParseState *pstate, TargetEntry *tle,
static WindowClause *findWindowClause(List *wclist, const char *name);
static Node *transformFrameOffset(ParseState *pstate, int frameOptions,
Node *clause);
+static inline void checkRangeBetweenOids(ParseState *pstate, WindowDef *windef,
+ Oid sortOid, Node *offset);
/*
@@ -2608,6 +2610,45 @@ transformSortClause(ParseState *pstate,
return sortlist;
}
+static inline void
+checkRangeBetweenOids(ParseState *pstate,
+ WindowDef *windef,
+ Oid sortOid,
+ Node *offset)
+{
+ Oid offsetOid = exprType(offset);
+ Oid intOid = INT8OID;
+ Oid floatOid = FLOAT8OID;
+ Oid intervalOid = INTERVALOID;
+
+ if (can_coerce_type(1, &sortOid, &intOid, COERCION_IMPLICIT))
+ {
+ if (!can_coerce_type(1, &offsetOid, &intOid, COERCION_IMPLICIT) ||
+ !can_coerce_type(1, &offsetOid, &intOid, COERCION_IMPLICIT))
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("Offsets must be an integral"),
+ parser_errposition(pstate, windef->location)));
+ }
+ else if (can_coerce_type(1, &sortOid, &floatOid, COERCION_IMPLICIT))
+ {
+ if (!can_coerce_type(1, &offsetOid, &floatOid, COERCION_IMPLICIT) ||
+ !can_coerce_type(1, &offsetOid, &floatOid, COERCION_IMPLICIT))
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("Offsets must be float"),
+ parser_errposition(pstate, windef->location)));
+ }
+ else if (!can_coerce_type(1, &offsetOid, &intervalOid, COERCION_IMPLICIT) ||
+ !can_coerce_type(1, &offsetOid, &intervalOid, COERCION_IMPLICIT))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("Offsets must be an interval"),
+ parser_errposition(pstate, windef->location)));
+ }
+}
+
/*
* transformWindowDefinitions -
* transform window definitions (WindowDef to WindowClause)
@@ -2753,6 +2794,7 @@ transformWindowDefinitions(ParseState *pstate,
parser_errposition(pstate, windef->location)));
}
wc->frameOptions = windef->frameOptions;
+
/* Process frame offset expressions */
wc->startOffset = transformFrameOffset(pstate, wc->frameOptions,
windef->startOffset);
@@ -2760,9 +2802,36 @@ transformWindowDefinitions(ParseState *pstate,
windef->endOffset);
wc->winref = winref;
+ /* The RANGE BETWEEN clause requires exactly one ORDER BY column,
+ * of either an int or date/time type and start/end values to match by type.
+ */
+ if (wc->frameOptions & FRAMEOPTION_RANGE_BETWEEN)
+ {
+ SortBy *sortby;
+ TargetEntry *tle;
+ Oid sortOid;
+
+ if (list_length(orderClause) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("RANGE clause requires exactly one ORDER BY column"),
+ parser_errposition(pstate, windef->location)));
+ sortby = (SortBy *) lfirst(list_head(windef->orderClause));
+ tle = findTargetlistEntrySQL99(pstate, sortby->node,
+ targetlist, EXPR_KIND_WINDOW_ORDER);
+
+ /* Check that the sortOid and start/end offset Oids match */
+ sortOid = exprType((Node *) tle->expr);
+ if (wc->frameOptions & FRAMEOPTION_START_VALUE)
+ checkRangeBetweenOids(pstate, windef, sortOid, wc->startOffset);
+ if (wc->frameOptions & FRAMEOPTION_END_VALUE)
+ checkRangeBetweenOids(pstate, windef, sortOid, wc->endOffset);
+
+ wc->firstSortColOid = sortOid;
+ }
+
result = lappend(result, wc);
}
-
return result;
}
@@ -3513,16 +3582,21 @@ transformFrameOffset(ParseState *pstate, int frameOptions, Node *clause)
}
else if (frameOptions & FRAMEOPTION_RANGE)
{
+ Oid nodeOid;
+ Oid intervalOid = INTERVALOID;
+
/* Transform the raw expression tree */
node = transformExpr(pstate, clause, EXPR_KIND_WINDOW_FRAME_RANGE);
/*
- * this needs a lot of thought to decide how to support in the context
- * of Postgres' extensible datatype framework
+ * Check that the specified type is an interval or integral
*/
constructName = "RANGE";
- /* error was already thrown by gram.y, this is just a backstop */
- elog(ERROR, "window frame with value offset is not implemented");
+ nodeOid = exprType(node);
+ if (can_coerce_type(1, &nodeOid, &intervalOid, COERCION_IMPLICIT))
+ node = coerce_to_specific_type(pstate, node, INTERVALOID, constructName);
+ else
+ node = coerce_to_specific_type(pstate, node, INT8OID, constructName);
}
else
{
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 06cf32f5d7..099555e1e6 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5908,6 +5908,12 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
else
Assert(false);
}
+ if (wc->frameOptions & FRAMEOPTION_EXCLUDE_CURRENT)
+ appendStringInfoString(buf, "EXCLUDE CURRENT ROW ");
+ else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES)
+ appendStringInfoString(buf, "EXCLUDE TIES ");
+ else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_NO_OTHERS)
+ appendStringInfoString(buf, "EXCLUDE NO OTHERS ");
/* we will now have a trailing space; remove it */
buf->len--;
}
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index e05bc04f52..2c5c2a7651 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1876,6 +1876,7 @@ typedef struct WindowAggState
int64 aggregatedbase; /* start row for current aggregates */
int64 aggregatedupto; /* rows before this one are aggregated */
+ Oid firstSortColOid; /* First ORDER BY Oid, used for RANGE with values */
int frameOptions; /* frame_clause options, see WindowDef */
ExprState *startOffset; /* expression for starting bound offset */
ExprState *endOffset; /* expression for ending bound offset */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 34d6afc80f..be31b45ca5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -515,6 +515,10 @@ typedef struct WindowDef
#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_START_VALUE \
(FRAMEOPTION_START_VALUE_PRECEDING | FRAMEOPTION_START_VALUE_FOLLOWING)
@@ -525,6 +529,9 @@ typedef struct WindowDef
(FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \
FRAMEOPTION_END_CURRENT_ROW)
+#define FRAMEOPTION_EXCLUSION \
+ (FRAMEOPTION_EXCLUDE_CURRENT | FRAMEOPTION_EXCLUDE_TIES)
+
/*
* RangeSubselect - subquery appearing in a FROM clause
*/
@@ -1275,6 +1282,7 @@ 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.
+ * firstSortColOid is set if the clause is a RANGE BETWEEN with values.
* 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;
@@ -1288,6 +1296,7 @@ typedef struct WindowClause
char *refname; /* referenced window name, if any */
List *partitionClause; /* PARTITION BY list */
List *orderClause; /* ORDER BY list */
+ Oid firstSortColOid; /* First ORDER BY Oid, used for RANGE with values */
int frameOptions; /* frame_clause options, see WindowDef */
Node *startOffset; /* expression for starting bound, if any */
Node *endOffset; /* expression for ending bound, if any */
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 9b38d44ba0..d01828cae3 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -806,6 +806,7 @@ typedef struct WindowAgg
int ordNumCols; /* number of columns in ordering clause */
AttrNumber *ordColIdx; /* their indexes in the target list */
Oid *ordOperators; /* equality operators for ordering columns */
+ Oid firstSortColOid; /* First ORDER BY Oid, used for RANGE with values */
int frameOptions; /* frame_clause options, see WindowDef */
Node *startOffset; /* expression for starting bound, if any */
Node *endOffset; /* expression for ending bound, if any */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f50e45e886..39807675be 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -282,6 +282,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)
@@ -393,6 +394,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/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 19f909f3d1..d8ae3807a2 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -5,19 +5,24 @@ CREATE TEMPORARY TABLE empsalary (
depname varchar,
empno bigint,
salary int,
- enroll_date date
+ enroll_date date,
+ enroll_time time,
+ enroll_timetz timetz,
+ enroll_interval interval,
+ enroll_timestamptz timestamptz,
+ enroll_timestamp timestamp
);
INSERT INTO empsalary VALUES
-('develop', 10, 5200, '2007-08-01'),
-('sales', 1, 5000, '2006-10-01'),
-('personnel', 5, 3500, '2007-12-10'),
-('sales', 4, 4800, '2007-08-08'),
-('personnel', 2, 3900, '2006-12-23'),
-('develop', 7, 4200, '2008-01-01'),
-('develop', 9, 4500, '2008-01-01'),
-('sales', 3, 4800, '2007-08-01'),
-('develop', 8, 6000, '2006-10-01'),
-('develop', 11, 5200, '2007-08-15');
+('develop', 10, 5200, '2007-08-01', '11:00', '11:00 BST', '1 year'::interval, TIMESTAMP '2000-10-19 10:23:54+01', TIMESTAMP '2000-10-19 10:23:54'),
+('sales', 1, 5000, '2006-10-01', '12:00', '12:00 BST', '2 years'::interval, TIMESTAMP '2001-10-19 10:23:54+01', TIMESTAMP '2001-10-19 10:23:54'),
+('personnel', 5, 3500, '2007-12-10', '13:00', '13:00 BST', '3 years'::interval, TIMESTAMP '2001-10-19 10:23:54+01', TIMESTAMP '2001-10-19 10:23:54'),
+('sales', 4, 4800, '2007-08-08', '14:00', '14:00 BST', '4 years'::interval, TIMESTAMP '2002-10-19 10:23:54+01', TIMESTAMP '2002-10-19 10:23:54'),
+('personnel', 2, 3900, '2006-12-23', '15:00', '15:00 BST', '5 years'::interval, TIMESTAMP '2003-10-19 10:23:54+01', TIMESTAMP '2003-10-19 10:23:54'),
+('develop', 7, 4200, '2008-01-01', '15:00', '15:00 BST', '5 years'::interval, TIMESTAMP '2004-10-19 10:23:54+01', TIMESTAMP '2004-10-19 10:23:54'),
+('develop', 9, 4500, '2008-01-01', '17:00', '17:00 BST', '7 years'::interval, TIMESTAMP '2005-10-19 10:23:54+01', TIMESTAMP '2005-10-19 10:23:54'),
+('sales', 3, 4800, '2007-08-01', '18:00', '18:00 BST', '8 years'::interval, TIMESTAMP '2006-10-19 10:23:54+01', TIMESTAMP '2006-10-19 10:23:54'),
+('develop', 8, 6000, '2006-10-01', '19:00', '19:00 BST', '9 years'::interval, TIMESTAMP '2007-10-19 10:23:54+01', TIMESTAMP '2007-10-19 10:23:54'),
+('develop', 11, 5200, '2007-08-15', '20:00', '20:00 BST', '10 years'::interval, TIMESTAMP '2008-10-19 10:23:54+01', TIMESTAMP '2008-10-19 10:23:54');
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
depname | empno | salary | sum
-----------+-------+--------+-------
@@ -819,6 +824,57 @@ 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 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 sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
@@ -887,13 +943,40 @@ 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 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 +1041,602 @@ 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 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 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
+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 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 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 (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 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 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 sum(salary) over (order by enroll_time range between '1 hour'::interval preceding and '2 hours'::interval following),
+ salary, enroll_time from empsalary;
+ sum | salary | enroll_time
+-------+--------+-------------
+ 13700 | 5200 | 11:00:00
+ 18500 | 5000 | 12:00:00
+ 21400 | 3500 | 13:00:00
+ 16400 | 4800 | 14:00:00
+ 17400 | 3900 | 15:00:00
+ 17400 | 4200 | 15:00:00
+ 15300 | 4500 | 17:00:00
+ 20500 | 4800 | 18:00:00
+ 16000 | 6000 | 19:00:00
+ 11200 | 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 current row), 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
+ 13500 | 3900 | 15:00:00
+ 13200 | 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
+-------+--------+-------------
+ 13700 | 5200 | 11:00:00
+ 18500 | 5000 | 12:00:00
+ 21400 | 3500 | 13:00:00
+ 16400 | 4800 | 14:00:00
+ 13200 | 3900 | 15:00:00
+ 13500 | 4200 | 15:00:00
+ 15300 | 4500 | 17:00:00
+ 20500 | 4800 | 18:00:00
+ 16000 | 6000 | 19:00:00
+ 11200 | 5200 | 20:00:00
+(10 rows)
+
+select sum(salary) over (order by enroll_timetz range between '1 hour'::interval preceding and '2 hours'::interval following),
+ salary, enroll_timetz from empsalary;
+ sum | salary | enroll_timetz
+-------+--------+---------------
+ 13700 | 5200 | 11:00:00+01
+ 18500 | 5000 | 12:00:00+01
+ 21400 | 3500 | 13:00:00+01
+ 16400 | 4800 | 14:00:00+01
+ 17400 | 3900 | 15:00:00+01
+ 17400 | 4200 | 15:00:00+01
+ 15300 | 4500 | 17:00:00+01
+ 20500 | 4800 | 18:00:00+01
+ 16000 | 6000 | 19:00:00+01
+ 11200 | 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 current row), 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
+ 13500 | 3900 | 15:00:00+01
+ 13200 | 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
+-------+--------+---------------
+ 13700 | 5200 | 11:00:00+01
+ 18500 | 5000 | 12:00:00+01
+ 21400 | 3500 | 13:00:00+01
+ 16400 | 4800 | 14:00:00+01
+ 13200 | 3900 | 15:00:00+01
+ 13500 | 4200 | 15:00:00+01
+ 15300 | 4500 | 17:00:00+01
+ 20500 | 4800 | 18:00:00+01
+ 16000 | 6000 | 19:00:00+01
+ 11200 | 5200 | 20:00:00+01
+(10 rows)
+
+select sum(salary) over (order by enroll_interval range between '1 year'::interval preceding and '2 years'::interval following),
+ salary, enroll_interval from empsalary;
+ sum | salary | enroll_interval
+-------+--------+-----------------
+ 13700 | 5200 | @ 1 year
+ 18500 | 5000 | @ 2 years
+ 21400 | 3500 | @ 3 years
+ 16400 | 4800 | @ 4 years
+ 17400 | 3900 | @ 5 years
+ 17400 | 4200 | @ 5 years
+ 15300 | 4500 | @ 7 years
+ 20500 | 4800 | @ 8 years
+ 16000 | 6000 | @ 9 years
+ 11200 | 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 current row), 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
+ 13500 | 3900 | @ 5 years
+ 13200 | 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
+-------+--------+-----------------
+ 13700 | 5200 | @ 1 year
+ 18500 | 5000 | @ 2 years
+ 21400 | 3500 | @ 3 years
+ 16400 | 4800 | @ 4 years
+ 13200 | 3900 | @ 5 years
+ 13500 | 4200 | @ 5 years
+ 15300 | 4500 | @ 7 years
+ 20500 | 4800 | @ 8 years
+ 16000 | 6000 | @ 9 years
+ 11200 | 5200 | @ 10 years
+(10 rows)
+
+select sum(salary) over (order by enroll_timestamptz range between '1 year'::interval preceding and '2 years'::interval following),
+ salary, enroll_timestamptz from empsalary;
+ sum | salary | enroll_timestamptz
+-------+--------+------------------------------
+ 18500 | 5200 | Thu Oct 19 10:23:54 2000 PDT
+ 22400 | 5000 | Fri Oct 19 10:23:54 2001 PDT
+ 22400 | 3500 | Fri Oct 19 10:23:54 2001 PDT
+ 21400 | 4800 | Sat Oct 19 10:23:54 2002 PDT
+ 17400 | 3900 | Sun Oct 19 10:23:54 2003 PDT
+ 17400 | 4200 | Tue Oct 19 10:23:54 2004 PDT
+ 19500 | 4500 | Wed Oct 19 10:23:54 2005 PDT
+ 20500 | 4800 | Thu Oct 19 10:23:54 2006 PDT
+ 16000 | 6000 | Fri Oct 19 10:23:54 2007 PDT
+ 11200 | 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 current row), salary, enroll_timestamptz from empsalary;
+ sum | salary | enroll_timestamptz
+-------+--------+------------------------------
+ 13300 | 5200 | Thu Oct 19 10:23:54 2000 PDT
+ 17400 | 5000 | Fri Oct 19 10:23:54 2001 PDT
+ 18900 | 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
+-------+--------+------------------------------
+ 18500 | 5200 | Thu Oct 19 10:23:54 2000 PDT
+ 18900 | 5000 | Fri Oct 19 10:23:54 2001 PDT
+ 17400 | 3500 | Fri Oct 19 10:23:54 2001 PDT
+ 21400 | 4800 | Sat Oct 19 10:23:54 2002 PDT
+ 17400 | 3900 | Sun Oct 19 10:23:54 2003 PDT
+ 17400 | 4200 | Tue Oct 19 10:23:54 2004 PDT
+ 19500 | 4500 | Wed Oct 19 10:23:54 2005 PDT
+ 20500 | 4800 | Thu Oct 19 10:23:54 2006 PDT
+ 16000 | 6000 | Fri Oct 19 10:23:54 2007 PDT
+ 11200 | 5200 | Sun Oct 19 10:23:54 2008 PDT
+(10 rows)
+
+select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and '2 years'::interval following),
+ salary, enroll_timestamp from empsalary;
+ sum | salary | enroll_timestamp
+-------+--------+--------------------------
+ 18500 | 5200 | Thu Oct 19 10:23:54 2000
+ 22400 | 5000 | Fri Oct 19 10:23:54 2001
+ 22400 | 3500 | Fri Oct 19 10:23:54 2001
+ 21400 | 4800 | Sat Oct 19 10:23:54 2002
+ 17400 | 3900 | Sun Oct 19 10:23:54 2003
+ 17400 | 4200 | Tue Oct 19 10:23:54 2004
+ 19500 | 4500 | Wed Oct 19 10:23:54 2005
+ 20500 | 4800 | Thu Oct 19 10:23:54 2006
+ 16000 | 6000 | Fri Oct 19 10:23:54 2007
+ 11200 | 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 current row), salary, enroll_timestamp from empsalary;
+ sum | salary | enroll_timestamp
+-------+--------+--------------------------
+ 13300 | 5200 | Thu Oct 19 10:23:54 2000
+ 17400 | 5000 | Fri Oct 19 10:23:54 2001
+ 18900 | 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
+-------+--------+--------------------------
+ 18500 | 5200 | Thu Oct 19 10:23:54 2000
+ 18900 | 5000 | Fri Oct 19 10:23:54 2001
+ 17400 | 3500 | Fri Oct 19 10:23:54 2001
+ 21400 | 4800 | Sat Oct 19 10:23:54 2002
+ 17400 | 3900 | Sun Oct 19 10:23:54 2003
+ 17400 | 4200 | Tue Oct 19 10:23:54 2004
+ 19500 | 4500 | Wed Oct 19 10:23:54 2005
+ 20500 | 4800 | Thu Oct 19 10:23:54 2006
+ 16000 | 6000 | Fri Oct 19 10:23:54 2007
+ 11200 | 5200 | Sun Oct 19 10:23:54 2008
+(10 rows)
+
+select sum(salary) over (order by enroll_timestamp range between current row and '2 years'::interval following),
+ salary, enroll_timestamp from empsalary;
+ sum | salary | enroll_timestamp
+-------+--------+--------------------------
+ 18500 | 5200 | Thu Oct 19 10:23:54 2000
+ 17200 | 5000 | Fri Oct 19 10:23:54 2001
+ 17200 | 3500 | Fri Oct 19 10:23:54 2001
+ 12900 | 4800 | Sat Oct 19 10:23:54 2002
+ 12600 | 3900 | Sun Oct 19 10:23:54 2003
+ 13500 | 4200 | Tue Oct 19 10:23:54 2004
+ 15300 | 4500 | Wed Oct 19 10:23:54 2005
+ 16000 | 4800 | Thu Oct 19 10:23:54 2006
+ 11200 | 6000 | Fri Oct 19 10:23:54 2007
+ 5200 | 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 current row),
+ salary, enroll_timestamp from empsalary;
+ sum | salary | enroll_timestamp
+-------+--------+--------------------------
+ 5200 | 5200 | Thu Oct 19 10:23:54 2000
+ 13700 | 5000 | Fri Oct 19 10:23:54 2001
+ 13700 | 3500 | Fri Oct 19 10:23:54 2001
+ 13300 | 4800 | Sat Oct 19 10:23:54 2002
+ 8700 | 3900 | Sun Oct 19 10:23:54 2003
+ 8100 | 4200 | Tue Oct 19 10:23:54 2004
+ 8700 | 4500 | Wed Oct 19 10:23:54 2005
+ 9300 | 4800 | Thu Oct 19 10:23:54 2006
+ 10800 | 6000 | Fri Oct 19 10:23:54 2007
+ 11200 | 5200 | Sun Oct 19 10:23:54 2008
+(10 rows)
+
+select sum(salary) over(order by enroll_date range between unbounded 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
+ 47100 | 3500 | 12-10-2007
+ 47100 | 4500 | 01-01-2008
+ 47100 | 4200 | 01-01-2008
+(10 rows)
+
+select sum(salary) over(order by enroll_date range between unbounded 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
+ 43600 | 3500 | 12-10-2007
+ 42600 | 4500 | 01-01-2008
+ 42900 | 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
+-------+--------+-------------
+ 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
+ 47100 | 3500 | 12-10-2007
+ 42900 | 4500 | 01-01-2008
+ 42600 | 4200 | 01-01-2008
+(10 rows)
+
+-- RANGE BETWEEN with values negative tests
+select sum(salary) over (order by enroll_timestamp, enroll_date range between '1 year'::interval preceding and '2 years'::interval following
+ exclude ties), salary, enroll_timestamp from empsalary;
+ERROR: RANGE clause requires exactly one ORDER BY column
+LINE 1: select sum(salary) over (order by enroll_timestamp, enroll_d...
+ ^
+select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following
+ exclude ties), salary, enroll_timestamp from empsalary;
+ERROR: RANGE clause 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_timestamp from empsalary;
+ERROR: ORDER BY column in window function must be an integral or date/time
+select max(enroll_date) over (order by enroll_timestamp range between 1 preceding and 2 following
+ exclude ties), salary, enroll_timestamp from empsalary;
+ERROR: Offsets must be an interval
+LINE 1: select max(enroll_date) over (order by enroll_timestamp rang...
+ ^
+select max(enroll_date) over (order by salary range between -1 preceding and 2 following
+ exclude ties), salary, enroll_timestamp from empsalary;
+ERROR: RANGE offsets cannot be negative. invalid value -1
+select max(enroll_date) over (order by salary range between 1 preceding and -2 following
+ exclude ties), salary, enroll_timestamp from empsalary;
+ERROR: RANGE offsets cannot be negative. invalid value -2
+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;
+ERROR: Offsets must be an integral
+LINE 1: select max(enroll_date) over (order by salary range between ...
+ ^
-- 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 e2a1a1cdd5..cd25200501 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -6,20 +6,25 @@ CREATE TEMPORARY TABLE empsalary (
depname varchar,
empno bigint,
salary int,
- enroll_date date
+ enroll_date date,
+ enroll_time time,
+ enroll_timetz timetz,
+ enroll_interval interval,
+ enroll_timestamptz timestamptz,
+ enroll_timestamp timestamp
);
INSERT INTO empsalary VALUES
-('develop', 10, 5200, '2007-08-01'),
-('sales', 1, 5000, '2006-10-01'),
-('personnel', 5, 3500, '2007-12-10'),
-('sales', 4, 4800, '2007-08-08'),
-('personnel', 2, 3900, '2006-12-23'),
-('develop', 7, 4200, '2008-01-01'),
-('develop', 9, 4500, '2008-01-01'),
-('sales', 3, 4800, '2007-08-01'),
-('develop', 8, 6000, '2006-10-01'),
-('develop', 11, 5200, '2007-08-15');
+('develop', 10, 5200, '2007-08-01', '11:00', '11:00 BST', '1 year'::interval, TIMESTAMP '2000-10-19 10:23:54+01', TIMESTAMP '2000-10-19 10:23:54'),
+('sales', 1, 5000, '2006-10-01', '12:00', '12:00 BST', '2 years'::interval, TIMESTAMP '2001-10-19 10:23:54+01', TIMESTAMP '2001-10-19 10:23:54'),
+('personnel', 5, 3500, '2007-12-10', '13:00', '13:00 BST', '3 years'::interval, TIMESTAMP '2001-10-19 10:23:54+01', TIMESTAMP '2001-10-19 10:23:54'),
+('sales', 4, 4800, '2007-08-08', '14:00', '14:00 BST', '4 years'::interval, TIMESTAMP '2002-10-19 10:23:54+01', TIMESTAMP '2002-10-19 10:23:54'),
+('personnel', 2, 3900, '2006-12-23', '15:00', '15:00 BST', '5 years'::interval, TIMESTAMP '2003-10-19 10:23:54+01', TIMESTAMP '2003-10-19 10:23:54'),
+('develop', 7, 4200, '2008-01-01', '15:00', '15:00 BST', '5 years'::interval, TIMESTAMP '2004-10-19 10:23:54+01', TIMESTAMP '2004-10-19 10:23:54'),
+('develop', 9, 4500, '2008-01-01', '17:00', '17:00 BST', '7 years'::interval, TIMESTAMP '2005-10-19 10:23:54+01', TIMESTAMP '2005-10-19 10:23:54'),
+('sales', 3, 4800, '2007-08-01', '18:00', '18:00 BST', '8 years'::interval, TIMESTAMP '2006-10-19 10:23:54+01', TIMESTAMP '2006-10-19 10:23:54'),
+('develop', 8, 6000, '2006-10-01', '19:00', '19:00 BST', '9 years'::interval, TIMESTAMP '2007-10-19 10:23:54+01', TIMESTAMP '2007-10-19 10:23:54'),
+('develop', 11, 5200, '2007-08-15', '20:00', '20:00 BST', '10 years'::interval, TIMESTAMP '2008-10-19 10:23:54+01', TIMESTAMP '2008-10-19 10:23:54');
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
@@ -189,6 +194,18 @@ 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 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 +222,13 @@ 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 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 +250,150 @@ 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 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');
+
+-- RANGE BETWEEN with values 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 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 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 (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 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 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),
+ 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 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 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),
+ 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 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 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),
+ 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 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 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),
+ 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 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 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),
+ 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 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 ties), salary, enroll_timestamp from empsalary;
+
+select sum(salary) over (order by enroll_timestamp range between current row and '2 years'::interval following),
+ salary, enroll_timestamp from empsalary;
+
+select sum(salary) over (order by enroll_timestamp range between '1 year'::interval preceding and current row),
+ salary, enroll_timestamp from empsalary;
+
+select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following),
+ salary, enroll_date from empsalary;
+
+select sum(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
+ 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 ties), salary, enroll_date from empsalary;
+
+-- RANGE BETWEEN with values negative tests
+select sum(salary) over (order by enroll_timestamp, enroll_date range between '1 year'::interval preceding and '2 years'::interval following
+ exclude ties), salary, enroll_timestamp from empsalary;
+
+select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following
+ exclude ties), salary, enroll_timestamp from empsalary;
+
+select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following
+ exclude ties), salary, enroll_timestamp from empsalary;
+
+select max(enroll_date) over (order by enroll_timestamp range between 1 preceding and 2 following
+ exclude ties), salary, enroll_timestamp from empsalary;
+
+select max(enroll_date) over (order by salary range between -1 preceding and 2 following
+ exclude ties), salary, enroll_timestamp from empsalary;
+
+select max(enroll_date) over (order by salary range between 1 preceding and -2 following
+ exclude ties), salary, enroll_timestamp 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_timestamp from empsalary;
+
-- with UNION
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
--
2.13.6
