public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ankit Kumar Pandey <[email protected]>
To: Samed YILDIRIM <[email protected]>
Cc: [email protected]
Subject: Re: [QUESTION] Window function with partition by and order by
Date: Sun, 27 Nov 2022 22:24:58 +0530
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAAo1mb=+SrSf9h7B55yGOu+Q70Suq7oQ0BQ3QuPJmooOM9MkZA@mail.gmail.com>
References: <[email protected]>
	<CAAo1mb=+SrSf9h7B55yGOu+Q70Suq7oQ0BQ3QuPJmooOM9MkZA@mail.gmail.com>

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 
> <[email protected]> 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
>
>
>
Thanks, this makes sense.

-- 
Regards,
Ankit Kumar Pandey

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: [QUESTION] Window function with partition by and order by
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox