Re: sum() over (partition by order) question - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: sum() over (partition by order) question
Date
Msg-id 162867790812311419n56e6d0f1qda590db5838d267a@mail.gmail.com
Whole thread Raw
In response to Re: sum() over (partition by order) question  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
2008/12/31 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> 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;
>
>> it should be noticed in doc?
>
> It is --- see the comments at the bottom of
>
> http://developer.postgresql.org/pgdocs/postgres/functions-window.html
>
>                        regards, tom lane
>

there is maybe bug. It works well with explicit definition, but it not
works when I will window
good
postgres=# select a, b, sum(a) over (partition by b order by a ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from foo;a  | b | sum
----+---+----- 1 | 1 |  19 1 | 1 |  19 2 | 1 |  19 2 | 1 |  19 4 | 1 |  19 4 | 1 |  19 5 | 1 |  1911 | 3 |  9312 | 3 |
9316| 3 |  9316 | 3 |  9316 | 3 |  9322 | 3 |  93
 
(13 rows)

wrong
postgres=# select a, b, sum(a) over (w) from foo window w as
(partition by b order by a ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING);a  | b | sum
----+---+----- 1 | 1 |   2 1 | 1 |   2 2 | 1 |   6 2 | 1 |   6 4 | 1 |  14 4 | 1 |  14 5 | 1 |  1911 | 3 |  1112 | 3 |
2316| 3 |  7116 | 3 |  7116 | 3 |  7122 | 3 |  93
 
(13 rows)

regards
Pavel Stehule


pgsql-hackers by date:

Previous
From: "Pavel Stehule"
Date:
Subject: Re: sum() over (partition by order) question
Next
From: "崔岩ccuiyyan@sina.com"
Date:
Subject: Buffer miss ratio