On Tue, 7 Mar 2023 at 16:11, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote: > > On Mon, Mar 6, 2023 at 7:51 PM David Rowley <dgrowleyml@xxxxxxxxx> wrote: >> 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? I was thinking in terms of a window with all values sorted in ascending order. Maybe your mental modal differs from mine. If Ben wants to implement some new aggregate functions in an extension, then he might think of better names. > 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. Ideally, we'd be able to just sort the top-1 value and not the entire window by population desc. Maybe SupportRequestWFuncMonotonic could be extended to instruct WindowAgg to do that for certain functions. Greg was talking about something like this in [1]. Likely that would be easier for row_number() since any number of rows could have rank==1. David [1] https://postgr.es/m/CAM-w4HN7D1wgTnKqUEnjie=E_6kJRC08CuGTLQgSirFPo3kY6A@xxxxxxxxxxxxxx
