根据时间从多个表中获取最接近数据的最快方法

我有三个表，设置如下：

TEMPERATURE_1
  time
  zone (FK)
  temperature
TEMPERATURE_2
  time
  zone (FK)
  temperature
TEMPERATURE_3
  time
  zone (FK)
  temperature

每个表中的数据会定期更新，但不一定同时更新（即，时间条目不相同）。

我希望每次都能访问每个表中最近的读数，即：

TEMPERATURES
  time
  zone (FK)
  temperature_1
  temperature_2
  temperature_3

换句话说，对于我的三个表中的每个唯一时间，我想要在 TEMPERATURES 表中显示一行，其中temperature_n值是每个原始表中最接近的温度读数。

目前，我使用两个视图进行了设置：

create view temptimes
as select time, zone 
  from temperature_1
union
  select time, zone
  from temperature_2
union
  select time, zone
  from temperature_3;
create view temperatures
as select tt.time,
          tt.zone,
          (select temperature 
           from temperature_1
           order by abs(timediff(time, tt.time))
           limit 1) as temperature_1,
          (select temperature 
           from temperature_2
           order by abs(timediff(time, tt.time))
           limit 1) as temperature_2,
          (select temperature 
           from temperature_3
           order by abs(timediff(time, tt.time))
           limit 1) as temperature_3,
from temptimes as tt
order by tt.time;

这种方法有效，但在生产中使用速度太慢（对于每个温度~1000条记录的小数据集，需要几分钟+）。

我对SQL不是很好，所以我确定我错过了正确的方法。我应该如何处理这个问题？

昂贵的部分是相关子查询必须计算每个temperature_*表的每一行的时间差，以便为主查询中一行的一列找到一个最接近的行。

如果可以根据索引选择当前时间之后的一行和当前时间之前的一行，并且只计算这两个候选项的时间差，那将大大加快速度。要快速做到这一点，您所需要的只是表中time列的索引。

我忽略了专栏zone，因为它在问题中的作用仍然不清楚，它只是给核心问题增加了更多的噪音。应该很容易添加到查询中。

如果没有其他视图，此查询将同时执行所有操作：

SELECT time
      ,COALESCE(temp1
            ,CASE WHEN timediff(time, time1a) > timediff(time1b, time) THEN
                (SELECT t.temperature
                 FROM   temperature_1 t
                 WHERE  t.time = y.time1b)
             ELSE
                (SELECT t.temperature
                 FROM   temperature_1 t
                 WHERE  t.time = y.time1a)
             END) AS temp1
      ,COALESCE(temp2
            ,CASE WHEN timediff(time, time2a) > timediff(time2b, time) THEN
                (SELECT t.temperature
                 FROM   temperature_2 t
                 WHERE  t.time = y.time2b)
             ELSE
                (SELECT t.temperature
                 FROM   temperature_2 t
                 WHERE  t.time = y.time2a)
             END) AS temp2
      ,COALESCE(temp3
            ,CASE WHEN timediff(time, time3a) > timediff(time3b, time) THEN
                (SELECT t.temperature
                 FROM   temperature_3 t
                 WHERE  t.time = y.time3b)
             ELSE
                (SELECT t.temperature
                 FROM   temperature_3 t
                 WHERE  t.time = y.time3a)
             END) AS temp3
FROM  (
  SELECT time
        ,max(t1) AS temp1
        ,max(t2) AS temp2
        ,max(t3) AS temp3
        ,CASE WHEN max(t1) IS NULL THEN
           (SELECT t.time FROM temperature_1 t
            WHERE  t.time < x.time
            ORDER  BY t.time DESC LIMIT 1) ELSE NULL END AS time1a
        ,CASE WHEN max(t1) IS NULL THEN
           (SELECT t.time FROM temperature_1 t
            WHERE  t.time > x.time
            ORDER  BY t.time      LIMIT 1) ELSE NULL END AS time1b
  
        ,CASE WHEN max(t2) IS NULL THEN
           (SELECT t.time FROM temperature_2 t
            WHERE  t.time < x.time
            ORDER  BY t.time DESC LIMIT 1) ELSE NULL END AS time2a
        ,CASE WHEN max(t2) IS NULL THEN
           (SELECT t.time FROM temperature_2 t
            WHERE  t.time > x.time
            ORDER  BY t.time      LIMIT 1) ELSE NULL END AS time2b
        ,CASE WHEN max(t3) IS NULL THEN
           (SELECT t.time FROM temperature_3 t
            WHERE  t.time < x.time
            ORDER  BY t.time DESC LIMIT 1) ELSE NULL END AS time3a
        ,CASE WHEN max(t3) IS NULL THEN
           (SELECT t.time FROM temperature_3 t
            WHERE  t.time > x.time
            ORDER  BY t.time      LIMIT 1) ELSE NULL END AS time3b
  FROM  (
      SELECT time, temperature AS t1, NULL AS t2, NULL AS t3 FROM temperature_1
      UNION ALL
      SELECT time, NULL AS t1, temperature AS t2, NULL AS t3 FROM temperature_2
      UNION ALL
      SELECT time, NULL AS t1, NULL AS t2, temperature AS t3 FROM temperature_3
      ) AS x
  GROUP BY time
  ) y
ORDER BY time;

->sqlfiddle

解释

SuqQuery X 替换您的视图temptimes并将温度带入结果中。如果所有三个表都同步并且具有所有相同时间点的温度，则甚至不需要其余表并且速度非常快.
对于三个表中有一个没有行的每个时间点，将按照指示获取温度：从每个表中获取"最近"的温度。

suqquery y 聚合 x 中的行，并根据当前时间从缺少温度的每个表中获取上一次时间（time1a）和下一次时间（time1b）。使用索引时，这些查找应该很快。

最后一个查询从实际缺失的每个温度具有最接近时间的行中获取温度。

如果MySQL允许从当前子查询上方的多个级别引用列，则此查询可能会更简单。位它不能。在PostgreSQL中工作得很好：->sqlfiddle

如果可以从相关的子查询返回多个列，那也会更简单，但我不知道如何在 MySQL 中做到这一点。

使用CTE和窗口函数会简单得多，但是MySQL不知道这些现代SQL功能（与其他相关的RDBMS不同）。

这很慢的原因是它需要 3 次表扫描来计算和排序差异。

我假设您都准备好了时区列上的索引 - 目前由于表扫描问题，它们无济于事。

有许多

选项可以避免这种情况，具体取决于您的需求和数据收集速率。

您已经说过，数据是定期收集的，但不是同时收集的。这提出了一些选择。

您需要临时数据的重要性级别 - 天，小时，分钟等。仅将时区信息存储到该重要性级别（或具有另一个列）并对此进行查询。
如果您知道 3 个壁橱时间将在某个时间范围内（小时、天等）内，请在 where 子句中放置一个 where 子句，以将计算限制为那些潜在的候选时间。您正在有效地构建直方图类型的存储桶 - 您将需要一个日历表来有效地执行此操作。
使比较单向，即将考虑限制在您要查找的时间之后的时间，因此，如果您要查找 12：00：00，则 13：45：32 是候选者，但 11：59：59 不是。

我理解您要完成的目标 - 问问自己为什么以及更简单的解决方案是否可以满足您的需求。

我的建议是，你不要花最接近的时间，而是在给定时间或之前第一次。原因很简单：通常给定时间的数据是当时已知的数据。对于大多数目的，合并未来信息通常不是一个好主意。

通过此更改，您可以修改查询以利用 time 上的索引。查询索引的问题在于该函数排除了索引的使用。

因此，如果您想要最新的温度，请对每个变量使用以下命令：

      (select temperature 
       from temperature_1 t2
       where t2.time <= tt.time
       order by t2.time desc
       limit 1
      ) as temperature_1,

实际上，您也可以像这样构造它：

      (select time 
       from temperature_1 t2
       where t2.time <= tt.time
       order by t2.time desc
       limit 1
      ) as time_1,

然后将温度信息重新加入。通过使用索引，这将是有效的。

考虑到这一点，您实际上可以有两个变量 time_1_before 和 time_1_after ，表示在或之前的最佳时间以及之后或之后的最佳时间。您可以使用选择中的逻辑来选择最接近的值。使用索引返回温度的连接应该是有效的。

但是，我要重申，我认为最后温度或之前可能是最好的选择。

解释

相关内容

最新更新

热门标签：