SQL: 열 값이 이전 행에서 변경된 행 선택

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    

해당 시스템의 이전 행에서 상태 A가 변경된 행을 선택하려면 어떻게 해야 합니까?상태 B는 중요하지 않습니다(이 질문에서는 상태 A가 변경되지 않는 각 시스템에 대해 연속된 행이 많을 수 있음을 설명하기 위해 표시합니다).위의 예제에서 쿼리는 2011-01-03 행을 반환해야 합니다(시스템 A의 경우 2011-01-01과 2011-01-03 사이에 상태 A가 변경됨). 2011-01-06, 2011-01-07.

테이블에 수만 개의 레코드가 있는 상태에서 쿼리를 빠르게 실행해야 합니다.

감사해요.

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 ;

로넘 사용

20000줄에서 0.05초가 있습니다.

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

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이 사용됩니다.

이는 이 두 가지 결합 중 하나가 작동한다는 것을 의미합니다.

ON과 함께 사용되는 conditional_expr은 WHERE 절에서 사용할 수 있는 양식의 조건부 표현입니다.일반적으로 테이블에 조인하는 방법을 지정하는 조건에는 ON 절을 사용하고 결과 집합에서 원하는 행을 제한하려면 WHERE 절을 사용해야 합니다.

상태가a.System = b.System테이블에 가입하는 방법' 범주에 속할 수 있으므로 이 경우에는 INNER JOIN을 사용하는 것이 더 좋습니다.

둘 다 동일한 결과를 도출하기 때문에 성능에 차이가 있을 수 있습니다.어느 쪽이 더 빠를지를 말하기 위해서는 내부적으로 조인이 어떻게 구현되는지, 즉 인덱스를 사용하여 조인을 수행하는지, 해시를 사용하여 조인을 수행하는지를 알아야 합니다.

이것이 바로 윈도잉 기능을 위해 만들어진 것입니다.LAG는 정확한 답변을 제공합니다.

create table t1 (ts date, sys char(1),stata varchar(10),statb varchar(10));

insert into t1 values
('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');

select * from (
select ts,sys,stata,lag(stata) over(partition by sys order by ts asc) as prev from t1
) as subsel where stata!=prev

여기 비슷한 논리를 가진 약간 짧은 버전이 있습니다.저는 이것을 자주 테스트해 보았는데, 주로 상관 관계가 있는 하위 쿼리(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;

MSSQL에서 Egor의 답변은 작은 변화 하나로 저에게 효과가 있었습니다.ROWNUM 문을 다음으로 대체해야 했습니다.

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

SELECT   a.*
FROM    (select row_number() over (partition by System order by Timestamp asc) as aRow, Timestamp, System, StatusA, StatusB from tableX) as a
left join (select row_number() over (partition by System order by Timestamp asc) as bRow, Timestamp, System, StatusA, StatusB from tableX) as b on a.aRow = b.bRow + 1 and a.System = b.System 
where (a.StatusA != b.StatusA or b.StatusA is null)

값이 다른 첫 번째 행과 행을 반환합니다.

Select * from table
Qualify lag(StatusA) is distinct from StatusA over (Partition by System order by Timestamp)
;

mysql이 Qualify그리고.is distinct from함수:

SELECT
    Timestamp, System, StatusA, StatusB 
FROM (
    SELECT
        *, lag(StatusA) OVER (Partition by System order by Timestamp) as prev 
    FROM
         table
) a
WHERE
    a.prev != StatusA AND a.prev is null
;

언급URL : https://stackoverflow.com/questions/6560000/sql-selecting-rows-where-column-value-changed-from-previous-row