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

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

pgsql-hackers by date

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

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