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

2008/12/31 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.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 |          511 | 3 |         16 |         1112 | 3 |         16 |         1216 | 3 |         16 |         1616 | 3
|        16 |         1616 | 3 |         16 |         1622 | 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 |          511 | 3 |         16 |         2212 | 3 |         16 |         2216 | 3 |         16 |         2216 | 3
|        16 |         2216 | 3 |         16 |         2222 | 3 |         16 |         22
 
(13 rows)

it should be noticed in doc?

regards
Pavel Stehule

>                        regards, tom lane
>