SQL: выбор строк, где значение столбца изменилось с предыдущей строки

Скажем, у меня есть эта (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 (StatusA изменен между 2011-01-01 и 2011-01-03 для SystemA), 2011-01-06, 2011-01-07.

Запрос должен выполняться быстро, когда таблица содержит десятки тысяч записей.

Спасибо

Ответ 1

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
      )

Но вы можете попробовать это также (с индексом на (System,Timestamp):

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 ;

Ответ 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 является синтаксическим эквивалентно INNER JOIN (они могут заменить друг друга). В стандартном SQL, они не эквивалентны. INNER JOIN - это используется с предложением ON, CROSS JOIN используется иначе.

Это означает, что любое из этих соединений будет работать.

Условие condition_expr, используемое с ON, равно любое условное выражение вида которые могут использоваться в предложении WHERE. Как правило, вы должны использовать ON для условий, которые определяют, как для объединения таблиц и предложения WHERE ограничить, какие строки вы хотите в результат.

Условие a.System = b.System, вероятно, относится к категории "как присоединиться к таблицам", поэтому использование INNER JOIN было бы лучше в этом случае.

Поскольку оба продукта дают одинаковые результаты, разница может быть в производительности. Чтобы сказать, что будет быстрее, мне нужно знать, как объединения выполняются внутри страны - используют ли они индексы или хеширование для соединения.

Ответ 3

Использовать rownum

У меня есть 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

Ответ 4

Здесь немного более короткая версия с аналогичной логикой. Я проверял это так часто, я уверен, что он эффективен; прежде всего потому, что он устраняет коррелированный подзапрос (WHERE NOT EXISIS).

"c" находится там, чтобы убедиться, что b находится непосредственно под a - он говорит, что c (между ними) не может быть найден (через тест NULL).

SELECT a.Timestamp, a.System, a.StatusA, a.StatusB
FROM tableX AS a
JOIN tableX AS b
    ON a.System = b.System
    AND a.Timestamp > b.Timestamp
LEFT JOIN tableX AS c
    ON a.System = b.System
    AND a.Timestamp > c.Timestamp
    AND b.Timestamp < c.Timestamp
WHERE c.System IS NULL
    AND a.StatusA <> b.StatusA;

Ответ 5

Ответ Egor работал у меня в MSSQL с небольшим изменением. Если бы заменить операторы ROWNUM на:

select row_number () over (order by TIMESTAMP) as R_NUM, ...

Ответ 6

ВЫБЕРИТЕ a. * FROM (выберите row_number() over (разделить по Порядку системы по Timestamp asc) в качестве aRow, Timestamp, System, StatusA, StatusB из tableX) в качестве левого соединения (выберите row_number() over (разделить по Порядку системы по Timestamp) asc) как bRow, Timestamp, System, StatusA, StatusB из таблицы X) как b в a.aRow = b.bRow + 1 и a.System = b.System, где (a.StatusA! = b.StatusA или b.StatusA является ноль)

Он вернет первую строку и строки, где значение отличается.