0

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.

  • I can't see a way to do this using window functions without stacking CTEs two deep. - something like lag(id,count-rank+1),id still should execute faster than a self join. – Jasen Jun 9 '18 at 6:29
  • @Jasen One "parent" may produce a list of records with different "childs". So You NEED at least one self-join. – Akina Jun 9 '18 at 6:55
  • @Jasen One source record cannot produce two output records. Ever when window function is used. – Akina Jun 9 '18 at 6:58
  • ah yeah, now I see, sensor 1 has duplicated values in both columns. – Jasen Jun 9 '18 at 7:28
  • @Akina that's a very intresting statement about input/output of records, that helps a lot in understanding your query. – Tiago Stapenhorst Martins Jun 12 '18 at 5:53
3
SELECT t1.id
     , t2.id
FROM sensor_time_series t1
INNER JOIN sensor_time_series t2 ON  t1.sensor_id = t2.sensor_id 
                                 AND t1."time" < t2."time"
LEFT JOIN sensor_time_series t3 ON  t1.sensor_id = t3.sensor_id 
                                AND t1."time" < t3."time" 
                                AND t3."time" < t2."time"
WHERE t3.id IS NULL;

or (thanks to Lennart for the idea)

SELECT t1.id
     , t2.id
FROM ( SELECT id
            , sensor_id
            , lead("time") over(partition by sensor_id order by time) AS leading
       FROM sensor_time_series ) t1
INNER JOIN sensor_time_series t2 ON  t1.sensor_id = t2.sensor_id 
                                 AND t1.id != t2.id
WHERE t2."time" = t1.leading;

In the last query the join/where conditions can be freely moved between those sections for the best visibility.

fiddle

2

If sensor_id and time where unique you could have used the window function lead:

select id, lead(id) over(partition by sensor_id order by time) as successor_id 
from sensor_time_series;

but since there can be several successors (if you have several consecutive multiple measurements the result will grow fast), you will need some kind of self-JOIN. This is a slight variation from Akina's solution, using a NOT EXISTS predicate instead of a LEFT JOIN:

SELECT t1.id, t2.id as successor_id
FROM sensor_time_series t1
JOIN sensor_time_series t2 
    ON t2.sensor_id = t1.sensor_id 
   AND t2.time > t1.time
WHERE NOT EXISTS (
    SELECT 1 
    FROM sensor_time_series t3
    WHERE t1.sensor_id = t3.sensor_id
      AND t3.time < t2.time 
      AND t3.time > t1.time
)    

If you want to include observations that don't have a successor, you can use a LEFT JOIN:

SELECT t1.id, t2.id as successor_id
FROM sensor_time_series t1
LEFT JOIN sensor_time_series t2 
    ON t2.sensor_id = t1.sensor_id 
   AND t2.time > t1.time
WHERE NOT EXISTS (
    SELECT 1 
    FROM sensor_time_series t3
    WHERE t1.sensor_id = t3.sensor_id
      AND t3.time < t2.time 
      AND t3.time > t1.time
)    

Another option is to use dense_rank() to determine the successors. It is convenient to use a CTE:

with t(id,sensor_id,rnk) as (
    select id, sensor_id
         , dense_rank() over (partition by sensor_id order by time) as rnk
    from sensor_time_series
)
select t1.id, t2.id
from t as t1
join t as t2
    on t1.sensor_id = t2.sensor_id
   and t2.rnk = t1.rnk+1;

Use LEFT JOIN between t1 and t2 if you want to include observations without successors.

  • Do you happen to know if your second query is faster than @Akina first query considering there is a btree index in (sensor_id, time)? – Tiago Stapenhorst Martins Jun 9 '18 at 23:01
  • Dont know out of the box, compare the plans and execution time for a representative amount of data – Lennart Jun 10 '18 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.