SQL find rows with closest higher values than each value in specified set of values

Question

I have statistics gathered each 6 hours in the database each saved with a timestamp. Then I have array of timestamp in my code. I need to select a value from database for each value in my array of timestamp and that row will have the closest higher or equal timestamp than is in the array.

To illustrate: Table with data

Id   Timestamp   Value
1    1400000027  10
2    1400000035  15
3    1400000043  20
4    1400000044  21
5    1400000048  30
6    1400000060  35

The array contains following timestamps:

[1400000020, 1400000024, 1400000035, 1400000050]

The rows I need to get from the database based on the input array are:

Id   Timestamp   Value
1    1400000027  10
1    1400000027  10
2    1400000035  15
6    1400000060  35

Is there a simple way to do this in one query? And the best solution would be in doctrine, since I am using Symfony 2 and Doctrine.

score 3 · Answer 1 · answered Jan 27 '16 at 12:18

To be honest, it might be easiest just to execute a separate query for each value:

select t.*
from table t
where t.TimeStamp >= $timestamp
order by TimeStamp
limit 1;

With an index on TimeStamp this query should be quite fast.

You can do this in a single query. I would be inclined to store the values in a table (you can unroll the array values if necessary). In Postgres 9.3 and later, you can phrase this as a lateral join:

with timestamps as (
      select 1400000020 as ts union all
      select 1400000024 union all
      select 1400000035 union all
      select 1400000050
     )
select t.*
from timestamps cross join lateral
     (select
      from table t
      where t.timestamp >= timestamps.ts
      order by t.timestamp
      limit 1
     ) t;

The reason for me not to do a query for each row is that there can be a large number of timestamps in the input array which will result in a lot of queries. — Elwhis, Jan 27 '16 at 13:08

score 1 · Accepted Answer · edited May 23 '17 at 11:46

This is typically done with DISTINCT ON in PostgreSQL (if you can use non-standard SQL)

SELECT    DISTINCT ON (ts_min) t.*
FROM      unnest(ARRAY[1400000020, 1400000024, 1400000035, 1400000050]) ts_min
LEFT JOIN table_name t ON t.timestamp >= ts_min
ORDER BY  ts_min, t.timestamp

If you cannot bind an array, you can use the values construct:

FROM      (VALUES (1400000020), (1400000024), (1400000035), (1400000050)) v(ts_min)

SQL find rows with closest higher values than each value in specified set of values

2 Answers2