반응형
2분마다 저장된 상태 로그에서 이벤트의 시작/종료 시간을 결정하는 방법
MariaDB 버전: 버전: 10.0.38-MariaDB-0+deb8u1
2분마다 디바이스의 상태(ON/OFF)와 Unix 타임의 타임스탬프가 보고되는 테이블이 있습니다.
select * from devices_stats
where device_id = 'LivingLight'
AND timestamp BETWEEN 1570080242 AND 1570084922;
+-------+-------------+--------+------------+-------------+
| id | device_id | status | timestamp | device_iddr |
+-------+-------------+--------+------------+-------------+
| 16416 | LivingLight | OFF | 1570080242 | 1 |
| 16427 | LivingLight | OFF | 1570080363 | 1 |
| 16438 | LivingLight | OFF | 1570080483 | 1 |
| 16449 | LivingLight | OFF | 1570080602 | 1 |
| 16460 | LivingLight | OFF | 1570080723 | 1 |
| 16471 | LivingLight | OFF | 1570080842 | 1 |
| 16482 | LivingLight | ON | 1570080963 | 1 |
| 16493 | LivingLight | ON | 1570081083 | 1 |
| 16504 | LivingLight | ON | 1570081203 | 1 |
| 16515 | LivingLight | ON | 1570081323 | 1 |
| 16526 | LivingLight | ON | 1570081443 | 1 |
| 16537 | LivingLight | ON | 1570081563 | 1 |
| 16548 | LivingLight | ON | 1570081682 | 1 |
| 16559 | LivingLight | ON | 1570081803 | 1 |
| 16570 | LivingLight | ON | 1570081922 | 1 |
| 16581 | LivingLight | ON | 1570082042 | 1 |
| 16592 | LivingLight | ON | 1570082163 | 1 |
| 16603 | LivingLight | ON | 1570082283 | 1 |
| 16614 | LivingLight | ON | 1570082402 | 1 |
| 16625 | LivingLight | ON | 1570082523 | 1 |
| 16636 | LivingLight | ON | 1570082643 | 1 |
| 16647 | LivingLight | ON | 1570082762 | 1 |
| 16658 | LivingLight | ON | 1570082882 | 1 |
| 16669 | LivingLight | OFF | 1570083003 | 1 |
| 16680 | LivingLight | OFF | 1570083123 | 1 |
| 16691 | LivingLight | OFF | 1570083242 | 1 |
| 16702 | LivingLight | OFF | 1570083363 | 1 |
| 16713 | LivingLight | OFF | 1570083483 | 1 |
| 16724 | LivingLight | OFF | 1570083603 | 1 |
| 16735 | LivingLight | OFF | 1570083722 | 1 |
| 16746 | LivingLight | OFF | 1570083843 | 1 |
| 16757 | LivingLight | OFF | 1570083963 | 1 |
| 16768 | LivingLight | OFF | 1570084083 | 1 |
| 16779 | LivingLight | OFF | 1570084202 | 1 |
| 16790 | LivingLight | OFF | 1570084323 | 1 |
| 16801 | LivingLight | OFF | 1570084442 | 1 |
| 16812 | LivingLight | ON | 1570084563 | 1 |
| 16823 | LivingLight | ON | 1570084683 | 1 |
| 16834 | LivingLight | OFF | 1570084803 | 1 |
| 16845 | LivingLight | OFF | 1570084922 | 1 |
+-------+-------------+--------+------------+-------------+
"ON" 이벤트 목록과 시작 및 종료 시간을 가져오고 싶습니다.
위의 예를 참고하여 다음과 같은 출력을 원합니다.
+-------------+------------+------------+
| device_id | start | stop |
+-------------+------------+------------+
| LivingLight | 1570080963 | 1570082882 |
| LivingLight | 1570084563 | 1570084683 |
문의 작성을 도와주시겠습니까?
다음은 사용자 정의 변수를 사용하는 방법입니다.주목할 점은 이 문제에서는 윈도 기능조차 쉽게 사용할 수 없다는 점입니다.단, 사용하시는 버전은 오래된 버전이기 때문에 지원도 하지 않습니다.다음 솔루션은 일반적인 솔루션이며 사용하지 않을 경우 시나리오를 처리합니다.WHERE을 조건으로 하다device_id여러 개를 원합니다.device_id결과 세트에 포함시킵니다.
여기서의 일반적인 생각은 '섬 수'(island number)를 계산하는 것입니다.chng(타임 스탬프에 근거해) 같은 것을 가지는 연속 행에 대해서,device_id그리고.status값(ON 또는 OFF)을 지정합니다.결국, 우리는 그 섬들만 걸러낼 수 있다.ON상태를 확인한 후 집계를 수행하여MIN()(스타트 타임 스탬프), 및MAX()(정지 타임스탬프).
스키마(MySQL v5.7)
CREATE TABLE device_stats
(`id` int, `device_id` varchar(11), `status` varchar(3), `timestamp` int, `device_iddr` int)
;
INSERT INTO device_stats
(`id`, `device_id`, `status`, `timestamp`, `device_iddr`)
VALUES
(16416, 'LivingLight', 'OFF', 1570080242, 1),
(16427, 'LivingLight', 'OFF', 1570080363, 1),
(16438, 'LivingLight', 'OFF', 1570080483, 1),
(16449, 'LivingLight', 'OFF', 1570080602, 1),
(16460, 'LivingLight', 'OFF', 1570080723, 1),
(16471, 'LivingLight', 'OFF', 1570080842, 1),
(16482, 'LivingLight', 'ON', 1570080963, 1),
(16493, 'LivingLight', 'ON', 1570081083, 1),
(16504, 'LivingLight', 'ON', 1570081203, 1),
(16515, 'LivingLight', 'ON', 1570081323, 1),
(16526, 'LivingLight', 'ON', 1570081443, 1),
(16537, 'LivingLight', 'ON', 1570081563, 1),
(16548, 'LivingLight', 'ON', 1570081682, 1),
(16559, 'LivingLight', 'ON', 1570081803, 1),
(16570, 'LivingLight', 'ON', 1570081922, 1),
(16581, 'LivingLight', 'ON', 1570082042, 1),
(16592, 'LivingLight', 'ON', 1570082163, 1),
(16603, 'LivingLight', 'ON', 1570082283, 1),
(16614, 'LivingLight', 'ON', 1570082402, 1),
(16625, 'LivingLight', 'ON', 1570082523, 1),
(16636, 'LivingLight', 'ON', 1570082643, 1),
(16647, 'LivingLight', 'ON', 1570082762, 1),
(16658, 'LivingLight', 'ON', 1570082882, 1),
(16669, 'LivingLight', 'OFF', 1570083003, 1),
(16680, 'LivingLight', 'OFF', 1570083123, 1),
(16691, 'LivingLight', 'OFF', 1570083242, 1),
(16702, 'LivingLight', 'OFF', 1570083363, 1),
(16713, 'LivingLight', 'OFF', 1570083483, 1),
(16724, 'LivingLight', 'OFF', 1570083603, 1),
(16735, 'LivingLight', 'OFF', 1570083722, 1),
(16746, 'LivingLight', 'OFF', 1570083843, 1),
(16757, 'LivingLight', 'OFF', 1570083963, 1),
(16768, 'LivingLight', 'OFF', 1570084083, 1),
(16779, 'LivingLight', 'OFF', 1570084202, 1),
(16790, 'LivingLight', 'OFF', 1570084323, 1),
(16801, 'LivingLight', 'OFF', 1570084442, 1),
(16812, 'LivingLight', 'ON', 1570084563, 1),
(16823, 'LivingLight', 'ON', 1570084683, 1),
(16834, 'LivingLight', 'OFF', 1570084803, 1),
(16845, 'LivingLight', 'OFF', 1570084922, 1)
;
쿼리 #1
SELECT
device_id, MIN(timestamp) AS start, MAX(timestamp) AS stop
FROM
(
SELECT
@c := IF(@s <> status OR @d <> device_id , @c+1, @c) AS chng,
@s := status AS status,
@d := device_id AS device_id,
timestamp
FROM
(
SELECT device_id, status, timestamp
FROM device_stats
WHERE device_id = 'LivingLight'
AND timestamp BETWEEN 1570080242 AND 1570084922
ORDER BY device_id, timestamp
) t1
CROSS JOIN (SELECT @s := '',
@d := '',
@c := 0) vars
) t2
WHERE t2.status = 'ON'
GROUP BY device_id, chng;
| device_id | start | stop |
| ----------- | ---------- | ---------- |
| LivingLight | 1570080963 | 1570082882 |
| LivingLight | 1570084563 | 1570084683 |
언급URL : https://stackoverflow.com/questions/58217219/how-to-determine-start-end-time-for-events-from-status-logs-stored-every-2-minut
반응형
'programing' 카테고리의 다른 글
| Linux에서 Mariadb 10과 호환되는 ODBC 드라이버가 있습니까? (0) | 2022.10.01 |
|---|---|
| URL에서 도메인 구문 분석 (0) | 2022.10.01 |
| 새 창이 아닌 새 탭에서 URL 열기 (0) | 2022.09.25 |
| array_filter() 뒤에 키를 리셋하여 0부터 순서대로 이동하려면 어떻게 해야 합니까? (0) | 2022.09.25 |
| 열 값이 동일할 때의 합계 값 (0) | 2022.09.25 |