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
