Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1c1a472

Browse files
committed
docs: Improve window function docs
Specifically, the behavior of general-purpose and statistical aggregatesas window functions was not clearly documented, and terms wereinconsistently used. Also add docs about the difference betweencume_dist and percent_rank, rather than just the formulas.Discussion: 20170406214918.GA5757@momjian.us
1 parenta4777f3 commit1c1a472

File tree

3 files changed

+57
-45
lines changed

3 files changed

+57
-45
lines changed

‎doc/src/sgml/advanced.sgml‎

Lines changed: 15 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -328,8 +328,8 @@ COMMIT;
328328
A <firstterm>window function</> performs a calculation across a set of
329329
table rows that are somehow related to the current row. This is comparable
330330
to the type of calculation that can be done with an aggregate function.
331-
But unlike regular aggregate functions, use of a window function does not
332-
cause rows to become grouped into a single output row &mdash; the
331+
However, window functions do not cause rows to become grouped into a single
332+
output row like non-window aggregate calls would. Instead, the
333333
rows retain their separate identities. Behind the scenes, the window
334334
function is able to access more than just the current row of the query
335335
result.
@@ -363,20 +363,19 @@ SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM emps
363363
<structname>empsalary</>, and there is one output row for each row in the
364364
table. The fourth column represents an average taken across all the table
365365
rows that have the same <structfield>depname</> value as the current row.
366-
(This actually is the same function as the regular <function>avg</>
367-
aggregate function, but the <literal>OVER</> clause causes it to be
368-
treated as a window function and computed across an appropriate set of
369-
rows.)
366+
(This actually is the same function as the non-window <function>avg</>
367+
aggregate, but the <literal>OVER</> clause causes it to be
368+
treated as a window function and computed across the window frame.)
370369
</para>
371370

372371
<para>
373372
A window function call always contains an <literal>OVER</> clause
374373
directly following the window function's name and argument(s). This is what
375-
syntactically distinguishes it from aregular function oraggregate
376-
function. The <literal>OVER</> clause determines exactly how the
374+
syntactically distinguishes it from anormal function ornon-window
375+
aggregate. The <literal>OVER</> clause determines exactly how the
377376
rows of the query are split up for processing by the window function.
378-
The <literal>PARTITION BY</>list within <literal>OVER</> specifies
379-
dividing the rows into groups, or partitions, that share the same
377+
The <literal>PARTITION BY</>clause within <literal>OVER</>
378+
divides the rows into groups, or partitions, that share the same
380379
values of the <literal>PARTITION BY</> expression(s). For each row,
381380
the window function is computed across the rows that fall into the
382381
same partition as the current row.
@@ -411,8 +410,8 @@ FROM empsalary;
411410
</screen>
412411

413412
As shown here, the <function>rank</> function produces a numerical rank
414-
within the current row's partitionfor each distinct <literal>ORDER BY</>
415-
value, in the order defined by the <literal>ORDER BY</> clause.
413+
for each distinct <literal>ORDER BY</> value in the current row's
414+
partition, using the order defined by the <literal>ORDER BY</> clause.
416415
<function>rank</> needs no explicit parameter, because its behavior
417416
is entirely determined by the <literal>OVER</> clause.
418417
</para>
@@ -424,20 +423,20 @@ FROM empsalary;
424423
if any. For example, a row removed because it does not meet the
425424
<literal>WHERE</> condition is not seen by any window function.
426425
A query can contain multiple window functions that slice up the data
427-
in different waysby means of different <literal>OVER</> clauses, but
426+
in different waysusing different <literal>OVER</> clauses, but
428427
they all act on the same collection of rows defined by this virtual table.
429428
</para>
430429

431430
<para>
432431
We already saw that <literal>ORDER BY</> can be omitted if the ordering
433432
of rows is not important. It is also possible to omit <literal>PARTITION
434-
BY</>, in which case there isjust one partition containing all the rows.
433+
BY</>, in which case there isa single partition containing all rows.
435434
</para>
436435

437436
<para>
438437
There is another important concept associated with window functions:
439438
for each row, there is a set of rows within its partition called its
440-
<firstterm>window frame</>.Many (but not all) window functions act only
439+
<firstterm>window frame</>.Some window functions act only
441440
on the rows of the window frame, rather than of the whole partition.
442441
By default, if <literal>ORDER BY</> is supplied then the frame consists of
443442
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;
515514
elsewhere, such as in <literal>GROUP BY</>, <literal>HAVING</>
516515
and <literal>WHERE</literal> clauses. This is because they logically
517516
execute after the processing of those clauses. Also, window functions
518-
execute afterregular aggregate functions. This means it is valid to
517+
execute afternon-window aggregate functions. This means it is valid to
519518
include an aggregate function call in the arguments of a window function,
520519
but not vice versa.
521520
</para>

‎doc/src/sgml/func.sgml‎

Lines changed: 28 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -13166,13 +13166,14 @@ NULL baz</literallayout>(3 rows)</entry>
1316613166

1316713167
<para>
1316813168
<firstterm>Aggregate functions</firstterm> compute a single result
13169-
from a set of input values. The built-in normal aggregate functions
13170-
are listed in
13171-
<xref linkend="functions-aggregate-table"> and
13172-
<xref linkend="functions-aggregate-statistics-table">.
13173-
The built-in ordered-set aggregate functions
13174-
are listed in <xref linkend="functions-orderedset-table"> and
13175-
<xref linkend="functions-hypothetical-table">. Grouping operations,
13169+
from a set of input values. The built-in general-purpose aggregate
13170+
functions are listed in <xref linkend="functions-aggregate-table">
13171+
and statistical aggregates in <xref
13172+
linkend="functions-aggregate-statistics-table">.
13173+
The built-in within-group ordered-set aggregate functions
13174+
are listed in <xref linkend="functions-orderedset-table">
13175+
while the built-in within-group hypothetical-set ones are in <xref
13176+
linkend="functions-hypothetical-table">. Grouping operations,
1317613177
which are closely related to aggregate functions, are listed in
1317713178
<xref linkend="functions-grouping-table">.
1317813179
The special syntax considerations for aggregate
@@ -14413,17 +14414,19 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1441314414
<para>
1441414415
The built-in window functions are listed in
1441514416
<xref linkend="functions-window-table">. Note that these functions
14416-
<emphasis>must</> be invoked using window function syntax; that is an
14417+
<emphasis>must</> be invoked using window function syntax, i.e., an
1441714418
<literal>OVER</> clause is required.
1441814419
</para>
1441914420

1442014421
<para>
14421-
In addition to these functions, any built-in or user-defined normal
14422-
aggregate function (but not ordered-set or hypothetical-set aggregates)
14422+
In addition to these functions, any built-in or user-defined
14423+
general-purpose or statistical
14424+
aggregate (i.e., not ordered-set or hypothetical-set aggregates)
1442314425
can be used as a window function; see
1442414426
<xref linkend="functions-aggregate"> for a list of the built-in aggregates.
1442514427
Aggregate functions act as window functions only when an <literal>OVER</>
14426-
clause follows the call; otherwise they act as regular aggregates.
14428+
clause follows the call; otherwise they act as non-window aggregates
14429+
and return a single row for the entire set.
1442714430
</para>
1442814431

1442914432
<table id="functions-window-table">
@@ -14488,7 +14491,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1448814491
<entry>
1448914492
<type>double precision</type>
1449014493
</entry>
14491-
<entry>relative rank of the current row: (<function>rank</> - 1) / (total rows - 1)</entry>
14494+
<entry>relative rank of the current row: (<function>rank</> - 1) / (totalpartitionrows - 1)</entry>
1449214495
</row>
1449314496

1449414497
<row>
@@ -14501,7 +14504,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1450114504
<entry>
1450214505
<type>double precision</type>
1450314506
</entry>
14504-
<entry>relative rank of the current row: (number of rows preceding or peer with current row) /(total rows)</entry>
14507+
<entry>cumulative distribution: (number ofpartitionrows preceding or peer with current row) / totalpartitionrows</entry>
1450514508
</row>
1450614509

1450714510
<row>
@@ -14634,9 +14637,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1463414637
All of the functions listed in
1463514638
<xref linkend="functions-window-table"> depend on the sort ordering
1463614639
specified by the <literal>ORDER BY</> clause of the associated window
14637-
definition. Rows that are not distinct in the <literal>ORDER BY</>
14638-
ordering are said to be <firstterm>peers</>; the four ranking functions
14639-
are defined so that they give the same answer for any two peer rows.
14640+
definition. Rows that are not distinct when considering only the
14641+
<literal>ORDER BY</> columns are said to be <firstterm>peers</>.
14642+
The four ranking functions (including <function>cume_dist</>) are
14643+
defined so that they give the same answer for all peer rows.
1464014644
</para>
1464114645

1464214646
<para>
@@ -14679,6 +14683,14 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
1467914683
</para>
1468014684
</note>
1468114685

14686+
<para>
14687+
<function>cume_dist</> computes the fraction of partition rows that
14688+
are less than or equal to the current row and its peers, while
14689+
<function>percent_rank</> computes the fraction of partition rows that
14690+
are less than the current row, assuming the current row does not exist
14691+
in the partition.
14692+
</para>
14693+
1468214694
</sect1>
1468314695

1468414696
<sect1 id="functions-subquery">

‎doc/src/sgml/syntax.sgml‎

Lines changed: 14 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1664,7 +1664,8 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
16641664
<para>
16651665
Placing <literal>ORDER BY</> within the aggregate's regular argument
16661666
list, as described so far, is used when ordering the input rows for
1667-
a <quote>normal</> aggregate for which ordering is optional. There is a
1667+
general-purpose and statistical aggregates, for which ordering is
1668+
optional. There is a
16681669
subclass of aggregate functions called <firstterm>ordered-set
16691670
aggregates</> for which an <replaceable>order_by_clause</replaceable>
16701671
is <emphasis>required</>, usually because the aggregate's computation is
@@ -1675,15 +1676,15 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
16751676
inside <literal>WITHIN GROUP (...)</>, as shown in the final syntax
16761677
alternative above. The expressions in
16771678
the <replaceable>order_by_clause</replaceable> are evaluated once per
1678-
input row just likenormal aggregate arguments, sorted as per
1679+
input row just likeregular aggregate arguments, sorted as per
16791680
the <replaceable>order_by_clause</replaceable>'s requirements, and fed
16801681
to the aggregate function as input arguments. (This is unlike the case
16811682
for a non-<literal>WITHIN GROUP</> <replaceable>order_by_clause</>,
16821683
which is not treated as argument(s) to the aggregate function.) The
16831684
argument expressions preceding <literal>WITHIN GROUP</>, if any, are
16841685
called <firstterm>direct arguments</> to distinguish them from
16851686
the <firstterm>aggregated arguments</> listed in
1686-
the <replaceable>order_by_clause</replaceable>. Unlikenormal aggregate
1687+
the <replaceable>order_by_clause</replaceable>. Unlikeregular aggregate
16871688
arguments, direct arguments are evaluated only once per aggregate call,
16881689
not once per input row. This means that they can contain variables only
16891690
if those variables are grouped by <literal>GROUP BY</>; this restriction
@@ -1779,10 +1780,10 @@ FROM generate_series(1,10) AS s(i);
17791780
<para>
17801781
A <firstterm>window function call</firstterm> represents the application
17811782
of an aggregate-like function over some portion of the rows selected
1782-
by a query. Unlikeregular aggregate function calls, this is not tied
1783+
by a query. Unlikenon-window aggregate calls, this is not tied
17831784
to grouping of the selected rows into a single output row &mdash; each
17841785
row remains separate in the query output. However the window function
1785-
is able to scan all the rows that would be part of the current row's
1786+
has access to all the rows that would be part of the current row's
17861787
group according to the grouping specification (<literal>PARTITION BY</>
17871788
list) of the window function call.
17881789
The syntax of a window function call is one of the following:
@@ -1831,20 +1832,20 @@ UNBOUNDED FOLLOWING
18311832
named window in the <literal>WINDOW</literal> clause; see the
18321833
<xref linkend="sql-select"> reference page for details. It's worth
18331834
pointing out that <literal>OVER wname</> is not exactly equivalent to
1834-
<literal>OVER (wname)</>; the latter implies copying and modifying the
1835+
<literal>OVER (wname ...)</>; the latter implies copying and modifying the
18351836
window definition, and will be rejected if the referenced window
18361837
specification includes a frame clause.
18371838
</para>
18381839

18391840
<para>
1840-
The <literal>PARTITION BY</>option groups the rows of the query into
1841+
The <literal>PARTITION BY</>clause groups the rows of the query into
18411842
<firstterm>partitions</>, which are processed separately by the window
18421843
function. <literal>PARTITION BY</> works similarly to a query-level
18431844
<literal>GROUP BY</> clause, except that its expressions are always just
18441845
expressions and cannot be output-column names or numbers.
18451846
Without <literal>PARTITION BY</>, all rows produced by the query are
18461847
treated as a single partition.
1847-
The <literal>ORDER BY</>option determines the order in which the rows
1848+
The <literal>ORDER BY</>clause determines the order in which the rows
18481849
of a partition are processed by the window function. It works similarly
18491850
to a query-level <literal>ORDER BY</> clause, but likewise cannot use
18501851
output-column names or numbers. Without <literal>ORDER BY</>, rows are
@@ -1921,17 +1922,17 @@ UNBOUNDED FOLLOWING
19211922
<para>
19221923
The built-in window functions are described in <xref
19231924
linkend="functions-window-table">. Other window functions can be added by
1924-
the user. Also, any built-in or user-definednormal aggregate function
1925-
can be used as a window function. Ordered-set aggregates presently
1926-
cannot be used as window functions, however.
1925+
the user. Also, any built-in or user-definedgeneral-purpose or
1926+
statistical aggregatecan be used as a window function.(Ordered-set
1927+
and hypothetical-set aggregatescannotpresentlybe used as window functions.)
19271928
</para>
19281929

19291930
<para>
19301931
The syntaxes using <literal>*</> are used for calling parameter-less
19311932
aggregate functions as window functions, for example
19321933
<literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
1933-
The asterisk (<literal>*</>) is customarily not used for non-aggregate window functions.
1934-
Aggregatewindow functions, unlike normal aggregatefunctions, do not
1934+
The asterisk (<literal>*</>) is customarily not used for
1935+
window-specific functions. Window-specificfunctions do not
19351936
allow <literal>DISTINCT</> or <literal>ORDER BY</> to be used within the
19361937
function argument list.
19371938
</para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp