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

I understand yours note about datatype-specific operations, so I need to
think more generic about it.
About yours additional note, I think that it is not possible to get easy
the same result with appropriate use of window framing options,
because "exclude ties" will not exclude "current row" itself, only peers of
it. So, that is the only difference and reason of DISTINCT aggregate.
Maybe, if we can specify at the same time to "exclude ties" and "exclude
current row" itself, there will not be need of DISTINCT, but right now
I think that nor "exclude ties" nor "exclude groups" or "exclude current
row", can specify it, because they can't be nested or used at the same time.

На пн, 13.01.2020 г. в 16:19 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> написа:

> 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? Also, to the extent that
> this is sensible, can't you get the same results already today
> with appropriate use of window framing options?
>
> > It's a WIP, because it doesn't have tests yet (I will add them later) and
> > also, it works for a int, float, and numeric types,
>
> As a rule of thumb, operations like this should not be coded to be
> datatype-specific. We threw out some features in the original window
> function patch until they could be rewritten to not be limited to a
> hard-coded set of data types (cf commit 0a459cec9), and I don't see
> why we'd apply a lesser standard here. Certainly DISTINCT for
> aggregates has no such limitation.
>
> regards, tom lane
>