8000 Extend the set of frame options supported for window functions. · postgres/postgres@ec4be2e · GitHub
[go: up one dir, main page]
Skip to content

Commit ec4be2e

Browse files
tglsfdctglsfdc
committed
Extend the set of frame options supported for window functions.
This patch allows the frame to start from CURRENT ROW (in either RANGE or ROWS mode), and it also adds support for ROWS n PRECEDING and ROWS n FOLLOWING start and end points. (RANGE value PRECEDING/FOLLOWING isn't there yet --- the grammar works, but that's all.) Hitoshi Harada, reviewed by Pavel Stehule
1 parent a5348fa commit ec4be2e

File tree

28 files changed

+1231
-248
lines changed

28 files changed

+1231
-248
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 12 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.501 2010/02/07 20:48:09 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.502 2010/02/12 17:33:19 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -10559,21 +10559,23 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1055910559
<function>nth_value</> consider only the rows within the <quote>window
1056010560
frame</>, which by default contains the rows from the start of the
1056110561
partition through the last peer of the current row. This is
10562-
likely to give unhelpful results for <function>nth_value</> and
10563-
particularly <function>last_value</>. You can redefine the frame as
10564-
being the whole partition by adding <literal>ROWS BETWEEN UNBOUNDED
10565-
PRECEDING AND UNBOUNDED FOLLOWING</> to the <literal>OVER</> clause.
10566-
See <xref linkend="syntax-window-functions"> for more information.
10562+
likely to give unhelpful results for <function>last_value</> and
10563+
sometimes also <function>nth_value</>. You can redefine the frame by
10564+
adding a suitable frame specification (<literal>RANGE</> or
10565+
<literal>ROWS</>) to the <literal>OVER</> clause.
10566+
See <xref linkend="syntax-window-functions"> for more information
10567+
about frame specifications.
1056710568
</para>
1056810569

1056910570
<para>
1057010571
When an aggregate function is used as a window function, it aggregates
10571-
over the rows within the current row's window frame. To obtain
10572-
aggregation over the whole partition, omit <literal>ORDER BY</> or use
10573-
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>.
10572+
over the rows within the current row's window frame.
1057410573
An aggregate used with <literal>ORDER BY</> and the default window frame
1057510574
definition produces a <quote>running sum</> type of behavior, which may or
10576-
may not be what's wanted.
10575+
may not be what's wanted. To obtain
10576+
aggregation over the whole partition, omit <literal>ORDER BY</> or use
10577+
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>.
10578+
Other frame specifications can be used to obtain other effects.
1057710579
</para>
1057810580

1057910581
<note>

‎doc/src/sgml/ref/select.sgml

Lines changed: 59 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.128 2009/10/28 14:55:37 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.129 2010/02/12 17:33:19 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -616,27 +616,66 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
616616
<para>
617617
The optional <replaceable class="parameter">frame_clause</> defines
618618
the <firstterm>window frame</> for window functions that depend on the
619-
frame (not all do). It can be one of
619+
frame (not all do). The window frame is a set of related rows for
620+
each row of the query (called the <firstterm>current row</>).
621+
The <replaceable class="parameter">frame_clause</> can be one of
622+
623+
<synopsis>
624+
[ RANGE | ROWS ] <replaceable>frame_start</>
625+
[ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</>
626+
</synopsis>
627+
628+
where <replaceable>frame_start</> and <replaceable>frame_end</> can be
629+
one of
630+
620631
<synopsis>
621-
RANGE UNBOUNDED PRECEDING
622-
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
623-
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
624-
ROWS UNBOUNDED PRECEDING
625-
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
626-
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
632+
UNBOUNDED PRECEDING
633+
<replaceable>value</replaceable> PRECEDING
634+
CURRENT ROW
635+
<replaceable>value</replaceable> FOLLOWING
636+
UNBOUNDED FOLLOWING
627637
</synopsis>
628-
The first two are equivalent and are also the default: they set the
629-
frame to be all rows from the partition start up through the current row's
630-
last peer in the <literal>ORDER BY</> ordering (which means all rows if
631-
there is no <literal>ORDER BY</>). The options
632-
<literal>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> and
633-
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>
634-
are also equivalent: they always select all rows in the partition.
635-
Lastly, <literal>ROWS UNBOUNDED PRECEDING</> or its verbose equivalent
636-
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</> select
637-
all rows up through the current row (regardless of duplicates).
638-
Beware that this option can produce implementation-dependent results
639-
if the <literal>ORDER BY</> ordering does not order the rows uniquely.
638+
639+
If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT
640+
ROW</>. Restrictions are that
641+
<replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>,
642+
<replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>,
643+
and the <replaceable>frame_end</> choice cannot appear earlier in the
644+
above list than the <replaceable>frame_start</> choice &mdash; for example
645+
<literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</>
646+
PRECEDING</literal> is not allowed.
647+
</para>
648+
649+
<para>
650+
The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
651+
which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
652+
CURRENT ROW</>; it sets the frame to be all rows from the partition start
653+
up through the current row's last peer in the <literal>ORDER BY</>
654+
ordering (which means all rows if there is no <literal>ORDER BY</>).
655+
In general, <literal>UNBOUNDED PRECEDING</> means that the frame
656+
starts with the first row of the partition, and similarly
657+
<literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last
658+
row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</>
659+
mode). In <literal>ROWS</> mode, <literal>CURRENT ROW</>
660+
means that the frame starts or ends with the current row; but in
661+
<literal>RANGE</> mode it means that the frame starts or ends with
662+
the current row's first or last peer in the <literal>ORDER BY</> ordering.
663+
The <replaceable>value</> <literal>PRECEDING</> and
664+
<replaceable>value</> <literal>FOLLOWING</> cases are currently only
665+
allowed in <literal>ROWS</> mode. They indicate that the frame starts
666+
or ends with the row that many rows before or after the current row.
667+
<replaceable>value</replaceable> must be an integer expression not
668+
containing any variables, aggregate functions, or window functions.
669+
The value must not be null or negative; but it can be zero, which
670+
selects the current row itself.
671+
</para>
672+
673+
<para>
674+
Beware that the <literal>ROWS</> options can produce unpredictable
675+
results if the <literal>ORDER BY</> ordering does not order the rows
676+
uniquely. The <literal>RANGE</> options are designed to ensure that
677+
rows that are peers in the <literal>ORDER BY</> ordering are treated
678+
alike; any two peer rows will be both in or both not in the frame.
640679
</para>
641680

642681
<para>

‎doc/src/sgml/syntax.sgml

Lines changed: 41 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.141 2010/02/04 00:19:28 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.142 2010/02/12 17:33:19 tgl Exp $ -->
22

33
<chapter id="sql-syntax">
44
<title>SQL Syntax</title>
@@ -1667,14 +1667,21 @@ SELECT array_agg(a ORDER BY b DESC) FROM table;
16671667
and the optional <replaceable class="parameter">frame_clause</replaceable>
16681668
can be one of
16691669
<synopsis>
1670-
RANGE UNBOUNDED PRECEDING
1671-
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
1672-
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
1673-
ROWS UNBOUNDED PRECEDING
1674-
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
1675-
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
1670+
[ RANGE | ROWS ] <replaceable>frame_start</>
1671+
[ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</>
16761672
</synopsis>
1673+
where <replaceable>frame_start</> and <replaceable>frame_end</> can be
1674+
one of
1675+
<synopsis>
1676+
UNBOUNDED PRECEDING
1677+
<replaceable>value</replaceable> PRECEDING
1678+
CURRENT ROW
1679+
<replaceable>value</replaceable> FOLLOWING
1680+
UNBOUNDED FOLLOWING
1681+
</synopsis>
1682+
</para>
16771683

1684+
<para>
16781685
Here, <replaceable>expression</replaceable> represents any value
16791686
expression that does not itself contain window function calls.
16801687
The <literal>PARTITION BY</> and <literal>ORDER BY</> lists have
@@ -1699,19 +1706,35 @@ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
16991706
The <replaceable class="parameter">frame_clause</replaceable> specifies
17001707
the set of rows constituting the <firstterm>window frame</>, for those
17011708
window functions that act on the frame instead of the whole partition.
1709+
If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT
1710+
ROW</>. Restrictions are that
1711+
<replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>,
1712+
<replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>,
1713+
and the <replaceable>frame_end</> choice cannot appear earlier in the
1714+
above list than the <replaceable>frame_start</> choice &mdash; for example
1715+
<literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</>
1716+
PRECEDING</literal> is not allowed.
17021717
The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
17031718
which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
1704-
CURRENT ROW</>; it selects rows up through the current row's last
1705-
peer in the <literal>ORDER BY</> ordering (which means all rows if
1706-
there is no <literal>ORDER BY</>). The options
1707-
<literal>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> and
1708-
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>
1709-
are also equivalent: they always select all rows in the partition.
1710-
Lastly, <literal>ROWS UNBOUNDED PRECEDING</> or its verbose equivalent
1711-
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</> select
1712-
all rows up through the current row (regardless of duplicates).
1713-
Beware that this option can produce implementation-dependent results
1714-
if the <literal>ORDER BY</> ordering does not order the rows uniquely.
1719+
CURRENT ROW</>; it sets the frame to be all rows from the partition start
1720+
up through the current row's last peer in the <literal>ORDER BY</>
1721+
ordering (which means all rows if there is no <literal>ORDER BY</>).
1722+
In general, <literal>UNBOUNDED PRECEDING</> means that the frame
1723+
starts with the first row of the partition, and similarly
1724+
<literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last
1725+
row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</>
1726+
mode). In <literal>ROWS</> mode, <literal>CURRENT ROW</>
1727+
means that the frame starts or ends with the current row; but in
1728+
<literal>RANGE</> mode it means that the frame starts or ends with
1729+
the current row's first or last peer in the <literal>ORDER BY</> ordering.
1730+
The <replaceable>value</> <literal>PRECEDING</> and
1731+
<replaceable>value</> <literal>FOLLOWING</> cases are currently only
1732+
allowed in <literal>ROWS</> mode. They indicate that the frame starts
1733+
or ends with the row that many rows before or after the current row.
1734+
<replaceable>value</replaceable> must be an integer expression not
1735+
containing any variables, aggregate functions, or window functions.
1736+
The value must not be null or negative; but it can be zero, which
1737+
selects the current row itself.
17151738
</para>
17161739

17171740
<para>

‎src/backend/executor/nodeAgg.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -71,7 +71,7 @@
7171
* Portions Copyright (c) 1994, Regents of the University of California
7272
*
7373
* IDENTIFICATION
74-
* $PostgreSQL: pgsql/src/backend/executor/nodeAgg.c,v 1.172 2010/02/08 20:39:51 tgl Exp $
74+
* $PostgreSQL: pgsql/src/backend/executor/nodeAgg.c,v 1.173 2010/02/12 17:33:19 tgl Exp $
7575
*
7676
*-------------------------------------------------------------------------
7777
*/
@@ -1999,7 +1999,7 @@ AggCheckCallContext(FunctionCallInfo fcinfo, MemoryContext *aggcontext)
19991999
if (fcinfo->context && IsA(fcinfo->context, WindowAggState))
20002000
{
20012001
if (aggcontext)
2002-
*aggcontext = ((WindowAggState *) fcinfo->context)->wincontext;
2002+
*aggcontext = ((WindowAggState *) fcinfo->context)->aggcontext;
20032003
return AGG_CONTEXT_WINDOW;
20042004
}
20052005

0 commit comments

Comments
 (0)
0