From 8e8854daa2b4b3ef9e3fc1a56c79608a70018058 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 31 Dec 2008 00:08:39 +0000 Subject: 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. --- doc/src/sgml/advanced.sgml | 33 ++++++++++++++---------- doc/src/sgml/func.sgml | 30 +++++++++++++--------- doc/src/sgml/queries.sgml | 4 +-- doc/src/sgml/ref/select.sgml | 60 +++++++++++++++++++++++++++++++++----------- doc/src/sgml/syntax.sgml | 33 +++++++++++++++++++++--- 5 files changed, 114 insertions(+), 46 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index ce8ef535dba..42d923b00dc 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -1,4 +1,4 @@ - + Advanced Features @@ -429,27 +429,27 @@ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary We already saw that ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION - BY, in which case the window function is computed over all rows of the - virtual table; that is, there is one partition containing all the rows. + BY, in which case there is just one partition containing all the rows. There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its - window frame. When ORDER BY is omitted the - frame is always the same as the partition. If ORDER BY is - supplied, the frame consists of all rows from the start of the partition - up to the current row, plus any following rows that are equal to the - current row according to the ORDER BY clause. + window frame. Many (but not all) window functions act only + on the rows of the window frame, rather than of the whole partition. + By default, if ORDER BY is supplied then the frame consists of + all rows from the start of the partition up through the current row, plus + any following rows that are equal to the current row according to the + ORDER BY clause. When ORDER BY is omitted the + default frame consists of all rows in the partition. - The SQL standard includes options to define the window frame in - other ways, but this definition is the only one currently supported - by PostgreSQL. + There are options to define the window frame in other ways, but + this tutorial does not cover them. See + for details. - Many window functions act only on the rows of the window frame, rather - than of the whole partition. Here is an example using sum: + Here is an example using sum: @@ -550,6 +550,13 @@ SELECT sum(salary) OVER w, avg(salary) OVER w WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); + + + More details about window functions can be found in + , + , and the + reference page. + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 205b71e9c9e..bf3293a0cf2 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -10160,9 +10160,8 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; Window functions provide the ability to perform calculations across sets of rows that are related to the current query - row. For information about this feature see - and - . + row. See for an introduction to this + feature. @@ -10392,18 +10391,23 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; Note that first_value, last_value, and nth_value consider only the rows within the window - frame, that is the rows from the start of the partition through the - last peer of the current row. This is particularly likely to give - unintuitive results for last_value. + frame, which by default contains the rows from the start of the + partition through the last peer of the current row. This is + likely to give unhelpful results for nth_value and + particularly last_value. You can redefine the frame as + being the whole partition by adding ROWS BETWEEN UNBOUNDED + PRECEDING AND UNBOUNDED FOLLOWING to the OVER clause. + See for more information. When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. To obtain - aggregation over the whole partition, be sure to omit ORDER BY - from the window definition. An aggregate used with ORDER BY - produces a running sum type of behavior, which may or may not - be what's wanted. + aggregation over the whole partition, omit ORDER BY or use + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. + An aggregate used with ORDER BY and the default window frame + definition produces a running sum type of behavior, which may or + may not be what's wanted. @@ -10416,7 +10420,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; same as the standard's default, namely RESPECT NULLS. Likewise, the standard's FROM FIRST or FROM LAST option for nth_value is not implemented: only the - default FROM FIRST behavior is supported. + default FROM FIRST behavior is supported. (You can achieve + the result of FROM LAST by reversing the ORDER BY + ordering.) diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index f1db64b273a..7aaeea993cf 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1,4 +1,4 @@ - + Queries @@ -993,7 +993,7 @@ SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit - Currently, use of window functions always forces sorting, and so the + Currently, window functions always require presorted data, and so the query output will be ordered according to one or another of the window functions' PARTITION BY/ORDER BY clauses. It is not recommendable to rely on this, however. Use an explicit diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 3a4e718fbf5..ba8bff0e523 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1,5 +1,5 @@ @@ -583,7 +583,21 @@ WINDOW window_name AS ( existing_window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] +[ framing_clause ] + + + + If an existing_window_name + is specified it must refer to an earlier entry in the WINDOW + list; the new window copies its partitioning clause from that entry, + as well as its ordering clause if any. In this case the new window cannot + specify its own PARTITION BY clause, and it can specify + ORDER BY only if the copied window does not have one. + The framing clause is never copied from the existing window. + + + The elements of the PARTITION BY list are interpreted in the same fashion as elements of a , and @@ -597,12 +611,29 @@ WINDOW window_name AS ( - If an existing_window_name - is specified it must refer to an earlier entry in the WINDOW - list; the new window copies its partitioning clause from that entry, - as well as its ordering clause if any. In this case the new window cannot - specify its own PARTITION BY clause, and it can specify - ORDER BY only if the copied window does not have one. + The optional framing_clause defines + the window frame for window functions that depend on the + frame (not all do). It can be one of + +RANGE UNBOUNDED PRECEDING +RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW +RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING +ROWS UNBOUNDED PRECEDING +ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW +ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + + The first two are equivalent and are also the default: they set the + frame to be all rows from the partition start up through the current row's + last peer in the ORDER BY ordering (which means all rows if + there is no ORDER BY). The options + RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + are also equivalent: they always select all rows in the partition. + Lastly, ROWS UNBOUNDED PRECEDING or its verbose equivalent + ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW select + all rows up through the current row (regardless of duplicates). + Beware that this option can produce implementation-dependent results + if the ORDER BY ordering does not order the rows uniquely. @@ -623,8 +654,9 @@ WINDOW window_name AS ( Window functions are described in detail in - and - . + , + , and + . @@ -1453,12 +1485,10 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; <literal>WINDOW</literal> Clause Restrictions - The SQL standard provides for an optional framing clause, - introduced by the key word RANGE or ROWS, - in window definitions. PostgreSQL does - not yet implement framing clauses, and always follows the - default framing behavior, which is equivalent to the framing clause - RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. + The SQL standard provides additional options for the window + framing_clause. + PostgreSQL currently supports only the + options listed above. diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 9d0833c2035..327bdd7f0f4 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,4 +1,4 @@ - + SQL Syntax @@ -1591,6 +1591,17 @@ sqrt(2) [ window_name ] [ PARTITION BY expression [, ...] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] +[ framing_clause ] + + and the optional framing_clause + can be one of + +RANGE UNBOUNDED PRECEDING +RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW +RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING +ROWS UNBOUNDED PRECEDING +ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW +ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Here, expression represents any value @@ -1603,19 +1614,33 @@ sqrt(2) Named window specifications are usually referenced with just OVER window_name, but it is also possible to write a window name inside the parentheses and then - optionally override its ordering clause with ORDER BY. + optionally override its ordering clause and/or framing clause. This latter syntax follows the same rules as modifying an existing window name within the WINDOW clause; see the reference page for details. + + The default framing option is RANGE UNBOUNDED PRECEDING, + which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND + CURRENT ROW; it selects rows up through the current row's last + peer in the ORDER BY ordering (which means all rows if + there is no ORDER BY). The options + RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + are also equivalent: they always select all rows in the partition. + Lastly, ROWS UNBOUNDED PRECEDING or its verbose equivalent + ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW select + all rows up through the current row (regardless of duplicates). + Beware that this option can produce implementation-dependent results + if the ORDER BY ordering does not order the rows uniquely. + + The built-in window functions are described in . Also, any built-in or user-defined aggregate function can be used as a window function. - Currently, there is no provision for user-defined window functions - other than aggregates. -- cgit v1.2.3