SQL 查找最接近高于指定值集中每个值的行 - SQL find rows with closest higher values than each value in specified set of values

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

我在数据库中每 6 小时收集一次统计数据，每个统计数据都保存有时间戳。然后我的代码中有时间戳数组。我需要从数据库中为时间戳数组中的每个值选择一个值，并且该行的时间戳将比数组中最接近的更高或相等。

举例说明：包含数据的表

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

该数组包含以下时间戳：

[1400000020, 1400000024, 1400000035, 1400000050]

我需要根据输入数组从数据库中获取的行是：

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

有没有一种简单的方法可以在一个查询中做到这一点？最好的解决方案是在教义中，因为我正在使用Symfony 2和Doctrine。

老实说，最简单的方法是为每个值执行单独的查询：

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

有了索引TimeStamp这个查询应该非常快。

您可以在单个查询中执行此操作。我倾向于将值存储在表中（如有必要，您可以展开数组值）。在 Postgres 9.3 及更高版本中，您可以将其表述为横向连接：

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;

这

通常是在PostgreSQL中使用DISTINCT ON来完成的（如果你可以使用非标准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

如果无法绑定数组，则可以使用 values 构造：

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