mysql - 一种提高以下查询性能的方法(更新表)

Question

这个问题与我之前发布的一个问题有关，可以在这里找到:Update values of database with values that are already in DB .

我有以下情况:一个表存储来自不同传感器的数据(我总共有 8 个传感器)。表格的每一行都具有以下结构:

SensorID --- TimestampMS --- RawData --- Data

例如，对于名为 TEMPSensor1 的温度传感器，我有以下内容:

TEMPSensor1 --- 1000 --- 200 --- 2
TEMPSensor1 --- 2000 --- 220 --- 2.2

以此类推，对于每个传感器(我总共有 8 个)。我在读取数据时遇到了一些问题，并且有些行的数据“不正确”。恰好当原始数据字段为 65535 时，我应该更新该特定行。我想做的是将下一个值(及时)放入那个“损坏的数据”。所以，如果我们有这个:

TEMPSensor1 --- 1000 --- 200 --- 2
TEMPSensor1 --- 2000 --- 220 --- 2.2
TEMPSensor1 --- 3000 --- 65535 --- 655.35
TEMPSensor1 --- 4000 --- 240 --- 2.4

做完Update之后，table的内容应该改为:

TEMPSensor1 --- 1000 --- 200 --- 2
TEMPSensor1 --- 2000 --- 220 --- 2.2
TEMPSensor1 --- 3000 --- 240 --- 2.4
TEMPSensor1 --- 4000 --- 240 --- 2.4

我最终做了以下事情:

UPDATE externalsensor es1
INNER JOIN externalsensor es2 ON es1.sensorid = es2.sensorid AND (es2.timestampms - es1.timestampms) > 60000 AND (es2.timestampms - es1.timestampms) < 120000  AND es1.rawdata <> 65535
SET es1.rawdata = es2.rawdata, es1.data = es2.data
WHERE es1.rawdata = 65535

因为我知道传感器的两次读取之间有 60000 到 120000 毫秒。但是，如果我有两个以下“损坏的”读数，那将不起作用。任何人都可以建议一种更有效地执行此操作的方法，而不使用子查询选择，而是加入吗？我的想法是有一个 JOIN，在它的 timestampms 之后为您提供该传感器的所有可能值，并且只获得第一个，但我不知道如何限制该 JOIN 结果。

感谢。

Answer 1

这是一个没有相关子查询但有三角连接的解决方案(不确定哪个更糟):

UPDATE externalsensor bad

  INNER JOIN (
    SELECT
      es1.SensorID,
      es1.TimestampMS,
      MIN(es2.TimestampMS) AS NextGoodTimestamp
    FROM externalsensor es1
      INNER JOIN externalsensor es2
        ON es1.SensorID = es2.SensorID AND
           es1.TimestampMS < es2.TimestampMS
    WHERE es1.RawData = 65535
      AND es2.RawData <> 65535
    GROUP BY
      es1.SensorID,
      es1.TimestampMS
  ) link ON bad.SensorID = link.SensorID AND
            bad.TimestampMS = link.TimestampMS

  INNER JOIN externalsensor good
    ON link.SensorID = good.SensorID AND
       link.NextGoodTimestamp = good.TimestampMS

SET
  bad.RawData = good.RawData,
  bad.Data = good.Data

假定时间戳在单个传感器组中是唯一的。