MySQL How to efficiently determine changes between rows using SQL

여러 센서에서 읽은 데이터가 포함 된 매우 큰 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이 제공해야하는 도구를 사용하여이를 최적화하는 가장 좋은 방법은 무엇입니까?

마지막 제약은 값이 데이터 테이블에 삽입되기 전에 정렬되지 않고 나중에 업데이트 될 수 있다는 것입니다. 이는 가능한 비정규 화 전략에 영향을 미칠 수 있습니다.

 

해결 방법

 

이 방법을 시도해 볼 수 있습니다. 더 나은 성능을 보장 할 수는 없지만 행을 "이전"행과 연관시키는 일반적인 방법입니다.

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 )과 후보 행 ( d_prev) 사이에있는 행 ( d_inter )을 찾습니다. ). 그러한 행을 찾을 수 없다면 ( d_inter.time은 null ) 그 후보는 실제로 이전 행입니다.

 

참조 페이지 https://stackoverflow.com/questions/6109894