Complete Series From Partial Series

Question

I'm struggling to come up with an answer to this problem and so hope to find some help here. I have the following time series data table with missing entries:

CREATE TABLE foo
AS
  SELECT timestamp::timestamp, deviceid, eventcount
  FROM ( VALUES
    ( '2017-01-01 00:00:00', 'AAAAA', 1000 ),
    ( '2017-01-01 00:30:00', 'AAAAA', 1000 ),
    ( '2017-01-01 00:40:00', 'AAAAA', 1000 ),
    ( '2017-01-01 00:00:00', 'BBBBB', 2000 ),
    ( '2017-01-01 00:10:00', 'BBBBB', 2000 ),
    ( '2017-01-01 00:50:00', 'BBBBB', 2000 )
  ) AS t(Timestamp, DeviceID, EventCount);

I need to generate from this a complete series i.e.

+---------------------+----------+------------+
|      Timestamp      | DeviceID | EventCount |
+---------------------+----------+------------+
| 2017-01-01 00:00:00 | AAAAA    |       1000 |
| 2017-01-01 00:10:00 | AAAAA    |          0 |
| 2017-01-01 00:20:00 | AAAAA    |          0 |
| 2017-01-01 00:30:00 | AAAAA    |       1000 |
| 2017-01-01 00:40:00 | AAAAA    |       1000 |
| 2017-01-01 00:50:00 | AAAAA    |          0 |
| 2017-01-01 00:00:00 | BBBBB    |       2000 |
| 2017-01-01 00:10:00 | BBBBB    |       2000 |
| 2017-01-01 00:20:00 | BBBBB    |          0 |
| 2017-01-01 00:30:00 | BBBBB    |          0 |
| 2017-01-01 00:40:00 | BBBBB    |          0 |
| 2017-01-01 00:50:00 | BBBBB    |       2000 |
+---------------------+----------+------------+

I have tried generating a series of timestamps with an interval of 10 minutes and then left joining on the first table but what I end up with is this:

+---------------------+----------+------------+
|      Timestamp      | DeviceID | EventCount |
+---------------------+----------+------------+
| 2017-01-01 00:00:00 | NULL     |          0 |
| 2017-01-01 00:10:00 | NULL     |          0 |
| 2017-01-01 00:20:00 | NULL     |          0 |
| 2017-01-01 00:30:00 | NULL     |          0 |
| 2017-01-01 00:40:00 | NULL     |          0 |
| 2017-01-01 00:50:00 | NULL     |          0 |
| 2017-01-01 00:00:00 | AAAAA    |       1000 |
| 2017-01-01 00:30:00 | AAAAA    |       1000 |
| 2017-01-01 00:40:00 | AAAAA    |       1000 |
| 2017-01-01 00:00:00 | BBBBB    |       2000 |
| 2017-01-01 00:10:00 | BBBBB    |       2000 |
| 2017-01-01 00:50:00 | BBBBB    |       2000 |
+---------------------+----------+------------+

Answer 1

This should work for you.

SELECT t.timestamp, t.deviceid, coalesce(eventcount, 0)
FROM foo
RIGHT OUTER JOIN (
  SELECT timestamp, deviceid
  FROM ( SELECT DISTINCT deviceid FROM foo ) AS d
  CROSS JOIN ( SELECT min(timestamp), max(timestamp) FROM foo ) AS ts
  CROSS JOIN LATERAL generate_series(ts.min, ts.max, '10m') AS gs(timestamp)
) AS t
ON foo.deviceid = t.deviceid
AND foo.timestamp = t.timestamp
ORDER BY 2, 1;

In the first run we generate a table with this

SELECT timestamp, deviceid
FROM ( SELECT DISTINCT deviceid FROM foo ) AS d
CROSS JOIN ( SELECT min(timestamp), max(timestamp) FROM foo ) AS ts
CROSS JOIN LATERAL generate_series(ts.min, ts.max, '10m') AS gs(timestamp)

This table we generate looks like this

      timestamp      | deviceid 
---------------------+----------
 2017-01-01 00:00:00 | AAAAA
 2017-01-01 00:10:00 | AAAAA
 2017-01-01 00:20:00 | AAAAA
 2017-01-01 00:30:00 | AAAAA
 2017-01-01 00:40:00 | AAAAA
 2017-01-01 00:50:00 | AAAAA
 2017-01-01 00:00:00 | BBBBB
 2017-01-01 00:10:00 | BBBBB
 2017-01-01 00:20:00 | BBBBB
 2017-01-01 00:30:00 | BBBBB
 2017-01-01 00:40:00 | BBBBB
 2017-01-01 00:50:00 | BBBBB
(12 rows)

It has all the time-ranges needed. Then we RIGHT-OUTER join it.

The end result looks like this

      timestamp      | deviceid | coalesce 
---------------------+----------+----------
 2017-01-01 00:00:00 | AAAAA    |     1000
 2017-01-01 00:10:00 | AAAAA    |        0
 2017-01-01 00:20:00 | AAAAA    |        0
 2017-01-01 00:30:00 | AAAAA    |     1000
 2017-01-01 00:40:00 | AAAAA    |     1000
 2017-01-01 00:50:00 | AAAAA    |        0
 2017-01-01 00:00:00 | BBBBB    |     2000
 2017-01-01 00:10:00 | BBBBB    |     2000
 2017-01-01 00:20:00 | BBBBB    |        0
 2017-01-01 00:30:00 | BBBBB    |        0
 2017-01-01 00:40:00 | BBBBB    |        0
 2017-01-01 00:50:00 | BBBBB    |     2000
(12 rows)

Answer 2

Left join is OK but you are missing one field.

You need to include DeviceID in your generated series and then left join on the two fields.
DeviceID is hardcoded but can be pulled from a table.
Start and stop series parameters also hardcoded.

Query to use in left join:

select a.ts, unnest(array['AAAAA', 'BBBBB']) deviceid from 
generate_series('2017-01-01 00:00:00', '2017-01-01 00:50:00', '10 minutes'::interval) a(ts)

Update
Make query gather data from table instead of hardcode it.

select s.ts, d.deviceid 
  from generate_series((select min(ts) from cte_data), (select max(ts) from cte_data), '10 minutes'::interval) s(ts)
  cross join (select distinct deviceid from cte_data) d

Full query (modified):

with cte_data(ts, deviceid, eventcount) as (

values
( '2017-01-01 00:00:00'::timestamp , 'AAAAA'    ,       1000 ),
( '2017-01-01 00:30:00' , 'AAAAA'    ,       1000 ),
( '2017-01-01 00:40:00' , 'AAAAA'    ,       1000 ),
( '2017-01-01 00:00:00' , 'BBBBB'    ,       2000 ),
( '2017-01-01 00:10:00' , 'BBBBB'    ,       2000 ),
( '2017-01-01 00:50:00' , 'BBBBB'    ,       2000 )

)
, cte_generated_full_timeseries as(
select s.ts, d.deviceid 
  from generate_series((select min(ts) from cte_data), (select max(ts) from cte_data), '10 minutes'::interval) s(ts)
  cross join (select distinct deviceid from cte_data) d
)

select t1.ts, t1.deviceid, coalesce(t2.eventcount, 0) eventcount from cte_generated_full_timeseries t1
left join cte_data t2 on (t1.ts = t2.ts and t1.deviceid = t2.deviceid)
order by deviceid, ts