mysql - 如何使用 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 中)排。然后做进一步的连接，试图找到一个存在于当前行(in d)和候选行(in d_prev)之间的行(in d_inter) )。如果我们找不到这样的一行(d_inter.time 为 null)，那么该候选行确实是前一行。