8000 Doc: improve tutorial section about grouped aggregates. · postgrespro/postgres@f05a5e0 · GitHub
[go: up one dir, main page]
Skip to content

    • Commit f05a5e0

    Browse files
    tglsfdctglsfdc
    committed
    Doc: improve tutorial section about grouped aggregates.
    Commit fede154 introduced FILTER by jamming it into the existing example 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 sample data 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 consistency expand all the examples in this segment to do that. Also adjust the example using WHERE ... LIKE so that it'd produce nonempty output with this sample data, and show that output. Back-patch, as the previous patch was. (Sadly, v10 is now out of scope.) Discussion: https://postgr.es/m/166794307526.652.9073408178177444190@wrigleys.postgresql.org
    1 parent b5621b6 commit f05a5e0

    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 the maximum low
    710-
    temperature observed in each city with:
    709+
    BY</literal> clauses. For example, we can get the number of readings
    710+
    and the maximum low temperature 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 (WHERE temp_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 (WHERE temp_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)
    0