mysql - 识别事件的变化

Question

mySQL 数据库

我需要一些帮助/建议来找出识别大型数据集中“事件”字段中的更改的最佳方法和最有效的方法。

我有一些记录温度数据的硬件。根据某些设置评估温度数据并进行分类(1、2 或 3)。我想做的是确定“事件”/状态的更改何时发生以及何时变回，获取此事件的时间戳。

我怀疑最好和最明显的解决方案是模拟 Windows LAG 功能。棘手的是，这个事件代码的 3 选项意味着不同的事情，所以我正在考虑使用事件和滞后(事件)的总和，结果将识别例如从状态 1 到状态 3 的变化。此外，由于数据集很长(数百万行)并且事件会改变，状态会经常改变，我也只需要最后一次状态变化(该周期的开始和周期结束)。还有很多仪表报告，在评估状态变化时，每个仪表必须保持独立。

有兴趣是否有人有任何建议或有类似的疑问以及如何解决。如果可以使查询快速高效并获得所需的结果，欢迎任何建议，甚至是结构的更改。

例如数据集

| date_time           | event_code | meter_id |
|---------------------|------------|----------|
| 2017-04-15 06:20:10 | 2          |1         |
| 2017-04-15 06:21:52 | 2          |2         |
| 2017-04-15 06:28:13 | 1          |2         |
| 2017-04-15 06:32:01 | 2          |1         |
| 2017-04-15 06:32:49 | 2          |2         |
| 2017-04-15 06:37:28 | 3          |1         |
| 2017-04-15 06:39:21 | 3          |1         |
| 2017-04-15 06:44:01 | 3          |1         |
| 2017-04-15 06:45:28 | 2          |1         |
| 2017-04-15 06:49:18 | 2          |2         |

期望的结果 - 假设 event_code 2 是正常状态

| date_time           | event_code | meter_id |
|---------------------|------------|----------|
| 2017-04-15 06:28:13 | 1          |2         | start and end event 1 meter 2
| 2017-04-15 06:32:49 | 2          |2         | start event 2 meter 2
| 2017-04-15 06:37:28 | 3          |1         | start event 3 meter 1
| 2017-04-15 06:44:01 | 3          |1         | end event 3 meter 1
| 2017-04-15 06:45:28 | 2          |1         | start and end event 2 meter 1
| 2017-04-15 06:49:18 | 2          |2         | end event 2 meter 2

编辑所需结果

| event_code | meter_id | last_evet_start     | last_evet_end       |
|------------|----------|---------------------|---------------------|
| 1          | 2        | 2017-04-15 06:45:28 | 2017-04-15 06:45:28 |
| 1          | 3        | 2017-04-15 06:37:28 | 2017-04-15 06:44:01 |
| 2          | 1        | 2017-04-15 06:28:13 | 2017-04-15 06:28:13 |
| 2          | 2        | 2017-04-15 06:32:49 | 2017-04-15 06:49:18 |

Answer 1

我不确定这是否是想要的结果，但您可以通过这种方式得到类似的结果:

Rextester here .

您可以模拟 LAG 功能，并在每次 event_code 更改时设置一个新组。

select date_time, event_code,
             if (@last_event = 0 or @last_event <> event_code, @grp := @grp + 1, @grp := @grp) reset_point,
             @last_event := event_code
      from (select @last_event := 0, @grp := 0) x,
           (select date_time, event_code
            from events
            where event_code <> 2
            order by date_time) y;

| date_time           | event_code | reset_point | @last_event := event_code |
|---------------------|------------|-------------|---------------------------|
| 15.04.2017 06:28:13 | 1          | 1           | 1                         |
| 15.04.2017 06:37:28 | 3          | 2           | 3                         |
| 15.04.2017 06:39:21 | 3          | 2           | 3                         |
| 15.04.2017 06:44:01 | 3          | 2           | 3                         |

您可以按新的 grp 列进行分组并获取最大和最小日期时间。

select event_code, min(date_time) as start, max(date_time) as end
from (
      select date_time, event_code,
             if (@last_event = 0 or @last_event <> event_code, @grp := @grp + 1, @grp := @grp) reset_point,
             @last_event := event_code
      from (select @last_event := 0, @grp := 0) x,
           (select date_time, event_code
            from events
            where event_code <> 2
            order by date_time) y
     ) z
group by reset_point
;

| event_code | start               | end                 |
|------------|---------------------|---------------------|
| 1          | 15.04.2017 06:28:13 | 15.04.2017 06:28:13 |
| 3          | 15.04.2017 06:37:28 | 15.04.2017 06:44:01 |

现在您可以再次取消旋转，但请告诉我此解决方案是否适合您。