Besoin d'une requête récursive?

Version imprimable

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:

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

Vous pouvez faire ainsi :

Code:

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

@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:

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. ;)

12/11/2013, 20h54
easy-bi

START WITH / CONNECT BY
Hello,

Perso j'utiliserai la fonction START WITH / CONNECT BY

Exemple:
Code:

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 ;)
13/11/2013, 07h43
Rei Ichido

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.

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

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:

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"...

19/11/2013, 10h13
Rei Ichido
C'est étrange, vous devriez plutôt vouloir :
Code:

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).

Arborescence

Citation:

Envoyé par Waldar

Vous pouvez faire ainsi :

Code:

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

:ccool:
A partir de quel version on trouve lead et lag

20/11/2013, 11h23
Waldar

Citation:

Envoyé par islamov2000

:ccool:
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.
20/11/2013, 14h52
islamov2000

successeur, prédécesseur de l'enregistrement

:merci: Waldar
lead et lag, c'est pour dire successeur, prédécesseur de l'enregistrement en cours.
04/02/2014, 16h25
JGLord

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!
04/02/2014, 16h34
JGLord
En plus, je viens de voir que j'avais une erreur dans mon résultat désiré... :roll:

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.

Bonjour,

Que donne ceci :

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
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);

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:

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      -

:ccool:

08/02/2014, 02h42
JGLord

8O

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)!

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:

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"?

10/02/2014, 09h23
punkoff

bonjour,

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