Besoin d'une requête récursive?

**JGLord** · 12/11/2013, 18h27

Bonjour à tous,

Je cherche un moyen de faire des requêtes Oracle récursives ou toute autre solution qui me permettrait d'accomplir la tâche suivante.

En gros, j'ai une énorme séquence d'information constituée de données invalides que j'aimerais omettre de mon résultat. Ma "vraie" séquence est constituée de plusieurs milliers d'informations.

À titre d'exemple, prenons la séquence réduite suivante: A7-X1-F6-X2-X3-C0-B1

Cette séquence pourrait être modélisée dans ma base de données comme suit:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
WITH MaSequence AS
(
SELECT 'A7' AS CLE, 'X1' AS SUIVEUR, null AS PREDECESSEUR FROM dual union ALL
SELECT 'X1'       , 'F6'           , 'A7'                 FROM dual union ALL
SELECT 'F6'       , 'X2'           , 'X1'                 FROM dual union ALL
SELECT 'X2'       , 'X3'           , 'F6'                 FROM dual union ALL
SELECT 'X3'       , 'C0'           , 'X2'                 FROM dual union ALL
SELECT 'C0'       , 'B1'           , 'X3'                 FROM dual union ALL
SELECT 'B1'       , null           , 'C0'                 FROM dual
)

La colonne "CLE" est le nom de la clé courante.
La colonne "SUIVEUR" est le nom de la clé suivant la clé courante.
La colonne "PREDECESSEUR" est le nom de la clé précédant la clé courante.

Dans ce modèle de données on défini comme "invalide" une clé débutant par la lettre "X".

Note IMPORTANTE: Il ne faut absolument pas se fier à l'ordre des informations dans la table. Bref, même si dans mon exemple on peut croire que la colonne "CLE" est triée pour respecter une séquence, ce n'est en réalité absolument pas le cas.

Donc, j'aimerais une façon d'omettre (sauter par dessus) les données invalides de façon à avoir le résultat suivant:

CLE,SUIVEUR,PRÉDÉCESSEUR
A7,F6,null
F6,C0,A7
C0,B1,F6
B1,null,C0

**Waldar** · 12/11/2013, 19h20

Vous pouvez faire ainsi :

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
WITH MaSequence (clef) AS
(
SELECT 'A7' FROM dual union ALL
SELECT 'X1' FROM dual union ALL
SELECT 'F6' FROM dual union ALL
SELECT 'X2' FROM dual union ALL
SELECT 'X3' FROM dual union ALL
SELECT 'C0' FROM dual union ALL
SELECT 'B1' FROM dual
)
select clef
     , lead(clef) over(order by rownum asc) as suiveur
     , lag (clef) over(order by rownum asc) as predecesseur
  from MaSequence
 where clef not like 'X%';
 
CLEF SUIVEUR PREDECESSEUR
---- ------- ------------
A7   F6       -
F6   C0      A7
C0   B1      F6
B1    -      C0

**JGLord** · 12/11/2013, 20h33

@Waldar:
Malheureusement, ça ne peut pas fonctionner de cette façon, car selon ce que j'avais mentionné, il ne faut absolument pas se fier à l'ordre des informations dans ma table (mes "clefs" ne sont jamais triées en ordre de connexité, même si ma table "MaSequence" laisse présager le contraire).

Bref, j'aimerais avoir le même résultat, même si les données de ma table était les suivantes:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
(
SELECT 'A7' AS CLE, 'X1' AS SUIVEUR, NULL AS PREDECESSEUR FROM dual union ALL
SELECT 'C0'       , 'B1'           , 'X3'                 FROM dual union ALL
SELECT 'F6'       , 'X2'           , 'X1'                 FROM dual union ALL
SELECT 'X2'       , 'X3'           , 'F6'                 FROM dual union ALL
SELECT 'X3'       , 'C0'           , 'X2'                 FROM dual union ALL
SELECT 'X1'       , 'F6'           , 'A7'                 FROM dual union ALL
SELECT 'B1'       , NULL           , 'C0'                 FROM dual
)

doit aussi donner

CLE,SUIVEUR,PRÉDÉCESSEUR
A7,F6,null
F6,C0,A7
C0,B1,F6
B1,null,C0

Pour y arriver, je ne vois pas d'autre moyen que d'utiliser une logique du genre:

-Pour la clef courante, vérifier son suiveur (dans la colonne correspondante)

-Prendre ce suiveur si différent de "X%", sinon

-Vérifier le suiveur du suiveur de la clef courante et prendre ce suiveur si différent de "X%", sinon

-Vérifier le suiveur du suiveur du suiveur... etc

Même logique pour les prédécesseurs.

En espérant que ce soit plus clair.

**easy-bi** · 12/11/2013, 21h54

Hello,

Perso j'utiliserai la fonction START WITH / CONNECT BY

Exemple:

Code :

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

1
2
3
4
select level, CLE, PREDECESSEUR, SUIVEUR 
from testtable2 
start with PREDECESSEUR is null 
connect by prior SUIVEUR = PREDECESSEUR;

En prime le champ level qui t'indique la "profondeur" de connexion

**Rei Ichido** · 13/11/2013, 08h43

Il faut faire une combinaison des propositions de easy-bi et de Waldar :

1. Une étape de requête récursive qui "trie" les clés avec leur niveau.
2. Puis utiliser lead et lag selon cet ordre.

**JGLord** · 13/11/2013, 15h41

Génial! En combinant effectivement les deux approches, j'arrive au résultat désiré. Merci!!

Par contre, lorsque j'applique votre solution sur mes vraies données, j'ai remarqué un petit problème que je peux reproduire avec la requête suivante:

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
 
WITH MaSequence AS
(
  SELECT 'A7' AS CLE, 'X1' AS SUIVEUR, NULL AS PREDECESSEUR FROM dual union ALL
  SELECT 'C0'       , 'B1'           , 'X3'                 FROM dual union ALL
  SELECT 'F6'       , 'X2'           , 'X1'                 FROM dual union ALL
  SELECT 'X2'       , 'X3'           , 'F6'                 FROM dual union ALL
  SELECT 'X3'       , 'C0'           , 'X2'                 FROM dual union ALL
  SELECT 'X1'       , 'F6'           , 'A7'                 FROM dual union ALL
  SELECT 'X1'       , 'G6'           , 'A7'                 FROM dual union ALL
  SELECT 'G6'       , NULL           , 'X1'                 FROM dual union ALL
  SELECT 'B1'       , NULL           , 'C0'                 FROM dual
)
, OrderedSequence AS
(
SELECT level, CLE, PREDECESSEUR, SUIVEUR 
FROM MaSequence 
  start WITH PREDECESSEUR IS NULL 
  connect BY prior SUIVEUR = CLE
)
SELECT cle
     , lead(cle) over(ORDER BY rownum ASC) AS suiveur
     , lag (cle) over(ORDER BY rownum ASC) AS predecesseur
FROM OrderedSequence
WHERE cle NOT LIKE 'X%'
;

En gros, il y a un problème lorsque j'ai une clé qui a deux suiveurs (ex. "X1" a le suiveur "F6" et "G6"). Dans mon exemple le "connect by" semble se faire sur les champs "null" et me donne le résultat suivant:

CLE SUIVEUR PREDECESSEUR
--- ------- ------------
A7  F6                   
F6  C0      A7           
C0  B1      F6           
B1  G6      C0           
G6          B1

alors que j'aimerais que le résultat soit:

CLE SUIVEUR PREDECESSEUR
--- ------- ------------
A7  F6                   
F6  C0      A7           
C0  B1      F6           
G6          B1

C'est surement très faicle, mais je ne connais absolument pas la fonction "connect by"...

**Rei Ichido** · 19/11/2013, 11h13

C'est étrange, vous devriez plutôt vouloir :

Code :

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

1
2
3
4
5
6
7
8
 
CLE SUIVEUR PREDECESSEUR
--- ------- ------------
A7  F6                   
F6  C0      A7           
C0  B1      F6           
B1          C0
G6        A7

Si vous avez plusieurs branches (1 élément peut avoir plusieurs suiveurs) mais uniquement dans ce sens là (1 élément ne peut pas avoir plusieurs suiveurs), alors il faut commencer la récursion par les derniers éléments, garder en stock la racine connect_by_root faire la récursion en remontant, et le lead / lag sur (partition by racine order by level).

**islamov2000** · 20/11/2013, 10h55

Envoyé par Waldar

Vous pouvez faire ainsi :

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
WITH MaSequence (clef) AS
(
SELECT 'A7' FROM dual union ALL
SELECT 'X1' FROM dual union ALL
SELECT 'F6' FROM dual union ALL
SELECT 'X2' FROM dual union ALL
SELECT 'X3' FROM dual union ALL
SELECT 'C0' FROM dual union ALL
SELECT 'B1' FROM dual
)
select clef
     , lead(clef) over(order by rownum asc) as suiveur
     , lag (clef) over(order by rownum asc) as predecesseur
  from MaSequence
 where clef not like 'X%';
 
CLEF SUIVEUR PREDECESSEUR
---- ------- ------------
A7   F6       -
F6   C0      A7
C0   B1      F6
B1    -      C0

A partir de quel version on trouve lead et lag

**Waldar** · 20/11/2013, 12h23

Envoyé par islamov2000

A partir de quel version on trouve lead et lag

Les fonctions analytiques (dont lead / lag) ont été introduites en 8i Enterprise Edition, mais démocratisées à partir de la 9i quand elles ont été utilisables par le plus grand nombre.

**islamov2000** · 20/11/2013, 15h52

Waldar
lead et lag, c'est pour dire successeur, prédécesseur de l'enregistrement en cours.

**JGLord** · 04/02/2014, 17h25

Merci à tous pour votre aide!

Malheureusement, le noeud du problème reste entier, car mes données dans ma table "MaSequence" ne sont pas triées et j'ai souvent des clés qui ont plusieurs suiveurs.

Je vais ouvrir une autre discussion avec la même problématique, mais cette fois-ci, je vais demander de l'aide en PL/SQL. Selon les messages personnels que j'ai reçus, ça serait plus facile en PL/SQL, mais je ne connais pas ce langage...

Voilà le lien: http://www.developpez.net/forums/d14...ive-omissions/

À suivre!

**JGLord** · 04/02/2014, 17h34

En plus, je viens de voir que j'avais une erreur dans mon résultat désiré...

On aurait du lire ceci:

CLE SUIVEUR PREDECESSEUR
--- ------- ------------
A7  F6                   
A7  G6
F6  C0      A7           
C0  B1      F6           
B1          C0
G6          A7

Au moins, cette erreur ne change pas le concept.

**aieeeuuuuu** · 04/02/2014, 19h02

Bonjour,

Que donne ceci :

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
WITH MaSequence AS
(
  SELECT 'A7' AS CLE, 'X1' AS SUIVEUR, NULL AS PREDECESSEUR FROM dual union ALL
  SELECT 'C0'       , 'B1'           , 'X3'                 FROM dual union ALL
  SELECT 'F6'       , 'X2'           , 'X1'                 FROM dual union ALL
  SELECT 'X2'       , 'X3'           , 'F6'                 FROM dual union ALL
  SELECT 'X3'       , 'C0'           , 'X2'                 FROM dual union ALL
  SELECT 'X1'       , 'F6'           , 'A7'                 FROM dual union ALL
  SELECT 'X1'       , 'G6'           , 'A7'                 FROM dual union ALL
  SELECT 'G6'       , NULL           , 'X1'                 FROM dual union ALL
  SELECT 'B1'       , NULL           , 'C0'                 FROM dual
),
cte (CLE, SUIVEUR)
 AS (
	SELECT CLE, SUIVEUR
	FROM MaSequence
	WHERE PREDECESSEUR IS NULL
	UNION ALL
	SELECT CASE WHEN S.CLE LIKE 'X%' THEN P.CLE ELSE S.CLE END , S.SUIVEUR
	FROM cte P
	INNER JOIN MaSequence S
		ON S.CLE=P.SUIVEUR
)
SELECT X.CLE, X.SUIVEUR, P.CLE AS PREDECESSEUR
FROM cte X
LEFT JOIN cte P
	ON P.SUIVEUR = X.CLE
WHERE X.CLE NOT LIKE 'X%'
AND (X.SUIVEUR NOT LIKE 'X%' OR X.SUIVEUR IS NULL);

**Waldar** · 04/02/2014, 19h42

Bravo aieeeuuuuu, c'est parfait !
Après corrections de petites erreurs de syntaxe et de copier / coller.

J'ai testé avec les données de l'autre sujet, et c'est correct aussi :

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
WITH MaSequence AS
(
SELECT 'ID1' AS CLE, 'INVALID1' AS SUIVEUR, NULL AS PREDECESSEUR FROM dual union ALL
SELECT 'INVALID1'  , 'ID2'              , 'ID1'          FROM dual union ALL
SELECT 'INVALID1'  , 'ID3'              , 'ID1'          FROM dual union ALL
SELECT 'ID2'       , 'INVALID2'         , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'ID4'              , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'INVALID3'         , 'INVALID1'     FROM dual union ALL
SELECT 'INVALID2'  , NULL               , 'ID2'          FROM dual union ALL
SELECT 'ID4'       , NULL               , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'ID5'              , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'INVALID4'         , 'ID3'          FROM dual union ALL
SELECT 'ID5'       , 'ID6'              , 'INVALID3'     FROM dual union ALL
SELECT 'INVALID4'  , 'ID7'              , 'INVALID3'     FROM dual union ALL
SELECT 'ID6'       , NULL               , 'ID5'          FROM dual union ALL
SELECT 'ID7'       , 'ID8'              , 'INVALID4'     FROM dual union ALL
SELECT 'ID8'       , NULL               , 'ID7'          FROM dual
),
cte (CLE, SUIVEUR)
 AS (
	SELECT CLE, SUIVEUR
	FROM MaSequence
	WHERE PREDECESSEUR IS NULL
	UNION ALL
	SELECT CASE WHEN S.CLE LIKE 'INVALID%' THEN P.CLE ELSE S.CLE END , S.SUIVEUR
	FROM cte P
	INNER JOIN MaSequence S
		ON S.CLE=P.SUIVEUR
)
SELECT X.CLE, X.SUIVEUR, P.CLE AS PREDECESSEUR
FROM cte X
LEFT JOIN cte P
	ON P.SUIVEUR = X.CLE
WHERE X.CLE NOT LIKE 'INVALID%'
AND (X.SUIVEUR NOT LIKE 'INVALID%' OR X.SUIVEUR IS NULL);
 
CLE SUIVEUR PREDECESSEUR  
--- ------- ------------ 
ID2  -      ID1
ID3 ID7     ID1
ID3 ID5     ID1
ID3 ID4     ID1
ID4  -      ID3
ID5 ID6     ID3
ID6  -      ID5
ID7 ID8     ID3
ID8  -      ID7
ID1 ID3      -
ID1 ID2      -

**JGLord** · 08/02/2014, 03h42

aieeeuuuuu, votre solution me laisse sans mots!

C'est exactement la résultante que je voulais. Malheureusement, toutes mes tentatives étaient restées vaines ou étaient simplement beaucoup plus compliquées (en nombre de ligne de code et en coût Oracle)!

**JGLord** · 08/02/2014, 18h10

Même après plusieurs minutes à analyser la requête de aieeeuuuuu, je vois bien que le résultat final est bon, mais je suis incapable de "vulgariser" la démarche. Quelqu'un pourrait-il décrire le cheminement de pensée (raisonnement) pour arriver à bâtir cette requête (surtout la partie dans la clause WITH et plus spécifiquement le 2e SELECT)? D'ailleurs, je ne savais même pas qu'on pouvait utiliser l'alias d'un WITH dans sa propre définition...

Aussi, je ne comprends pas pourquoi cette partie de code:

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
WITH MaSequence AS
(
SELECT 'ID1' AS CLE, 'INVALID1' AS SUIVEUR, NULL AS PREDECESSEUR FROM dual union ALL
SELECT 'INVALID1'  , 'ID2'              , 'ID1'          FROM dual union ALL
SELECT 'INVALID1'  , 'ID3'              , 'ID1'          FROM dual union ALL
SELECT 'ID2'       , 'INVALID2'         , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'ID4'              , 'INVALID1'     FROM dual union ALL
SELECT 'ID3'       , 'INVALID3'         , 'INVALID1'     FROM dual union ALL
SELECT 'INVALID2'  , NULL               , 'ID2'          FROM dual union ALL
SELECT 'ID4'       , NULL               , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'ID5'              , 'ID3'          FROM dual union ALL
SELECT 'INVALID3'  , 'INVALID4'         , 'ID3'          FROM dual union ALL
SELECT 'ID5'       , 'ID6'              , 'INVALID3'     FROM dual union ALL
SELECT 'INVALID4'  , 'ID7'              , 'INVALID3'     FROM dual union ALL
SELECT 'ID6'       , NULL               , 'ID5'          FROM dual union ALL
SELECT 'ID7'       , 'ID8'              , 'INVALID4'     FROM dual union ALL
SELECT 'ID8'       , NULL               , 'ID7'          FROM dual
),
cte (CLE, SUIVEUR)
 AS (
	SELECT CLE, SUIVEUR
	FROM MaSequence
	WHERE PREDECESSEUR IS NULL
	UNION ALL
	SELECT CASE WHEN S.CLE LIKE 'INVALID%' THEN P.CLE ELSE S.CLE END , S.SUIVEUR
	FROM cte P
	INNER JOIN MaSequence S
		ON S.CLE=P.SUIVEUR
)
SELECT * FROM cte;

me donne le résultat:

CLE SUIVEUR
--- -------
ID1 INVALID1
ID1 ID2
ID1 ID3
ID2 INVALID2
ID3 ID4
ID3 INVALID3
ID2 (null)
ID4 (null)
ID3 ID5
ID3 INVALID4
ID5 ID6
ID3 ID7		pourquoi pas (INVALID3 ID7)?
ID6 (null)
ID7 ID8
ID8 (null)

d'où vient le row "ID3, ID7"?

**punkoff** · 10/02/2014, 10h23

bonjour,

lisez ceci : http://sqlpro.developpez.com/cours/s...te-recursives/

Besoin d'une requête récursive? [11gR2]

SQL Oracle

Discussions similaires

Partager

Partager