Re: [QUESTION] Window function with partition by and order by

On 27/11/22 21:53, Samed YILDIRIM wrote:
> Hello Ankit,
>
> It is absolutely expected behaviour of a window function with ORDER BY 
> clause. The default frame clause of window definition is *RANGE 
> BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW*. If you add an ORDER BY 
> clause in a window definition, PostgreSQL takes the current row and 
> all rows before it within the partition into calculation. If you don't 
> add, it means all rows within the partition are peers, and PostgreSQL 
> uses all rows for calculation. I'm putting the related part from the 
> documentation and its link below.
>
>     The default framing option is RANGE UNBOUNDED PRECEDING, which is
>     the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; it
>     sets the frame to be all rows from the partition start up through
>     the current row's last peer (a row that the window's ORDER BY
>     clause considers equivalent to the current row; all rows are peers
>     if there is no ORDER BY).
>
> https://www.postgresql.org/docs/15/sql-select.html#SQL-WINDOW
>
> Best regards.
> Samed YILDIRIM
>
>
> On Sun, 27 Nov 2022 at 18:08, Ankit Kumar Pandey 
> <itsankitkp@gmail.com> wrote:
>
>     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
>
>
Okay, I understand this now.

-- 
Regards,
Ankit Kumar Pandey