Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cumulative (Running) Sum

Cumulative (Running) Sum

From
Matt Culbreth
Date:
Hello Group,

I might have missed this somewhere, but PostgreSQL doesn't presently
support the cumulative/running aggregate function SUM() OVER
(PARTITION BY) syntax that Oracle does, right?

Here's an example of what I'm talking about:

Say we have a table of sales by month & person.  We want to query this
table and show both a month's sales AND the cumulative sum for that
person.  Something like this:

MONTH         PERSON       VALUE CUMULATIVE_SUM
-------------------- -------------------- ---------- --------------
January        David        50    50
January        Matt         10    10
February       David        45    95
February       Matt         5     15
March          David        60    155
March          Matt         20    35

In Oracle this is nicely accomplished by using the following syntax:

SELECT
    c.Month,
    c.Person,
    c.Value,
    sum(c.value) over(partition by c.Person order by c.Month_Num,
c.Person) as Cumulative_Sum
FROM
    CS_Test c
ORDER BY
    c.Month_Num ASC,
    c.Person ASC

In PostgreSQL however, we can do this, but we have to use a subquery:

SELECT
    c.Month,
    c.Person,
    c.Value,
    (select sum(c2.value) from CS_Test c2 where c2.Month_Num <=
c.Month_num and c2.person = c.person) as Cumulative_Sum
FROM
    CS_Test c
ORDER BY
    c.Month_Num ASC,
    c.Person ASC

So is there planned support for the newer syntax or is a subquery the
best/only way to go on PostgreSQL for now?

Thanks,

Matt

Re: Cumulative (Running) Sum

From
Tom Lane
Date:
Matt Culbreth <mattculbreth@gmail.com> writes:
> I might have missed this somewhere, but PostgreSQL doesn't presently
> support the cumulative/running aggregate function SUM() OVER
> (PARTITION BY) syntax that Oracle does, right?

Right.  There are people interested in this, and it'll likely show up in
8.4 or later, but it doesn't exist now.

(What's actually on the road map is the SQL:2003 windowing functions.
I'm not sure the spec is exactly compatible with Oracle, but it
definitely has this type of functionality.)

            regards, tom lane

Re: Cumulative (Running) Sum

From
hubert depesz lubaczewski
Date:
On Fri, Mar 07, 2008 at 06:50:17AM -0800, Matt Culbreth wrote:
> I might have missed this somewhere, but PostgreSQL doesn't presently
> support the cumulative/running aggregate function SUM() OVER
> (PARTITION BY) syntax that Oracle does, right?

you might find this useful:
http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/

please also read comments.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Re: Cumulative (Running) Sum

From
Matt Culbreth
Date:
On Mar 8, 4:46 pm, dep...@depesz.com (hubert depesz lubaczewski)
wrote:

>
> you might find this useful:http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-s...
>
> please also read comments.
>
> depesz
>

This is very helpful.  It sees that there's a way to do it without
hacking it into the actual PG source, which I was tempted to do.
Thanks,