Message-ID: <7f9f9b1d-b612-d0bd-4674-cfb5a8b2d343@gmail.com>
Date: Sun, 27 Nov 2022 21:38:33 +0530
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Content-Language: en-US
To: pgsql-sql@lists.postgresql.org
From: Ankit Kumar Pandey <itsankitkp@gmail.com>
Subject: [QUESTION] Window function with partition by and order by
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/7f9f9b1d-b612-d0bd-4674-cfb5a8b2d343%40gmail.com>
Precedence: bulk

Hello,

While looking at aggregates in window function, I found something 
unusual and would be glad I could get some clarification.

Consider following table (mytable):

id, name

1, A

1, A

2, B

3, A

1, A


select *, avg(id) over (partition by name, order by id) from mytable;

Output:

id, name, avg

1, A, 1

1, A, 1

1, A, 1

3, A, 1.5

2, B, 2


Question is: Average of id for partition name (A) should be 6/4 = 1.5 
for all rows in that partition but this result is seen only at the last 
one row in partition (A). Am I missing here something?


Thanks


-- 
Regards,
Ankit Kumar Pandey