Filter rows in mysql

Question

I'm trying to solve a MySQL problem without going crazy. Not sure if it is feasible or not. Data come from a door/light sensor to detect if toilet is occupied. When door is closed or opened, I get the info + light info. If I have info of closed door and light<10, I say that toilet is not occupied, if light>10, toilet is occupied, and if door is open, toilet is not occupied.

Here is an example of my data :

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

The idea is at the end to have only a series of door_open to 0 to 1, it's not possible to have two 0 or two 1 consecutively. So I need to keep first door_open=0 with light>10 following a door_open=1, and first door_open=1 after door_open=0, whatever light value.

Is it possible with MySQL? I use MariaDB 10.3.9.

Thanks for your ideas.

The output should be like that :

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

(I simplified the time, it's not really important here) Here is a fiddle

Answer 1

Here is the potential answer to my problem, after working on Nick solution. I had to reorder my table (after deleting rows) to avoid an order mess.

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

Next thing is to update the query to use a partition over toilet_id to separate data from each id.

Answer 2

This query should do what you want. It uses a MySQL variable to delay the value of door_open by 1 row, and then returns rows where door_open=0 with light>10 following a door_open=1, and first door_open=1 after door_open=0, whatever light value:

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

Output (from your fiddle data):

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