如何使用SQL有效地确定行之间的更改

Question

我有一个非常大的MySQL表，其中包含从多个传感器读取的数据。本质上，这里有一个时间戳和一个值列。我将省略传感器ID，在此处为其他详细信息编制索引：

CREATE TABLE `data` (
  `time` datetime NOT NULL,
  `value` float NOT NULL
)

该value列很少改变，我需要找点时间，当发生这些变化。假设每分钟都有一个值，以下查询恰好返回了我需要的值：

SELECT d.*, 
  (SELECT value FROM data WHERE time<d.time ORDER by time DESC limit 1) 
    AS previous_value 
FROM data d 
HAVING d.value<>previous_value OR previous_value IS NULL;

+---------------------+-------+----------------+
| time                | value | previous_value |
+---------------------+-------+----------------+
| 2011-05-23 16:05:00 |     1 |           NULL |
| 2011-05-23 16:09:00 |     2 |              1 |
| 2011-05-23 16:11:00 |   2.5 |              2 |
+---------------------+-------+----------------+

唯一的问题是，这是非常低效的，主要是由于依赖子查询。使用MySQL 5.1必须提供的工具来对此进行优化的最佳方法是什么？

最后一个约束是，在将值插入数据表之前不对它们进行排序，并且可能在以后更新它们。这可能会影响任何可能的非规范化策略。

Answer 1

您可以尝试一下-我不保证它的性能会更好，但这是我通常将行与“上一个”行相关联的方式：

SELECT
    * --TODO, list columns
FROM
    data d
       left join
    data d_prev
       on
           d_prev.time < d.time --TODO - Other key columns?
       left join
    data d_inter
       on
           d_inter.time < d.time and
           d_prev.time < d_inter.time --TODO - Other key columns?
WHERE
    d_inter.time is null AND
    (d_prev.value is null OR d_prev.value <> d.value)

（我认为这是正确的-可以使用一些样本数据来对其进行验证）。

基本上，这种想法是将表与其自身连接起来，并针对“上一个”行的每一行（在中d）找到候选行（在中d_prev）。然后进行进一步的联接，以尝试找到d_inter存在于当前行（in
d）和候选行（in d_prev）之间的行（in ）。如果我们找不到这样的行（d_inter.time is null），则该候选项确实是前一行。