Cumulative (Running) Sum
| От | Matt Culbreth | 
|---|---|
| Тема | Cumulative (Running) Sum | 
| Дата | |
| Msg-id | 7170375d-6851-4613-94cc-9ecb695d4c39@e31g2000hse.googlegroups.com обсуждение исходный текст | 
| Ответы | Re: Cumulative (Running) Sum Re: Cumulative (Running) Sum | 
| Список | pgsql-general | 
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
		
	В списке pgsql-general по дате отправления: