From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: sum() over (partition by order) question |
Date: | 2008-12-31 22:19:14 |
Message-ID: | 162867790812311419n56e6d0f1qda590db5838d267a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
2008/12/31 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)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 | 19
11 | 3 | 93
12 | 3 | 93
16 | 3 | 93
16 | 3 | 93
16 | 3 | 93
22 | 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 | 19
11 | 3 | 11
12 | 3 | 23
16 | 3 | 71
16 | 3 | 71
16 | 3 | 71
22 | 3 | 93
(13 rows)
regards
Pavel Stehule
From | Date | Subject | |
---|---|---|---|
Next Message | Jaime Casanova | 2008-12-31 22:26:39 | Re: sum() over (partition by order) question |
Previous Message | Pavel Stehule | 2008-12-31 22:13:29 | Re: sum() over (partition by order) question |