在mysql中筛选行

Question

我正在试着在不发疯的情况下解决一个MySQL问题。不确定它是否可行。数据来自门/光传感器，以检测厕所是否有人使用。当门关闭或打开时，我会得到信息+灯光信息。如果我有关闭的门和light<10的信息，我说厕所没有被占用，如果light>10，厕所被占用，如果门是打开的，则厕所没有被占用。

以下是我的数据示例：

id   wc_id   door_open  light      time
138     0         1     64     2018-10-10 12:28:51
139     0         0     58     2018-10-10 12:34:00
140     0         0     54     2018-10-10 12:34:38
141     0         1     68     2018-10-10 12:35:11
142     0         1     3      2018-10-10 12:35:36
143     0         0     60     2018-10-10 12:37:56
144     0         0     60     2018-10-10 12:37:57
145     0         0     57     2018-10-10 12:38:30
146     0         1     65     2018-10-10 12:43:53
147     0         1     3      2018-10-10 12:44:17
148     0         0     63     2018-10-10 13:10:55
149     0         0     59     2018-10-10 13:11:16
150     0         1     71     2018-10-10 13:12:09
151     0         1     4      2018-10-10 13:12:14
152     0         1     1      2018-10-10 13:15:07
153     0         0     62     2018-10-10 13:17:18
154     0         0     58     2018-10-10 13:18:01
155     0         1     68     2018-10-10 13:19:20
156     0         1     3      2018-10-10 13:19:56
157     0         1     42     2018-10-10 13:26:41
158     0         0     63     2018-10-10 13:26:44
159     0         0     58     2018-10-10 13:27:39
160     0         1     71     2018-10-10 13:27:40
161     0         1     3      2018-10-10 13:28:37

这个想法是在最后只有一系列从0到1的door_open，不可能有两个0或两个1连续。所以我需要保持light>10跟在door_open=1之后的第一个door_open=0，以及door_open=0之后的第一个door_open=1，不管是什么轻量值。

是否可以使用MySQL？我使用MariaDB 10.3.9。

谢谢你的点子。

输出应如下所示：

id   wc_id   door_open  light      time
139     0         0     58       12:34:00
141     0         1     68       12:35:11
143     0         0     60       12:37:56
146     0         1     65       12:43:53
148     0         0     63       13:10:55
150     0         1     71       13:12:09
153     0         0     62       13:17:18
155     0         1     68       13:19:20
158     0         0     63       13:26:44
160     0         1     71       13:27:40

(我简化了时间，这在这里并不重要)这是一个fiddle

Answer 1

这个查询应该可以执行您想要的操作。它使用MySQL变量将door_open的值延迟1行，然后返回其中door_open=0在door_open=1之后的行，以及在door_open=0之后的第一个door_open=1，无论轻量值是什么：

SELECT events.*, @door_open := door_open
FROM events
JOIN (SELECT @door_open := 1) do
WHERE @door_open = 0 AND door_open = 1 OR
      @door_open = 1 AND door_open = 0 AND light > 10

输出(来自您的小提琴数据)：

id      toilet_id   door_open   light   time                    @door_open := door_open
101     0           false       62      2018-10-10T11:39:31Z    0
103     0           true        69      2018-10-10T11:39:34Z    1
104     0           false       62      2018-10-10T11:42:16Z    0
106     0           true        68      2018-10-10T11:45:50Z    1
109     0           false       56      2018-10-10T12:13:11Z    0

Updated SQLFiddle

Answer 2

这是我的问题的潜在答案，工作之后的尼克解决方案。我不得不重新排序我的表(在删除行之后)，以避免顺序混乱。

select es.id, 
   es.idNext,
   es.toilet_id,
   es.time,
   es.nextTime,
   timediff(es.nextTime, es.time) AS duration 
   from (
        SELECT id, toilet_id, time,
               @door_open := door_open as door_open,
               lead(id, 1) OVER(ORDER BY id) idNext,
               lead(time, 1) OVER(ORDER BY id) nextTime
               FROM events e
               JOIN (SELECT @door_open := 1) do
                  WHERE @door_open = 0 AND door_open = 1 OR
                  @door_open = 1 AND door_open = 0 AND light > 20
        ) es 
    where 
        es.door_open=0 and 
        timediff(es.nextTime, es.time)>5

下一步是更新查询，使用toilet_id上的分区将数据与每个id分开。