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

Commit2dca4d3

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 parentb664e35 commit2dca4d3

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
@@ -708,40 +708,39 @@ SELECT city FROM weather
708708
<indexterm><primary>HAVING</primary></indexterm>
709709

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

714714
<programlisting>
715-
SELECT city, max(temp_lo)
715+
SELECT city,count(*),max(temp_lo)
716716
FROM weather
717717
GROUP BY city;
718718
</programlisting>
719719

720720
<screen>
721-
city | max
722-
---------------+-----
723-
Hayward | 37
724-
San Francisco | 46
721+
city |count |max
722+
---------------+-------+-----
723+
Hayward | 1 |37
724+
San Francisco | 2 |46
725725
(2 rows)
726726
</screen>
727727

728728
which gives us one output row per city. Each aggregate result is
729729
computed over the table rows matching that city.
730730
We can filter these grouped
731-
rows using <literal>HAVING</literal> and the output count using
732-
<literal>FILTER</literal>:
731+
rows using <literal>HAVING</literal>:
733732

734733
<programlisting>
735-
SELECT city,max(temp_lo),count(*) FILTER (WHEREtemp_lo &lt; 30)
734+
SELECT city, count(*), max(temp_lo)
736735
FROM weather
737736
GROUP BY city
738737
HAVING max(temp_lo) &lt; 40;
739738
</programlisting>
740739

741740
<screen>
742-
city |max |count
743-
---------+-----+-------
744-
Hayward |37 | 5
741+
city |count |max
742+
---------+-------+-----
743+
Hayward | 1 | 37
745744
(1 row)
746745
</screen>
747746

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

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

798831

0 commit comments

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