Postgresql group month wise with missing values

Question

first an example of my table:

id_object;time;value;status
1;2014-05-22 09:30:00;1234;1
1;2014-05-22 09:31:00;2341;2
1;2014-05-22 09:32:00;1234;1
...
1;2014-06-01 00:00:00;4321;1
...

Now i need count all rows with status=1 and id_object=1 monthwise for example. this is my query:

SELECT COUNT(*)
FROM my_table
WHERE id_object=1
  AND status=1
  AND extract(YEAR FROM time)=2014
GROUP BY extract(MONTH FROM time)

The result for this example is:

2
1

2 for may and 1 for june but i need a output with all 12 months, also months with no data. for this example i need this ouput:

0 0 0 0 2 1 0 0 0 0 0 0

Thx for help.

score 6 · Accepted Answer · answered Jun 11 '14 at 07:00

6

you can use generate_series() function like this:

select
    g.month,
    count(m)
from generate_series(1, 12) as g(month)
    left outer join my_table as m on
        m.id_object = 1 and
        m.status = 1 and
        extract(year from m.time) = 2014 and
        extract(month from m.time) = g.month
group by g.month
order by g.month

sql fiddle demo

answered Jun 11 '14 at 07:00

Roman Pekar

107,110
28
195
197

Except using something like `EXTRACT(...)` will cause the db to ignore indices (will be slow on large tables). – Clockwork-Muse Jun 11 '14 at 10:50
@Clockwork-Muse Except if the indexes were built using `extract()` – Clodoaldo Neto Jun 11 '14 at 11:14
@Clockwork-Muse correct, but it was not the point of the answer :) – Roman Pekar Jun 11 '14 at 11:21

Clockwork-Muse · Answer 2 · 2014-06-11T22:44:14.450

Rather than comparing with an extracted value, you'll want to use a range-table instead. Something that looks like this:

month  startOfMonth  nextMonth
1      '2014-01-01'  '2014-02-01'
2      '2014-02-01'  '2014-03-01'
......
12     '2014-12-01'  '2015-01-01'

As in @Roman's answer, we'll start with generate_series(), this time using it to generate the range table:

WITH Month_Range AS (SELECT EXTRACT(MONTH FROM month) AS month, 
                            month AS startOfMonth,
                            month + INTERVAL '1 MONTH' AS nextMonth
                     FROM generate_series(CAST('2014-01-01' AS DATE),
                                          CAST('2014-12-01' AS DATE),
                                          INTERVAL '1 month') AS mr(month))
SELECT Month_Range.month, COUNT(My_Table) 
FROM Month_Range
LEFT JOIN My_Table
       ON My_Table.time >= Month_Range.startOfMonth
          AND My_Table.time < Month_Range.nextMonth
          AND my_table.id_object = 1
          AND my_table.status = 1
GROUP BY Month_Range.month
ORDER BY Month_Range.month

(As a side note, I'm now annoyed at how PostgreSQL handles intervals)

SQL Fiddle Demo

The use of the range will allow any index including My_Table.time to be used (although not if an index was built over an EXTRACTed column.

EDIT:

Modified query to take advantage of the fact that generate_series(...) will also handle date/time series.

That is not how interval's arithmetic is done in Postgresql. You just do `'2014-01-01'::date + (month - 1) * interval '1 month'`. No casting. No string building. — Clodoaldo Neto, Jun 11 '14 at 14:29
@ClodoaldoNeto - thanks for pointing that out. I'm (still) used to DB2, which doesn't require dealing with any of the operands with strings. Actually, your showing that `generate_series(...)` deals with date/time/timestamps is more useful (I probably need to read more of the reference for PostgreSQL). — Clockwork-Muse, Jun 11 '14 at 22:46

Clodoaldo Neto · Answer 3 · 2014-06-12T09:30:29.720

0

generate_series can generate timestamp series

select
    g.month,
    count(t)
from
    generate_series(
        (select date_trunc('year', min(t.time)) from t),
        (select date_trunc('year', max(t.time)) + interval '11 months' from t),
        interval '1 month'
    ) as g(month)
    left outer join
    t on
        t.id_object = 1 and
        t.status = 1 and
        date_trunc('month', t.time) = g.month
where date_trunc('year', g.month) = '2014-01-01'::date
group by g.month
order by g.month

edited Jun 12 '14 at 09:30

answered Jun 11 '14 at 14:42

Clodoaldo Neto

118,695
26
233
260

Except, given that it's likely the table doesn't have dates from the future, you won't have the full range of months. Depending on the given `id_object` you don't know what time might be missing. And unless you've generated an index including `DATE_TRUNC(...)` (less likely than one including the base column), this will _still_ ignore indices. – Clockwork-Muse Jun 11 '14 at 22:33
@Clockwork-Muse Ok I fixed the missing months part. About the _ignore indices_ part that is an interesting point but I don't have the time now to discuss it. – Clodoaldo Neto Jun 12 '14 at 09:31

Postgresql group month wise with missing values

3 Answers3

EDIT:

Linked