public inbox for [email protected]
help / color / mirror / Atom feedFrom: William Alves Da Silva <[email protected]>
To: [email protected]
To: Ankit Kumar Pandey <[email protected]>
Subject: Re: [QUESTION] Window function with partition by and order by
Date: Sun, 27 Nov 2022 13:21:44 -0300
Message-ID: <2cc5a0eb-ef51-4561-9bc5-d015aab92228@Spark> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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:
"You can also control the order in which rows are processed by window functions by using ORDER BY within OVER. (The window ORDER BY doesn't even have to match the order in which the rows are produced)."
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=# select *, avg(id) over (partition by name) from my_teste ;
id | name | avg
----+------+--------------------
1 | A | 1.5000000000000000
1 | A | 1.5000000000000000
3 | A | 1.5000000000000000
1 | A | 1.5000000000000000
2 | B | 2.0000000000000000
(5 rows)
--
Regards,
William Alves
On 27 Nov 2022 13:08 -0300, 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
>
>
>
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: <2cc5a0eb-ef51-4561-9bc5-d015aab92228@Spark>
* 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