Filling missing dates in each group while querying data from PostgreSQL

Question

I have data in my table of the following form:

| date     | type | value |
+----------+------+-------+
|2020-01-01| A    |     29|
|2020-02-01| A    |     32|
|2020-04-01| A    |     56|
|2020-05-01| A    |     78|
|2020-01-01| B    |     12|
|2020-02-01| B    |     89|
|2020-03-01| B    |     44|
|2020-05-01| B    |     33|

Now while querying this data, I want the missing dates to be filled with null values.

In PostgreSQL, I can use generate_series() to create a series of dates and then left join with it. But that only works if I have just a series of dates and values. In this case, since I have two types, I don't really have any missing dates in the series as a whole, but I have missing dates in each group/partition.

Is it possible to use generate_series() with left join to fill rows with null in this case?

Tim Biegeleisen · Accepted Answer · 2020-07-28 08:56:39Z

6

You may cross join with a table which contains all types, and then use the same left join approach you were already considering:

SELECT
    date_trunc('day', cal)::date AS date,
    t1.type,
    t2.value
FROM generate_series
    ( '2020-01-01'::timestamp 
    , '2020-12-31'::timestamp
    , '1 day'::interval) cal
CROSS JOIN (SELECT DISTINCT type FROM yourTable) t1
LEFT JOIN yourTable t2
    ON t2.date = cal.date AND t2.type = t1.type
ORDER BY
    t1.type,
    cal.date;

answered Jul 28, 2020 at 8:56

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

gouravkr Over a year ago

Cross join was what I needed, I wasn't aware of such a function. This solution worked for me, thanks.

Collectives™ on Stack Overflow

Filling missing dates in each group while querying data from PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related