Does Postgres window function perform an implicit filtering when using order by in the partitions?

Question

I'm wondering what is happening here in postgres, consider this ready-to-run snippet

select id, 
    value,
    array_agg(id) over (order by value asc) as "array_agg(id) with order",
    array_agg(id) over () as "array_agg(id) without order"
from
(
    values
    (1, 1000),
    (2, 2000)
) as rows (id, value)
order by id asc

You may notice that i'm using window function to get aggregrates from the window frame. In both projections "array_agg(id) with order" and "array_agg(id) without order", no filtering is beign made. So i wonder, why the column where i'm ordering the partition is actually giving me the impression of filtering by having just one id in the ordered and two in the not ordered, ¿what verb you put here? for me it would be filtering. The weird thing that gets me more paranoid is that the window frame looks like the same in both partitions when I use a not aggregate function like "lead", please serve yourself:

select id, 
    value,
    lead(id) over (order by value asc) as "lead(id) with order",
    lead(id) over () as "lead(id) without order",
    array_agg(id) over (order by value asc) as "array_agg(id) with order",
    array_agg(id) over () as "array_agg(id) without order"
from
(
    values
    (1, 1000),
    (2, 2000)
) as rows (id, value)
order by id asc

I read the official doc about Window Functions with no clues on this. If any one may explain what is the true behind the milanesa i will more than greatful.

Laurenz Albe · Accepted Answer · 2022-01-25 13:46:59Z

3

Like the documentation says:

The frame_clause specifies the set of rows constituting the window frame, which is a subset of the current partition, for those window functions that act on the frame instead of the whole partition.

Now array_agg acts on the frame (it aggregates all rows in the frame), while lead doesn't. The documentation goes on to explain:

The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY, this sets the frame to be all rows from the partition start up through the current row's last ORDER BY peer. Without ORDER BY, this means all rows of the partition are included in the window frame, since all rows become peers of the current row.

So the presence of ORDER BY changes the meaning of the default frame of the window.

edited Jan 25, 2022 at 13:46

answered Jan 25, 2022 at 3:27

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Victor Over a year ago

As always, thanks Laurenz for the answer. So thanks, that explains a lot, the window frame is actually being narrowing when employed order by clause, we could say that ir does a filtering over the resulting window frame (¿what is doing if it is not that?). The thing i still do wonder is what about the lead function, ¿why that in both cases returns the same (correct) value?. If you have something to share about this, you will be most welcome.

Laurenz Albe Over a year ago

I have extended the answer.

Eugene Stanko Over a year ago

I was one step away from creating the same question. @LaurenzAlbe Perfect answer , thanks!

jjanes · Accepted Answer · 2022-01-25 03:32:29Z

3

You have overlooked this paragraph from the tutorial:

By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause.

To overcome that, use this construct:

select id, 
    value,
    array_agg(id) over (order by value asc rows between unbounded preceding and unbounded following) as "array_agg(id) with order",
    array_agg(id) over () as "array_agg(id) without order"
from
(
    values
    (1, 1000),
    (2, 2000)
) as rows (id, value)
order by id asc;

answered Jan 25, 2022 at 3:32

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

1 Comment

Victor Over a year ago

Thanks, i read that but i don't get it, now, thanks to your kind explanation is another story. Thanks again. I get that the frame windown is actually narrow (so it is actually filtering to put a verb here), but the thing is, still, ¿why the lead(id) works like the same in both partitions? because one ordered (and thus narrowed) and other not ordered (thus not narrowed)

Collectives™ on Stack Overflow

Does Postgres window function perform an implicit filtering when using order by in the partitions?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related