Skip to content

Commit 8e8854d

Browse files
tglsfdctglsfdc
committed
Add some basic support for window frame clauses to the window-functions
patch. This includes the ability to force the frame to cover the whole partition, and the ability to make the frame end exactly on the current row rather than its last ORDER BY peer. Supporting any more of the full SQL frame-clause syntax will require nontrivial hacking on the window aggregate code, so it'll have to wait for 8.5 or beyond.
1 parent 0fb9be7 commit 8e8854d

File tree

24 files changed

+757
-307
lines changed

24 files changed

+757
-307
lines changed

‎doc/src/sgml/advanced.sgml

Lines changed: 20 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.55 2008/12/28 18:53:53 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.56 2008/12/31 00:08:32 tgl Exp $ -->
22

33
<chapter id="tutorial-advanced">
44
<title>Advanced Features</title>
@@ -429,27 +429,27 @@ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary
429429
<para>
430430
We already saw that <literal>ORDER BY</> can be omitted if the ordering
431431
of rows is not important. It is also possible to omit <literal>PARTITION
432-
BY</>, in which case the window function is computed over all rows of the
433-
virtual table; that is, there is one partition containing all the rows.
432+
BY</>, in which case there is just one partition containing all the rows.
434433
</para>
435434

436435
<para>
437436
There is another important concept associated with window functions:
438437
for each row, there is a set of rows within its partition called its
439-
<firstterm>window frame</>. When <literal>ORDER BY</> is omitted the
440-
frame is always the same as the partition. If <literal>ORDER BY</> is
441-
supplied, the frame consists of all rows from the start of the partition
442-
up to the current row, plus any following rows that are equal to the
443-
current row according to the <literal>ORDER BY</> clause.
438+
<firstterm>window frame</>. Many (but not all) window functions act only
439+
on the rows of the window frame, rather than of the whole partition.
440+
By default, if <literal>ORDER BY</> is supplied then the frame consists of
441+
all rows from the start of the partition up through the current row, plus
442+
any following rows that are equal to the current row according to the
443+
<literal>ORDER BY</> clause. When <literal>ORDER BY</> is omitted the
444+
default frame consists of all rows in the partition.
444445
<footnote>
445446
<para>
446-
The SQL standard includes options to define the window frame in
447-
other ways, but this definition is the only one currently supported
448-
by <productname>PostgreSQL</productname>.
447+
There are options to define the window frame in other ways, but
448+
this tutorial does not cover them. See
449+
<xref linkend="syntax-window-functions"> for details.
449450
</para>
450451
</footnote>
451-
Many window functions act only on the rows of the window frame, rather
452-
than of the whole partition. Here is an example using <function>sum</>:
452+
Here is an example using <function>sum</>:
453453
</para>
454454

455455
<programlisting>
@@ -550,6 +550,13 @@ SELECT sum(salary) OVER w, avg(salary) OVER w
550550
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
551551
</programlisting>
552552
</para>
553+
554+
<para>
555+
More details about window functions can be found in
556+
<xref linkend="syntax-window-functions">,
557+
<xref linkend="queries-window">, and the
558+
<xref linkend="sql-select" endterm="sql-select-title"> reference page.
559+
</para>
553560
</sect1>
554561

555562

‎doc/src/sgml/func.sgml

Lines changed: 18 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.464 2008/12/28 18:53:53 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.465 2008/12/31 00:08:33 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -10160,9 +10160,8 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1016010160
<para>
1016110161
<firstterm>Window functions</firstterm> provide the ability to perform
1016210162
calculations across sets of rows that are related to the current query
10163-
row. For information about this feature see
10164-
<xref linkend="tutorial-window"> and
10165-
<xref linkend="syntax-window-functions">.
10163+
row. See <xref linkend="tutorial-window"> for an introduction to this
10164+
feature.
1016610165
</para>
1016710166

1016810167
<para>
@@ -10392,18 +10391,23 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1039210391
<para>
1039310392
Note that <function>first_value</>, <function>last_value</>, and
1039410393
<function>nth_value</> consider only the rows within the <quote>window
10395-
frame</>, that is the rows from the start of the partition through the
10396-
last peer of the current row. This is particularly likely to give
10397-
unintuitive results for <function>last_value</>.
10394+
frame</>, which by default contains the rows from the start of the
10395+
partition through the last peer of the current row. This is
10396+
likely to give unhelpful results for <function>nth_value</> and
10397+
particularly <function>last_value</>. You can redefine the frame as
10398+
being the whole partition by adding <literal>ROWS BETWEEN UNBOUNDED
10399+
PRECEDING AND UNBOUNDED FOLLOWING</> to the <literal>OVER</> clause.
10400+
See <xref linkend="syntax-window-functions"> for more information.
1039810401
</para>
1039910402

1040010403
<para>
1040110404
When an aggregate function is used as a window function, it aggregates
1040210405
over the rows within the current row's window frame. To obtain
10403-
aggregation over the whole partition, be sure to omit <literal>ORDER BY</>
10404-
from the window definition. An aggregate used with <literal>ORDER BY</>
10405-
produces a <quote>running sum</> type of behavior, which may or may not
10406-
be what's wanted.
10406+
aggregation over the whole partition, omit <literal>ORDER BY</> or use
10407+
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>.
10408+
An aggregate used with <literal>ORDER BY</> and the default window frame
10409+
definition produces a <quote>running sum</> type of behavior, which may or
10410+
may not be what's wanted.
1040710411
</para>
1040810412

1040910413
<note>
@@ -10416,7 +10420,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1041610420
same as the standard's default, namely <literal>RESPECT NULLS</>.
1041710421
Likewise, the standard's <literal>FROM FIRST</> or <literal>FROM LAST</>
1041810422
option for <function>nth_value</> is not implemented: only the
10419-
default <literal>FROM FIRST</> behavior is supported.
10423+
default <literal>FROM FIRST</> behavior is supported. (You can achieve
10424+
the result of <literal>FROM LAST</> by reversing the <literal>ORDER BY</>
10425+
ordering.)
1042010426
</para>
1042110427
</note>
1042210428

‎doc/src/sgml/queries.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.51 2008/12/28 18:53:54 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.52 2008/12/31 00:08:35 tgl Exp $ -->
22

33
<chapter id="queries">
44
<title>Queries</title>
@@ -993,7 +993,7 @@ SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
993993
</para>
994994

995995
<para>
996-
Currently, use of window functions always forces sorting, and so the
996+
Currently, window functions always require presorted data, and so the
997997
query output will be ordered according to one or another of the window
998998
functions' <literal>PARTITION BY</>/<literal>ORDER BY</> clauses.
999999
It is not recommendable to rely on this, however. Use an explicit

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

Lines changed: 45 additions & 15 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.114 2008/12/29 18:23:53 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.115 2008/12/31 00:08:35 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -583,7 +583,21 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
583583
[ <replaceable class="parameter">existing_window_name</replaceable> ]
584584
[ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
585585
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
586+
[ <replaceable class="parameter">framing_clause</replaceable> ]
586587
</synopsis>
588+
</para>
589+
590+
<para>
591+
If an <replaceable class="parameter">existing_window_name</replaceable>
592+
is specified it must refer to an earlier entry in the <literal>WINDOW</>
593+
list; the new window copies its partitioning clause from that entry,
594+
as well as its ordering clause if any. In this case the new window cannot
595+
specify its own <literal>PARTITION BY</> clause, and it can specify
596+
<literal>ORDER BY</> only if the copied window does not have one.
597+
The framing clause is never copied from the existing window.
598+
</para>
599+
600+
<para>
587601
The elements of the <literal>PARTITION BY</> list are interpreted in
588602
the same fashion as elements of a
589603
<xref linkend="sql-groupby" endterm="sql-groupby-title">, and
@@ -597,12 +611,29 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
597611
</para>
598612

599613
<para>
600-
If an <replaceable class="parameter">existing_window_name</replaceable>
601-
is specified it must refer to an earlier entry in the <literal>WINDOW</>
602-
list; the new window copies its partitioning clause from that entry,
603-
as well as its ordering clause if any. In this case the new window cannot
604-
specify its own <literal>PARTITION BY</> clause, and it can specify
605-
<literal>ORDER BY</> only if the copied window does not have one.
614+
The optional <replaceable class="parameter">framing_clause</> defines
615+
the <firstterm>window frame</> for window functions that depend on the
616+
frame (not all do). It can be one of
617+
<synopsis>
618+
RANGE UNBOUNDED PRECEDING
619+
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
620+
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
621+
ROWS UNBOUNDED PRECEDING
622+
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
623+
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
624+
</synopsis>
625+
The first two are equivalent and are also the default: they set the
626+
frame to be all rows from the partition start up through the current row's
627+
last peer in the <literal>ORDER BY</> ordering (which means all rows if
628+
there is no <literal>ORDER BY</>). The options
629+
<literal>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> and
630+
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>
631+
are also equivalent: they always select all rows in the partition.
632+
Lastly, <literal>ROWS UNBOUNDED PRECEDING</> or its verbose equivalent
633+
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</> select
634+
all rows up through the current row (regardless of duplicates).
635+
Beware that this option can produce implementation-dependent results
636+
if the <literal>ORDER BY</> ordering does not order the rows uniquely.
606637
</para>
607638

608639
<para>
@@ -623,8 +654,9 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
623654

624655
<para>
625656
Window functions are described in detail in
626-
<xref linkend="tutorial-window"> and
627-
<xref linkend="syntax-window-functions">.
657+
<xref linkend="tutorial-window">,
658+
<xref linkend="syntax-window-functions">, and
659+
<xref linkend="queries-window">.
628660
</para>
629661
</refsect2>
630662

@@ -1453,12 +1485,10 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
14531485
<title><literal>WINDOW</literal> Clause Restrictions</title>
14541486

14551487
<para>
1456-
The SQL standard provides for an optional <quote>framing clause</>,
1457-
introduced by the key word <literal>RANGE</> or <literal>ROWS</>,
1458-
in window definitions. <productname>PostgreSQL</productname> does
1459-
not yet implement framing clauses, and always follows the
1460-
default framing behavior, which is equivalent to the framing clause
1461-
<literal>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</>.
1488+
The SQL standard provides additional options for the window
1489+
<replaceable class="parameter">framing_clause</>.
1490+
<productname>PostgreSQL</productname> currently supports only the
1491+
options listed above.
14621492
</para>
14631493
</refsect2>
14641494

‎doc/src/sgml/syntax.sgml

Lines changed: 29 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.127 2008/12/28 18:53:54 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.128 2008/12/31 00:08:35 tgl Exp $ -->
22

33
<chapter id="sql-syntax">
44
<title>SQL Syntax</title>
@@ -1591,6 +1591,17 @@ sqrt(2)
15911591
[ <replaceable class="parameter">window_name</replaceable> ]
15921592
[ PARTITION BY <replaceable class="parameter">expression</replaceable> [, ...] ]
15931593
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
1594+
[ <replaceable class="parameter">framing_clause</replaceable> ]
1595+
</synopsis>
1596+
and the optional <replaceable class="parameter">framing_clause</replaceable>
1597+
can be one of
1598+
<synopsis>
1599+
RANGE UNBOUNDED PRECEDING
1600+
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
1601+
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
1602+
ROWS UNBOUNDED PRECEDING
1603+
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
1604+
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
15941605
</synopsis>
15951606

15961607
Here, <replaceable>expression</replaceable> represents any value
@@ -1603,19 +1614,33 @@ sqrt(2)
16031614
Named window specifications are usually referenced with just
16041615
<literal>OVER</> <replaceable>window_name</replaceable>, but it is
16051616
also possible to write a window name inside the parentheses and then
1606-
optionally override its ordering clause with <literal>ORDER BY</>.
1617+
optionally override its ordering clause and/or framing clause.
16071618
This latter syntax follows the same rules as modifying an existing
16081619
window name within the <literal>WINDOW</literal> clause; see the
16091620
<xref linkend="sql-select" endterm="sql-select-title"> reference
16101621
page for details.
16111622
</para>
16121623

1624+
<para>
1625+
The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
1626+
which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
1627+
CURRENT ROW</>; it selects rows up through the current row's last
1628+
peer in the <literal>ORDER BY</> ordering (which means all rows if
1629+
there is no <literal>ORDER BY</>). The options
1630+
<literal>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> and
1631+
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>
1632+
are also equivalent: they always select all rows in the partition.
1633+
Lastly, <literal>ROWS UNBOUNDED PRECEDING</> or its verbose equivalent
1634+
<literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</> select
1635+
all rows up through the current row (regardless of duplicates).
1636+
Beware that this option can produce implementation-dependent results
1637+
if the <literal>ORDER BY</> ordering does not order the rows uniquely.
1638+
</para>
1639+
16131640
<para>
16141641
The built-in window functions are described in <xref
16151642
linkend="functions-window-table">. Also, any built-in or
16161643
user-defined aggregate function can be used as a window function.
1617-
Currently, there is no provision for user-defined window functions
1618-
other than aggregates.
16191644
</para>
16201645

16211646
<para>

0 commit comments

Comments
 (0)