Re: sum() over (partition by order) question

2008/12/31 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I didn't expect so ORDER can change result of function sum.
>
> Read the stuff about window frames. The results you show are
> exactly per spec.
>

I have to do it, when I tested last_value and first_value function I
was surprised more - order by changing partitions

postgres=# select a, b, last_value(a) over (partition by b),
last_value(a) over (partition by b order by a) from foo;
a | b | last_value | last_value
----+---+------------+------------
1 | 1 | 5 | 1
1 | 1 | 5 | 1
2 | 1 | 5 | 2
2 | 1 | 5 | 2
4 | 1 | 5 | 4
4 | 1 | 5 | 4
5 | 1 | 5 | 5
11 | 3 | 16 | 11
12 | 3 | 16 | 12
16 | 3 | 16 | 16
16 | 3 | 16 | 16
16 | 3 | 16 | 16
22 | 3 | 16 | 22
(13 rows)

so I have to modify query to get expected values
postgres=# select a, b, last_value(a) over (partition by b),
last_value(a) over (partition by b order by a RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING) from foo;
a | b | last_value | last_value
----+---+------------+------------
1 | 1 | 5 | 5
1 | 1 | 5 | 5
2 | 1 | 5 | 5
2 | 1 | 5 | 5
4 | 1 | 5 | 5
4 | 1 | 5 | 5
5 | 1 | 5 | 5
11 | 3 | 16 | 22
12 | 3 | 16 | 22
16 | 3 | 16 | 22
16 | 3 | 16 | 22
16 | 3 | 16 | 22
22 | 3 | 16 | 22
(13 rows)

it should be noticed in doc?

regards
Pavel Stehule

> regards, tom lane
>