如何从每 2 分钟存储的状态日志中确定事件的开始/结束时间
MariaDB 版本:版本:10.0.38-MariaDB-0+deb8u1
MariaDB version: version: 10.0.38-MariaDB-0+deb8u1
我有一个表格,每 2 分钟报告一次设备状态(开/关),其时间戳为 unix 时间.
I have a table where every 2 minutes is reported the status for a device (ON/OFF) with it's timestamp in unix time.
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"事件列表.
I would like to retrieve a list of "ON" events with start and end time.
考虑到上面的例子,我想要这样的输出:
considering the example above i want to have an output like this:
+-------------+------------+------------+
| device_id | start | stop |
+-------------+------------+------------+
| LivingLight | 1570080963 | 1570082882 |
| LivingLight | 1570084563 | 1570084683 |
您能帮我创建查询吗?
推荐答案
这是一种使用用户定义变量的方法.值得注意的是,即使是 Windowing 函数也不能在这个问题中直接使用.但是,您的版本很旧,也不支持它们.以下解决方案是通用的,如果您不在 device_id
上使用 WHERE
条件并且希望结果集中有多个 device_id
,则可以处理场景.
Here is an approach using user-defined variables. It is noteworthy that even Windowing functions cannot be used in a straightforward manner in this problem. Nevertheless, your version is old, and don't support them either. Following solution is generic, and handle scenario if you dont use WHERE
condition on the device_id
and want multiple device_id
in the result-set.
这里的一般想法是,我们为具有相同 device_id
和 status
值(ON 或 OFF).最终,我们可以只过滤掉那些处于ON
状态的岛,然后聚合得到MIN()
(开始时间戳)和MAX()
(停止时间戳).
General idea here is that we compute an "island number" (denoted as chng
in the query) for successive rows (based on timestamp) having same device_id
and status
value (either ON or OFF). Eventually, we can filter out only those island which are having ON
status, and then do aggregation to get MIN()
(start timestamp), and MAX()
(stop timestamp).
架构 (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 |
<小时>
查看 DB Fiddle
相关文章