Display null values for non existing row data in postgres DB

Question

I have the below Dataset which looks like this.

t               mean        max     min     std     data_id
4/14/2010 0:00  12.6941 12.6941 12.6941 12.6941          1
4/14/2010 0:00  12.3851 12.3851 12.3851 12.3851          2
4/14/2010 0:20  12.389  12.389  12.389  12.389           1
4/14/2010 0:20  12.1836 12.1836 12.1836 12.1836          2
4/14/2010 0:20  11.3887 11.3887 11.3887 11.3887          6

Here the unique data_id are(1,2,6) but i have another data_id set (1,2,4,5,6) which i want to use to get the data.

Now for all the data_id not present for the time t i want to add the null (mean,max.std,min) values to them so in this case i want the below result set:-

'2010-04-14 00:00:00','12.6941,12.6941,12.6941,12.6941,12.3851,12.3851,12.3851,12.3851,,,,,,,,,,,,,'
'2010-04-14 00:20:00','12.389,12.389,12.389,12.389,12.1836,12.1836,12.1836,12.1836,,,,,,,,,11.3887,11.3887,11.3887,11.3887'

I have used the below query:-

with dataset as (
      select *
      from (values ('2010-04-14T00:00'::TIMESTAMP, 12.6941, 12.6941, 12.6941, 12.6941, 1),
                   ('2010-04-14T00:00'::TIMESTAMP, 12.3851, 12.3851, 12.3851, 12.3851, 2),
                   ('2010-04-14T00:20'::TIMESTAMP, 12.389, 12.389, 12.389, 12.389, 1),
                   ('2010-04-14T00:20'::TIMESTAMP, 12.1836, 12.1836, 12.1836, 12.1836, 2),
                   ('2010-04-14T00:20'::TIMESTAMP, 11.3887, 11.3887, 11.3887, 11.3887, 6)
           ) AS data(t, mean, max, min, std, data_id)
      ),
     dataset_full as (
       select t.t, d.data_id,
              ds.mean, ds.max, ds.min, ds.std
       from (select distinct t from dataset) t cross join
            (select distinct data_id from dataset) d left join
            dataset ds
            on ds.t = t.t and ds.data_id = d.data_id
     )
select t,string_agg(concat(mean, ',', max, ',', min, ',', std), ',' order by data_id)
from dataset_full
group by t
order by t;

And i am getting the below result:-

'2010-04-14 00:00:00','12.6941,12.6941,12.6941,12.6941,12.3851,12.3851,12.3851,12.3851,,,,'
    '2010-04-14 00:20:00','12.389,12.389,12.389,12.389,12.1836,12.1836,12.1836,12.1836,11.3887,11.3887,11.3887,11.3887'

I am not getting the null values for data_id (4,5,6) at = 4/14/2010 0:00 and data_id (4,5) at t=4/14/2010 0:20.

Gordon Linoff · Accepted Answer · 2018-06-02 12:16:19Z

1

Just include the ids that you want when you define data_set_full:

dataset_full as (
       select t.t, d.data_id,
              ds.mean, ds.max, ds.min, ds.std
       from (select distinct t from dataset) t cross join
            (values (1), (2), (4), (5), (6)) d(data_id) left join
            dataset ds
            on ds.t = t.t and ds.data_id = d.data_id
     )

The purpose of the cross join is to get all the records that you want in the result set. So, include the ids and the timestamps that you want. The left join then brings in the appropriate data, if any.

answered Jun 2, 2018 at 12:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Display null values for non existing row data in postgres DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related