Discussion :

[king72]

Bonjour à tous,

Après avoir passé pas mal de temps à chercher, je ne réussis pas à obtenir le résultat souhaité pour ma requête Mysql, je viens donc solliciter votre aide svp

Avant toute chose je vous informe que je n'ai accès à la base concernée qu'en Lecture, et que je cherche à exporter (via un SELECT) uniquement les données dont j'ai besoin afin d'optimiser ma requête, car actuellement j'ai réussi à obtenir ce que je voulais sous PowerBI mais après avoir importé une grande partie de ma table (plus de 3 millions de lignes), le temps de traitement est faramineux... alors qu'en réalité je n'ai besoin que d'environ 10000 lignes sur la table mais je ne réussis pas à les cibler...

Je vous explique. Voici ma table en mode très simplifié :

Ligne	Increment	ID	Objet
Ligne 1	345678	ID131	table
Ligne 2	347681	ID131	table
Ligne 3	347682	ID131	chaise
Ligne 4	347683	ID131	chaise
Ligne 5	347684	ID131	chaise
Ligne 6	342631	ID198	bureau
Ligne 7	347629	ID227	canape
Ligne 8	350112	ID299	chaise
Ligne 9	350113	ID299	table
Ligne 10	350119	ID299	chaise

Mon souhait :
Après avoir trié ma table par ID puis par Increment (pas de problème pour cette partie), je souhaite pouvoir récupérer toutes les lignes dont la valeur de la colonne Objet est (chaise OU table) ET dont la ligne précédente avait une valeur (chaise OU table) différente de la précédente (toujours parmi chaise OU table). Je ne veux pas des lignes ou l'objet de la ligne précédente (meme si c'est CHAISE ou TABLE) est identique à la ligne en cours.
Donc pour ma table en exemple, il faudrait que cela me retourne uniquement les Lignes 2, 3, 8, 9 et 10

J'avais cru trouvé une solution avec les commandes "LEAD" et "LAG" mais je n'ai pas réussi à les faire foncionner

Merci d'avance pour votre aide, j'espère avoir été clair, n'hésitez pas si ce n'est pas le cas

[vanagreg]

Bonjour,

En effet il faut utiliser la fonction analytique LAG :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
with v as (select ligne, 
             increment,
             id,
             objet,
             case when lag(objet) over (order by id, increment) = objet then 0 else 1 end flag
      from t
 
     )
select * from v    
where objet in ('chaise', 'table') and flag = 1
order by id, increment;

Par contre ce ne sont pas plutôt les lignes 1, 3, 8, 9, 10 ?

[king72]

Bonjour, et merci pour ta réponse très rapide

Ta requête me génère malheureusement cette erreur (je n'ai pas précisé, peut être que ça vient de là, mais la version de Mysql installée sur le serveur est très ancienne (5.6)

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'v as (select ligne, increment, id, objet, c' at line 1

Quant au résultat attendu, non il s'agit bien de 2, 3, 8, 9 et 10 car si je prends la ligne 1 et 2, l'objet de la ligne 1 n'est pas différent de l'objet de la ligne 2, or je ne veux retourner que les lignes dont les objets de la ligne précédente, pour un même ID, (après tri) sont différents. En l'occurence la ligne 3 (chaise) est différente de la ligne 2 (table), je souhaite donc retourner les lignes 2 et 3

[vanagreg]

Ok, en fait il faut vérifier la ligne précédente et la ligne suivante. Concernant l'erreur, oui j'ai cru comprendre que MySQL ne gérait les fonctions analytiques qu'à partir de la version 8. Il va donc falloir probablement faire une auto jointure pour s'en sortir. Je laisse les experts MySQL répondre car ils ont certainement plus l'habitude que moi de bricoler des requêtes pour surmonter les limites de MySQL.

[CinePhil]

Bonjour,

1ère étape :

je souhaite pouvoir récupérer toutes les lignes dont la valeur de la colonne Objet est (chaise OU table)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Increment, ID, Objet
FROM la_table
WHERE Objet IN ('chaise', 'table')

Ensuite, le résultat que vous attendez ne semble pas correspondre à ce que vous demandez !

dont la ligne précédente avait une valeur (chaise OU table) différente de la précédente (toujours parmi chaise OU table)

Donc pour ma table en exemple, il faudrait que cela me retourne uniquement les Lignes 2, 3, 8, 9 et 10

La ligne 2 a pour valeur table et la ligne précédente a pour valeur table donc elle ne devrait pas être retrounée.
Pour la ligne 3 => OK.
Ligne 8 a pour valeur chaise mais la précédente ligne répondant à la première condition donnée par la requête ci-dessus était aussi une chaise ! => KO
Pour les lignes 9 et 10 => OK.

Il va donc falloir que vous exprimiez mieux votre besoin !

[king72]

Bonjour et merci pour vos réponses,

Oui en fait vanagreg a bien compris le besoin, le SELECT doit bien "analyser" si la valeur de l'objet a changé entre deux lignes (si l'ID est le même), mais il doit retourner les deux lignes (la ligne "source", donc la 2, et la ligne "destination", donc la 3)
J'ai voulu simplifier à l'extrême pour mon exemple mais c'était peut être pas une bonne idée

Pour que vous compreniez bien, sans rentrer trop dans le détail, je travaille en fait sur les réaffectations entre équipes des tickets support ouverts par les utilisateur
J'ai donc besoin d'extraire de ma base chaque réaffectation de ticket, en gardant comme information le groupe source (dans mon exemple, ligne 2, la table), et le groupe destination (ligne 3, la chaise)

C'est du coup la même raison pour les lignes 8 9 et 10, sauf que dans ce cas la, la ligne 8 est la "source", la ligne 9 est la "destination", puis à nouveau, la ligne 9 est la "source" et la ligne 10 est la "destination".
J'espère avoir été plus clair, désolé pour le manque de précisions initial

En fait en l'écrivant, je me dis qu'une autre solution, peut être plus simple (mais je ne sais pas si c'est possible), serait de générer une nouvelle colonne (uniquement dans le SELECT, je n'ai pas les droits en écriture je rappelle ), avec pour chaque ligne l'information de l'objet de la ligne suivante (que ce soit chaise, table ou autre chose, mais uniquement lorsqu'il s'agit du même ID)

Ca donnerait :

Ligne	Increment	ID	Objet	LigneSuivante
Ligne 1	345678	ID131	table	table
Ligne 2	347681	ID131	table	chaise
Ligne 3	347682	ID131	chaise	chaise
Ligne 4	347683	ID131	chaise	chaise
Ligne 5	347684	ID131	chaise	#N/A
Ligne 6	342631	ID198	bureau	#N/A
Ligne 7	347629	ID227	canape	#N/A
Ligne 8	350112	ID299	chaise	table
Ligne 9	350113	ID299	table	chaise
Ligne 10	350119	ID299	chaise	#N/A

Les #N/A correspondent aux lignes précédentes ou suivantes dont l'ID précédent ou suivant n'est pas le même que la ligne concernée

pensez-vous que ce soit plus jouable comme ça ?
Avec un tel résultat je pourrais ensuite très facilement extraire uniquement ce dont j'ai besoin . Ce serait même encore plus optimisé puisque je n'aurais plus besoin d'avoir la ligne précédente dans mon extract, uniquement les lignes ou "objet" et "lignesuivante" sont différentes

[manzeki]

--
-- Pour un ID donné, on identifie chaque paire de lignes concernée par une réaffectation.
-- ID 131 : paire (2,3)
-- ID 299 : paire (8,9) et paire (9,10)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
DROP TABLE IF EXISTS meubles;
CREATE TABLE meubles (
	id integer auto_increment  primary key,
	score integer,
	meuble_id smallint,
	meuble varchar(50)
);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
INSERT INTO meubles (score, meuble_id, meuble) VALUES 
(345678, 131, 'table'),
(347681, 131, 'table'),
(347682, 131, 'chaise'),
(347683, 131, 'chaise'),
(347684, 131, 'chaise'),
(342631, 198, 'bureau'),
(347629, 227, 'canape'),
(350112, 299, 'chaise'),
(350113, 299, 'table'),
(350119, 299, 'chaise');

Pour chaque ligne d'un même ID
(1) On vérifie si la précédente est différente
(2) Ou que la suivante est différente

Toute ligne qui respecte l'une des deux conditions fait partie d'une réaffection

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
 
SELECT id, meuble_id, meuble, ok
FROM (
	SELECT id, meuble_id, meuble, meuble != (
						SELECT meuble 
                        FROM meubles 
                        WHERE id < a.id AND meuble in ('chaise', 'table') AND meuble_id = a.meuble_id 
                        ORDER BY id DESC
                        LIMIT 1) as ok
	FROM meubles a
	UNION
	SELECT id, meuble_id, meuble, meuble != (
						SELECT meuble
						FROM meubles
						WHERE a.id < id AND meuble in ('chaise', 'table') AND meuble_id = a.meuble_id
						ORDER BY id ASC
						LIMIT 1) as ok
	FROM meubles a
) a
WHERE ok
ORDER BY id ASC;

[king72]

Bonjour Manzeki,

Désolé pour le temps de réponse, j'étais en formation depuis et n'ai pas pu tester ta proposition avant aujourd'hui.

Alors déjà, merci beaucoup, cela répond pratiquement à mon besoin, j'ai néanmoins constaté en la testant que des lignes qui ne devraient pas apparaître ressortent quand même

Pour reprendre avec ton code, voici ce que ça donne (j'ai alimenté les données avec plus de lignes pour que tu puisses visualiser le problème):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
DROP TABLE IF EXISTS meubles;
CREATE TABLE meubles (
	id integer auto_increment  primary key,
	score integer,
	meuble_id smallint,
	meuble varchar(50)
);
 
INSERT INTO meubles (score, meuble_id, meuble) VALUES 
(345678, 131, 'table'),
(347681, 131, 'table'),
(347682, 131, 'chaise'),
(347683, 131, 'chaise'),
(347684, 131, 'chaise'),
(342631, 198, 'bureau'),
(347629, 227, 'canape'),
(350112, 299, 'chaise'),
(350113, 299, 'table'),
(350119, 299, 'chaise'),
(25013707, 714, 'bureau'),
(25013708, 714, 'table'),
(25013709, 714, 'table'),
(25013710, 714, 'table'),
(25013711, 714, 'table'),
(25052865, 714, 'bureau'),
(25052866, 714, 'bureau'),
(25389845, 714, 'bureau'),
(25389846, 714, 'bureau'),
(25389847, 714, 'bureau'),
(25389848, 714, 'table'),
(25389849, 714, 'table'),
(25389850, 714, 'table'),
(25389851, 714, 'table'),
(25920241, 714, 'table');

Avec ce jeu de données, par rapport à mon besoin, je ne devrais donc avoir aucun résultat sur mon meuble_id 714 puisqu'à a aucun moment je n'ai un passage de "table" à "chaise" ou de "chaise" à "table" sur la ligne précédente ou suivante.

Or ta requête me retourne ce résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
id	meuble_id	meuble	ok
2	131	table	1
3	131	chaise	1
8	299	chaise	1
9	299	table	1
10	299	chaise	1
11	714	bureau	1
16	714	bureau	1
17	714	bureau	1
18	714	bureau	1
19	714	bureau	1
20	714	bureau	1

Même si j'ai compris comment fonctionnait ta requête, je t'avoue n'avoir aucune idée de comment corriger ça ^^, donc si tu peux encore me donner un coup de main stp ce serait super

Merci beaucoup

[manzeki]

J'ai ajouté cette condition pour restreindre les résultats

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 WHERE a.meuble in ('chaise', 'table')

--
--

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT id, meuble_id, meuble, ok
FROM (
	SELECT id, meuble_id, meuble, meuble != (
						SELECT meuble 
                        FROM meubles
                        WHERE id < a.id AND meuble in ('chaise', 'table') AND meuble_id = a.meuble_id 
                        ORDER BY id DESC
                        LIMIT 1) as ok
	FROM meubles a
    WHERE a.meuble in ('chaise', 'table')
	UNION
	SELECT id, meuble_id, meuble, meuble != (
						SELECT meuble
						FROM meubles
						WHERE a.id < id AND meuble in ('chaise', 'table') AND meuble_id = a.meuble_id
						ORDER BY id ASC
						LIMIT 1) as ok
	FROM meubles a
    WHERE a.meuble in ('chaise', 'table')
) a
WHERE ok
ORDER BY id ASC;

--
--

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
SELECT id, meuble_id, meuble
FROM (
	SELECT id, meuble_id, meuble
	FROM meubles a
	WHERE a.meuble in ('chaise', 'table') AND meuble != (
						SELECT meuble 
						FROM meubles
						WHERE id < a.id AND meuble in ('chaise', 'table') AND meuble_id = a.meuble_id 
						ORDER BY id DESC
						LIMIT 1)
	UNION
	SELECT id, meuble_id, meuble
	FROM meubles a
	WHERE a.meuble in ('chaise', 'table') AND meuble != (
						SELECT meuble
						FROM meubles
						WHERE a.id < id AND meuble in ('chaise', 'table') AND meuble_id = a.meuble_id
						ORDER BY id ASC
						LIMIT 1)
) a
ORDER BY id ASC;

[Artemus24]

Salut à tous.

En premier lieu, un jeu d'essai traitant tous les cas de figures aurait été le bienvenue !

Nous ne savons pas si votre table est l'exacte reflet de votre demande.
Par exemple, est-ce que la colonne "ligne" existe réellement ? J'en doute !

De même, nous ne savons pas quel est le critère qui produit l'ordre des lignes dans votre exemple.
En gros, seul la colonne "objet" est pertinente.
Je rajoute une colonne clef qui va me seervir pour le tri de la table.
C'est sur ce tri que je vais faire la requête de sélection.

Les explications du résultat à produire ne sont pas très clairs, non plus.
Cela fait beaucoup d'inconnu pour un tel exercice.

Pour finir, un SGBDR n'est pas fait pour traité séquentiellement les lignes. Bonjour les performances !

D'après ce que j'ai pu comprendre, vous travaillez avec la version mysql 5.6.
Pas question de vous donner une solution avec les CTE puisque cela ne fonctionne qu'avec la version mysql 8.0.

Vous désirez comparer la ligne précédente avec la ligne suivante afin de rechercher les ruptures de séquence sur la colonne "objet".
Si ce critère fonctionne alors vous désirez obtenir la ligne précédente et suivante si et seulement si la colonne "objet" contient "table" ou "chaise".

Voici ce que je vous propose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
(  `clef`   integer unsigned NOT NULL Primary key,
   `objet`  varchar(255)     NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `test` (`clef`,`objet`) VALUES
  (65, 'bureau'),(77, 'canape'),(39, 'chaise'),(41, 'chaise'),(53, 'chaise'),(82, 'chaise'),(104, 'chaise'),(16, 'table'),(28, 'table'),(90, 'table')
--------------
 
--------------
select * from `test`
--------------
 
+------+--------+
| clef | objet  |
+------+--------+
|   16 | table  |
|   28 | table  |
|   39 | chaise |
|   41 | chaise |
|   53 | chaise |
|   65 | bureau |
|   77 | canape |
|   82 | chaise |
|   90 | table  |
|  104 | chaise |
+------+--------+
--------------
drop table `temp`
--------------
 
ERROR 1051 (42S02) at line 45: Table 'base.temp' inconnue
--------------
create table `temp` as
  select           t2.ligne,
                   t2.flag,
                   t2.cour_clef,
                   t2.cour_objet,
                   case when t3.clef  is NULL then '' else t3.clef  end as prec_clef,
                   case when t3.objet is NULL then '' else t3.objet end as prec_objet
 
             from  (  select  t1.flag,
                              t1.lien,
                              t1.ligne,
                              t1.cour_objet,
                              t1.cour_clef
 
                        from  (  SELECT  @i:= if (objet<>@p and @p<>'' and (objet='table' or objet='chaise'),clef, '') as flag,
                                         @j:= if (objet<>@p and @p<>'' and (objet='table' or objet='chaise'),@q,   '') as lien,
                                         @k:=@k+1        as ligne,
                                         @p:=objet       as cour_objet,
                                         @q:=clef        as cour_clef
                                   FROM  (  SELECT  *
                                              FROM  `test`
                                          ORDER BY  clef
                                         ) as x
                             CROSS JOIN  (  SELECT @i:='',@j:='',@k:=0,@p:=''  ) as y
                              ) as t1
                 ) as t2
 
LEFT OUTER JOIN  `test` as t3
             on  t3.clef = t2.lien
            and  t3.objet in ('table','chaise')
--------------
 
--------------
select * from `temp`
--------------
 
+-------+------+-----------+------------+-----------+------------+
| ligne | flag | cour_clef | cour_objet | prec_clef | prec_objet |
+-------+------+-----------+------------+-----------+------------+
|     1 |      |        16 | table      |           |            |
|     2 |      |        28 | table      |           |            |
|     3 | 39   |        39 | chaise     | 28        | table      |
|     4 |      |        41 | chaise     |           |            |
|     5 |      |        53 | chaise     |           |            |
|     6 |      |        65 | bureau     |           |            |
|     7 |      |        77 | canape     |           |            |
|     8 | 82   |        82 | chaise     |           |            |
|     9 | 90   |        90 | table      | 82        | chaise     |
|    10 | 104  |       104 | chaise     | 90        | table      |
+-------+------+-----------+------------+-----------+------------+
--------------
select  *
  from  `test` as t1
 
where exists (  select 1
                  from  `temp` as t2
                 where  (t2.flag      = t1.clef)
                    or  (t2.prec_clef = t1.clef)
             )
--------------
 
+------+--------+
| clef | objet  |
+------+--------+
|   28 | table  |
|   39 | chaise |
|   82 | chaise |
|   90 | table  |
|  104 | chaise |
+------+--------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Flag indique si la ligne courante est sélectionnée ou pas.
La colonne "prec_clef" indique si la ligne précédente est sélectionnée ou pas.

La sélection donne les lignes 2, 3, 8, 9 et 10.

@+