Filtrer des lignes

Version imprimable

Filtrer des lignes

Bonjour à tous,

Je suis en train de m'arracher les cheveux sur un problème MySQL, je cherche donc des pistes, ou si ce que je cherche à faire n'est pas faisable.
Les données proviennent d'un capteur d'ouverture de porte avec capteur de luminosité, pour détecter l'occupation des WC. Quand la porte est ouvert ou fermée, je récupère l'info + l'info sur la luminosité. Si j'ai une info porte fermée et luminosité<10, je considère que les WC ne sont pas occupés.

Voici un example de ma table :

id wc_id door_open light time

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

L'idée c'est d'avoir au final une suite de door_open à 0 ou 1, on ne peut pas avoir deux 0 ou deux 1 qui se suivent.
Je dois donc garder le premier door_open=0 avec light>10, et le premier door_open=1, peu importe light.

Est-ce que c'est faisable en SQL directement? J'utilise MariaDB 10.3.9

Merci pour vos idées.

10/10/2018, 16h29
CinePhil

Ce que vous présentez est votre table actuelle ?
Que cherchez-vous à obtenir comme résultat ?

Oui, c'est ma table actuelle, une partie en tout cas.

Le résultat devrait ressembler à ça :

id wc_id door_open light time

138
139
141
143
146
148
150
153
155
158
160 0
0
0
0
0
0
0
0
0
0
0 1
0
1
0
1
0
1
0
1
0
1 64
58
68
60
65
63
71
62
68
63
71 2018-10-10 12:28:51
2018-10-10 12:34:00
2018-10-10 12:35:11
2018-10-10 12:37:56
2018-10-10 12:43:53
2018-10-10 13:10:55
2018-10-10 13:12:09
2018-10-10 13:17:18
2018-10-10 13:19:20
2018-10-10 13:26:44
2018-10-10 13:27:40

Salut geraldgg.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`         integer  unsigned  NOT NULL auto_increment primary key,
  `wc_id`      integer  unsigned  NOT NULL,
  `door_open`  boolean            NOT NULL,
  `light`      tinyint  unsigned  NOT NULL,
  `temps`      datetime           NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`id`,`wc_id`,`door_open`,`light`,`temps`) VALUES
  (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')
--------------
 
--------------
select * from `test`
--------------
 
+-----+-------+-----------+-------+---------------------+
| id  | wc_id | door_open | light | temps               |
+-----+-------+-----------+-------+---------------------+
| 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 |
+-----+-------+-----------+-------+---------------------+
--------------
select  id,
        wc_id,
        door_open,
        light,
        temps
  from  (  select      @i:=if (door_open=@p,0,1) as flag,
                       @p:=`door_open` as prec,
                       id,
                       wc_id,
                       door_open,
                       light,
                       temps
 
                 from  test
           cross join  (  SELECT @i:=0,@p:='') as y
               having  flag = 1
        ) as x
--------------
 
+-----+-------+-----------+-------+---------------------+
| id  | wc_id | door_open | light | temps               |
+-----+-------+-----------+-------+---------------------+
| 138 |     0 |         1 |    64 | 2018-10-10 12:28:51 |
| 139 |     0 |         0 |    58 | 2018-10-10 12:34:00 |
| 141 |     0 |         1 |    68 | 2018-10-10 12:35:11 |
| 143 |     0 |         0 |    60 | 2018-10-10 12:37:56 |
| 146 |     0 |         1 |    65 | 2018-10-10 12:43:53 |
| 148 |     0 |         0 |    63 | 2018-10-10 13:10:55 |
| 150 |     0 |         1 |    71 | 2018-10-10 13:12:09 |
| 153 |     0 |         0 |    62 | 2018-10-10 13:17:18 |
| 155 |     0 |         1 |    68 | 2018-10-10 13:19:20 |
| 158 |     0 |         0 |    63 | 2018-10-10 13:26:44 |
| 160 |     0 |         1 |    71 | 2018-10-10 13:27:40 |
+-----+-------+-----------+-------+---------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

1 pièce(s) jointe(s)

Plus simple avec une requête récursive et les fonctions de fenetrage :

Le jeu d'essai

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE TABLE T_EVENT_DOOR
( id         integer    NOT NULL  primary key,
  wc_id      integer    NOT NULL,
  door_open  boolean    NOT NULL,
  light      tinyint    NOT NULL,
  temps      datetime   NOT NULL);
 
INSERT INTO T_EVENT_DOOR
VALUES
  (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');

La requête suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH
T0 AS 
(SELECT *, ROW_NUMBER() OVER(PARTITION BY wc_id ORDER BY temps) AS RN
 FROM   T_EVENT_DOOR),
T1 AS
(SELECT *, 1 AS OK
 FROM   T0 
 WHERE  RN = 1
 UNION  ALL
 SELECT T0.*, CASE WHEN T1.door_open <> T0.door_open THEN 1 ELSE 0 END
 FROM   T1 
        JOIN T0 ON T1.wc_id = T0.wc_id AND T1.RN + 1 = T0.RN)
SELECT *
FROM   T1 
WHERE  OK = 1
ORDER BY wc_id, temps;

Cette requête marche quelque soit le nombre de WC et fonctionne de manière ensembliste, contrairement à ce que vous a donné Artemus24 qui ne marche que pour un unique WC et de plus en itératif (bonjour les performances) et de surcroit avec un code imbitable, qui ne passerait pas dans aucun autre SGBDR digne de ce nom, MySQmerde n'étant pas du tout relationnel !...

A +

Et pour vous former au SQL, lisez mon livre :
Pièce jointe 418866

11/10/2018, 17h21
Artemus24

Salut SQLPRO.

Peut-être bien que votre approche récursive fonctionne correctement.
Mais tout le monde n'a pas la dernière version 8.0 de mysql, là où l'on trouve les fonctions de fenêtrage !

@+
11/10/2018, 17h39
SQLpro

Citation:

Envoyé par Artemus24

Salut SQLPRO.

Peut-être bien que votre approche récursive fonctionne correctement.
Mais tout le monde n'a pas la dernière version 8.0 de mysql, là où l'on trouve les fonctions de fenêtrage !

@+

Oui c'est vrai que MySQL a 19 ans de retard… Pour votre information, les fonctions de fenêtrage sont nées avec la norme SQL de 1999....

Il était temps !

A +

Salut SQLPRO.

Citation:

Envoyé par SQLPRO

Pour votre information, les fonctions de fenêtrage sont nées avec la norme SQL de 1999....

Oui, vous avez raison, mais ce n'est pas pour autant donner une usine à gaz comme solution.

En l'état, votre solution ne fonctionne pas sous MySql car vous avez oublié de préciser "WITH RECURSIVE T0 AS ( SELECT *,".

Et il est inutile de passer par une méthode récursive pour obtenir le résultat attendu :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--------------
select      t1.*
      from  `test` as t1
 
left outer join  `test` as t2
        on  t2.id = (  select  max(t3.id)
                         from  `test` as t3
                        where  t3.id < t1.id
                          and  t3.wc_id = t1.wc_id
                    )
     where  t2.door_open <> t1.door_open
        or  t2.door_open is null
  order by t1.wc_id, t1.temps
--------------
 
+-----+-------+-----------+-------+---------------------+
| id  | wc_id | door_open | light | temps               |
+-----+-------+-----------+-------+---------------------+
| 138 |     0 |         1 |    64 | 2018-10-10 12:28:51 |
| 139 |     0 |         0 |    58 | 2018-10-10 12:34:00 |
| 141 |     0 |         1 |    68 | 2018-10-10 12:35:11 |
| 143 |     0 |         0 |    60 | 2018-10-10 12:37:56 |
| 146 |     0 |         1 |    65 | 2018-10-10 12:43:53 |
| 148 |     0 |         0 |    63 | 2018-10-10 13:10:55 |
| 150 |     0 |         1 |    71 | 2018-10-10 13:12:09 |
| 153 |     0 |         0 |    62 | 2018-10-10 13:17:18 |
| 155 |     0 |         1 |    68 | 2018-10-10 13:19:20 |
| 158 |     0 |         0 |    63 | 2018-10-10 13:26:44 |
| 160 |     0 |         1 |    71 | 2018-10-10 13:27:40 |
+-----+-------+-----------+-------+---------------------+

12/10/2018, 13h45
SQLpro

Citation:

Envoyé par Artemus24

Salut SQLPRO.

Oui, vous avez raison, mais ce n'est pas pour autant donner une usine à gaz comme solution.

En l'état, votre solution ne fonctionne pas sous MySql car vous avez oublié de préciser "WITH RECURSIVE T0 AS ( SELECT *,".
[/code]

Ce mot clef n'est absolument pas obligatoire (norme SQL).

Pour information votre solution est plus complexe en écriture et surtout itérative, ce qui contrevient aux principes mêmes des SGBD Relationnels et plombe les performances !

A +
12/10/2018, 14h45
Artemus24

Salut SQLPRO.

Citation:

Envoyé par SQLPRO

Ce mot clef n'est absolument pas obligatoire (norme SQL).

Sous MySql, si on ne précise pas "recursive", et bien, ça ne fonctionne pas d'une manière récursive.
Je rappelle que ce n'est pas moi qui décide comment mysql doit fonctionner.

Dans la seconde solution, je recherche la rupture de séquence entre la ligne courante et le ligne précédente.
De toute façon, même avec une approche récursive, la méthode est toujours itérative !

@+
14/10/2018, 09h46
SQLpro

Citation:

Envoyé par Artemus24

Dans la seconde solution, je recherche la rupture de séquence entre la ligne courante et le ligne précédente.
De toute façon, même avec une approche récursive, la méthode est toujours itérative !

@+

Non, car si la récursivité est bien itérative, il est possible de faire le cheminement pour plusieurs branches dont la valeur de racine diffère (wc_id) ! C'est le comportement ensembliste naturel aux SGBDR, dont d'ailleurs MySQL est peu pourvu !

A +

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