In a table of timed observations from multiple sensors, how to optimally retrieve the last observation for each sensor

user_9423137 0
Viewed 53

My table looks as follows:

sensor time value
AAA 2021-01-05 04:10:14 3.14159
AAA 2021-01-05 05:08:07 3.94756
ABC 2021-01-05 03:40:54 4.32543

I'm looking for a query that retrieves the rows corresponding to the last observation for each sensor, i.e.:

sensor time value
AAA 2021-01-05 05:08:07 3.94756
ABC 2021-01-05 03:40:54 4.32543

After doing some research I came across this solution:

SELECT DISTINCT ON (sensor) sensor, time, value
FROM observations
ORDER BY sensor, time DESC

The problem with the above is that it's rather costly for large tables.

A possible solution would be to have another table holding only the last observation for each sensor, separate from the one holding all the historical ones. While that would work, I was wondering if there is something more elegant, i.e. that allows me to keep a single table, while having a better performance.

Thanks in advance.

greatest-n-per-group postgresql sql
3 Answers

You can use window function such as ROW_NUMBER()

SELECT sensor, time, value
 FROM
 (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY sensor ORDER BY time DESC) AS rn
    FROM observations o ) oo
 WHERE rn = 1

assuming ties occur for time values, then DENSE_RANK() function might be used instead in order to include all equal time values within the result set.

Demo

user_58413060

You could try to use the recursive CTE:

with recursive t as (
    (select sensor, time, value
     from observations
     order by sensor, time desc limit 1)
    union all
    (select o.sensor, o.time, o.value
     from observations as o join t on (o.sensor > t.sensor)
     order by o.sensor, o.time desc limit 1))
select * from t;

Index on observations(sensor, time desc) could help a lot.

user_5931440

For this query:

SELECT DISTINCT ON (sensor) sensor, time, value
FROM observations
ORDER BY sensor, time DESC

You want an index on (sensor, time desc).

With such an index, this is probably the fastest method for doing what you want.

user_11440350
Related