1

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:

enter image description here

Why order by clause has impact on aggregation function?

1
  • Results as images are generally not a good idea. It's better to post them as formatted text You can e.g. use the aligned output from psql.
    – user1822
    Jul 27, 2018 at 10:06

1 Answer 1

5

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 the order by expression.

Quote from the manual

There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its window frame. Some window functions act only on the rows of the window frame, rather than of the whole partition. 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. When ORDER BY is omitted the default frame consists of all rows in the partition.

(Emphasis mine)

For a more detailed explanation see this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.