Thread: sum() over (partition by order) question
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
"Pavel Stehule" <pavel.stehule@gmail.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.
regards, tom lane
On Wed, Dec 31, 2008 at 4:34 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 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; AFAIUI, this means one sum per b value, the result in the sum column will be equivalent to "select sum(a) from foo group by b" > > postgres=# select sum(a) over (partition by b order by a), a, b from foo; and this means something like accumulate the value of a per b value and for every value of b accumulate per a value... maybe this can be described better... don't know exactly if we can imitate this behaviour without window functions -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
2008/12/31 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.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 | 511 | 3 | 16 | 1112 | 3 | 16 | 1216 | 3 | 16 | 1616 | 3
| 16 | 1616 | 3 | 16 | 1622 | 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 | 511 | 3 | 16 | 2212 | 3 | 16 | 2216 | 3 | 16 | 2216 | 3
| 16 | 2216 | 3 | 16 | 2222 | 3 | 16 | 22
(13 rows)
it should be noticed in doc?
regards
Pavel Stehule
> regards, tom lane
>
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 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);
Should be "over w". "over (w)" is a <window specification> that
modifies an existing window, not just a reference, and in particular
that means we don't copy the framing clause; see SQL2008 7.11
general rule 1) b) i) 6).
Hmm... I think 7.11 syntax rule 10) e) actually wants us to throw an
error for this case, not just silently ignore the referenced window's
framing clause. I had thought that was just being overly anal, but now
it seems that this case can result in user confusion, so maybe we'd
better throw the error after all.
regards, tom lane
"Pavel Stehule" <pavel.stehule@gmail.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
2008/12/31 Tom Lane <tgl@sss.pgh.pa.us>: > "Pavel Stehule" <pavel.stehule@gmail.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 > oh, yes, there it is, thank you and Happy New Year regards Pavel Stehule
2008/12/31 Tom Lane <tgl@sss.pgh.pa.us>: > "Pavel Stehule" <pavel.stehule@gmail.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 | 1911 | 3 | 9312 | 3 | 9316| 3 | 9316 | 3 | 9316 | 3 | 9322 | 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 | 1911 | 3 | 1112 | 3 | 2316| 3 | 7116 | 3 | 7116 | 3 | 7122 | 3 | 93 (13 rows) regards Pavel Stehule