Re: Proposal: QUALIFY clause

On 22/07/2025 17:07, Nico Williams wrote:
> On Tue, Jul 22, 2025 at 01:14:20AM -0400, Tom Lane wrote:
>> Nico Williams <nico@cryptonector.com> writes:
>>> On Mon, Jul 21, 2025 at 09:43:15PM -0600, Merlin Moncure wrote:
>>>> Hm, HAVING requires to apply 'group by' which windows functions do not
>>>> require (unlike aggregates).
>>> Pavel's point is precisely to allow HAVING w/o a GROUP BY when there are
>>> window functions since window functions are "+/-" ("more or less")
>>> aggregate functions.  That makes sense to me.
>> No, it's really quite wrong.  Aggregate functions are not equivalent
>> to window functions: if you have both in a query, they execute in
>> separate passes, with the window functions operating on the grouped
>> rows output by the aggregation step (and then filtered by HAVING,
>> if any).
> Pavel doesn't say that window functions are aggregate functions.  Pavel
> said they are +/- (more or less, really, just similar to) aggregate
> functions.  There is a similarity.  But I appreciate the point about
> which passes get which, and that definitely makes the two-HAVING-
> clauses concept much more unwieldy.


Window functions and aggregates have only one thing in common, and that 
is that they can both operate on a window frame. Otherwise the 
difference is night and day.  Especially when you consider nested window 
clauses (that postgres does not support yet).


> I agree that its own clause is best; I just greatly dislike QUALIFY.


Sorry.

-- 

Vik Fearing