On Mon, Mar 6, 2023 at 7:51 PM David Rowley <dgrowle...@gmail.com> wrote:
> On Tue, 7 Mar 2023 at 12:40, Tom Lane <t...@sss.pgh.pa.us> wrote: > > > > Ben Clements <benhasgonewalk...@gmail.com> writes: > > > As shown above, the following calculated column can bring in the city > name, > > > even though the city name isn't in the GROUP BY: > > > max(city) keep (dense_rank first order by population desc) > > > > You haven't really explained what this does, let alone why it can't > > be implemented with existing features such as FILTER and ORDER BY. > > (It wasn't clear to me until I watched the youtube video.) > Likely KEEP is more flexible than just the given example but I think > that something similar to the example given could be done by inventing > a TOP() and BOTTOM() aggregate. Then you could write something like: > > select > country, > count(*), > max(population), > bottom(city, population) > from > cities > group by > country > having > count(*) > 1 > > the transfn for bottom() would need to remember the city and the > population for the highest yet seen value of the 2nd arg. BOTTOM() remembers the highest value? > Where this wouldn't work would be if multiple columns were > required to tiebreak the sort. > TOP(city, ROW(population, land_area)) ? I'd assume since the whole thing can be done with > a subquery that the entire point of having special syntax for this > would be because we don't want to pay the price of looking at the > table twice, i.e. performance must matter, so the ability to have > parallel aggregates here seems good. > SELECT country, city, rank() over (partition by country order by population desc), count() OVER (partition by country) FROM cities WINDOW_HAVING count > 0 AND rank = 1; That would be, IMO, the idiomatic query form to perform ranking - not abusing GROUP BY. To add this encourages abusing GROUP BY. Though I suppose if there is a sufficient performance gain to be had under GROUP BY the effort might make sense if further improvements to window function processing cannot be found. David J.
