Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-docs-owner+M9175=pgsql+2Ddocs=arkaria.postgresql.org@postgresql.org>)
	id 1cwHqr-00057W-On
	for pgsql-docs@arkaria.postgresql.org; Fri, 07 Apr 2017 00:34:09 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-docs-owner+M9175=pgsql+2Ddocs=arkaria.postgresql.org@postgresql.org>)
	id 1cwHqq-0005hO-Ux
	for pgsql-docs@arkaria.postgresql.org; Fri, 07 Apr 2017 00:34:08 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <bruce@momjian.us>)
	id 1cwHqp-0005g6-Pk
	for pgsql-docs@postgresql.org; Fri, 07 Apr 2017 00:34:07 +0000
Received: from momjian.us ([72.94.173.45])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.84_2)
	(envelope-from <bruce@momjian.us>)
	id 1cwHqh-0006zH-9e
	for pgsql-docs@postgresql.org; Fri, 07 Apr 2017 00:34:07 +0000
Received: from bruce by momjian.us with local (Exim 4.84_2)
	(envelope-from <bruce@momjian.us>)
	id 1cwHqf-0005Hy-Fk; Thu, 06 Apr 2017 20:33:57 -0400
Date: Thu, 6 Apr 2017 20:33:57 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>
Subject: Re: Window function docs
Message-ID: <20170407003357.GB5757@momjian.us>
References: <20170406214918.GA5757@momjian.us>
 <15355.1491516824@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="A6N2fC+uXW/VQSAv"
Content-Disposition: inline
In-Reply-To: <15355.1491516824@sss.pgh.pa.us>
User-Agent: Mutt/1.5.23 (2014-03-12)
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-docs>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-docs.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@postgresql.org>
List-Post: <mailto:pgsql-docs@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-docs>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
X-Mailing-List: pgsql-docs
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org


--A6N2fC+uXW/VQSAv
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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 +

--A6N2fC+uXW/VQSAv
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="window.diff"

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 &mdash; 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 &mdash; 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>

--A6N2fC+uXW/VQSAv
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

--A6N2fC+uXW/VQSAv--