Обсуждение: nicer examples for aggregate calls

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/14/tutorial-agg.html
Description:

currently, all of the examples are very simple, like

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city
    HAVING max(temp_lo) < 40;

this example would be more complex and would allow users to search for
clause "filter":

Finally, if we only care about cities whose names begin with “S” and we want
to calculate the number of observations in each city with temp_lo over 30,
we might do:

SELECT city, max(temp_lo), count(*) filter (temp_lo>30), 
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city
    HAVING max(temp_lo) < 40;

On Sat, Oct 23, 2021 at 01:51:48PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/14/tutorial-agg.html
> Description:
> 
> currently, all of the examples are very simple, like
> 
> SELECT city, max(temp_lo)
>     FROM weather
>     WHERE city LIKE 'S%'            -- (1)
>     GROUP BY city
>     HAVING max(temp_lo) < 40;
> 
> this example would be more complex and would allow users to search for
> clause "filter":
> 
> Finally, if we only care about cities whose names begin with “S” and we want
> to calculate the number of observations in each city with temp_lo over 30,
> we might do:
> 
> SELECT city, max(temp_lo), count(*) filter (temp_lo>30), 
>     FROM weather
>     WHERE city LIKE 'S%'            -- (1)
>     GROUP BY city
>     HAVING max(temp_lo) < 40;

Good idea.  We didn't support FILTER at the time this query was added.
Here is a patch which adds it.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

On Wed, Aug 17, 2022 at 12:20:10PM -0400, Bruce Momjian wrote:
> On Sat, Oct 23, 2021 at 01:51:48PM +0000, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> > 
> > Page: https://www.postgresql.org/docs/14/tutorial-agg.html
> > Description:
> > 
> > currently, all of the examples are very simple, like
> > 
> > SELECT city, max(temp_lo)
> >     FROM weather
> >     WHERE city LIKE 'S%'            -- (1)
> >     GROUP BY city
> >     HAVING max(temp_lo) < 40;
> > 
> > this example would be more complex and would allow users to search for
> > clause "filter":
> > 
> > Finally, if we only care about cities whose names begin with “S” and we want
> > to calculate the number of observations in each city with temp_lo over 30,
> > we might do:
> > 
> > SELECT city, max(temp_lo), count(*) filter (temp_lo>30), 
> >     FROM weather
> >     WHERE city LIKE 'S%'            -- (1)
> >     GROUP BY city
> >     HAVING max(temp_lo) < 40;
> 
> Good idea.  We didn't support FILTER at the time this query was added.
> Here is a patch which adds it.

Patch applied back to PG 10.  Thanks.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson