From: | Ankit Kumar Pandey <itsankitkp(at)gmail(dot)com> |
---|---|
To: | pghackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Questions regarding distinct operation implementation |
Date: | 2022-11-23 18:18:20 |
Message-ID: | 84a50dd6-9351-e11c-b46c-7e509f8aacce@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 m/ytable
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2022-11-23 18:30:48 | Re: Document parameter count limit |
Previous Message | Tom Lane | 2022-11-23 18:11:01 | Re: fixing CREATEROLE |