sum() over (partition by order) question

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: sum() over (partition by order) question
Date: 2008-12-31 21:34:08
Message-ID: 162867790812311334k2afdb530nddf801b6b9c8cf2d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello

I am play with windows function. I was surprised so these queries has
different results.

postgres=# select sum(a) over (partition by b), a, b from foo;
sum | a | b
-----+----+---
19 | 1 | 1
19 | 1 | 1
19 | 2 | 1
19 | 4 | 1
19 | 2 | 1
19 | 4 | 1
19 | 5 | 1
93 | 11 | 3
93 | 12 | 3
93 | 22 | 3
93 | 16 | 3
93 | 16 | 3
93 | 16 | 3
(13 rows)

postgres=# select sum(a) over (partition by b order by a), a, b from foo;
sum | a | b
-----+----+---
2 | 1 | 1
2 | 1 | 1
6 | 2 | 1
6 | 2 | 1
14 | 4 | 1
14 | 4 | 1
19 | 5 | 1
11 | 11 | 3
23 | 12 | 3
71 | 16 | 3
71 | 16 | 3
71 | 16 | 3
93 | 22 | 3
(13 rows)

I didn't expect so ORDER can change result of function sum. Please,
can somebody explain it?

regards
Pavel Stehule

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-12-31 21:36:27 Re: sum() over (partition by order) question
Previous Message Stephen Frost 2008-12-31 19:56:55 Re: pg_dump roles support [Review]