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 21:55:22
Message-ID: 162867790812311355yb447639p609e4168d21d9cfa@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:
>> 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
>

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-12-31 22:06:23
Subject: Re: sum() over (partition by order) question
Previous:From: Tom LaneDate: 2008-12-31 21:45:57
Subject: Re: reloptions and toast tables

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