From: David Fetter <david@fetter.org>
To: Oliver Ford <ojford@gmail.com>
Cc: Erik Rijkers <er@xs4all.nl>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Add RANGE with values and exclusions clauses to the Window Functions
Date: Tue, 28 Nov 2017 05:38:29 +0100
Message-ID: <20171128043828.GD5332@fetter.org> (raw)
In-Reply-To: <CAGMVOdvETRCKpeT06Uoq5RsNUOdH7d1iYy7C1Pze=L5=gBzs-Q@mail.gmail.com>
References: <CAGMVOdu9sivPAxbNN0X+q19Sfv9edEPv=HibOJhB14TJv_RCQg@mail.gmail.com>
<b2374b9461c349b17b50c77f3a70e1a1@xs4all.nl>
<CAGMVOdsopg5ZAuJh9+ceq-s0pU2qqZsZn-jbL4A1c=h1iWaZVA@mail.gmail.com>
<CAGMVOdvrZCwJg0BngM9GMO+LK=j20vhSfdk1gTTBNNH311Vgsw@mail.gmail.com>
<bc8a1d397fa81d6ae33c297d0224184b@xs4all.nl>
<2c084c1c2bf599ceb2afa87874de8bc1@xs4all.nl>
<CAGMVOdvETRCKpeT06Uoq5RsNUOdH7d1iYy7C1Pze=L5=gBzs-Q@mail.gmail.com>
On Mon, Nov 27, 2017 at 04:55:17PM +0000, Oliver Ford wrote:
> On Mon, Nov 27, 2017 at 4:40 PM, Erik Rijkers <er@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/donateAttachments:0001-window-frame-v5.patch (text/x-diff, 80.9K) download
From 9144e33c2bb3c6acccfe27bfde8155850faacd87 Mon Sep 17 00:00:00 2001
From: David Fetter <david@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.sgmlindex 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.sgmlindex 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.cindex 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.cindex 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.cindex 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.cindex 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.cindex 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.cindex 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.yindex 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.cindex 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.cindex 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.hindex 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.hindex 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.hindex 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.hindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: david@fetter.org, ojford@gmail.com, er@xs4all.nl
Subject: Re: Add RANGE with values and exclusions clauses to the Window Functions
In-Reply-To: <20171128043828.GD5332@fetter.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox