Cumulative (Running) Sum

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

Предыдущее
От: "Alex Turner"
Дата:
Сообщение: Re: Problems with 8.3
Следующее
От: "Wei Wu"
Дата:
Сообщение: cache lookup failed for relation X