Postgres generate_series

Question

What I want to is to make statistics on a table and for this I'm using generate_series();

Here is 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 like get the amount for specifics users I have to apply them twice. Is there a way to avoid filtering twice, or to rewrite this in a more efficient way because I'm not sure if it's the right way to do it?

Grzegorz Szpetkowski · Accepted Answer · 2011-08-21 10:23:40Z

9

You could 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;

Example 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)

edited Aug 21, 2011 at 10:23

answered Aug 21, 2011 at 10:17

Grzegorz Szpetkowski

38.1k6 gold badges94 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

yokoloko Over a year ago

I never think about the CTE but they are really usefull. Thank's a lot it's just perfect.

Bohemian Over a year ago

+1 I learned something I didn't know about postgres - thank you :)

Rizwan Patel Over a year ago

how to generate series like select m from generate_series(01,12) m output :01, 02 , 03 , 04, 05, 06, 07, 08 ,09, 10 ,11, 12

piotrp · Accepted Answer · 2011-08-21 10:18:49Z

6

You can execute your query within a WITH clause and then use SELECT to add 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

answered Aug 21, 2011 at 10:18

piotrp

3,9841 gold badge26 silver badges28 bronze badges

Collectives™ on Stack Overflow

Postgres generate_series

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related