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?
psql
.