如何从每2分钟存储的状态日志中确定事件的开始/结束时间 [英] How to determine start/end time for events from status logs stored every 2 minutes

查看：55
本文介绍了如何从每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)的连续行(基于时间戳)计算一个岛号"(在查询中表示为chng).最终，我们只能过滤出具有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 |

在数据库小提琴上查看

这篇关于如何从每2分钟存储的状态日志中确定事件的开始/结束时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆