Here is example:
drop table if exists tst;
create table tst (
num integer not null
);
insert into tst values (1), (2), (3);
-- window functions WITH order by clause
select *, max(num) over (partition by true order by num asc), array_agg(num) over (partition by true order by num asc) as test
from tst;
-- window functions WITHOUT order by clause
select *, max(num) over (partition by true), array_agg(num) over (partition by true) as test
from tst;
The result is as follows:
Why order by clause has impact on aggregation function?
Best Answer
That's expected.
An aggregate function used as a window function together with an
order by
acts as a "rolling" aggregate, i.e. it only considers values up to the "current rows" based on theorder by
expression.Quote from the manual
(Emphasis mine)
For a more detailed explanation see this answer