I have a table containing multiple time series of different types. The timestamps of cohesive points from different series do not match exactly (i. e. the difference can be up to an hour).
Schema
Below is the schema with two example series:
CREATE TABLE series (id integer, series_type integer, charttime timestamp,
value integer, PRIMARY KEY (id));
INSERT INTO series VALUES (1, 1, '2018-03-01 12:10:00', 40),
(2, 1, '2018-03-01 13:25:00', 30), (3, 1, '2018-03-01 14:10:00', 50);
INSERT INTO series VALUES (4, 2, '2018-03-01 11:20:00', 2), (5, 2, '2018-03-01 12:15:00', 6),
(6, 2, '2018-03-01 13:00:00', 7), (7, 2, '2018-03-01 13:45:00', 1);
id |series_type |charttime |value |
---|------------|--------------------|------|
1 |1 |2018-03-01 12:10:00 |40 |
2 |1 |2018-03-01 13:25:00 |30 |
3 |1 |2018-03-01 14:10:00 |50 |
4 |2 |2018-03-01 11:20:00 |2 |
5 |2 |2018-03-01 12:15:00 |6 |
7 |2 |2018-03-01 13:45:00 |1 |
6 |2 |2018-03-01 13:00:00 |7 |
Goal
The goal is to select one series together with the closest datapoint from another series. For the example dataset the result should be:
charttime |s1 |s2 |
--------------------|---|---|
2018-03-01 12:10:00 |40 |6 |
2018-03-01 13:25:00 |30 |1 |
2018-03-01 14:10:00 |50 |1 |
First working approach
My current approach is to select the best matching data point from the other series by a subquery:
SELECT l.charttime, l.value AS s1,
( SELECT r.value
FROM series r
WHERE ABS( EXTRACT( EPOCH FROM l.charttime - r.charttime ) / 3600 ) < 1
AND r.series_type = 2
ORDER BY ABS( EXTRACT( EPOCH FROM l.charttime - r.charttime )) ASC LIMIT 1
) AS s2
FROM series l
WHERE l.series_type = 1
ORDER BY l.charttime ASC
This does not seem to be the best approach as the dataset is quite huge and thus performing many subqueries slows down the query.
Second approach
A different idea is to self-join the table and filter for close data timestamps:
SELECT l.charttime, l.value AS s1, r.charttime, r.value AS s2
FROM series l, series r
WHERE abs(EXTRACT(EPOCH FROM l.charttime - r.charttime) / 3600) < 1
AND l.series_type = 1 AND r.series_type = 2
charttime |s1 |charttime |s2 |
--------------------|---|--------------------|---|
2018-03-01 12:10:00 |40 |2018-03-01 11:20:00 |2 |
2018-03-01 12:10:00 |40 |2018-03-01 12:15:00 |6 |
2018-03-01 12:10:00 |40 |2018-03-01 13:00:00 |7 |
2018-03-01 13:25:00 |30 |2018-03-01 13:45:00 |1 |
2018-03-01 13:25:00 |30 |2018-03-01 13:00:00 |7 |
2018-03-01 14:10:00 |50 |2018-03-01 13:45:00 |1 |
The problem then are the duplicate data points. Grouping in the first column does not work as the best matching s2
cannot be selected.
Is there a better approach?
s2
come fromseries_type
= 2.series_type = 1
. As pointed out,s2
are the matching points fromseries_type=2
.