IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PL/SQL Oracle Discussion :

Requête récursive avec omissions


Sujet :

PL/SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Mai 2008
    Messages
    30
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2008
    Messages : 30
    Par défaut Requête récursive avec omissions
    Bonjour à vous experts PL/SQL!

    À la suite d'une demande d'aide concernant une requête SQL récursive, on m'a conseillé d'utiliser le PL/SQL pour obtenir le résultat désiré. Malheureusement, je ne m'y connais pas en PL/SQL, alors j'apprécierais qu'on puisse m'aider à y arriver (idéalement avec une solution complète (fonctionnelle), détaillée et commentée).

    La problématique est la suivante: J'essaie d'obtenir une liste de données classée par connectivité (relation parent-enfant), et ce, tout en omettant certains types de données dans un environnement Oracle 11gR2. Pour illustrer mes propos, c'est comme si je voulais partir d'un arbre généalogique et en créer un nouveau, mais en excluant les personnes ayant des cheveux roux (je n'ai rien contre les personnes rousses )...

    À titre d'exemple, voici les informations que j'ai dans ma table que j'appelle "OriginalSequence"

    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
    WITH OriginalSequence AS
    (
    SELECT 'ID1' AS KEY, 'INVALID1' AS CHILD, NULL AS PARENT 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
    )
    IMPORTANT: Même si dans mon exemple je montre le contraire, les données de ma "vraie" table NE SONT PAS triées en ordre de connectivité.


    Dans ma table "OriginalSequence"
    • La colonne "KEY" est le nom de ma clé courante.
    • La colonne "Child" est le nom de la clé suivant (après) la clé courante.
    • La colonne "Parent" est le nom de la clé précédant (avant) la clé courante.
    • Est considéré "invalide" une clé qui débute par "INVALID".


    De façon visuelle, les données de mon exemple ressemblent à ceci: http://i.xomf.com/npckv.png

    Par contre, NE JAMAIS oublier que les données de ma "vrai" table NE SONT PAS triées en ordre de connectivité.

    Alors, ce que j'aimerais faire c'est d'omettre (sauter par dessus) les données invalides de façon à avoir le résultat suivant:

    KEY,NEW_CHILD,NEW_PARENT
    ID1,ID2      ,null
    ID1,ID3      ,null
    ID2,null     ,ID1
    ID3,ID4      ,ID1
    ID3,ID5      ,ID1
    ID3,ID7      ,ID1
    ID4,null     ,ID3
    ID5,ID6      ,ID3
    ID6,null     ,ID5
    ID7,ID8      ,ID3
    ID8,null     ,ID7
    Ce qui donne de façon visuelle ceci: http://i.xomf.com/vqqcp.png

    Merci beaucoup!

  2. #2
    Membre averti
    Homme Profil pro
    Développeur Décisionnel
    Inscrit en
    Janvier 2012
    Messages
    18
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Développeur Décisionnel

    Informations forums :
    Inscription : Janvier 2012
    Messages : 18
    Par défaut
    Bonjour,

    NOTE: Ceci est une méthode pour 10g. La méthode 11g, bien plus normée a été exposée dans le lien proposé plus bas dans ce post et ramenant à la section SQL.
    Tout d'abord, merci pour votre sujet interessant, j'ai adoré passer un petit moment à me torturer l'esprit pour tenter de le résoudre.

    Je fais cependant une entorse à votre demande car je vais tenter une approche purement SQL Oracle (Je fais bien noter SQL Oracle, car je vais faire appel à des fonctionnalités du SGBD).

    Problème à solutionner et postulats:
    • Les données de la table ne sont pas triées
    • Certaines lignes, dont les racines peuvent être considérées comme invalides
    • Contrairement à une structure d'arbre classique, les noeuds apparaissent plusieurs fois
    • La restitution des lignes se fait tel un parcours en profondeur mais les noeuds de branches sont répétés (?)
    • Pas de récursivité dans l'arbre


    Fonctionnalités utilisées
    Ma solution se base sur un certain nombre de fonctionnalités Oracle
    • Fonctions SQL Hiérarchiques : Permettront de reconstituer l'arbre à partir des données sources
    • Fonctions d'Expressions régulières : Permettront de récupérer les parents en sautant les Invalides
    • Jointures Externes : Permettront de démultiplier les noeuds pour chaque branche
    • Parcours en profondeur : Trouver le dernier parent valide revient en partant des branches à remonter l'arbre en parcours par profondeur jusqu'à trouver un noeud valide.


    Approche et Résolution
    Avant toute chose, et pour simplifier le problème et les requêtes j'ai préféré mettre les données dans une seule et même table afin de ne pas me trimballer un WITH de plus.
    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
     
    CREATE TABLE OriginalSequence AS 
    WITH OriginalSequence AS
    (
    SELECT 'ID1' AS KEY, 'INVALID1' AS CHILD, NULL AS PARENT FROM dual union ALL
    --SELECT 'INVALID0' AS KEY, 'INVALID1' AS CHILD, NULL AS PARENT FROM dual union ALL -- Pour tester les racines Invalides
    --SELECT 'INVALID1'  , 'ID2'              , 'INVALID0'          FROM dual union ALL -- Pour tester les racines Invalides
    --SELECT 'INVALID1'  , 'ID3'              , 'INVALID0'          FROM dual union ALL -- Pour tester les racines Invalides
    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
    ) SELECT * FROM OriginalSequence;
    La suppression :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    DROP TABLE OriginalSequence;
    Voici la bête complète:
    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
     
    WITH tree_data_os
    AS (
    SELECT os.rown
          ,os.is_leaf
          ,os.key
          ,os.old_parent
          ,os.new_parent
          --,os.is_invalid
          --,os.tree_path_all
          --,os.tree_path_valid_clean
    FROM
    ( 
    SELECT os.rown
          ,os.is_leaf
          ,os.key
          ,os.parent as old_parent
          --,os.is_invalid
          --,os.tree_path_all
          --,os.tree_path_valid_clean
          ,CASE WHEN instr(os.tree_path_valid_clean, '/', -1, 2) > 0 THEN
                   substr(
                      substr(os.tree_path_valid_clean,
                             instr(os.tree_path_valid_clean, '/', -1, 2)),
                      2,
                      length('/' || os.key) -1
                   )  
                ELSE
                  NULL
           END as new_parent
      FROM (SELECT rownum as rown
                  ,CONNECT_BY_ROOT(os.key) as root
                  ,connect_by_isleaf AS is_leaf
                  ,os.key
                  ,os.parent
                  --,os.is_invalid
                  ,regexp_replace(sys_connect_by_path(os.key_valid,'/'),'/+', '/') as tree_path_valid_clean
                  --,sys_connect_by_path(os.key,'/') as  tree_path_all             
                  ,os.key_valid
              FROM (SELECT DISTINCT os.key
                                   ,os.parent
                                   -- ,CASE WHEN instr(os.key,'INVALID')  > 0 THEN 1    ELSE 0      END AS is_invalid
                                   ,CASE WHEN instr(os.key ,'INVALID') > 0 THEN NULL ELSE os.key END AS key_valid
                      FROM originalsequence os) os
            CONNECT BY PRIOR os.key = os.parent
             START WITH PARENT IS NULL) os
            WHERE os.key_valid IS NOT NULL) os)
    SELECT tos1.key AS key,
           tos2.key AS new_child,
           tos1.new_parent AS new_parent,
           tos1.rown as order1,
           tos2.rown as order2       
    FROM   tree_data_os tos1
    LEFT OUTER  JOIN tree_data_os tos2
    ON     tos1.key = tos2.new_parent
    ORDER BY tos1.rown, tos2.rown
    Voici ma méthoden en pur SQL. (Oui, il serait possible de le faire en Hiérarchique récursive 11g. J'ai du le faire en 10g d'où l'utilisation des fonctions CONNECT BY ...)
    PREMIER SELECT
    L'objectif ici est de préparer les données pour les rendre utilisables sous forme d'arbre.
    1. Récupération uniquement l'identifiant "KEY" et le "PARENT" afin de faire une requête hiérarchique
    2. Suppression des doublons causés par la colonne "CHILD".
    3. Marquage des lignes que je considère comme non valides en ne gardant que la "KEY" des éléments valides


    SECOND SELECT
    L'objectif est de construire un arbre à partir des données via requête hiérarchique, de sauter les noeuds invalides
    1. Construction d'une requête hiérarchique. On part des racines, les lignes "PARENT" IS NULL et l'on construit un arbre en joignant via "CONNECT BY PRIOR" les lignes dont le "PARENT" est "KEY"
    2. Récupération du chemin parcouru par le moteur pour la construction de l'arbre via "SYS_CONNECT_BY_PATH". Le moteur Oracle parcourt alors les données en profondeur et retourne un arbre trié ainsi. La fonction SYS_CONNECT_BY_PATH permet de retourner le cheminement du parcours en profondeur pour aller jusqu'au noeud courant. C'est exactement le cheminement qu'il nous faut. Sauter des noeuds parents ne peut se faire simplement que via un parcours en profondeur. On fera donc le SYS_CONNECT_BY_PATH sur "KEY_VALID" qui mettra des NULL sur les noeuds invalides et se chargera se ne garder que le parcours d'éléments valides de bas en haut.
    3. Suppression des occurrences multiples de séparateurs '/' issus des noeuds invalides => Obtention d'un cheminement en sautant les invalides depuis la racine
    4. Récupération de l'ordre des lignes pour ne pas perdre le tri
    5. Récupération de données tierces pour le DEBUG et les tests (IS_LEAF et ROOT)
      Code : Sélectionner tout - Visualiser dans une fenêtre à part
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
       
      ROWN  ROOT  IS_LEAF  KEY       PARENT    IS_INVALID  TREE_PATH_VALID_CLEAN  TREE_PATH_ALL                                KEY_VALID
      1     ID1   0        ID1                 0           /ID1                   /ID1                                         ID1
      2     ID1   0        INVALID1  ID1       1           /ID1/                  /ID1/INVALID1  
      3     ID1   0        ID2       INVALID1  0           /ID1/ID2               /ID1/INVALID1/ID2                            ID2
      4     ID1   1        INVALID2  ID2       1           /ID1/ID2/              /ID1/INVALID1/ID2/INVALID2  
      5     ID1   0        ID3       INVALID1  0           /ID1/ID3               /ID1/INVALID1/ID3                            ID3
      6     ID1   1        ID4       ID3       0           /ID1/ID3/ID4           /ID1/INVALID1/ID3/ID4                        ID4
      7     ID1   0        INVALID3  ID3       1           /ID1/ID3/              /ID1/INVALID1/ID3/INVALID3  
      8     ID1   0        ID5       INVALID3  0           /ID1/ID3/ID5           /ID1/INVALID1/ID3/INVALID3/ID5               ID5
      9     ID1   1        ID6       ID5       0           /ID1/ID3/ID5/ID6       /ID1/INVALID1/ID3/INVALID3/ID5/ID6           ID6
      10    ID1   0        INVALID4  INVALID3  1           /ID1/ID3/              /ID1/INVALID1/ID3/INVALID3/INVALID4  
      11    ID1   0        ID7       INVALID4  0           /ID1/ID3/ID7           /ID1/INVALID1/ID3/INVALID3/INVALID4/ID7      ID7
      12    ID1   1        ID8       ID7       0           /ID1/ID3/ID7/ID8       /ID1/INVALID1/ID3/INVALID3/INVALID4/ID7/ID8  ID8
    6. Suppression des doublons causés par la colonne "CHILD".


    TROISIEME SELECT
    L'arbre est remonté, toutes les lignes valides valides sont à garder. Pour chaque ligne valide, on connait le cheminement via le "TREE_PATH_VALID_CLEAN" pour aller jusqu'à chaque élément. Trouver le parent, dans la chaîne formée revient à trouver, si il existe le noeud parcouru juste avant. Toute ligne qui n'a pas au moins deux neuds sera donc une racine.

    • Récupération du dernier parent "NEW_PARENT" à partir du "TREE_PATH_VALID_CLEAN"
      1. Récupération des deux dernières parties de chaque "TREE_PATH_VALID_CLEAN" en prenant tous les caractères de la chaîne à partir de l'avant dernier '/' en partant de la fin
      2. Puis retrait de la chaîne '/' || <ID DU NOEUD COURANT afin de ne garder que le noeud parent valide.
      3. Bien prendre les caractères en ne prenant pas le premier '/'

      Code : Sélectionner tout - Visualiser dans une fenêtre à part
      1
      2
      3
      4
      /ID1/ID3/ID7  ===> Parent: ID3
      /ID1/......IDN/IDY  ===> Parent: IDN
      /ID1  ===> Parent: NULL
      Ne faire celà que pour les lignes ayant deux noeuds. les autres seront donc des racines et seront NULL.
    • Suppression des lignes invalides maintenant inutiles


    A ce niveau il est possible de récupérer un arbre classique des données:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    KEY  NEW_PARENT  ORDER1
    ID1              1
    ID2  ID1         3
    ID3  ID1         5
    ID4  ID3         6
    ID5  ID3         8
    ID6  ID5         9
    ID7  ID3         11
    ID8  ID7         12

    QUATRIEME SELECT
    Création d'une auto-jointure à gauche des données de l'arbre structuré afin de permettre de dédoubler à nouveau les lignes de noeuds de branches comme souhaité. Celà va permettre de récupérer ainsi la notion de "NEW_CHILD"
    Afin de garder le même ordre, je trie une première fois sur le rown de la première table à gauche pour garder le semblant d'ordre de parcours en profondeur, puis je trie en secondaire sur le rown de la jointure de droite pour prendre les noeuds de gauche à droite.

    Résultat Final
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    KEY  NEW_CHILD  NEW_PARENT  ORDER1  ORDER2  
    ID1  ID2                    1       3
    ID1  ID3                    1       5
    ID2             ID1         3       3
    ID3  ID4        ID1         5       6
    ID3  ID5        ID1         5       8
    ID3  ID7        ID1         5       11
    ID4             ID3         6       
    ID5  ID6        ID3         8       9
    ID6             ID5         9       
    ID7  ID8        ID3         11      12
    ID8             ID7         12
    J'espère que je n'ai perdu personne dans la démonstration. J'espère surtout ne pas avoir fait de boulettes, car je n'ai pu consacrer au problème qu'un petit moment.

    NOTE: Oui, il y a surement bien plus optimisé sur certaines parties. Il y a même surement des incohérences ou des améliorations à apporter sur la gestion des doublons.
    Certes, dans le meilleur des mondes il faudrait utiliser la syntaxe WITH + Hierarchical Récursif pour se mettre au goût du jour ... Mais cette approche reste un exemple pour donner des idées de ce qui peut-être fait.

    Dans cette optique, merci de faire des remarques, donner des avis et des optimisations :]

    En PL/SQL ...
    A mon avis, il serait simple de fabriquer une structure de stockage des données sous forme d'arbre avec des types PL/SQL ou Oracle, de stocker les données sous forme d'arbre puis de faire un parcours en profondeur pour marquer les NEW_PARENT en fonction du dernier parent valide.

    Peut-être que si j'ai du temps ce WE je le ferai...


    Références:

  3. #3
    Membre émérite Avatar de jkofr
    Homme Profil pro
    Senior Consultant DBA (Trivadis SA)
    Inscrit en
    Octobre 2006
    Messages
    484
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 56
    Localisation : Suisse

    Informations professionnelles :
    Activité : Senior Consultant DBA (Trivadis SA)
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 484
    Par défaut
    Hello,
    Intéressant comme case:-)

    Il me semble qu'il y a plus simple mais à confirmer...
    ps: J'ai volontairement changé l'ordre des lignes:-)

    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
     
    select LPAD(' ', 2*lev-1)||thekey 
    from
    (WITH OriginalSequence AS
    (
    SELECT 'INVALID3'AS KEY , 'INVALID4'AS CHILD,'ID3'AS PARENT  FROM dual union ALL
    SELECT 'ID1'       , 'INVALID1'         , NULL           FROM dual union ALL
    SELECT 'ID4'       , NULL               , 'ID3'          FROM dual union ALL
    SELECT 'INVALID3'  , 'ID5'              , 'ID3'          FROM dual union ALL
    SELECT 'ID5'       , 'ID6'              , 'INVALID3'     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 'ID6'       , NULL               , 'ID5'          FROM dual union ALL
    SELECT 'ID7'       , 'ID8'              , 'INVALID4'     FROM dual union ALL
    SELECT 'INVALID4'  , 'ID7'              , 'INVALID3'     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 'ID8'       , NULL               , 'ID7'          FROM dual
    )
    select distinct key thekey, level lev
    from OriginalSequence connect by prior key=parent start with parent is null
    order  by 1)
    where thekey not like '%INV%'
    Résultat:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    THEKEY   
    ---------------------------
     ID1                       
         ID2                   
         ID3                   
           ID4                 
             ID5               
               ID6             
               ID7             
                 ID8           
     
     8 rows selected
    Par la suite en PL/SQL tu fais un curseur dans un block ou une procédure.

    Dit moi si cela correspond à ton besoin.
    jko

  4. #4
    Membre averti
    Homme Profil pro
    Développeur Décisionnel
    Inscrit en
    Janvier 2012
    Messages
    18
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Développeur Décisionnel

    Informations forums :
    Inscription : Janvier 2012
    Messages : 18
    Par défaut
    La solution que vous proposez est simple et évidente et sert de base à la première partie de l'algorithme de ma réponse.

    Dans le cas ou l'utilisateur n'a pas à remonter les notions de NEW_PARENT et de NEW_CHILD, elle sert amplement (pour faire un arbre directement par exemple).

    Par contre, toute remontée des informations de parentée, entrainera de la plomberie supplémentaire.

  5. #5
    Membre émérite Avatar de jkofr
    Homme Profil pro
    Senior Consultant DBA (Trivadis SA)
    Inscrit en
    Octobre 2006
    Messages
    484
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 56
    Localisation : Suisse

    Informations professionnelles :
    Activité : Senior Consultant DBA (Trivadis SA)
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 484
    Par défaut
    Hello,

    Exact.

    C'était juste en observant les 2 exemples.
    Maintenant attendons de voir si JGLord est intéressé d'avoir la totalité des infos.

    jko

  6. #6
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    Une réponse SQL a été fournie par aieeeuuuuu dans ce sujet :
    http://www.developpez.net/forums/d13...ete-recursive/

  7. #7
    Membre averti
    Profil pro
    Inscrit en
    Mai 2008
    Messages
    30
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2008
    Messages : 30
    Par défaut
    Malheureusement, je ne crois pas que cette approche va m'être bien utile, car je dois effectivement "remonter les notions de NEW_PARENT et de NEW_CHILD". Par exemple, pour mes traitements les ID5 et ID7 doivent être au même niveau que le ID4.

    Citation Envoyé par jkofr Voir le message
    Hello,
    Intéressant comme case:-)

    Il me semble qu'il y a plus simple mais à confirmer...
    ps: J'ai volontairement changé l'ordre des lignes:-)

    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
     
    select LPAD(' ', 2*lev-1)||thekey 
    from
    (WITH OriginalSequence AS
    (
    SELECT 'INVALID3'AS KEY , 'INVALID4'AS CHILD,'ID3'AS PARENT  FROM dual union ALL
    SELECT 'ID1'       , 'INVALID1'         , NULL           FROM dual union ALL
    SELECT 'ID4'       , NULL               , 'ID3'          FROM dual union ALL
    SELECT 'INVALID3'  , 'ID5'              , 'ID3'          FROM dual union ALL
    SELECT 'ID5'       , 'ID6'              , 'INVALID3'     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 'ID6'       , NULL               , 'ID5'          FROM dual union ALL
    SELECT 'ID7'       , 'ID8'              , 'INVALID4'     FROM dual union ALL
    SELECT 'INVALID4'  , 'ID7'              , 'INVALID3'     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 'ID8'       , NULL               , 'ID7'          FROM dual
    )
    select distinct key thekey, level lev
    from OriginalSequence connect by prior key=parent start with parent is null
    order  by 1)
    where thekey not like '%INV%'
    Résultat:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    THEKEY   
    ---------------------------
     ID1                       
         ID2                   
         ID3                   
           ID4                 
             ID5               
               ID6             
               ID7             
                 ID8           
     
     8 rows selected
    Par la suite en PL/SQL tu fais un curseur dans un block ou une procédure.

    Dit moi si cela correspond à ton besoin.
    jko

  8. #8
    Membre averti
    Profil pro
    Inscrit en
    Mai 2008
    Messages
    30
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2008
    Messages : 30
    Par défaut
    Premièrement, un GROS merci à Zyniel pour avoir donné tant de détail dans la démarche, je n'aurais jamais pu espérer mieux!

    J'ai bien hâte de tester le tout dans mon "vrai" environnement et de comparer la performance à la version de aieeeuuuuu, qui est aussi terriblement performante.



    Néanmoins, j'apprécierais qu'on puisse me donner une solution en PL/SQL (idéalement aussi détaillée que la solution de Zyniel) afin que je puisse voir et surtout apprendre la logique du PL/SQL en fonction de mon exemple (c'est toujours plus facile de partir d'exemple qu'on connaît). Je vous rappelle que je ne connais pas le PL .

    Avec un exemple en PL/SQL fonctionnel, je pourrais comparer la performance d'utiliser cette avenue par rapport aux solutions de Zyniel et aieeeuuuuu. Je parle de performance, car dans mon "vrai" environnement j'ai plusieurs centaines de millions de rows à traiter...


    Finalement, pour ceux qui aimeraient essayer d'optimiser la solution de Zyniel:
    http://sqlfiddle.com/#!4/f2c33/1
    ou de aieeeuuuuu:
    http://sqlfiddle.com/#!4/7988c/1

  9. #9
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Par défaut
    Bonjour,

    En fait je pense que l'on peut améliorer la solution que j'avais proposée, en calculant le prédécesseur directement dans la récursive, afin d'éviter la jointure externe finale.
    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
     
    ;WITH cte ( CLE, SUIVEUR,PREDECESSEUR)
     AS (
    	SELECT  CLE, SUIVEUR, PREDECESSEUR
    	FROM MaSequence
    	WHERE PREDECESSEUR IS NULL
    	UNION ALL
    	SELECT 
           CASE WHEN S.CLE LIKE 'INVALID%' THEN P.CLE ELSE S.CLE END 
         , S.SUIVEUR
         , CASE WHEN P.SUIVEUR LIKE 'INVALID%' THEN P.PREDECESSEUR ELSE P.CLE END
    	FROM cte P
    	INNER JOIN MaSequence S
    		ON S.CLE=P.SUIVEUR
    )
    SELECT CLE, SUIVEUR, PREDECESSEUR
    FROM cte 
    WHERE CLE NOT LIKE 'INVALID%'
    AND (SUIVEUR NOT LIKE 'INVALID%' OR SUIVEUR IS NULL)

Discussions similaires

  1. Requête récursive avec jointure
    Par Jmdu_44fr dans le forum SQL
    Réponses: 2
    Dernier message: 07/07/2011, 15h10
  2. [WD14] Procédure récursive avec requête
    Par mail.spam dans le forum WinDev
    Réponses: 7
    Dernier message: 02/07/2010, 16h37
  3. Réponses: 4
    Dernier message: 04/05/2006, 19h01
  4. SQL SERVER 2005 -- requête récursive avec bcp données
    Par evans dans le forum MS SQL Server
    Réponses: 4
    Dernier message: 04/05/2006, 19h01
  5. [SQL Server]Problème avec une requête récursive
    Par evans dans le forum Langage SQL
    Réponses: 3
    Dernier message: 05/04/2006, 20h16

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo