Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause

On Tue, 7 Mar 2023 at 16:11, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Mon, Mar 6, 2023 at 7:51 PM David Rowley <dgrowleyml(at)gmail(dot)com> 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@mail.gmail.com