Обсуждение: 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