Re: Questions regarding distinct operation implementation

Hello,

I have questions regarding distinct operation and would be glad if someone could help me out.

Consider the following table (mytable):

id, name

1, A

1, A

2, B

3, A

1, A

If we do select avg(id) over (partition by name) from mytable, partition logic goes like this:

for A: 1, 1, 3, 1

If we want to implement something like this select avg(distinct id) over (partition by name) from mytable

and remove duplicate by storing last datum of aggregate column (id) and comparing it with current value. It fails here because aggregate column is not sorted within the partition.

Questions:

1. Is sorting prerequisite for finding distinct values?

2. Is it okay to sort aggregate column (within partition) for distinct to work in case of window function?

3. Is an alternative way exists to handle this scenario (because here sort is of no use in aggregation)?

Thanks

-- 
Regards,
Ankit Kumar Pandey

Hi,

After little more digging, I can see that aggregation on Window functions are of running type, it would be bit more effective if a lookup hashtable is created where every value in current aggregate column get inserted. Whenever frame moves ahead, a lookup if performed for presence of duplicate.

On performance standpoint, this might be bad idea though.

Please let me know any opinions on this.

-- 
Regards,
Ankit Kumar Pandey