I have the following table:
╔════╦═══════════╦═════════════════════╦═══════╗
║ id ║ sensor_id ║ time ║ value ║
╠════╬═══════════╬═════════════════════╬═══════╣
║ 1 ║ 1 ║ 2018-01-01 00:00:01 ║ 1 ║
║ 2 ║ 1 ║ 2018-01-01 00:00:02 ║ 2 ║
║ 3 ║ 1 ║ 2018-01-01 00:00:03 ║ 3 ║
║ 4 ║ 1 ║ 2018-01-01 00:00:03 ║ 4 ║
║ 5 ║ 1 ║ 2018-01-01 00:00:04 ║ 3 ║
║ 6 ║ 2 ║ 2018-01-01 00:00:01 ║ 1 ║
║ 7 ║ 2 ║ 2018-01-01 00:00:01 ║ 2 ║
║ 8 ║ 2 ║ 2018-01-01 00:00:02 ║ 3 ║
║ 9 ║ 2 ║ 2018-01-01 00:00:03 ║ 4 ║
║ 10 ║ 2 ║ 2018-01-01 00:00:04 ║ 5 ║
╚════╩═══════════╩═════════════════════╩═══════╝
CREATE TABLE sensor_time_series
(
id SERIAL PRIMARY KEY,
"time" TIMESTAMP NOT NULL,
sensor_id INTEGER NOT NULL,
value NUMERIC NOT NULL,
);
It's a timeseries table that represents the value of a sensor at a specific time. Yes, I know it's strange that "time" is not unique inside each "sensor_id", that's an error from the dataset.
What I want is to make a new table/view with a graph structure, connecting each "sensor_id" sample to its successor in "time". The table should look something like this:
╔════════════╦══════════════╗
║ current_id ║ successor_id ║
╠════════════╬══════════════╣
║ 1 ║ 2 ║
║ 2 ║ 3 ║
║ 2 ║ 4 ║
║ 3 ║ 5 ║
║ 4 ║ 5 ║
║ 6 ║ 8 ║
║ 7 ║ 8 ║
║ 8 ║ 9 ║
║ 9 ║ 10 ║
╚════════════╩══════════════╝
CREATE TABLE sensor_time_series_graph
(
current_id INTEGER,
successor_id INTEGER,
FOREIGN KEY (current_id) REFERENCES sensor_time_series(id),
FOREIGN KEY (successor_id) REFERENCES sensor_time_series(id)
);
Both columns (current_id and successor_id) FOREIGN KEY's id from the first table
How can I create something like this in PostgreSQL 10?
I was looking into PostgreSQL window functions and I think they can help me, but did not realized how yet.
Best Answer
or (thanks to Lennart for the idea)
In the last query the join/where conditions can be freely moved between those sections for the best visibility.
fiddle