pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedWindow function docs
11+ messages / 3 participants
[nested] [flat]
* Window function docs
@ 2017-04-06 21:49 Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 11+ messages in thread
From: Bruce Momjian @ 2017-04-06 21:49 UTC (permalink / raw)
To: pgsql-docs
As part of writing a talk about window functions, I have done some
cleanups of the window function docs, attached, that I would like to
apply to head.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
window.diff (text/x-diff, 12.1K) download
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 1e45511..59c1e7e 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -328,7 +328,7 @@ COMMIT;
A <firstterm>window function</> performs a calculation across a set of
table rows that are somehow related to the current row. This is comparable
to the type of calculation that can be done with an aggregate function.
- But unlike regular aggregate functions, use of a window function does not
+ But unlike normal aggregate functions, use of a window function does not
cause rows to become grouped into a single output row — the
rows retain their separate identities. Behind the scenes, the window
function is able to access more than just the current row of the query
@@ -363,20 +363,19 @@ SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM emps
<structname>empsalary</>, and there is one output row for each row in the
table. The fourth column represents an average taken across all the table
rows that have the same <structfield>depname</> value as the current row.
- (This actually is the same function as the regular <function>avg</>
+ (This actually is the same function as the normal <function>avg</>
aggregate function, but the <literal>OVER</> clause causes it to be
- treated as a window function and computed across an appropriate set of
- rows.)
+ treated as a window function and computed across the entire partition.)
</para>
<para>
A window function call always contains an <literal>OVER</> clause
directly following the window function's name and argument(s). This is what
- syntactically distinguishes it from a regular function or aggregate
+ syntactically distinguishes it from a normal function or aggregate
function. The <literal>OVER</> clause determines exactly how the
rows of the query are split up for processing by the window function.
- The <literal>PARTITION BY</> list within <literal>OVER</> specifies
- dividing the rows into groups, or partitions, that share the same
+ The <literal>PARTITION BY</> clause within <literal>OVER</>
+ divides the rows into groups, or partitions, that share the same
values of the <literal>PARTITION BY</> expression(s). For each row,
the window function is computed across the rows that fall into the
same partition as the current row.
@@ -411,8 +410,8 @@ FROM empsalary;
</screen>
As shown here, the <function>rank</> function produces a numerical rank
- within the current row's partition for each distinct <literal>ORDER BY</>
- value, in the order defined by the <literal>ORDER BY</> clause.
+ for each distinct <literal>ORDER BY</> value in the current row's
+ partition, in the order defined by the <literal>ORDER BY</> clause.
<function>rank</> needs no explicit parameter, because its behavior
is entirely determined by the <literal>OVER</> clause.
</para>
@@ -424,20 +423,20 @@ FROM empsalary;
if any. For example, a row removed because it does not meet the
<literal>WHERE</> condition is not seen by any window function.
A query can contain multiple window functions that slice up the data
- in different ways by means of different <literal>OVER</> clauses, but
+ in different ways using different <literal>OVER</> clauses, but
they all act on the same collection of rows defined by this virtual table.
</para>
<para>
We already saw that <literal>ORDER BY</> can be omitted if the ordering
of rows is not important. It is also possible to omit <literal>PARTITION
- BY</>, in which case there is just one partition containing all the rows.
+ BY</>, in which case there is a single partition containing all rows.
</para>
<para>
There is another important concept associated with window functions:
for each row, there is a set of rows within its partition called its
- <firstterm>window frame</>. Many (but not all) window functions act only
+ <firstterm>window frame</>. Some window functions act only
on the rows of the window frame, rather than of the whole partition.
By default, if <literal>ORDER BY</> is supplied then the frame consists of
all rows from the start of the partition up through the current row, plus
@@ -515,7 +514,7 @@ SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
elsewhere, such as in <literal>GROUP BY</>, <literal>HAVING</>
and <literal>WHERE</literal> clauses. This is because they logically
execute after the processing of those clauses. Also, window functions
- execute after regular aggregate functions. This means it is valid to
+ execute after normal aggregate functions. This means it is valid to
include an aggregate function call in the arguments of a window function,
but not vice versa.
</para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e3d852c..91c76a3 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14413,17 +14413,17 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<para>
The built-in window functions are listed in
<xref linkend="functions-window-table">. Note that these functions
- <emphasis>must</> be invoked using window function syntax; that is an
+ <emphasis>must</> be invoked using window function syntax, i.e. an
<literal>OVER</> clause is required.
</para>
<para>
In addition to these functions, any built-in or user-defined normal
- aggregate function (but not ordered-set or hypothetical-set aggregates)
+ aggregate function (i.e. not ordered-set or hypothetical-set aggregates)
can be used as a window function; see
<xref linkend="functions-aggregate"> for a list of the built-in aggregates.
- Aggregate functions act as window functions only when an <literal>OVER</>
- clause follows the call; otherwise they act as regular aggregates.
+ Normal aggregate functions act as window functions only when an <literal>OVER</>
+ clause follows the call; otherwise they act as normal aggregates.
</para>
<table id="functions-window-table">
@@ -14488,7 +14488,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<entry>
<type>double precision</type>
</entry>
- <entry>relative rank of the current row: (<function>rank</> - 1) / (total rows - 1)</entry>
+ <entry>relative rank of the current row: (<function>rank</> - 1) / (total partition rows - 1)</entry>
</row>
<row>
@@ -14501,7 +14501,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<entry>
<type>double precision</type>
</entry>
- <entry>relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)</entry>
+ <entry>cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows</entry>
</row>
<row>
@@ -14634,9 +14634,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
All of the functions listed in
<xref linkend="functions-window-table"> depend on the sort ordering
specified by the <literal>ORDER BY</> clause of the associated window
- definition. Rows that are not distinct in the <literal>ORDER BY</>
- ordering are said to be <firstterm>peers</>; the four ranking functions
- are defined so that they give the same answer for any two peer rows.
+ definition. Rows that are not distinct based on <literal>ORDER BY</>
+ ordering are said to be <firstterm>peers</>. The four ranking
+ functions (includes <function>cume_dist</>) are defined so that they
+ give the same answer for any two peer rows.
</para>
<para>
@@ -14646,14 +14647,13 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
partition through the last peer of the current row. This is
likely to give unhelpful results for <function>last_value</> and
sometimes also <function>nth_value</>. You can redefine the frame by
- adding a suitable frame specification (<literal>RANGE</> or
- <literal>ROWS</>) to the <literal>OVER</> clause.
- See <xref linkend="syntax-window-functions"> for more information
- about frame specifications.
+ modifying the frame specification; see <xref
+ linkend="syntax-window-functions"> for more information about frame
+ specifications.
</para>
<para>
- When an aggregate function is used as a window function, it aggregates
+ When a normal aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame.
An aggregate used with <literal>ORDER BY</> and the default window frame
definition produces a <quote>running sum</> type of behavior, which may or
@@ -14679,6 +14679,13 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
</para>
</note>
+ <para>
+ <function>cume_dist</> computes the ratio of partition rows that
+ are less than or equal to the current row and its peers, while
+ <function>percent_rank</> computes the ratio of lesser partition rows,
+ assuming the current row is does not exist in the partition.
+ </para>
+
</sect1>
<sect1 id="functions-subquery">
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 40f722c..bdb4d52 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1779,10 +1779,10 @@ FROM generate_series(1,10) AS s(i);
<para>
A <firstterm>window function call</firstterm> represents the application
of an aggregate-like function over some portion of the rows selected
- by a query. Unlike regular aggregate function calls, this is not tied
+ by a query. Unlike normal aggregate function calls, this is not tied
to grouping of the selected rows into a single output row — each
row remains separate in the query output. However the window function
- is able to scan all the rows that would be part of the current row's
+ has access to all the rows that would be part of the current row's
group according to the grouping specification (<literal>PARTITION BY</>
list) of the window function call.
The syntax of a window function call is one of the following:
@@ -1831,20 +1831,20 @@ UNBOUNDED FOLLOWING
named window in the <literal>WINDOW</literal> clause; see the
<xref linkend="sql-select"> reference page for details. It's worth
pointing out that <literal>OVER wname</> is not exactly equivalent to
- <literal>OVER (wname)</>; the latter implies copying and modifying the
+ <literal>OVER (wname ...)</>; the latter implies copying and modifying the
window definition, and will be rejected if the referenced window
specification includes a frame clause.
</para>
<para>
- The <literal>PARTITION BY</> option groups the rows of the query into
+ The <literal>PARTITION BY</> clause groups the rows of the query into
<firstterm>partitions</>, which are processed separately by the window
function. <literal>PARTITION BY</> works similarly to a query-level
<literal>GROUP BY</> clause, except that its expressions are always just
expressions and cannot be output-column names or numbers.
Without <literal>PARTITION BY</>, all rows produced by the query are
treated as a single partition.
- The <literal>ORDER BY</> option determines the order in which the rows
+ The <literal>ORDER BY</> clause determines the order in which the rows
of a partition are processed by the window function. It works similarly
to a query-level <literal>ORDER BY</> clause, but likewise cannot use
output-column names or numbers. Without <literal>ORDER BY</>, rows are
@@ -1922,8 +1922,8 @@ UNBOUNDED FOLLOWING
The built-in window functions are described in <xref
linkend="functions-window-table">. Other window functions can be added by
the user. Also, any built-in or user-defined normal aggregate function
- can be used as a window function. Ordered-set aggregates presently
- cannot be used as window functions, however.
+ can be used as a window function. However, ordered-set aggregate
+ cannot presently be used as window functions.
</para>
<para>
* Re: Window function docs
@ 2017-04-06 22:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 2 replies; 11+ messages in thread
From: Tom Lane @ 2017-04-06 22:13 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs
Bruce Momjian <bruce@momjian.us> writes:
> As part of writing a talk about window functions, I have done some
> cleanups of the window function docs, attached, that I would like to
> apply to head.
I think this change is just wrong:
aggregate function, but the <literal>OVER</> clause causes it to be
- treated as a window function and computed across an appropriate set of
- rows.)
+ treated as a window function and computed across the entire partition.)
</para>
avg() is going to be evaluated across the current frame, not the whole
partition. I don't mind trying to be more precise than "appropriate set",
but it doesn't help to be apparently precise but wrong.
Not really convinced by the s/regular/normal/g changes. That doesn't seem
to clarify much either. Maybe it would help to define "normal" near here.
There's a definition in func.sgml (that they are not ordered-set or
hypothetical-set aggregates) but that's too far away.
In this bit:
- definition. Rows that are not distinct in the <literal>ORDER BY</>
- ordering are said to be <firstterm>peers</>; the four ranking functions
- are defined so that they give the same answer for any two peer rows.
+ definition. Rows that are not distinct based on <literal>ORDER BY</>
+ ordering are said to be <firstterm>peers</>. The four ranking
+ functions (includes <function>cume_dist</>) are defined so that they
+ give the same answer for any two peer rows.
"based on" seems little better than the previous wording. Maybe it'd be
better to say "Rows that are not distinct when considering only the
<literal>ORDER BY</> columns are said to be ..."
sometimes also <function>nth_value</>. You can redefine the frame by
- adding a suitable frame specification (<literal>RANGE</> or
- <literal>ROWS</>) to the <literal>OVER</> clause.
- See <xref linkend="syntax-window-functions"> for more information
- about frame specifications.
+ modifying the frame specification; see <xref
+ linkend="syntax-window-functions"> for more information about frame
+ specifications.
</para>
Don't really like this change; it makes it vaguer for no apparent benefit.
+ <para>
+ <function>cume_dist</> computes the ratio of partition rows that
+ are less than or equal to the current row and its peers, while
+ <function>percent_rank</> computes the ratio of lesser partition rows,
+ assuming the current row is does not exist in the partition.
+ </para>
The first one probably ought to be "computes the fraction of partition
rows". The second definition seems nonsensical --- ratio to what? Or
probably better, fraction of what? (And "is does not" needs help too.)
the user. Also, any built-in or user-defined normal aggregate function
- can be used as a window function. Ordered-set aggregates presently
- cannot be used as window functions, however.
+ can be used as a window function. However, ordered-set aggregate
+ cannot presently be used as window functions.
</para>
Grammar (don't lose the "s" in ordered-set aggregates).
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window function docs
@ 2017-04-06 22:53 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2017-04-06 22:53 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs
On Thu, Apr 6, 2017 at 2:49 PM, Bruce Momjian <bruce@momjian.us> wrote:
> As part of writing a talk about window functions, I have done some
> cleanups of the window function docs, attached, that I would like to
> apply to head.
+1 Overall. Some minor technical items noted below. The only conceptual
item is whether (and, if so, how) to deal with partitions vs. frames.
- But unlike regular aggregate functions, use of a window function does
not
+ But unlike normal aggregate functions, use of a window function does
not
How broadly did you look for usage of normal versus regular? The window
function doc page uses "normal" once, vis-a-vis "ordered-set", I haven't
looked further. Note on that page the intro paragraph doesn't mention
hypothetical-set aggregates as being distinct from ordered-set aggregates
yet the tables and the docs here do.
https://www.postgresql.org/docs/current/static/functions-aggregate.html
+ treated as a window function and computed across the entire partition.)
Would introducing the concept of frame here trade precision for
comprehension?
- <firstterm>window frame</>. Many (but not all) window functions act
only
+ <firstterm>window frame</>. Some window functions act only
This is a semantic change as to whether the dominate behavior is to act on
frames or partitions. IIUC very few window functions, when presented with
a ORDER BY'd OVER and thus a limited frame, will still act on the entire
partition instead of the just the frame they are provided. In particular
don't all normal aggregates (and user-defined ones) operate on frames? The
cume_dist function noted later is one of the few exceptions since it
effectively operates on both - the frame for the numerator and the
partition for the denominator.
+ aggregate function (i.e. not ordered-set or hypothetical-set aggregates)
I, and others, subscribe to the believe that both i.e., and e.g., should
have trailing commas...
http://english.stackexchange.com/questions/6894/should-there-be-a-comma-after-i-e
+ functions (includes <function>cume_dist</>) are defined so that they
s/b "including <function>cume_dist</>"
+ give the same answer for any two peer rows.
for all peer rows?
+ assuming the current row is does not exist in the partition.
remove "is"
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window function docs
@ 2017-04-07 00:33 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2017-04-07 00:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
On Thu, Apr 6, 2017 at 06:13:44PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > As part of writing a talk about window functions, I have done some
> > cleanups of the window function docs, attached, that I would like to
> > apply to head.
>
> I think this change is just wrong:
>
> aggregate function, but the <literal>OVER</> clause causes it to be
> - treated as a window function and computed across an appropriate set of
> - rows.)
> + treated as a window function and computed across the entire partition.)
> </para>
>
> avg() is going to be evaluated across the current frame, not the whole
> partition. I don't mind trying to be more precise than "appropriate set",
> but it doesn't help to be apparently precise but wrong.
Good point. I have adjusted the text to say "window frame".
> Not really convinced by the s/regular/normal/g changes. That doesn't seem
> to clarify much either. Maybe it would help to define "normal" near here.
> There's a definition in func.sgml (that they are not ordered-set or
> hypothetical-set aggregates) but that's too far away.
Yeah, I went back and forth on that, and it seems like a mine-field.
The problem is that SUM() is both a non-window aggregate, and a window
function, and there isn't a clear terminology for non-window aggregates,
so changed the patch to just call them that. Hopefully the dual nature
of this is now clearer.
> In this bit:
>
> - definition. Rows that are not distinct in the <literal>ORDER BY</>
> - ordering are said to be <firstterm>peers</>; the four ranking functions
> - are defined so that they give the same answer for any two peer rows.
> + definition. Rows that are not distinct based on <literal>ORDER BY</>
> + ordering are said to be <firstterm>peers</>. The four ranking
> + functions (includes <function>cume_dist</>) are defined so that they
> + give the same answer for any two peer rows.
>
> "based on" seems little better than the previous wording. Maybe it'd be
> better to say "Rows that are not distinct when considering only the
> <literal>ORDER BY</> columns are said to be ..."
OK, I used your wording.
> sometimes also <function>nth_value</>. You can redefine the frame by
> - adding a suitable frame specification (<literal>RANGE</> or
> - <literal>ROWS</>) to the <literal>OVER</> clause.
> - See <xref linkend="syntax-window-functions"> for more information
> - about frame specifications.
> + modifying the frame specification; see <xref
> + linkend="syntax-window-functions"> for more information about frame
> + specifications.
> </para>
>
> Don't really like this change; it makes it vaguer for no apparent benefit.
Reverted. I had changed it because it is more the frame definition that
we want to highlight here, not ROW vs. RANGE, but there is no clearer
way to specify that clause.
> + <para>
> + <function>cume_dist</> computes the ratio of partition rows that
> + are less than or equal to the current row and its peers, while
> + <function>percent_rank</> computes the ratio of lesser partition rows,
> + assuming the current row is does not exist in the partition.
> + </para>
>
> The first one probably ought to be "computes the fraction of partition
> rows". The second definition seems nonsensical --- ratio to what? Or
> probably better, fraction of what? (And "is does not" needs help too.)
Updated.
> the user. Also, any built-in or user-defined normal aggregate function
> - can be used as a window function. Ordered-set aggregates presently
> - cannot be used as window functions, however.
> + can be used as a window function. However, ordered-set aggregate
> + cannot presently be used as window functions.
> </para>
>
> Grammar (don't lose the "s" in ordered-set aggregates).
Thanks for the review. Updated patch attached.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
window.diff (text/x-diff, 11.3K) download
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 1e45511..7563131 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -328,8 +328,8 @@ COMMIT;
A <firstterm>window function</> performs a calculation across a set of
table rows that are somehow related to the current row. This is comparable
to the type of calculation that can be done with an aggregate function.
- But unlike regular aggregate functions, use of a window function does not
- cause rows to become grouped into a single output row — the
+ However, window functions do not cause rows to become grouped into a single
+ output row like non-window aggregate calls would. Instead, the
rows retain their separate identities. Behind the scenes, the window
function is able to access more than just the current row of the query
result.
@@ -363,20 +363,19 @@ SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM emps
<structname>empsalary</>, and there is one output row for each row in the
table. The fourth column represents an average taken across all the table
rows that have the same <structfield>depname</> value as the current row.
- (This actually is the same function as the regular <function>avg</>
- aggregate function, but the <literal>OVER</> clause causes it to be
- treated as a window function and computed across an appropriate set of
- rows.)
+ (This actually is the same function as the non-window <function>avg</>
+ aggregate, but the <literal>OVER</> clause causes it to be
+ treated as a window function and computed across the window frame.)
</para>
<para>
A window function call always contains an <literal>OVER</> clause
directly following the window function's name and argument(s). This is what
- syntactically distinguishes it from a regular function or aggregate
- function. The <literal>OVER</> clause determines exactly how the
+ syntactically distinguishes it from a normal function or non-window
+ aggregate. The <literal>OVER</> clause determines exactly how the
rows of the query are split up for processing by the window function.
- The <literal>PARTITION BY</> list within <literal>OVER</> specifies
- dividing the rows into groups, or partitions, that share the same
+ The <literal>PARTITION BY</> clause within <literal>OVER</>
+ divides the rows into groups, or partitions, that share the same
values of the <literal>PARTITION BY</> expression(s). For each row,
the window function is computed across the rows that fall into the
same partition as the current row.
@@ -411,8 +410,8 @@ FROM empsalary;
</screen>
As shown here, the <function>rank</> function produces a numerical rank
- within the current row's partition for each distinct <literal>ORDER BY</>
- value, in the order defined by the <literal>ORDER BY</> clause.
+ for each distinct <literal>ORDER BY</> value in the current row's
+ partition, using the order defined by the <literal>ORDER BY</> clause.
<function>rank</> needs no explicit parameter, because its behavior
is entirely determined by the <literal>OVER</> clause.
</para>
@@ -424,20 +423,20 @@ FROM empsalary;
if any. For example, a row removed because it does not meet the
<literal>WHERE</> condition is not seen by any window function.
A query can contain multiple window functions that slice up the data
- in different ways by means of different <literal>OVER</> clauses, but
+ in different ways using different <literal>OVER</> clauses, but
they all act on the same collection of rows defined by this virtual table.
</para>
<para>
We already saw that <literal>ORDER BY</> can be omitted if the ordering
of rows is not important. It is also possible to omit <literal>PARTITION
- BY</>, in which case there is just one partition containing all the rows.
+ BY</>, in which case there is a single partition containing all rows.
</para>
<para>
There is another important concept associated with window functions:
for each row, there is a set of rows within its partition called its
- <firstterm>window frame</>. Many (but not all) window functions act only
+ <firstterm>window frame</>. Some window functions act only
on the rows of the window frame, rather than of the whole partition.
By default, if <literal>ORDER BY</> is supplied then the frame consists of
all rows from the start of the partition up through the current row, plus
@@ -515,7 +514,7 @@ SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
elsewhere, such as in <literal>GROUP BY</>, <literal>HAVING</>
and <literal>WHERE</literal> clauses. This is because they logically
execute after the processing of those clauses. Also, window functions
- execute after regular aggregate functions. This means it is valid to
+ execute after non-window aggregate functions. This means it is valid to
include an aggregate function call in the arguments of a window function,
but not vice versa.
</para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e3d852c..a37aa82 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14413,17 +14413,18 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<para>
The built-in window functions are listed in
<xref linkend="functions-window-table">. Note that these functions
- <emphasis>must</> be invoked using window function syntax; that is an
+ <emphasis>must</> be invoked using window function syntax, i.e. an
<literal>OVER</> clause is required.
</para>
<para>
In addition to these functions, any built-in or user-defined normal
- aggregate function (but not ordered-set or hypothetical-set aggregates)
+ aggregate function (i.e. not ordered-set or hypothetical-set aggregates)
can be used as a window function; see
<xref linkend="functions-aggregate"> for a list of the built-in aggregates.
Aggregate functions act as window functions only when an <literal>OVER</>
- clause follows the call; otherwise they act as regular aggregates.
+ clause follows the call; otherwise they act as non-window aggregates
+ and return a single row for the entire set.
</para>
<table id="functions-window-table">
@@ -14488,7 +14489,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<entry>
<type>double precision</type>
</entry>
- <entry>relative rank of the current row: (<function>rank</> - 1) / (total rows - 1)</entry>
+ <entry>relative rank of the current row: (<function>rank</> - 1) / (total partition rows - 1)</entry>
</row>
<row>
@@ -14501,7 +14502,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<entry>
<type>double precision</type>
</entry>
- <entry>relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)</entry>
+ <entry>cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows</entry>
</row>
<row>
@@ -14634,9 +14635,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
All of the functions listed in
<xref linkend="functions-window-table"> depend on the sort ordering
specified by the <literal>ORDER BY</> clause of the associated window
- definition. Rows that are not distinct in the <literal>ORDER BY</>
- ordering are said to be <firstterm>peers</>; the four ranking functions
- are defined so that they give the same answer for any two peer rows.
+ definition. Rows that are not distinct when considering only the
+ <literal>ORDER BY</> columns are said to be <firstterm>peers</>.
+ The four ranking functions (includes <function>cume_dist</>) are
+ defined so that they give the same answer for any two peer rows.
</para>
<para>
@@ -14679,6 +14681,14 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
</para>
</note>
+ <para>
+ <function>cume_dist</> computes the fraction of partition rows that
+ are less than or equal to the current row and its peers, while
+ <function>percent_rank</> computes the fraction of partition rows that
+ are less than the current row, assuming the current row does not exist
+ in the partition.
+ </para>
+
</sect1>
<sect1 id="functions-subquery">
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 40f722c..4746ae5 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1779,10 +1779,10 @@ FROM generate_series(1,10) AS s(i);
<para>
A <firstterm>window function call</firstterm> represents the application
of an aggregate-like function over some portion of the rows selected
- by a query. Unlike regular aggregate function calls, this is not tied
+ by a query. Unlike non-window aggregate calls, this is not tied
to grouping of the selected rows into a single output row — each
row remains separate in the query output. However the window function
- is able to scan all the rows that would be part of the current row's
+ has access to all the rows that would be part of the current row's
group according to the grouping specification (<literal>PARTITION BY</>
list) of the window function call.
The syntax of a window function call is one of the following:
@@ -1831,20 +1831,20 @@ UNBOUNDED FOLLOWING
named window in the <literal>WINDOW</literal> clause; see the
<xref linkend="sql-select"> reference page for details. It's worth
pointing out that <literal>OVER wname</> is not exactly equivalent to
- <literal>OVER (wname)</>; the latter implies copying and modifying the
+ <literal>OVER (wname ...)</>; the latter implies copying and modifying the
window definition, and will be rejected if the referenced window
specification includes a frame clause.
</para>
<para>
- The <literal>PARTITION BY</> option groups the rows of the query into
+ The <literal>PARTITION BY</> clause groups the rows of the query into
<firstterm>partitions</>, which are processed separately by the window
function. <literal>PARTITION BY</> works similarly to a query-level
<literal>GROUP BY</> clause, except that its expressions are always just
expressions and cannot be output-column names or numbers.
Without <literal>PARTITION BY</>, all rows produced by the query are
treated as a single partition.
- The <literal>ORDER BY</> option determines the order in which the rows
+ The <literal>ORDER BY</> clause determines the order in which the rows
of a partition are processed by the window function. It works similarly
to a query-level <literal>ORDER BY</> clause, but likewise cannot use
output-column names or numbers. Without <literal>ORDER BY</>, rows are
@@ -1922,8 +1922,8 @@ UNBOUNDED FOLLOWING
The built-in window functions are described in <xref
linkend="functions-window-table">. Other window functions can be added by
the user. Also, any built-in or user-defined normal aggregate function
- can be used as a window function. Ordered-set aggregates presently
- cannot be used as window functions, however.
+ can be used as a window function. However, ordered-set aggregates
+ cannot presently be used as window functions.
</para>
<para>
* Re: Window function docs
@ 2017-04-07 01:14 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 2017-04-07 01:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-docs
On Thu, Apr 6, 2017 at 03:53:24PM -0700, David G. Johnston wrote:
> On Thu, Apr 6, 2017 at 2:49 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> As part of writing a talk about window functions, I have done some
> cleanups of the window function docs, attached, that I would like to
> apply to head.
>
>
> +1 Overall. Some minor technical items noted below. The only conceptual item
> is whether (and, if so, how) to deal with partitions vs. frames.
>
> - But unlike regular aggregate functions, use of a window function does not
> + But unlike normal aggregate functions, use of a window function does not
>
> How broadly did you look for usage of normal versus regular? The window
> function doc page uses "normal" once, vis-a-vis "ordered-set", I haven't looked
> further. Note on that page the intro paragraph doesn't mention
I did a grep looking for 'regular.*agg'.
> hypothetical-set aggregates as being distinct from ordered-set aggregates yet
> the tables and the docs here do.
Do you have any suggested text for that?
> https://www.postgresql.org/docs/current/static/functions-aggregate.html
>
> + treated as a window function and computed across the entire partition.)
>
> Would introducing the concept of frame here trade precision for comprehension?
I modified this to say "window frame" as part of the patch for Tom.
> - <firstterm>window frame</>. Many (but not all) window functions act only
> + <firstterm>window frame</>. Some window functions act only
>
> This is a semantic change as to whether the dominate behavior is to act on
> frames or partitions. IIUC very few window functions, when presented with a
> ORDER BY'd OVER and thus a limited frame, will still act on the entire
> partition instead of the just the frame they are provided. In particular don't
If you look at slide 79 here you can get a good overview of what
operates on frames vs. partitions:
http://momjian.us/main/writings/pgsql/window.pdf
> all normal aggregates (and user-defined ones) operate on frames? The cume_dist
> function noted later is one of the few exceptions since it effectively operates
> on both - the frame for the numerator and the partition for the denominator.
Actually, cume_dist does not operate on frames because if you specify
ORDER BY x ROWS CURRENT ROW, cume_dist still operates on the peer and
lesser rows. So, the frame clause is ignored by cume_dist --- in a way
it has RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW wired into the
window function.
> + aggregate function (i.e. not ordered-set or hypothetical-set aggregates)
>
> I, and others, subscribe to the believe that both i.e., and e.g., should have
> trailing commas...
>
> http://english.stackexchange.com/questions/6894/
> should-there-be-a-comma-after-i-e
Done.
> + functions (includes <function>cume_dist</>) are defined so that they
>
> s/b "including <function>cume_dist</>"
Done.
> + give the same answer for any two peer rows.
>
> for all peer rows?
Much better, done.
> + assuming the current row is does not exist in the partition.
>
> remove "is"
Fixed in Tom's patch.
Updated patch attached. Thanks for the review.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
window.diff (text/x-diff, 11.3K) download
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 1e45511..7563131 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -328,8 +328,8 @@ COMMIT;
A <firstterm>window function</> performs a calculation across a set of
table rows that are somehow related to the current row. This is comparable
to the type of calculation that can be done with an aggregate function.
- But unlike regular aggregate functions, use of a window function does not
- cause rows to become grouped into a single output row — the
+ However, window functions do not cause rows to become grouped into a single
+ output row like non-window aggregate calls would. Instead, the
rows retain their separate identities. Behind the scenes, the window
function is able to access more than just the current row of the query
result.
@@ -363,20 +363,19 @@ SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM emps
<structname>empsalary</>, and there is one output row for each row in the
table. The fourth column represents an average taken across all the table
rows that have the same <structfield>depname</> value as the current row.
- (This actually is the same function as the regular <function>avg</>
- aggregate function, but the <literal>OVER</> clause causes it to be
- treated as a window function and computed across an appropriate set of
- rows.)
+ (This actually is the same function as the non-window <function>avg</>
+ aggregate, but the <literal>OVER</> clause causes it to be
+ treated as a window function and computed across the window frame.)
</para>
<para>
A window function call always contains an <literal>OVER</> clause
directly following the window function's name and argument(s). This is what
- syntactically distinguishes it from a regular function or aggregate
- function. The <literal>OVER</> clause determines exactly how the
+ syntactically distinguishes it from a normal function or non-window
+ aggregate. The <literal>OVER</> clause determines exactly how the
rows of the query are split up for processing by the window function.
- The <literal>PARTITION BY</> list within <literal>OVER</> specifies
- dividing the rows into groups, or partitions, that share the same
+ The <literal>PARTITION BY</> clause within <literal>OVER</>
+ divides the rows into groups, or partitions, that share the same
values of the <literal>PARTITION BY</> expression(s). For each row,
the window function is computed across the rows that fall into the
same partition as the current row.
@@ -411,8 +410,8 @@ FROM empsalary;
</screen>
As shown here, the <function>rank</> function produces a numerical rank
- within the current row's partition for each distinct <literal>ORDER BY</>
- value, in the order defined by the <literal>ORDER BY</> clause.
+ for each distinct <literal>ORDER BY</> value in the current row's
+ partition, using the order defined by the <literal>ORDER BY</> clause.
<function>rank</> needs no explicit parameter, because its behavior
is entirely determined by the <literal>OVER</> clause.
</para>
@@ -424,20 +423,20 @@ FROM empsalary;
if any. For example, a row removed because it does not meet the
<literal>WHERE</> condition is not seen by any window function.
A query can contain multiple window functions that slice up the data
- in different ways by means of different <literal>OVER</> clauses, but
+ in different ways using different <literal>OVER</> clauses, but
they all act on the same collection of rows defined by this virtual table.
</para>
<para>
We already saw that <literal>ORDER BY</> can be omitted if the ordering
of rows is not important. It is also possible to omit <literal>PARTITION
- BY</>, in which case there is just one partition containing all the rows.
+ BY</>, in which case there is a single partition containing all rows.
</para>
<para>
There is another important concept associated with window functions:
for each row, there is a set of rows within its partition called its
- <firstterm>window frame</>. Many (but not all) window functions act only
+ <firstterm>window frame</>. Some window functions act only
on the rows of the window frame, rather than of the whole partition.
By default, if <literal>ORDER BY</> is supplied then the frame consists of
all rows from the start of the partition up through the current row, plus
@@ -515,7 +514,7 @@ SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
elsewhere, such as in <literal>GROUP BY</>, <literal>HAVING</>
and <literal>WHERE</literal> clauses. This is because they logically
execute after the processing of those clauses. Also, window functions
- execute after regular aggregate functions. This means it is valid to
+ execute after non-window aggregate functions. This means it is valid to
include an aggregate function call in the arguments of a window function,
but not vice versa.
</para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e3d852c..d87e74a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14413,17 +14413,18 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<para>
The built-in window functions are listed in
<xref linkend="functions-window-table">. Note that these functions
- <emphasis>must</> be invoked using window function syntax; that is an
+ <emphasis>must</> be invoked using window function syntax, i.e., an
<literal>OVER</> clause is required.
</para>
<para>
In addition to these functions, any built-in or user-defined normal
- aggregate function (but not ordered-set or hypothetical-set aggregates)
+ aggregate function (i.e., not ordered-set or hypothetical-set aggregates)
can be used as a window function; see
<xref linkend="functions-aggregate"> for a list of the built-in aggregates.
Aggregate functions act as window functions only when an <literal>OVER</>
- clause follows the call; otherwise they act as regular aggregates.
+ clause follows the call; otherwise they act as non-window aggregates
+ and return a single row for the entire set.
</para>
<table id="functions-window-table">
@@ -14488,7 +14489,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<entry>
<type>double precision</type>
</entry>
- <entry>relative rank of the current row: (<function>rank</> - 1) / (total rows - 1)</entry>
+ <entry>relative rank of the current row: (<function>rank</> - 1) / (total partition rows - 1)</entry>
</row>
<row>
@@ -14501,7 +14502,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<entry>
<type>double precision</type>
</entry>
- <entry>relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)</entry>
+ <entry>cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows</entry>
</row>
<row>
@@ -14634,9 +14635,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
All of the functions listed in
<xref linkend="functions-window-table"> depend on the sort ordering
specified by the <literal>ORDER BY</> clause of the associated window
- definition. Rows that are not distinct in the <literal>ORDER BY</>
- ordering are said to be <firstterm>peers</>; the four ranking functions
- are defined so that they give the same answer for any two peer rows.
+ definition. Rows that are not distinct when considering only the
+ <literal>ORDER BY</> columns are said to be <firstterm>peers</>.
+ The four ranking functions (including <function>cume_dist</>) are
+ defined so that they give the same answer for all peer rows.
</para>
<para>
@@ -14679,6 +14681,14 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
</para>
</note>
+ <para>
+ <function>cume_dist</> computes the fraction of partition rows that
+ are less than or equal to the current row and its peers, while
+ <function>percent_rank</> computes the fraction of partition rows that
+ are less than the current row, assuming the current row does not exist
+ in the partition.
+ </para>
+
</sect1>
<sect1 id="functions-subquery">
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 40f722c..4746ae5 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1779,10 +1779,10 @@ FROM generate_series(1,10) AS s(i);
<para>
A <firstterm>window function call</firstterm> represents the application
of an aggregate-like function over some portion of the rows selected
- by a query. Unlike regular aggregate function calls, this is not tied
+ by a query. Unlike non-window aggregate calls, this is not tied
to grouping of the selected rows into a single output row — each
row remains separate in the query output. However the window function
- is able to scan all the rows that would be part of the current row's
+ has access to all the rows that would be part of the current row's
group according to the grouping specification (<literal>PARTITION BY</>
list) of the window function call.
The syntax of a window function call is one of the following:
@@ -1831,20 +1831,20 @@ UNBOUNDED FOLLOWING
named window in the <literal>WINDOW</literal> clause; see the
<xref linkend="sql-select"> reference page for details. It's worth
pointing out that <literal>OVER wname</> is not exactly equivalent to
- <literal>OVER (wname)</>; the latter implies copying and modifying the
+ <literal>OVER (wname ...)</>; the latter implies copying and modifying the
window definition, and will be rejected if the referenced window
specification includes a frame clause.
</para>
<para>
- The <literal>PARTITION BY</> option groups the rows of the query into
+ The <literal>PARTITION BY</> clause groups the rows of the query into
<firstterm>partitions</>, which are processed separately by the window
function. <literal>PARTITION BY</> works similarly to a query-level
<literal>GROUP BY</> clause, except that its expressions are always just
expressions and cannot be output-column names or numbers.
Without <literal>PARTITION BY</>, all rows produced by the query are
treated as a single partition.
- The <literal>ORDER BY</> option determines the order in which the rows
+ The <literal>ORDER BY</> clause determines the order in which the rows
of a partition are processed by the window function. It works similarly
to a query-level <literal>ORDER BY</> clause, but likewise cannot use
output-column names or numbers. Without <literal>ORDER BY</>, rows are
@@ -1922,8 +1922,8 @@ UNBOUNDED FOLLOWING
The built-in window functions are described in <xref
linkend="functions-window-table">. Other window functions can be added by
the user. Also, any built-in or user-defined normal aggregate function
- can be used as a window function. Ordered-set aggregates presently
- cannot be used as window functions, however.
+ can be used as a window function. However, ordered-set aggregates
+ cannot presently be used as window functions.
</para>
<para>
* Re: Window function docs
@ 2017-04-07 01:43 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2017-04-07 01:43 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs
On Thu, Apr 6, 2017 at 6:14 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Thu, Apr 6, 2017 at 03:53:24PM -0700, David G. Johnston wrote:
>
> > hypothetical-set aggregates as being distinct from ordered-set
> aggregates yet
> > the tables and the docs here do.
>
> Do you have any suggested text for that?
>
- The built-in ordered-set aggregate functions are listed in Table 9-53
and Table 9-54.
+ The built-in ordered-set aggregate functions are listed in Table 9-53
<https://www.postgresql.org/docs/9.6/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE;
while
the built-in hypothetical-set ones are in Table 9-54
<https://www.postgresql.org/docs/9.6/static/functions-aggregate.html#FUNCTIONS-HYPOTHETICAL-TABLE;
.
That's a direct translation. Reading some more, though, I think that:
+ The built-in within-group aggregate functions are listed in Table 9-53
(ordered-set) and Table 9-54 (hypothetical-set).
So now we'd have:
*grouping-aggregates:*
normal aggregates (no suffix modifiers, though can embed ORDER BY in the
aggregate function argument area)
*within-group aggregates (requires WITHIN GROUP suffix modifier)*
ordered-set aggregates -> given parameters determine a record value,
possibly imputed (within the defined group)
hypothetical-set aggregates -> given a (virtual) record determine a
parameter (within the defined group)
*non-grouping aggregates:*
window aggregates (requires OVER suffix modifier)
The CREATE AGGREGATE only names those three specific types in the notes
section.
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window function docs
@ 2017-04-07 01:52 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2017-04-07 01:52 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-docs
On Thu, Apr 6, 2017 at 06:43:38PM -0700, David G. Johnston wrote:
> On Thu, Apr 6, 2017 at 6:14 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Thu, Apr 6, 2017 at 03:53:24PM -0700, David G. Johnston wrote:
>
> > hypothetical-set aggregates as being distinct from ordered-set aggregates
> yet
> > the tables and the docs here do.
>
> Do you have any suggested text for that?
>
>
> - The built-in ordered-set aggregate functions are listed in Table 9-53 and
> Table 9-54.
> + The built-in ordered-set aggregate functions are listed in Table 9-53 while
> the built-in hypothetical-set ones are in Table 9-54.
>
> That's a direct translation. Reading some more, though, I think that:
>
> + The built-in within-group aggregate functions are listed in Table 9-53
> (ordered-set) and Table 9-54 (hypothetical-set).
I went with this, for simplicity:
The built-in within-group ordered-set aggregate functions
are listed in <xref linkend="functions-orderedset-table">
while the built-in within-group hypothetical-set ones are in <xref
linkend="functions-hypothetical-table">.
> So now we'd have:
>
> grouping-aggregates:
> normal aggregates (no suffix modifiers, though can embed ORDER BY in the
> aggregate function argument area)
>
> within-group aggregates (requires WITHIN GROUP suffix modifier)
> ordered-set aggregates -> given parameters determine a record value, possibly
> imputed (within the defined group)
> hypothetical-set aggregates -> given a (virtual) record determine a parameter
> (within the defined group)
>
> non-grouping aggregates:
> window aggregates (requires OVER suffix modifier)
>
> The CREATE AGGREGATE only names those three specific types in the notes
> section.
Yes, very complicated.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window function docs
@ 2017-04-07 16:28 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 2017-04-07 16:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
On Thu, Apr 6, 2017 at 06:13:44PM -0400, Tom Lane wrote:
> avg() is going to be evaluated across the current frame, not the whole
> partition. I don't mind trying to be more precise than "appropriate set",
> but it doesn't help to be apparently precise but wrong.
>
> Not really convinced by the s/regular/normal/g changes. That doesn't seem
> to clarify much either. Maybe it would help to define "normal" near here.
> There's a definition in func.sgml (that they are not ordered-set or
> hypothetical-set aggregates) but that's too far away.
This change is not on my patch yes but what do you think about changing
"regular" and "normal" aggregate designations to "generic"? That seems
clearer because the aggregates work in non-window and window contexts.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window function docs
@ 2017-04-07 17:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2017-04-07 17:06 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs
Bruce Momjian <bruce@momjian.us> writes:
> On Thu, Apr 6, 2017 at 06:13:44PM -0400, Tom Lane wrote:
>> Not really convinced by the s/regular/normal/g changes. That doesn't seem
>> to clarify much either. Maybe it would help to define "normal" near here.
>> There's a definition in func.sgml (that they are not ordered-set or
>> hypothetical-set aggregates) but that's too far away.
> This change is not on my patch yes but what do you think about changing
> "regular" and "normal" aggregate designations to "generic"? That seems
> clearer because the aggregates work in non-window and window contexts.
I don't think "generic" is le mot juste here. What's more generic about
avg() than about mode(), for instance?
My dictionary says "generic" means "characteristic of a class or group
of things", which doesn't seem to have anything to do with this.
I actually think "regular" might be a better choice than "normal" for this
purpose, mainly because somebody looking for statistical aggregates might
think that "normal" has something to do with normal distributions. So
maybe we should leave that one group of uses alone and s/normal/regular/
in section 9.20. But in any case it would help to define the term
explicitly in both sections.
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Window function docs
@ 2017-04-10 19:15 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 2017-04-10 19:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
On Fri, Apr 7, 2017 at 01:06:53PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > This change is not on my patch yes but what do you think about changing
> > "regular" and "normal" aggregate designations to "generic"? That seems
> > clearer because the aggregates work in non-window and window contexts.
>
> I don't think "generic" is le mot juste here. What's more generic about
> avg() than about mode(), for instance?
>
> My dictionary says "generic" means "characteristic of a class or group
> of things", which doesn't seem to have anything to do with this.
>
> I actually think "regular" might be a better choice than "normal" for this
> purpose, mainly because somebody looking for statistical aggregates might
> think that "normal" has something to do with normal distributions. So
> maybe we should leave that one group of uses alone and s/normal/regular/
> in section 9.20. But in any case it would help to define the term
> explicitly in both sections.
I don't think adding another defined term in this dense text is going to
help so I just specified "general-purpose and statistical aggregates"
where needed; patch attached.
I didn't touch any mention of "normal" in reference to
pg_aggregate.aggfnoid where normal/"n" has a specific meaning.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
window.diff (text/x-diff, 22.5K) download
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
new file mode 100644
index 1e45511..7563131
*** a/doc/src/sgml/advanced.sgml
--- b/doc/src/sgml/advanced.sgml
*************** COMMIT;
*** 328,335 ****
A <firstterm>window function</> performs a calculation across a set of
table rows that are somehow related to the current row. This is comparable
to the type of calculation that can be done with an aggregate function.
! But unlike regular aggregate functions, use of a window function does not
! cause rows to become grouped into a single output row — the
rows retain their separate identities. Behind the scenes, the window
function is able to access more than just the current row of the query
result.
--- 328,335 ----
A <firstterm>window function</> performs a calculation across a set of
table rows that are somehow related to the current row. This is comparable
to the type of calculation that can be done with an aggregate function.
! However, window functions do not cause rows to become grouped into a single
! output row like non-window aggregate calls would. Instead, the
rows retain their separate identities. Behind the scenes, the window
function is able to access more than just the current row of the query
result.
*************** SELECT depname, empno, salary, avg(salar
*** 363,382 ****
<structname>empsalary</>, and there is one output row for each row in the
table. The fourth column represents an average taken across all the table
rows that have the same <structfield>depname</> value as the current row.
! (This actually is the same function as the regular <function>avg</>
! aggregate function, but the <literal>OVER</> clause causes it to be
! treated as a window function and computed across an appropriate set of
! rows.)
</para>
<para>
A window function call always contains an <literal>OVER</> clause
directly following the window function's name and argument(s). This is what
! syntactically distinguishes it from a regular function or aggregate
! function. The <literal>OVER</> clause determines exactly how the
rows of the query are split up for processing by the window function.
! The <literal>PARTITION BY</> list within <literal>OVER</> specifies
! dividing the rows into groups, or partitions, that share the same
values of the <literal>PARTITION BY</> expression(s). For each row,
the window function is computed across the rows that fall into the
same partition as the current row.
--- 363,381 ----
<structname>empsalary</>, and there is one output row for each row in the
table. The fourth column represents an average taken across all the table
rows that have the same <structfield>depname</> value as the current row.
! (This actually is the same function as the non-window <function>avg</>
! aggregate, but the <literal>OVER</> clause causes it to be
! treated as a window function and computed across the window frame.)
</para>
<para>
A window function call always contains an <literal>OVER</> clause
directly following the window function's name and argument(s). This is what
! syntactically distinguishes it from a normal function or non-window
! aggregate. The <literal>OVER</> clause determines exactly how the
rows of the query are split up for processing by the window function.
! The <literal>PARTITION BY</> clause within <literal>OVER</>
! divides the rows into groups, or partitions, that share the same
values of the <literal>PARTITION BY</> expression(s). For each row,
the window function is computed across the rows that fall into the
same partition as the current row.
*************** FROM empsalary;
*** 411,418 ****
</screen>
As shown here, the <function>rank</> function produces a numerical rank
! within the current row's partition for each distinct <literal>ORDER BY</>
! value, in the order defined by the <literal>ORDER BY</> clause.
<function>rank</> needs no explicit parameter, because its behavior
is entirely determined by the <literal>OVER</> clause.
</para>
--- 410,417 ----
</screen>
As shown here, the <function>rank</> function produces a numerical rank
! for each distinct <literal>ORDER BY</> value in the current row's
! partition, using the order defined by the <literal>ORDER BY</> clause.
<function>rank</> needs no explicit parameter, because its behavior
is entirely determined by the <literal>OVER</> clause.
</para>
*************** FROM empsalary;
*** 424,443 ****
if any. For example, a row removed because it does not meet the
<literal>WHERE</> condition is not seen by any window function.
A query can contain multiple window functions that slice up the data
! in different ways by means of different <literal>OVER</> clauses, but
they all act on the same collection of rows defined by this virtual table.
</para>
<para>
We already saw that <literal>ORDER BY</> can be omitted if the ordering
of rows is not important. It is also possible to omit <literal>PARTITION
! BY</>, in which case there is just one partition containing all the rows.
</para>
<para>
There is another important concept associated with window functions:
for each row, there is a set of rows within its partition called its
! <firstterm>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 <literal>ORDER BY</> is supplied then the frame consists of
all rows from the start of the partition up through the current row, plus
--- 423,442 ----
if any. For example, a row removed because it does not meet the
<literal>WHERE</> condition is not seen by any window function.
A query can contain multiple window functions that slice up the data
! in different ways using different <literal>OVER</> clauses, but
they all act on the same collection of rows defined by this virtual table.
</para>
<para>
We already saw that <literal>ORDER BY</> can be omitted if the ordering
of rows is not important. It is also possible to omit <literal>PARTITION
! BY</>, in which case there is a single partition containing all rows.
</para>
<para>
There is another important concept associated with window functions:
for each row, there is a set of rows within its partition called its
! <firstterm>window frame</>. Some window functions act only
on the rows of the window frame, rather than of the whole partition.
By default, if <literal>ORDER BY</> is supplied then the frame consists of
all rows from the start of the partition up through the current row, plus
*************** SELECT salary, sum(salary) OVER (ORDER B
*** 515,521 ****
elsewhere, such as in <literal>GROUP BY</>, <literal>HAVING</>
and <literal>WHERE</literal> clauses. This is because they logically
execute after the processing of those clauses. Also, window functions
! execute after regular aggregate functions. This means it is valid to
include an aggregate function call in the arguments of a window function,
but not vice versa.
</para>
--- 514,520 ----
elsewhere, such as in <literal>GROUP BY</>, <literal>HAVING</>
and <literal>WHERE</literal> clauses. This is because they logically
execute after the processing of those clauses. Also, window functions
! execute after non-window aggregate functions. This means it is valid to
include an aggregate function call in the arguments of a window function,
but not vice versa.
</para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 6d7c7b8..adab303
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** NULL baz</literallayout>(3 rows)</entry>
*** 13166,13178 ****
<para>
<firstterm>Aggregate functions</firstterm> compute a single result
! from a set of input values. The built-in normal aggregate functions
! are listed in
! <xref linkend="functions-aggregate-table"> and
! <xref linkend="functions-aggregate-statistics-table">.
! The built-in ordered-set aggregate functions
! are listed in <xref linkend="functions-orderedset-table"> and
! <xref linkend="functions-hypothetical-table">. Grouping operations,
which are closely related to aggregate functions, are listed in
<xref linkend="functions-grouping-table">.
The special syntax considerations for aggregate
--- 13166,13179 ----
<para>
<firstterm>Aggregate functions</firstterm> compute a single result
! from a set of input values. The built-in general-purpose aggregate
! functions are listed in <xref linkend="functions-aggregate-table">
! and statistical aggregates in <xref
! linkend="functions-aggregate-statistics-table">.
! The built-in within-group ordered-set aggregate functions
! are listed in <xref linkend="functions-orderedset-table">
! while the built-in within-group hypothetical-set ones are in <xref
! linkend="functions-hypothetical-table">. Grouping operations,
which are closely related to aggregate functions, are listed in
<xref linkend="functions-grouping-table">.
The special syntax considerations for aggregate
*************** SELECT xmlagg(x) FROM (SELECT x FROM tes
*** 14413,14429 ****
<para>
The built-in window functions are listed in
<xref linkend="functions-window-table">. Note that these functions
! <emphasis>must</> be invoked using window function syntax; that is an
<literal>OVER</> clause is required.
</para>
<para>
! In addition to these functions, any built-in or user-defined normal
! aggregate function (but not ordered-set or hypothetical-set aggregates)
can be used as a window function; see
<xref linkend="functions-aggregate"> for a list of the built-in aggregates.
Aggregate functions act as window functions only when an <literal>OVER</>
! clause follows the call; otherwise they act as regular aggregates.
</para>
<table id="functions-window-table">
--- 14414,14432 ----
<para>
The built-in window functions are listed in
<xref linkend="functions-window-table">. Note that these functions
! <emphasis>must</> be invoked using window function syntax, i.e., an
<literal>OVER</> clause is required.
</para>
<para>
! In addition to these functions, any built-in or user-defined
! general-purpose or statistical
! aggregate (i.e., not ordered-set or hypothetical-set aggregates)
can be used as a window function; see
<xref linkend="functions-aggregate"> for a list of the built-in aggregates.
Aggregate functions act as window functions only when an <literal>OVER</>
! clause follows the call; otherwise they act as non-window aggregates
! and return a single row for the entire set.
</para>
<table id="functions-window-table">
*************** SELECT xmlagg(x) FROM (SELECT x FROM tes
*** 14488,14494 ****
<entry>
<type>double precision</type>
</entry>
! <entry>relative rank of the current row: (<function>rank</> - 1) / (total rows - 1)</entry>
</row>
<row>
--- 14491,14497 ----
<entry>
<type>double precision</type>
</entry>
! <entry>relative rank of the current row: (<function>rank</> - 1) / (total partition rows - 1)</entry>
</row>
<row>
*************** SELECT xmlagg(x) FROM (SELECT x FROM tes
*** 14501,14507 ****
<entry>
<type>double precision</type>
</entry>
! <entry>relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)</entry>
</row>
<row>
--- 14504,14510 ----
<entry>
<type>double precision</type>
</entry>
! <entry>cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows</entry>
</row>
<row>
*************** SELECT xmlagg(x) FROM (SELECT x FROM tes
*** 14634,14642 ****
All of the functions listed in
<xref linkend="functions-window-table"> depend on the sort ordering
specified by the <literal>ORDER BY</> clause of the associated window
! definition. Rows that are not distinct in the <literal>ORDER BY</>
! ordering are said to be <firstterm>peers</>; the four ranking functions
! are defined so that they give the same answer for any two peer rows.
</para>
<para>
--- 14637,14646 ----
All of the functions listed in
<xref linkend="functions-window-table"> depend on the sort ordering
specified by the <literal>ORDER BY</> clause of the associated window
! definition. Rows that are not distinct when considering only the
! <literal>ORDER BY</> columns are said to be <firstterm>peers</>.
! The four ranking functions (including <function>cume_dist</>) are
! defined so that they give the same answer for all peer rows.
</para>
<para>
*************** SELECT xmlagg(x) FROM (SELECT x FROM tes
*** 14679,14684 ****
--- 14683,14696 ----
</para>
</note>
+ <para>
+ <function>cume_dist</> computes the fraction of partition rows that
+ are less than or equal to the current row and its peers, while
+ <function>percent_rank</> computes the fraction of partition rows that
+ are less than the current row, assuming the current row does not exist
+ in the partition.
+ </para>
+
</sect1>
<sect1 id="functions-subquery">
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
new file mode 100644
index 40f722c..a2d136e
*** a/doc/src/sgml/syntax.sgml
--- b/doc/src/sgml/syntax.sgml
*************** SELECT string_agg(a ORDER BY a, ',') FRO
*** 1664,1670 ****
<para>
Placing <literal>ORDER BY</> within the aggregate's regular argument
list, as described so far, is used when ordering the input rows for
! a <quote>normal</> aggregate for which ordering is optional. There is a
subclass of aggregate functions called <firstterm>ordered-set
aggregates</> for which an <replaceable>order_by_clause</replaceable>
is <emphasis>required</>, usually because the aggregate's computation is
--- 1664,1671 ----
<para>
Placing <literal>ORDER BY</> within the aggregate's regular argument
list, as described so far, is used when ordering the input rows for
! general-purpose and statistical aggregates, for which ordering is
! optional. There is a
subclass of aggregate functions called <firstterm>ordered-set
aggregates</> for which an <replaceable>order_by_clause</replaceable>
is <emphasis>required</>, usually because the aggregate's computation is
*************** SELECT string_agg(a ORDER BY a, ',') FRO
*** 1675,1681 ****
inside <literal>WITHIN GROUP (...)</>, as shown in the final syntax
alternative above. The expressions in
the <replaceable>order_by_clause</replaceable> are evaluated once per
! input row just like normal aggregate arguments, sorted as per
the <replaceable>order_by_clause</replaceable>'s requirements, and fed
to the aggregate function as input arguments. (This is unlike the case
for a non-<literal>WITHIN GROUP</> <replaceable>order_by_clause</>,
--- 1676,1682 ----
inside <literal>WITHIN GROUP (...)</>, as shown in the final syntax
alternative above. The expressions in
the <replaceable>order_by_clause</replaceable> are evaluated once per
! input row just like regular aggregate arguments, sorted as per
the <replaceable>order_by_clause</replaceable>'s requirements, and fed
to the aggregate function as input arguments. (This is unlike the case
for a non-<literal>WITHIN GROUP</> <replaceable>order_by_clause</>,
*************** SELECT string_agg(a ORDER BY a, ',') FRO
*** 1683,1689 ****
argument expressions preceding <literal>WITHIN GROUP</>, if any, are
called <firstterm>direct arguments</> to distinguish them from
the <firstterm>aggregated arguments</> listed in
! the <replaceable>order_by_clause</replaceable>. Unlike normal aggregate
arguments, direct arguments are evaluated only once per aggregate call,
not once per input row. This means that they can contain variables only
if those variables are grouped by <literal>GROUP BY</>; this restriction
--- 1684,1690 ----
argument expressions preceding <literal>WITHIN GROUP</>, if any, are
called <firstterm>direct arguments</> to distinguish them from
the <firstterm>aggregated arguments</> listed in
! the <replaceable>order_by_clause</replaceable>. Unlike regular aggregate
arguments, direct arguments are evaluated only once per aggregate call,
not once per input row. This means that they can contain variables only
if those variables are grouped by <literal>GROUP BY</>; this restriction
*************** FROM generate_series(1,10) AS s(i);
*** 1779,1788 ****
<para>
A <firstterm>window function call</firstterm> represents the application
of an aggregate-like function over some portion of the rows selected
! by a query. Unlike regular aggregate function calls, this is not tied
to grouping of the selected rows into a single output row — each
row remains separate in the query output. However the window function
! is able to scan all the rows that would be part of the current row's
group according to the grouping specification (<literal>PARTITION BY</>
list) of the window function call.
The syntax of a window function call is one of the following:
--- 1780,1789 ----
<para>
A <firstterm>window function call</firstterm> represents the application
of an aggregate-like function over some portion of the rows selected
! by a query. Unlike non-window aggregate calls, this is not tied
to grouping of the selected rows into a single output row — each
row remains separate in the query output. However the window function
! has access to all the rows that would be part of the current row's
group according to the grouping specification (<literal>PARTITION BY</>
list) of the window function call.
The syntax of a window function call is one of the following:
*************** UNBOUNDED FOLLOWING
*** 1831,1850 ****
named window in the <literal>WINDOW</literal> clause; see the
<xref linkend="sql-select"> reference page for details. It's worth
pointing out that <literal>OVER wname</> is not exactly equivalent to
! <literal>OVER (wname)</>; the latter implies copying and modifying the
window definition, and will be rejected if the referenced window
specification includes a frame clause.
</para>
<para>
! The <literal>PARTITION BY</> option groups the rows of the query into
<firstterm>partitions</>, which are processed separately by the window
function. <literal>PARTITION BY</> works similarly to a query-level
<literal>GROUP BY</> clause, except that its expressions are always just
expressions and cannot be output-column names or numbers.
Without <literal>PARTITION BY</>, all rows produced by the query are
treated as a single partition.
! The <literal>ORDER BY</> option determines the order in which the rows
of a partition are processed by the window function. It works similarly
to a query-level <literal>ORDER BY</> clause, but likewise cannot use
output-column names or numbers. Without <literal>ORDER BY</>, rows are
--- 1832,1851 ----
named window in the <literal>WINDOW</literal> clause; see the
<xref linkend="sql-select"> reference page for details. It's worth
pointing out that <literal>OVER wname</> is not exactly equivalent to
! <literal>OVER (wname ...)</>; the latter implies copying and modifying the
window definition, and will be rejected if the referenced window
specification includes a frame clause.
</para>
<para>
! The <literal>PARTITION BY</> clause groups the rows of the query into
<firstterm>partitions</>, which are processed separately by the window
function. <literal>PARTITION BY</> works similarly to a query-level
<literal>GROUP BY</> clause, except that its expressions are always just
expressions and cannot be output-column names or numbers.
Without <literal>PARTITION BY</>, all rows produced by the query are
treated as a single partition.
! The <literal>ORDER BY</> clause determines the order in which the rows
of a partition are processed by the window function. It works similarly
to a query-level <literal>ORDER BY</> clause, but likewise cannot use
output-column names or numbers. Without <literal>ORDER BY</>, rows are
*************** UNBOUNDED FOLLOWING
*** 1921,1937 ****
<para>
The built-in window functions are described in <xref
linkend="functions-window-table">. Other window functions can be added by
! the user. Also, any built-in or user-defined normal aggregate function
! can be used as a window function. Ordered-set aggregates presently
! cannot be used as window functions, however.
</para>
<para>
The syntaxes using <literal>*</> are used for calling parameter-less
aggregate functions as window functions, for example
<literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
! The asterisk (<literal>*</>) is customarily not used for non-aggregate window functions.
! Aggregate window functions, unlike normal aggregate functions, do not
allow <literal>DISTINCT</> or <literal>ORDER BY</> to be used within the
function argument list.
</para>
--- 1922,1938 ----
<para>
The built-in window functions are described in <xref
linkend="functions-window-table">. Other window functions can be added by
! the user. Also, any built-in or user-defined general-purpose or
! statistical aggregate can be used as a window function. (Ordered-set
! and hypothetical-set aggregates cannot presently be used as window functions.)
</para>
<para>
The syntaxes using <literal>*</> are used for calling parameter-less
aggregate functions as window functions, for example
<literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
! The asterisk (<literal>*</>) is customarily not used for
! window-specific functions. Window-specific functions do not
allow <literal>DISTINCT</> or <literal>ORDER BY</> to be used within the
function argument list.
</para>
* Re: Window function docs
@ 2017-04-11 14:48 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2017-04-11 14:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
On Mon, Apr 10, 2017 at 03:15:58PM -0400, Bruce Momjian wrote:
> On Fri, Apr 7, 2017 at 01:06:53PM -0400, Tom Lane wrote:
> > I actually think "regular" might be a better choice than "normal" for this
> > purpose, mainly because somebody looking for statistical aggregates might
> > think that "normal" has something to do with normal distributions. So
> > maybe we should leave that one group of uses alone and s/normal/regular/
> > in section 9.20. But in any case it would help to define the term
> > explicitly in both sections.
>
> I don't think adding another defined term in this dense text is going to
> help so I just specified "general-purpose and statistical aggregates"
> where needed; patch attached.
>
> I didn't touch any mention of "normal" in reference to
> pg_aggregate.aggfnoid where normal/"n" has a specific meaning.
Patch applied.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2017-04-11 14:48 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-06 21:49 Window function docs Bruce Momjian <bruce@momjian.us>
2017-04-06 22:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-04-07 00:33 ` Bruce Momjian <bruce@momjian.us>
2017-04-07 16:28 ` Bruce Momjian <bruce@momjian.us>
2017-04-07 17:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-04-10 19:15 ` Bruce Momjian <bruce@momjian.us>
2017-04-11 14:48 ` Bruce Momjian <bruce@momjian.us>
2017-04-06 22:53 ` David G. Johnston <david.g.johnston@gmail.com>
2017-04-07 01:14 ` Bruce Momjian <bruce@momjian.us>
2017-04-07 01:43 ` David G. Johnston <david.g.johnston@gmail.com>
2017-04-07 01:52 ` Bruce Momjian <bruce@momjian.us>
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