Re: Questions regarding distinct operation implementation

On 02/12/22 00:40, Ankit Kumar Pandey wrote:
>
>
> On 25/11/22 11:00, Ankit Kumar Pandey wrote:
>>
>> On 25/11/22 02:14, David Rowley wrote:
>>> On Fri, 25 Nov 2022 at 06:57, Ankit Kumar Pandey
>>> <itsankitkp(at)gmail(dot)com> wrote:
>>>> Please let me know any opinions on this.
>>> I think if you're planning on working on this then step 1 would have
>>> to be checking the SQL standard to see which set of rows it asks
>>> implementations to consider for duplicate checks when deciding if the
>>> transition should be performed or not.  Having not looked, I don't
>>> know if this is the entire partition or just the rows in the current
>>> frame.
>>>
>>> Depending on what you want, an alternative today would be to run a
>>> subquery to uniquify the rows the way you want and then do the window
>>> function stuff.
>>>
>>> David
>> Thanks David, these are excellent pointers, I will look into SQL
>> standard first and so on.
>>
> Hi,
>
> Looking further into it, I am bit clear about expectations of having
> distinct in Windows Aggregates (although I couldn't got hands on SQL
> standard as it is not in public domain but distinct in windows
> aggregate is supported by Oracle and I am using it as reference).
>
> For table (mytable):
>
> id, name
>
> 1, A
>
> 1, A
>
> 10, B
>
> 3, A
>
> 1, A
>
>
> /select avg(distinct id) over (partition by name)/ from mytable (in
> oracle db) yields:
>
> 2
>
> 2
>
> 2
>
> 2
>
> 10
>
>
> From this, it is seen distinct is taken across the all rows in the
> partition.
>
> I also thought of using a subquery approach: /select avg(id) over
> (partition by name) from (select distinct(id), name from mytable)/
>
> but this obviously doesn't yield right answer because result should
> contain same number of rows as input. This implies we need to find
> partition first and then remove duplicates within the partition.
>
> Can we avoid any ordering/sort until existing logic finds if value is
> in frame (so as to respect any /order by/ clause given by user), and
> once it is determined that tuple is in frame, skip the tuple if it is
> a duplicate? If aforementioned approach is right, question is how do
> we check if it is duplicate? Should we create a lookup table (as
> tuples coming to advance_windowaggregate can be in arbitrary order)?
> Or any other approach would be better?
>
> Any opinion on this will be appreciated.
>
> --
> Regards,
> Ankit Kumar Pandey

Hi,

I am still looking at this but unable to move ahead as I am not able to
use prior use-cases (normal aggregates) to implement distinct in window
function because they both differ in design (and window function is bit
unique in general). One approach (among others) that I thought was that
during spool_tuples, rescan tuplestore and add new tuples only if they
are not already present. This is not very efficient because of multiple
read operation on tuplestore, only for checking if tuple already exists
and other issues (like tuplestore_in_memory forcing entire partition to
get spooled in one go) etc.

Any ideas will be much appreciated.

Thanks.

--
Regards,
Ankit Kumar Pandey