mysql - 选择最接近时间戳的非空值

Question

我有一个带有时间戳和值 V 的非常大的表。一些 V 可能为空:

timestamp,V
sometime_1,value1
sometime_2,value2
sometime_3,NULL
sometime_4,value4

我想要一个查询来选择给定时间戳 T 的值 V，但如果它为 NULL，则在 T 的任一侧获取最接近 T 的时间的值。例如，如果我有

2010-09-01 00:00:01,v1
2010-09-01 00:00:02,v2
2010-09-01 00:00:03,NULL
2010-09-01 00:00:04,NULL
2010-09-01 00:00:05,v3

我希望时间戳“3”的查询返回“v2”，因为 2 比 5 更接近 3，但我希望时间戳“4”的查询返回 v3，因为 5 更接近。

时间也不能保证是连续的，例如，我们可能有:

2010-09-01 00:00:01,v1
2010-09-01 00:00:04,v2
2010-09-01 00:00:30,NULL
2010-09-01 00:00:42,NULL
2010-09-01 00:00:50,v3

在这种情况下，v3 最接近 30 和 42。

目前，我通过将 Python 连接到 SQL 并从相关时间戳开始双向循环并返回不返回 NULL 的最接近值来执行此操作。但是，如果我可以在 SQL 中执行此操作，那将更加清晰。我不会写存储过程；这必须是单个查询。

我这样做是因为我需要给定时间戳的最接近(时间上)有效值，但有时时间戳的值为 NULL。

Answer 1

类似于@par 解决方案，但有时间:

SELECT v
FROM (
 (SELECT v, TIMEDIFF(T, `timestamp`) AS tdiff
   FROM table_name
   WHERE `timestamp` <= T AND v IS NOT NULL
   ORDER BY `timestamp` DESC
   LIMIT 1)
UNION ALL
 (SELECT v, TIMEDIFF(`timestamp`, T) AS tdiff
   FROM table_name
   WHERE `timestamp` > T AND v IS NOT NULL
   ORDER BY `timestamp` ASC
   LIMIT 1)
) u
ORDER BY tdiff
LIMIT 1

此处 T 被赋予时间戳以搜索值 V。

要加快速度，您必须在 timestamp 列上建立索引。