如何从每 2 分钟存储的状态日志中确定事件的开始/结束时间

2022-01-15 00:00:00 sql mariadb mysql

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

相关文章