Grouped gap filling in Postgresql / Timescaledb

Question

I have measurement from different devices, let's say Device_A and Device_B. For each device I measure temperature and humidity. From time to time some or all measurements are missing: +---------------------+-------------+-------------+-------+ | ts | device_type | measurement | value | +---------------------+-------------+-------------+-------+ | 2018-04-30 23:59:59 | Device_A | Temperature | 10.1 | | 2018-04-30 23:59:59 | Device_A | Humidity | 66 | | 2018-04-30 23:59:59 | Device_B | Temperature | 19.1 | | 2018-05-03 23:59:59 | Device_A | Temperature | 12.1 | | 2018-05-03 23:59:59 | Device_B | Humidity | 67 | | 2018-05-03 23:59:59 | Device_B | Temperature | 16.1 | | 2018-05-04 23:59:59 | Device_A | Temperature | 17 | | 2018-05-04 23:59:59 | Device_A | Humidity | 63 | | 2018-05-04 23:59:59 | Device_B | Temperature | 12.1 | | 2018-05-04 23:59:59 | Device_B | Humidity | 73 | +---------------------+-------------+-------------+-------+

I want to get mean temperature and humidity for each day, when there is no data, I want it to be 0 (or any other arbitrary value) - interesting points are on 2018-05-01 and 2018-05-02 +---------------------+-------------+-------+ | date | measurement | mean | +---------------------+-------------+-------+ | 2018-04-30 23:59:59 | Humidity | 66 | | 2018-04-30 23:59:59 | Temperature | 14.6 | | 2018-05-01 23:59:59 | Temperature | 0 | | 2018-05-01 23:59:59 | Humidity | 0 | | 2018-05-02 23:59:59 | Temperature | 0 | | 2018-05-02 23:59:59 | Humidity | 0 | | 2018-05-03 23:59:59 | Humidity | 67 | | 2018-05-03 23:59:59 | Temperature | 14.1 | | 2018-05-04 23:59:59 | Humidity | 68 | | 2018-05-04 23:59:59 | Temperature | 14.55 | +---------------------+-------------+-------+

I experimented with gap filling described here, but got stuck with NULL values in the measurement column. Additionally I get only one row per day with no values at all with NULL measurement. Ideally I wuild like to get 2 rows per day - one with temperature and one with humidity, both with value set to 0.

Is there any way to generate output like the one above? I know that transposing data from "long" to "wide" format would solve my problem, but wonder if there is another solution?

My code:

CREATE SCHEMA tmp ;
SET search_path = tmp;

DROP TABLE IF EXISTS sample_data CASCADE;
CREATE TABLE sample_data (
  "ts" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  "device_type" character varying,
  "measurement" character varying,
  "value" DOUBLE PRECISION
);

INSERT INTO sample_data(ts, device_type, measurement, value) VALUES
('2018-04-30 23:59:59', 'Device_A', 'Temperature', 10.1),
('2018-04-30 23:59:59', 'Device_A', 'Humidity', 66.0),
('2018-04-30 23:59:59', 'Device_B', 'Temperature', 19.1),
('2018-05-03 23:59:59', 'Device_A', 'Temperature', 12.1),
('2018-05-03 23:59:59', 'Device_B', 'Humidity', 67.0),
('2018-05-03 23:59:59', 'Device_B', 'Temperature', 16.1),
('2018-05-04 23:59:59', 'Device_A', 'Temperature', 17.0),
('2018-05-04 23:59:59', 'Device_A', 'Humidity', 63.0),
('2018-05-04 23:59:59', 'Device_B', 'Temperature', 12.1),
('2018-05-04 23:59:59', 'Device_B', 'Humidity', 73.0)
;

WITH period AS (
  SELECT date
  FROM generate_series('2018-04-30 23:59:59'::timestamp, 
  '2018-05-04 23:59:59', interval '1 day') date
),
sample AS ( SELECT * FROM sample_data)

SELECT period.date,
      measurement,
      coalesce(sum(sample.value), 0) AS value
FROM period
LEFT JOIN sample ON period.date = sample.ts
GROUP BY
    period.date,
    sample.measurement
ORDER BY period.date,
        sample.measurement
;

Output: +---------------------+-------------+-------+ | date | measurement | mean | +---------------------+-------------+-------+ | 2018-04-30 23:59:59 | Humidity | 66 | | 2018-04-30 23:59:59 | Temperature | 14.6 | | 2018-05-01 23:59:59 | NULL | 0 | | 2018-05-02 23:59:59 | NULL | 0 | | 2018-05-03 23:59:59 | Humidity | 67 | | 2018-05-03 23:59:59 | Temperature | 14.1 | | 2018-05-04 23:59:59 | Humidity | 68 | | 2018-05-04 23:59:59 | Temperature | 14.55 | +---------------------+-------------+-------+

Not an answer, but you may want to fill the missing values with the mean or median from that column. — Tim Biegeleisen

Pawel Stradowski Pawel Stradowski · Accepted Answer · 2018-08-04T15:55:47

Just found an answer - the period table must also contain measurements:

WITH period AS (
  SELECT date, m.measurement
  FROM generate_series('2018-04-30 23:59:59'::timestamp, '2018-05-04 23:59:59', interval '1 day') date
  NATURAL JOIN
    (SELECT DISTINCT measurement FROM sample_data) m
)

SELECT period.date,
      period.measurement,
      coalesce(sum(sample_data.value), 0) AS value
FROM period
LEFT JOIN sample_data ON period.date = sample_data.ts AND period.measurement = sample_data.measurement
GROUP BY
    period.date,
    period.measurement
ORDER BY 
    period.date,
    period.measurement
;

Grouped gap filling in Postgresql / Timescaledb

1 Answers