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

Commit2e42296

Browse files
committed
Doc: improve tutorial section about grouped aggregates.
Commitfede154 introduced FILTER by jamming it into the existingexample introducing HAVING, which seems pedagogically poor to me;and it added no information about what the keyword actually does.Not to mention that the claimed output didn't match the sampledata being used in this running example.Revert that and instead make an independent example using FILTER.To help drive home the point that it's a per-aggregate filter,we need to use two aggregates not just one; for consistencyexpand all the examples in this segment to do that.Also adjust the example using WHERE ... LIKE so that it'd producenonempty output with this sample data, and show that output.Back-patch, as the previous patch was. (Sadly, v10 is now outof scope.)Discussion:https://postgr.es/m/166794307526.652.9073408178177444190@wrigleys.postgresql.org
1 parent6dde6ed commit2e42296

File tree

1 file changed

+49
-16
lines changed

1 file changed

+49
-16
lines changed

‎doc/src/sgml/query.sgml

Lines changed: 49 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -706,40 +706,39 @@ SELECT city FROM weather
706706
<indexterm><primary>HAVING</primary></indexterm>
707707

708708
Aggregates are also very useful in combination with <literal>GROUP
709-
BY</literal> clauses. For example, we can get themaximum low
710-
temperature observed in each city with:
709+
BY</literal> clauses. For example, we can get thenumber of readings
710+
and the maximum lowtemperature observed in each city with:
711711

712712
<programlisting>
713-
SELECT city, max(temp_lo)
713+
SELECT city,count(*),max(temp_lo)
714714
FROM weather
715715
GROUP BY city;
716716
</programlisting>
717717

718718
<screen>
719-
city | max
720-
---------------+-----
721-
Hayward | 37
722-
San Francisco | 46
719+
city |count |max
720+
---------------+-------+-----
721+
Hayward | 1 |37
722+
San Francisco | 2 |46
723723
(2 rows)
724724
</screen>
725725

726726
which gives us one output row per city. Each aggregate result is
727727
computed over the table rows matching that city.
728728
We can filter these grouped
729-
rows using <literal>HAVING</literal> and the output count using
730-
<literal>FILTER</literal>:
729+
rows using <literal>HAVING</literal>:
731730

732731
<programlisting>
733-
SELECT city,max(temp_lo),count(*) FILTER (WHEREtemp_lo &lt; 30)
732+
SELECT city, count(*), max(temp_lo)
734733
FROM weather
735734
GROUP BY city
736735
HAVING max(temp_lo) &lt; 40;
737736
</programlisting>
738737

739738
<screen>
740-
city |max |count
741-
---------+-----+-------
742-
Hayward |37 | 5
739+
city |count |max
740+
---------+-------+-----
741+
Hayward | 1 | 37
743742
(1 row)
744743
</screen>
745744

@@ -749,12 +748,18 @@ SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo &lt; 30)
749748
names begin with <quote><literal>S</literal></quote>, we might do:
750749

751750
<programlisting>
752-
SELECT city,max(temp_lo),count(*) FILTER (WHEREtemp_lo &lt; 30)
751+
SELECT city, count(*), max(temp_lo)
753752
FROM weather
754753
WHERE city LIKE 'S%' -- <co id="co.tutorial-agg-like"/>
755-
GROUP BY city
756-
HAVING max(temp_lo) &lt; 40;
754+
GROUP BY city;
757755
</programlisting>
756+
757+
<screen>
758+
city | count | max
759+
---------------+-------+-----
760+
San Francisco | 2 | 46
761+
(1 row)
762+
</screen>
758763
<calloutlist>
759764
<callout arearefs="co.tutorial-agg-like">
760765
<para>
@@ -791,6 +796,34 @@ SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo &lt; 30)
791796
because we avoid doing the grouping and aggregate calculations
792797
for all rows that fail the <literal>WHERE</literal> check.
793798
</para>
799+
800+
<para>
801+
Another way to select the rows that go into an aggregate
802+
computation is to use <literal>FILTER</literal>, which is a
803+
per-aggregate option:
804+
805+
<programlisting>
806+
SELECT city, count(*) FILTER (WHERE temp_lo &lt; 45), max(temp_lo)
807+
FROM weather
808+
GROUP BY city;
809+
</programlisting>
810+
811+
<screen>
812+
city | count | max
813+
---------------+-------+-----
814+
Hayward | 1 | 37
815+
San Francisco | 1 | 46
816+
(2 rows)
817+
</screen>
818+
819+
<literal>FILTER</literal> is much like <literal>WHERE</literal>,
820+
except that it removes rows only from the input of the particular
821+
aggregate function that it is attached to.
822+
Here, the <literal>count</literal> aggregate counts only
823+
rows with <literal>temp_lo</literal> below 45; but the
824+
<literal>max</literal> aggregate is still applied to all rows,
825+
so it still finds the reading of 46.
826+
</para>
794827
</sect1>
795828

796829

0 commit comments

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