Postgres generate_series

Question

I want to make statistics on a table, and for this I use generate_series();

That's what I'm doing:

 SELECT x.month, amount FROM (SELECT generate_series( min(date_trunc('month', date)), max(date_trunc('month', date)), '1 month' ) AS month FROM table WHERE user_id = 55 AND ... ) x LEFT JOIN ( SELECT SUM(amount) AS amount, date_trunc('month', date) AS month FROM table WHERE user_id = 55 AND ... GROUP BY month ) q ON q.month = x.month ORDER BY month 

This works well, but when I want to apply filters, for example, to get the number for specific users, I have to apply them twice. Is there a way to avoid filtering twice, or rewrite it in a more efficient way, because I'm not sure if this is the right thing to do?

Answer 1

You can write WITH query for this:

 WITH month_amount AS ( SELECT sum(amount) AS amount, date_trunc('month', date) AS month FROM Amount WHERE user_id = 55 -- AND ... GROUP BY month ) SELECT month, amount FROM (SELECT generate_series(min(month), max(month), '1 month') AS month FROM month_amount) x LEFT JOIN month_amount USING (month) ORDER BY month; 

Result:

 SELECT * FROM amount WHERE user_id = 55; amount_id | user_id | amount | date -----------+---------+--------+------------ 3 | 55 | 7 | 2011-03-16 4 | 55 | 5 | 2011-03-22 5 | 55 | 2 | 2011-05-07 6 | 55 | 18 | 2011-05-27 7 | 55 | 4 | 2011-06-14 (5 rows) WITH month_amount .. month | amount ------------------------+-------- 2011-03-01 00:00:00+01 | 12 2011-04-01 00:00:00+02 | 2011-05-01 00:00:00+02 | 20 2011-06-01 00:00:00+02 | 4 (4 rows) 

Answer 2

You can execute the query in the WITH clause, and then use SELECT to add the missing months:

 WITH query AS ( SELECT SUM(amount) AS amount, date_trunc('month', date) AS month FROM table WHERE user_id = 55 AND ... GROUP BY month ) SELECT date(d.month) AS month, q.amount FROM ( SELECT generate_series(min(month), max(month), '1 month') AS month FROM query ) d LEFT JOIN query q ON q.month = d.month ORDER BY month