Running/cumulative count using windows

Поиск
Список
Период
Сортировка
От Oliver Kohll - Mailing Lists
Тема Running/cumulative count using windows
Дата
Msg-id DF46540C-2F08-40F0-BD8D-492E7C1461C2@gtwm.co.uk
обсуждение исходный текст
Ответы Re: Running/cumulative count using windows
Список pgsql-general
Дерево обсуждения
Hello,

I'm still reasonably new to windowing functions, having used a few since 8.4 came out. I wonder if anyone can help with this one.

I've got a table of email addresses in a CRM system similar to the following:

CREATE TABLE test(
  signup_date timestamp,
  email_address varchar(1000)
);
INSERT INTO test(signup_date, email_address) VALUES(now(), 'test@test.com');
INSERT INTO test(signup_date, email_address) VALUES(now(), 'test@test1.com');
INSERT INTO test(signup_date, email_address) VALUES(now() - '1 month'::interval, 'test@test2.com');

I'd like a running count, or cumulative count of the number of signups per month. I'm pretty sure a window function would do it but I can't work it out.

So a plain count by month would be

SELECT date_part('year',signup_date) as year, date_part('month',signup_date) as month, count(*)
FROM test
GROUP BY year, month
ORDER BY year, month;

giving

 year | month | count 
------+-------+-------
 2010 |     2 |     1
 2010 |     3 |     2

How would you make the count a cumulative one? The output should then be

 year | month | count 
------+-------+-------
 2010 |     2 |     1
 2010 |     3 |     3


Regards
Oliver Kohll


oliver@agilebase.co.uk / +44(0)845 456 1810 / skype:okohll
www.gtwm.co.uk - company

В списке pgsql-general по дате отправления: