Re: [PATCH] distinct aggregates within a window function WIP

On 13/01/2020 15:19, Tom Lane wrote:
> Krasiyan Andreev <krasiyan(at)gmail(dot)com> writes:
>> I want to propose to you an old patch for Postgres 11, off-site developed
>> by Oliver Ford,
>> but I have permission from him to publish it and to continue it's
>> development,
>> that allow distinct aggregates, like select sum(distinct nums) within a
>> window function.
> I started to respond by asking whether that's well-defined, but
> reading down further I see that that's not actually what the feature
> is: what it is is attaching DISTINCT to a window function itself.
> I'd still ask whether it's well-defined though, or even minimally
> sensible. Window functions are generally supposed to produce one
> row per input row --- how does that square with the implicit row
> merging of DISTINCT? They're also typically row-order-sensitive
> --- how does that work with DISTINCT?

It's a little strange because the spec says:

<q>
If the window ordering clause or the window framing clause of the window
structure descriptor that describes the <window name or specification>
is present, then no <aggregate function> simply contained in <window
function> shall specify DISTINCT or <ordered set function>.
</q>

So it seems to be well defined if all you have is a partition.

But then it also says:

<q>
DENSE_RANK() OVER WNS is equivalent to the <window function>:
    COUNT (DISTINCT ROW ( VE 1 , ..., VE N ) )
    OVER (WNS1 RANGE UNBOUNDED PRECEDING)
</q>

And that kind of looks like a framing clause there.

> Also, to the extent that
> this is sensible, can't you get the same results already today
> with appropriate use of window framing options?

I don't see how.

I have sometimes wanted this feature so I am +1 on us getting at least a
minimal form of it.

--

Vik