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@gmail.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
