mysql - 选择时间戳最接近的行

Question

我有一个类似于下表的表格 - 主要包含时间戳以及其他一些列:

WeatherTable
+---------------------+---------+----------------+      +
| TS                  | MonthET | InsideHumidity | .... |
+---------------------+---------+----------------+      |
| 2014-10-27 14:24:22 |       0 |             54 |      |
| 2014-10-27 14:24:24 |       0 |             54 |      |
| 2014-10-27 14:24:26 |       0 |             52 |      |
| 2014-10-27 14:24:28 |       0 |             54 |      |
| 2014-10-27 14:24:30 |       0 |             53 |      |
| 2014-10-27 14:24:32 |       0 |             55 |      |
| 2014-10-27 14:24:34 |       9 |             54 |      |
.......

我正在尝试制定一个 SQL 查询，该查询以特定的任意粒度(例如，每 15 秒)返回特定时间范围内的所有行(此处没有问题)。该数字始终以秒为单位指定，但不限于小于 60 的值。更复杂的是，时间戳不一定落在所需的粒度上，因此不能简单地选择 14:24:00 的时间戳, 14:24:15, 14:24:30 等 - 时间戳与每个值最接近的行需要包含在结果中。

例如，如果开始时间为 14:24:30，结束时间为 14:32:00，粒度为 130，则理想时间为:

14:24:30
14:26:40
14:28:50
14:31:00

但是，时间戳可能不存在于每个时间，在这种情况下，应选择时间戳与每个理想时间戳最接近的行。如果两个时间戳距离理想时间戳的距离相等，则应选择较早的时间戳。

数据库是 Web 服务的一部分，所以目前我只是忽略了 SQL 查询中的粒度，稍后在 (Java) 代码中过滤掉不需要的结果。但是，就内存消耗和性能而言，这似乎远非理想。

有什么想法吗？

Answer 1

你可以尝试这样做:

首先创建一个时间间隔列表。使用 Get a list of dates between two dates 中的存储过程 make_intervals创建一个以某种方式调用它的临时表:

call make_intervals(@startdate,@enddate,15,'SECOND');

然后您将有一个表time_intervals，其中两列之一名为interval_start。使用它来找到最接近每个间隔的时间戳，就像这样:

CREATE TEMPORARY TABLE IF NOT EXISTS time_intervals_copy
  AS (SELECT * FROM time_intervals);

SELECT
  time_intervals.interval_start,
  WeatherTable.*
FROM time_intervals
JOIN WeatherTable
  ON WeatherTable.TS BETWEEN @startdate AND @enddate
JOIN (SELECT
        time_intervals.interval_start AS interval_start,
        MIN(ABS(time_intervals.interval_start - WeatherTable.TS)) AS ts_diff
      FROM time_intervals_copy AS time_intervals
      JOIN WeatherTable
      WHERE WeatherTable.TS BETWEEN @startdate AND @enddate
      GROUP BY time_intervals.interval_start) AS min
  ON min.interval_start = time_intervals.interval_start AND
     ABS(time_intervals.interval_start - WeatherTable.TS) = min.ts_diff
GROUP BY time_intervals.interval_start;

这将找到与每个 time_interval 最接近的时间戳。注意:WeatherTable 中的每一行都可以列出多次，如果使用的间隔小于存储数据间隔的一半(或类似的东西，你明白了；)).

注意:我没有测试查询，它们是我脑子里写的。请根据您的用例进行调整并更正可能存在的小错误...