Re: [PoC] Implementation of distinct in Window Aggregates

On 04/01/23 18:10, Ankit Kumar Pandey wrote:
> On 29/12/22 20:58, Ankit Kumar Pandey wrote:
> >
> > On 24/12/22 18:22, Ankit Pandey wrote:
> >> Hi,
> >>
> >> This is a PoC patch which implements distinct operation in window
> >> aggregates (without order by and for single column aggregation, final
> >> version may vary wrt these limitations). Purpose of this PoC is to
> >> get feedback on the approach used and corresponding implementation,
> >> any nitpicking as deemed reasonable.
> >>
> >> Distinct operation is mirrored from implementation in nodeAgg.
> >> Existing partitioning logic determines if row is in partition and
> >> when distinct is required, all tuples for the aggregate column are
> >> stored in tuplesort. When finalize_windowaggregate gets called,
> >> tuples are sorted and duplicates are removed, followed by calling the
> >> transition function on each tuple.
> >> When distinct is not required, the above process is skipped and the
> >> transition function gets called directly and nothing gets inserted
> >> into tuplesort.
> >> Note: For each partition, in tuplesort_begin and tuplesort_end is
> >> involved to rinse tuplesort, so at any time, max tuples in tuplesort
> >> is equal to tuples in a particular partition.
> >>
> >> I have verified it for interger and interval column aggregates (to
> >> rule out obvious issues related to data types).
> >>
> >> Sample cases:
> >>
> >> create table mytable(id int, name text);
> >> insert into mytable values(1, 'A');
> >> insert into mytable values(1, 'A');
> >> insert into mytable values(5, 'B');
> >> insert into mytable values(3, 'A');
> >> insert into mytable values(1, 'A');
> >>
> >> select avg(distinct id) over (partition by name) from mytable;
> >>         avg
> >> --------------------
> >> 2.0000000000000000
> >> 2.0000000000000000
> >> 2.0000000000000000
> >> 2.0000000000000000
> >> 5.0000000000000000
> >>
> >> select avg(id) over (partition by name) from mytable;
> >>         avg
> >> --------------------
> >>  1.5000000000000000
> >>  1.5000000000000000
> >>  1.5000000000000000
> >>  1.5000000000000000
> >>  5.0000000000000000
> >>
> >> select avg(distinct id) over () from mytable;
> >>         avg
> >> --------------------
> >>  3.0000000000000000
> >>  3.0000000000000000
> >>  3.0000000000000000
> >>  3.0000000000000000
> >>  3.0000000000000000
> >>
> >> select avg(distinct id)  from mytable;
> >>         avg
> >> --------------------
> >>  3.0000000000000000
> >>
> >> This is my first-time contribution. Please let me know if anything
> >> can be
> >> improved as I`m eager to learn.
> >>
> >> Regards,
> >> Ankit Kumar Pandey
> >
> > Hi all,
> >
> > I know everyone is busy with holidays (well, Happy Holidays!) but I
> > will be glad if someone can take a quick look at this PoC and share
> > thoughts.
> >
> > This is my first time contribution so I am pretty sure there will be
> > some very obvious feedbacks (which will help me to move forward with
> > this change).
> >
> >
> Updated patch with latest master. Last patch was an year old.
>
Attaching patch with rebase from latest HEAD

Thanks,

Ankit