Discussion :

[JGLord]

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]

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]

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

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]

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]

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

[islamov2000]

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]

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]

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

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