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

Commit34d2687

Browse files
committed
Support ORDER BY within aggregate function calls, at long last providing a
non-kluge method for controlling the order in which values are fed to anaggregate function. At the same time eliminate the old implementationrestriction that DISTINCT was only supported for single-argument aggregates.Possibly release-notable behavioral change: formerly, agg(DISTINCT x)dropped null values of x unconditionally. Now, it does so only if theagg transition function is strict; otherwise nulls are treated as DISTINCTnormally would, ie, you get one copy.Andrew Gierth, reviewed by Hitoshi Harada
1 parent6a6efb9 commit34d2687

File tree

31 files changed

+1183
-247
lines changed

31 files changed

+1183
-247
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 26 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.492 2009/11/24 19:21:15 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.493 2009/12/15 17:57:46 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -8440,7 +8440,8 @@ SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),
84408440
The function <function>xmlagg</function> is, unlike the other
84418441
functions described here, an aggregate function. It concatenates the
84428442
input values to the aggregate function call,
8443-
like <function>xmlconcat</function> does.
8443+
much like <function>xmlconcat</function> does, except that concatenation
8444+
occurs across rows rather than across expressions in a single row.
84448445
See <xref linkend="functions-aggregate"> for additional information
84458446
about aggregate functions.
84468447
</para>
@@ -8459,18 +8460,29 @@ SELECT xmlagg(x) FROM test;
84598460
</para>
84608461

84618462
<para>
8462-
To determine the order of the concatenation, something like the
8463-
following approach can be used:
8463+
To determine the order of the concatenation, an <literal>ORDER BY</>
8464+
clause may be added to the aggregate call as described in
8465+
<xref linkend="syntax-aggregates">. For example:
84648466

84658467
<screen><![CDATA[
8466-
SELECT xmlagg(x) FROM (SELECT * FROM testORDER BY y DESC)AS tab;
8468+
SELECT xmlagg(xORDER BY y DESC)FROM test;
84678469
xmlagg
84688470
----------------------
84698471
<bar/><foo>abc</foo>
84708472
]]></screen>
8473+
</para>
84718474

8472-
Again, see <xref linkend="functions-aggregate"> for additional
8473-
information.
8475+
<para>
8476+
The following non-standard approach used to be recommended
8477+
in previous versions, and may still be useful in specific
8478+
cases:
8479+
8480+
<screen><![CDATA[
8481+
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
8482+
xmlagg
8483+
----------------------
8484+
<bar/><foo>abc</foo>
8485+
]]></screen>
84748486
</para>
84758487
</sect3>
84768488

@@ -9887,20 +9899,19 @@ SELECT count(*) FROM sometable;
98879899
The aggregate functions <function>array_agg</function>
98889900
and <function>xmlagg</function>, as well as similar user-defined
98899901
aggregate functions, produce meaningfully different result values
9890-
depending on the order of the input values. In the current
9891-
implementation, the order of the input is in principle unspecified.
9892-
Supplying the input values from a sorted subquery
9893-
will usually work, however. For example:
9902+
depending on the order of the input values. This ordering is
9903+
unspecified by default, but can be controlled by writing an
9904+
<literal>ORDER BY</> clause within the aggregate call, as shown in
9905+
<xref linkend="syntax-aggregates">.
9906+
Alternatively, supplying the input values from a sorted subquery
9907+
will usually work. For example:
98949908

98959909
<screen><![CDATA[
98969910
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
98979911
]]></screen>
98989912

98999913
But this syntax is not allowed in the SQL standard, and is
9900-
not portable to other database systems. A future version of
9901-
<productname>PostgreSQL</> might provide an additional feature to control
9902-
the order in a better-defined way (<literal>xmlagg(expr ORDER BY expr, expr,
9903-
...)</literal>).
9914+
not portable to other database systems.
99049915
</para>
99059916

99069917
<para>

‎doc/src/sgml/syntax.sgml‎

Lines changed: 47 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.138 2009/11/05 23:24:22 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.139 2009/12/15 17:57:46 tgl Exp $ -->
22

33
<chapter id="sql-syntax">
44
<title>SQL Syntax</title>
@@ -1525,17 +1525,19 @@ sqrt(2)
15251525
syntax of an aggregate expression is one of the following:
15261526

15271527
<synopsis>
1528-
<replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable> [ , ... ] )
1529-
<replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable> [ , ... ] )
1530-
<replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable>)
1528+
<replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable> [ , ... ][ <replaceable>order_by_clause</replaceable> ])
1529+
<replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable> [ , ... ][ <replaceable>order_by_clause</replaceable> ])
1530+
<replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ])
15311531
<replaceable>aggregate_name</replaceable> ( * )
15321532
</synopsis>
15331533

15341534
where <replaceable>aggregate_name</replaceable> is a previously
1535-
defined aggregate (possibly qualified with a schema name), and
1535+
defined aggregate (possibly qualified with a schema name),
15361536
<replaceable>expression</replaceable> is
15371537
any value expression that does not itself contain an aggregate
1538-
expression or a window function call.
1538+
expression or a window function call, and
1539+
<replaceable>order_by_clause</replaceable> is a optional
1540+
<literal>ORDER BY</> clause as described below.
15391541
</para>
15401542

15411543
<para>
@@ -1545,8 +1547,9 @@ sqrt(2)
15451547
whether to ignore null values or not &mdash; but all the standard ones do.)
15461548
The second form is the same as the first, since
15471549
<literal>ALL</literal> is the default. The third form invokes the
1548-
aggregate for all distinct non-null values of the expressions found
1549-
in the input rows. The last form invokes the aggregate once for
1550+
aggregate for all distinct values of the expressions found
1551+
in the input rows (ignoring nulls if the function chooses to do so).
1552+
The last form invokes the aggregate once for
15501553
each input row regardless of null or non-null values; since no
15511554
particular input value is specified, it is generally only useful
15521555
for the <function>count(*)</function> aggregate function.
@@ -1560,6 +1563,40 @@ sqrt(2)
15601563
distinct non-null values of <literal>f1</literal>.
15611564
</para>
15621565

1566+
<para>
1567+
Ordinarily, the input rows are fed to the aggregate function in an
1568+
unspecified order. In many cases this does not matter; for example,
1569+
<function>min</> produces the same result no matter what order it
1570+
receives the inputs in. However, some aggregate functions
1571+
(such as <function>array_agg</> and <function>xmlagg</>) produce
1572+
results that depend on the ordering of the input rows. When using
1573+
such an aggregate, the optional <replaceable>order_by_clause</> can be
1574+
used to specify the desired ordering. The <replaceable>order_by_clause</>
1575+
has the same syntax as for a query-level <literal>ORDER BY</> clause, as
1576+
described in <xref linkend="queries-order">, except that its expressions
1577+
are always just expressions and cannot be output-column names or numbers.
1578+
For example:
1579+
1580+
<programlisting>
1581+
SELECT array_agg(a ORDER BY b DESC) FROM table;
1582+
</programlisting>
1583+
</para>
1584+
1585+
<para>
1586+
If <literal>DISTINCT</> is specified in addition to an
1587+
<replaceable>order_by_clause</>, then all the <literal>ORDER BY</>
1588+
expressions must match regular arguments of the aggregate; that is,
1589+
you cannot sort on an expression that is not included in the
1590+
<literal>DISTINCT</> list.
1591+
</para>
1592+
1593+
<note>
1594+
<para>
1595+
The ability to specify both <literal>DISTINCT</> and <literal>ORDER BY</>
1596+
in an aggregate function is a <productname>PostgreSQL</> extension.
1597+
</para>
1598+
</note>
1599+
15631600
<para>
15641601
The predefined aggregate functions are described in <xref
15651602
linkend="functions-aggregate">. Other aggregate functions can be added
@@ -1588,13 +1625,6 @@ sqrt(2)
15881625
appearing only in the result list or <literal>HAVING</> clause
15891626
applies with respect to the query level that the aggregate belongs to.
15901627
</para>
1591-
1592-
<note>
1593-
<para>
1594-
<productname>PostgreSQL</productname> currently does not support
1595-
<literal>DISTINCT</> with more than one input expression.
1596-
</para>
1597-
</note>
15981628
</sect2>
15991629

16001630
<sect2 id="syntax-window-functions">
@@ -1697,7 +1727,8 @@ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
16971727
<literal>count(*) OVER (PARTITION BY x ORDER BY y)</>.
16981728
<literal>*</> is customarily not used for non-aggregate window functions.
16991729
Aggregate window functions, unlike normal aggregate functions, do not
1700-
allow <literal>DISTINCT</> to be used within the function argument list.
1730+
allow <literal>DISTINCT</> or <literal>ORDER BY</> to be used within the
1731+
function argument list.
17011732
</para>
17021733

17031734
<para>

0 commit comments

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