Get a row for each day of date range even when no values exist

user_613605 0
Viewed 56

I have a readings table. It is defined as:

   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 created_at | timestamp without time zone |           | not null |
 device     | character varying(25)       |           | not null |
 type       | character varying(25)       |           | not null |
 value      | numeric                     |           | not null |

It has data such as:

     created_at      |  device   |    type     |    value
---------------------+-----------+-------------+-------------
 2021-03-16 07:46:47 | 465125783 | temperature |        36.5
 2021-03-16 07:51:48 | 465125783 | temperature | 36.40000153
 2021-03-16 07:52:47 | 465125783 | temperature | 36.40000153
 2021-03-16 07:53:47 | 465125783 | temperature | 36.29999924
 2021-03-24 17:53:47 | 123456789 | pressure    |          79
 2021-03-24 17:54:48 | 123456789 | pressure    |          77
 2021-03-28 05:38:48 | 123456789 | flow        |          12
 2021-03-28 05:45:48 | 123456789 | flow        |          14
 2021-03-28 05:49:47 | 123456789 | pressure    |          65
 2021-03-28 05:50:47 | 123456789 | flow        |          32
 2021-03-28 05:51:47 | 123456789 | flow        |          40

Current Query

So far I have the following query:

select created_at::date, device,
       avg(value) filter (where type = 'temperature') as temperature,
       avg(value) filter (where type = 'pressure') as pressure,
       avg(value) filter (where type = 'flow') as flow
from readings
where device = '123456789' and created_at::date > created_at::date - interval '14 days'
group by created_at::date, device
order by created_at::date desc;

The query works out a daily average value for each type for the past two weeks.

Current Output

When I run the query, I get the following:

 created_at |  device   | temperature |      pressure       |        flow
------------+-----------+-------------+---------------------+---------------------
 2021-03-28 | 123456789 |             | 65.0000000000000000 | 24.5000000000000000
 2021-03-24 | 123456789 |             | 78.0000000000000000 |

Desired Output

What I really want is a row for each date for the past two weeks, so I want to end up with:

 created_at |  device   | temperature |      pressure       |        flow
------------+-----------+-------------+---------------------+---------------------
 2021-04-02 | 123456789 |             |                     | 
 2021-04-01 | 123456789 |             |                     | 
 2021-03-31 | 123456789 |             |                     | 
 2021-03-30 | 123456789 |             |                     | 
 2021-03-29 | 123456789 |             |                     | 
 2021-03-28 | 123456789 |             | 65.0000000000000000 | 24.5000000000000000
 2021-03-27 | 123456789 |             |                     | 
 2021-03-26 | 123456789 |             |                     | 
 2021-03-25 | 123456789 |             |                     | 
 2021-03-24 | 123456789 |             | 78.0000000000000000 | 
 2021-03-23 | 123456789 |             |                     | 
 2021-03-22 | 123456789 |             |                     | 
 2021-03-21 | 123456789 |             |                     | 
 2021-03-20 | 123456789 |             |                     |

How can I can achieve that?

I have a db-fiddle.

pivot postgresql sql
1 Answers

Use generate_series():

select gs.dte, '123456789' as device,
       avg(value) filter (where type = 'temperature') as temperature,
       avg(value) filter (where type = 'pressure') as pressure,
       avg(value) filter (where type = 'flow') as flow
from generate_series('2021-03-20'::date, '2021-04-02'::date, interval '1 day') gs(dte) left join
     readings r
     on r.device = '123456789' and
        r.created_at::date = gs.dte
group by gs.dte
order by gs.dte desc;

Here is a db<>fiddle.

user_11440350
Related