Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cxenc-0004u0-9E for pgsql-docs@arkaria.postgresql.org; Mon, 10 Apr 2017 19:16:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cxenb-0002Kx-SH for pgsql-docs@arkaria.postgresql.org; Mon, 10 Apr 2017 19:16:27 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cxenD-0000xM-5g for pgsql-docs@postgresql.org; Mon, 10 Apr 2017 19:16:03 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cxen9-00074W-MW for pgsql-docs@postgresql.org; Mon, 10 Apr 2017 19:16:01 +0000 Received: from bruce by momjian.us with local (Exim 4.84_2) (envelope-from ) id 1cxen8-0001uX-Fq; Mon, 10 Apr 2017 15:15:58 -0400 Date: Mon, 10 Apr 2017 15:15:58 -0400 From: Bruce Momjian To: Tom Lane Cc: PostgreSQL-documentation Subject: Re: Window function docs Message-ID: <20170410191558.GD9801@momjian.us> References: <20170406214918.GA5757@momjian.us> <15355.1491516824@sss.pgh.pa.us> <20170407162854.GH5757@momjian.us> <13483.1491584813@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="eJnRUKwClWJh1Khz" Content-Disposition: inline In-Reply-To: <13483.1491584813@sss.pgh.pa.us> User-Agent: Mutt/1.5.23 (2014-03-12) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org --eJnRUKwClWJh1Khz Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Fri, Apr 7, 2017 at 01:06:53PM -0400, Tom Lane wrote: > Bruce Momjian 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 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 + --eJnRUKwClWJh1Khz 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 new file mode 100644 index 1e45511..7563131 *** a/doc/src/sgml/advanced.sgml --- b/doc/src/sgml/advanced.sgml *************** COMMIT; *** 328,335 **** A 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 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 **** 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 depname value as the current row. ! (This actually is the same function as the regular avg ! aggregate function, but the OVER clause causes it to be ! treated as a window function and computed across an appropriate set of ! rows.) A window function call always contains an 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 OVER clause determines exactly how the rows of the query are split up for processing by the window function. ! The PARTITION BY list within OVER specifies ! dividing the rows into groups, or partitions, that share the same values of the 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 ---- 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 depname value as the current row. ! (This actually is the same function as the non-window avg ! aggregate, but the OVER clause causes it to be ! treated as a window function and computed across the window frame.) A window function call always contains an 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 OVER clause determines exactly how the rows of the query are split up for processing by the window function. ! The PARTITION BY clause within OVER ! divides the rows into groups, or partitions, that share the same values of the 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 **** As shown here, the rank function produces a numerical rank ! within the current row's partition for each distinct ORDER BY ! value, in the order defined by the ORDER BY clause. rank needs no explicit parameter, because its behavior is entirely determined by the OVER clause. --- 410,417 ---- As shown here, the rank function produces a numerical rank ! for each distinct ORDER BY value in the current row's ! partition, using the order defined by the ORDER BY clause. rank needs no explicit parameter, because its behavior is entirely determined by the OVER clause. *************** FROM empsalary; *** 424,443 **** if any. For example, a row removed because it does not meet the 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 OVER clauses, but they all act on the same collection of rows defined by this virtual table. We already saw that ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION ! BY, in which case there is just one partition containing all the rows. There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its ! window frame. Many (but not all) window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus --- 423,442 ---- if any. For example, a row removed because it does not meet the 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 OVER clauses, but they all act on the same collection of rows defined by this virtual table. We already saw that ORDER BY can be omitted if the ordering of rows is not important. It is also possible to omit PARTITION ! BY, in which case there is a single partition containing all rows. There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its ! window frame. Some window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus *************** SELECT salary, sum(salary) OVER (ORDER B *** 515,521 **** elsewhere, such as in GROUP BY, HAVING and WHERE 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. --- 514,520 ---- elsewhere, such as in GROUP BY, HAVING and WHERE 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. 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(3 rows) *** 13166,13178 **** Aggregate functions compute a single result ! from a set of input values. The built-in normal aggregate functions ! are listed in ! and ! . ! The built-in ordered-set aggregate functions ! are listed in and ! . Grouping operations, which are closely related to aggregate functions, are listed in . The special syntax considerations for aggregate --- 13166,13179 ---- Aggregate functions compute a single result ! from a set of input values. The built-in general-purpose aggregate ! functions are listed in ! and statistical aggregates in . ! The built-in within-group ordered-set aggregate functions ! are listed in ! while the built-in within-group hypothetical-set ones are in . Grouping operations, which are closely related to aggregate functions, are listed in . The special syntax considerations for aggregate *************** SELECT xmlagg(x) FROM (SELECT x FROM tes *** 14413,14429 **** The built-in window functions are listed in . Note that these functions ! must be invoked using window function syntax; that is an OVER clause is required. ! 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 for a list of the built-in aggregates. Aggregate functions act as window functions only when an OVER ! clause follows the call; otherwise they act as regular aggregates. --- 14414,14432 ---- The built-in window functions are listed in . Note that these functions ! must be invoked using window function syntax, i.e., an OVER clause is required. ! 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 for a list of the built-in aggregates. Aggregate functions act as window functions only when an OVER ! clause follows the call; otherwise they act as non-window aggregates ! and return a single row for the entire set.
*************** SELECT xmlagg(x) FROM (SELECT x FROM tes *** 14488,14494 **** double precision ! relative rank of the current row: (rank - 1) / (total rows - 1) --- 14491,14497 ---- double precision ! relative rank of the current row: (rank - 1) / (total partition rows - 1) *************** SELECT xmlagg(x) FROM (SELECT x FROM tes *** 14501,14507 **** double precision ! relative rank of the current row: (number of rows preceding or peer with current row) / (total rows) --- 14504,14510 ---- double precision ! cumulative distribution: (number of partition rows preceding or peer with current row) / total partition rows *************** SELECT xmlagg(x) FROM (SELECT x FROM tes *** 14634,14642 **** All of the functions listed in depend on the sort ordering specified by the ORDER BY clause of the associated window ! definition. Rows that are not distinct in the ORDER BY ! ordering are said to be peers; the four ranking functions ! are defined so that they give the same answer for any two peer rows. --- 14637,14646 ---- All of the functions listed in depend on the sort ordering specified by the ORDER BY clause of the associated window ! definition. Rows that are not distinct when considering only the ! ORDER BY columns are said to be peers. ! The four ranking functions (including cume_dist) are ! defined so that they give the same answer for all peer rows. *************** SELECT xmlagg(x) FROM (SELECT x FROM tes *** 14679,14684 **** --- 14683,14696 ---- + + cume_dist computes the fraction of partition rows that + are less than or equal to the current row and its peers, while + 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. + + 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 **** Placing ORDER BY within the aggregate's regular argument list, as described so far, is used when ordering the input rows for ! a normal aggregate for which ordering is optional. There is a subclass of aggregate functions called ordered-set aggregates for which an order_by_clause is required, usually because the aggregate's computation is --- 1664,1671 ---- Placing 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 ordered-set aggregates for which an order_by_clause is required, usually because the aggregate's computation is *************** SELECT string_agg(a ORDER BY a, ',') FRO *** 1675,1681 **** inside WITHIN GROUP (...), as shown in the final syntax alternative above. The expressions in the order_by_clause are evaluated once per ! input row just like normal aggregate arguments, sorted as per the order_by_clause's requirements, and fed to the aggregate function as input arguments. (This is unlike the case for a non-WITHIN GROUP order_by_clause, --- 1676,1682 ---- inside WITHIN GROUP (...), as shown in the final syntax alternative above. The expressions in the order_by_clause are evaluated once per ! input row just like regular aggregate arguments, sorted as per the order_by_clause's requirements, and fed to the aggregate function as input arguments. (This is unlike the case for a non-WITHIN GROUP order_by_clause, *************** SELECT string_agg(a ORDER BY a, ',') FRO *** 1683,1689 **** argument expressions preceding WITHIN GROUP, if any, are called direct arguments to distinguish them from the aggregated arguments listed in ! the order_by_clause. 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 GROUP BY; this restriction --- 1684,1690 ---- argument expressions preceding WITHIN GROUP, if any, are called direct arguments to distinguish them from the aggregated arguments listed in ! the order_by_clause. 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 GROUP BY; this restriction *************** FROM generate_series(1,10) AS s(i); *** 1779,1788 **** A window function call 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 (PARTITION BY list) of the window function call. The syntax of a window function call is one of the following: --- 1780,1789 ---- A window function call 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 (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 WINDOW clause; see the reference page for details. It's worth pointing out that OVER wname is not exactly equivalent to ! OVER (wname); the latter implies copying and modifying the window definition, and will be rejected if the referenced window specification includes a frame clause. ! The PARTITION BY option groups the rows of the query into partitions, which are processed separately by the window function. PARTITION BY works similarly to a query-level GROUP BY clause, except that its expressions are always just expressions and cannot be output-column names or numbers. Without PARTITION BY, all rows produced by the query are treated as a single partition. ! The 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 ORDER BY clause, but likewise cannot use output-column names or numbers. Without ORDER BY, rows are --- 1832,1851 ---- named window in the WINDOW clause; see the reference page for details. It's worth pointing out that OVER wname is not exactly equivalent to ! OVER (wname ...); the latter implies copying and modifying the window definition, and will be rejected if the referenced window specification includes a frame clause. ! The PARTITION BY clause groups the rows of the query into partitions, which are processed separately by the window function. PARTITION BY works similarly to a query-level GROUP BY clause, except that its expressions are always just expressions and cannot be output-column names or numbers. Without PARTITION BY, all rows produced by the query are treated as a single partition. ! The 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 ORDER BY clause, but likewise cannot use output-column names or numbers. Without ORDER BY, rows are *************** UNBOUNDED FOLLOWING *** 1921,1937 **** The built-in window functions are described in . 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. The syntaxes using * are used for calling parameter-less aggregate functions as window functions, for example count(*) OVER (PARTITION BY x ORDER BY y). ! The asterisk (*) is customarily not used for non-aggregate window functions. ! Aggregate window functions, unlike normal aggregate functions, do not allow DISTINCT or ORDER BY to be used within the function argument list. --- 1922,1938 ---- The built-in window functions are described in . 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.) The syntaxes using * are used for calling parameter-less aggregate functions as window functions, for example count(*) OVER (PARTITION BY x ORDER BY y). ! The asterisk (*) is customarily not used for ! window-specific functions. Window-specific functions do not allow DISTINCT or ORDER BY to be used within the function argument list. --eJnRUKwClWJh1Khz 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 --eJnRUKwClWJh1Khz--