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

I have currently suspended development of this patch, based on it's review,
but I will continue development of the other Oliver Ford's work about
adding support of respect/ignore nulls
for lag(),lead(),first_value(),last_value() and nth_value() and from
first/last for nth_value() patch,
but I am not sure how to proceed with it's implementation and any feedback
will be very helpful.

I have dropped support of from first/last for nth_value(), but also I
reimplemented it in a different way,
by using negative number for the position argument, to be able to get the
same frame in exact reverse order.
After that patch becomes much more simple and major concerns about
precedence hack has gone,
but maybe it can be additionally simplified.

I have not renamed special bool type "ignorenulls", because it is probably
not acceptable way for calling extra version
of window functions (but it makes things very easy and it can reuse
frames), but I removed the other special bool type "fromlast".

Attached file is for PostgreSQL 13 (master git branch) and I will add it
now to a March commit fest, to be able to track changes.
Everything works and patch is in very good shape, all tests are passed and
also, I use it from some time for SQL analysis purposes
(because ignore nulls is one of the most needed feature in OLAP/BI area and
Oracle, Amazon Redshift, even Informix have it).

After patch review and suggestions about what to do with special bool type
and unreserved keywords, I will reimplement it, if needed.

На пн, 13.01.2020 г. в 18:19 Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com>
написа:

> 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
>
>