How to fill the time gap after grouping date record for months in postgres

user_11763573 0
Viewed 151

I have table records as -

date                n_count
2020-02-19 00:00:00  4
2020-07-14 00:00:00  1
2020-07-17 00:00:00  1
2020-07-30 00:00:00  2
2020-08-03 00:00:00  1
2020-08-04 00:00:00  2
2020-08-25 00:00:00  2
2020-09-23 00:00:00  2
2020-09-30 00:00:00  3
2020-10-01 00:00:00  11
2020-10-05 00:00:00  12
2020-10-19 00:00:00  1
2020-10-20 00:00:00  1
2020-10-22 00:00:00  1
2020-11-02 00:00:00  376
2020-11-04 00:00:00  72
2020-11-11 00:00:00  1

I want to be grouped all the records into months for finding month total count which is working, but there is a missing of month. how to fill this gap.

time           month_count
"2020-02-01"    4
"2020-07-01"    4
"2020-08-01"    5
"2020-09-01"    5
"2020-10-01"    26
"2020-11-01"    449

This is what I have tried.

SELECT (date_trunc('month', date))::date AS time,
       sum(n_count) as month_count      
FROM table1
group by time
order by time asc
date-arithmetic aggregate-functions datetime postgresql sql
2 Answers

You can use generate_series() to generate all starts of months between the earliest and latest date available in the table, then bring the table with a left join:

select d.dt, coalesce(sum(t.n_count), 0) as month_count      
from (
    select generate_series(date_trunc('month', min(date)), date_trunc('month', max(date)), '1 month') as dt 
    from table1
) as d(dt)
left join table1 t on t.date >= d.dt and t.date < d.dt + interval '1 month'
group by d.dt
order by d.dt
user_106767160

I would simply UNION a date series, generated from MIN and MAX date:

demo:db<>fiddle

WITH cte AS (                                      -- 1
    SELECT
        *,
        date_trunc('month', date)::date AS time
    FROM
        t
)
SELECT 
    time,
    SUM(n_count) as month_count                    --3
FROM (
    SELECT
        time,
        n_count
    FROM cte

    UNION

    SELECT                                        -- 2
        generate_series(
            (SELECT MIN(time) FROM cte),
            (SELECT MAX(time) FROM cte),
            interval '1 month'
        )::date,
        0
) s
GROUP BY time
ORDER BY time
  1. Use CTE to calculate date_trunc only once. Could be left out if you like to call your table twice in the UNION below
  2. Generate monthly date series from MIN to MAX date containing your n_count value = 0. Add it to the table
  3. Do your calculation
user_39842210
Related