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(at)cryptonector(dot)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