Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

In response to

Responses

pgsql-hackers by date

Next:From: Jaime CasanovaDate: 2008-12-31 22:26:39
Subject: Re: sum() over (partition by order) question
Previous:From: Pavel StehuleDate: 2008-12-31 22:13:29
Subject: Re: sum() over (partition by order) question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group