SQL:选择列值从上一行更改的行

Question

假设我有这个（MySQL）数据库，按时间戳递增排序:

Timestamp   System StatusA StatusB 
2011-01-01     A      Ok     Ok      
2011-01-02     B      Ok     Ok     
2011-01-03     A     Fail   Fail     
2011-01-04     B      Ok    Fail     
2011-01-05     A     Fail    Ok      
2011-01-06     A      Ok     Ok      
2011-01-07     B     Fail   Fail    

如何选择StatusA从该系统的上一行更改的行？ StatusB无关紧要（我在这个问题中展示它只是为了说明StatusA不变的每个系统可能有许多连续行）。 在上面的示例中，查询应该返回行2011-01-03（对于SystemA，状态在2011-01-01和2011-01-03之间更改），2011-01-06和2011-01-07。

查询应该在表中有数万条记录的情况下快速执行。

谢谢

Answer 1

使用行号

我有0.05秒处理20000行

select a1.*
  from (select rownum R_NUM, TIMESTAMP, System, StatusA from TableX) a1 
  join (select rownum R_NUM, TIMESTAMP, SYSTEM, STATUSA from TABLEX) a2 
    on a1.R_NUM = a2.R_NUM+1 
 where a1.system = a2.system 
   and a1.StatusA != a2.StatusA

Answer 2

select a.Timestamp, a.System, a.StatusA, a.StatusB
from tableX as a
cross join tableX as b
where a.System = b.System
and a.Timestamp > b.Timestamp
and not exists (select * 
    from tableX as c
    where a.System = c.System
    and a.Timestamp > c.Timestamp
    and c.Timestamp > b.Timestamp
)
and a.StatusA <> b.StatusA;

更新处理注释:为什么不使用内部联接而不是交叉联接？

这个问题需要一个MySQL解决方案。 根据文件:

在MySQL中，CROSS JOIN是与内部JOIN等效的语法（它们可以相互替换）。 在标准SQL中，它们是不等价的。 内部联接与ON子句一起使用，否则使用交叉联接。

这意味着这两种连接中的任何一种都可以工作。

与ON一起使用的conditional_expr是可以在WHERE子句中使用的任何形式的条件表达式。 通常，对于指定如何联接表的条件，您应该使用ON子句，而对于在结果集中需要哪些行，您应该使用WHERE子句。

条件a.system=b.system可能属于'How to join tables'类别，因此在这种情况下使用内部联接会更好。

由于两者产生相同的结果，因此可能在性能上存在差异。 要说哪个会更快，我需要知道连接是如何在内部实现的--它们是使用索引还是哈希来进行连接。

Answer 3

SELECT a.*
FROM tableX AS a
WHERE a.StatusA <>
      ( SELECT b.StatusA
        FROM tableX AS b
        WHERE a.System = b.System
          AND a.Timestamp > b.Timestamp
        ORDER BY b.Timestamp DESC
        LIMIT 1
      ) 

但是您也可以尝试（使用（系统，时间戳）上的索引:

SELECT System, Timestamp, StatusA, StatusB
FROM
  ( SELECT (@statusPre <> statusA AND @systemPre=System) AS statusChanged
         , System, Timestamp, StatusA, StatusB
         , @statusPre := StatusA
         , @systemPre := System
    FROM tableX
       , (SELECT @statusPre:=NULL, @systemPre:=NULL) AS d
    ORDER BY System
           , Timestamp
  ) AS good
WHERE statusChanged ;