Date: Sun, 27 Nov 2022 13:21:44 -0300
From: William Alves Da Silva <william_silva@unochapeco.edu.br>
To: pgsql-sql@lists.postgresql.org, Ankit Kumar Pandey
 <itsankitkp@gmail.com>
Message-ID: <2cc5a0eb-ef51-4561-9bc5-d015aab92228@Spark>
In-Reply-To: <7f9f9b1d-b612-d0bd-4674-cfb5a8b2d343@gmail.com>
References: <7f9f9b1d-b612-d0bd-4674-cfb5a8b2d343@gmail.com>
Subject: Re: [QUESTION] Window function with partition by and order by
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="63838e9d_79e2a9e3_263"
Archived-At: 
 <https://www.postgresql.org/message-id/2cc5a0eb-ef51-4561-9bc5-d015aab92228%40Spark>
Precedence: bulk

--63838e9d_79e2a9e3_263
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello Ankit.

This behavior is correct. This is because you are using ORDER BY in your =
aggregation function.

Looking at the documentation you will find the following quote:
=22You can also control the order in which rows are processed by window f=
unctions by using ORDER BY within OVER. (The window ORDER BY doesn't even=
 have to match the order in which the rows are produced).=22

So, if you use ORDER BY you are controlling how the row are processed.

If you don't use, the result is like this:

postgres=3D=23 select *, avg(id) over (partition by name) from my=5Fteste=
 ;
=C2=A0id =7C name =7C avg
----+------+--------------------
=C2=A01 =7C A =7C 1.5000000000000000
=C2=A01 =7C A =7C 1.5000000000000000
=C2=A03 =7C A =7C 1.5000000000000000
=C2=A01 =7C A =7C 1.5000000000000000
=C2=A02 =7C B =7C 2.0000000000000000
(5 rows)


--
Regards,
William Alves
On 27 Nov 2022 13:08 -0300, Ankit Kumar Pandey <itsankitkp=40gmail.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 =3D 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=3F
>
>
> Thanks
>
>
> --
> Regards,
> Ankit Kumar Pandey
>
>
>

--63838e9d_79e2a9e3_263
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>Hello Ankit.<br />
<br />
This behavior is correct. This is because you are using ORDER BY in your =
aggregation function.<br />
<br />
Looking at the documentation you will find the following quote:<br />
=22You can also control the order in which rows are processed by window f=
unctions by using ORDER BY within OVER. (The window ORDER BY doesn't even=
 have to match the order in which the rows are produced).=22<br />
<br />
So, if you use ORDER BY you are controlling how the row are processed.<br=
 />
<br />
If you don't use, the result is like this:<br />
<br />
postgres=3D=23 select *, avg(id) over (partition by name) from my=5Fteste=
 ;<br />
&=23160;id =7C name =7C avg<br />
----+------+--------------------<br />
&=23160;1 =7C A =7C 1.5000000000000000<br />
&=23160;1 =7C A =7C 1.5000000000000000<br />
&=23160;3 =7C A =7C 1.5000000000000000<br />
&=23160;1 =7C A =7C 1.5000000000000000<br />
&=23160;2 =7C B =7C 2.0000000000000000<br />
(5 rows)&=23160;</div>
</div>
<div name=3D=22messageSignatureSection=22><br />
<div><br /></div>
<div>
<p style=3D=22caret-color: rgb(213, 218, 222); box-sizing: border-box; ma=
rgin: 0px; padding: 0px; counter-reset: list-1 0 list-2 0 list-3 0 list-4=
 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; white-space: pre-wrap;=22=
>--</p>
<p style=3D=22caret-color: rgb(213, 218, 222); box-sizing: border-box; ma=
rgin: 0px; padding: 0px; counter-reset: list-1 0 list-2 0 list-3 0 list-4=
 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; white-space: pre-wrap;=22=
>Regards,</p>
<p style=3D=22caret-color: rgb(213, 218, 222); box-sizing: border-box; ma=
rgin: 0px; padding: 0px; counter-reset: list-1 0 list-2 0 list-3 0 list-4=
 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; white-space: pre-wrap;=22=
>William Alves</p>
</div>
</div>
<div name=3D=22messageReplySection=22>On 27 Nov 2022 13:08 -0300, Ankit K=
umar Pandey &lt;itsankitkp=40gmail.com&gt;, wrote:<br />
<blockquote type=3D=22cite=22 style=3D=22border-left-color: grey; border-=
left-width: thin; border-left-style: solid; margin: 5px 5px;padding-left:=
 10px;=22>Hello,<br />
<br />
While looking at aggregates in window function, I found something<br />
unusual and would be glad I could get some clarification.<br />
<br />
Consider following table (mytable):<br />
<br />
id, name<br />
<br />
1, A<br />
<br />
1, A<br />
<br />
2, B<br />
<br />
3, A<br />
<br />
1, A<br />
<br />
<br />
select *, avg(id) over (partition by name, order by id) from mytable;<br =
/>
<br />
Output:<br />
<br />
id, name, avg<br />
<br />
1, A, 1<br />
<br />
1, A, 1<br />
<br />
1, A, 1<br />
<br />
3, A, 1.5<br />
<br />
2, B, 2<br />
<br />
<br />
Question is: Average of id for partition name (A) should be 6/4 =3D 1.5<b=
r />
for all rows in that partition but this result is seen only at the last<b=
r />
one row in partition (A). Am I missing here something=3F<br />
<br />
<br />
Thanks<br />
<br />
<br />
--<br />
Regards,<br />
Ankit Kumar Pandey<br />
<br />
<br />
<br /></blockquote>
</div>
</body>
</html>

--63838e9d_79e2a9e3_263--