Combinaisons par connect by

Version imprimable

17/12/2009, 17h44
PierreV01

Combinaisons par connect by

Bonjour, (Oracle 10G)
J'essaie d'obtenir les combinaisons valides pour des ensembles de questions et réponses. Une solution est valide lorsque toutes les questions d'un cas font partie de la combinaison. Voici la table des données.
No_cas quest reponse
1 3 A
1 4 A
1 4 C
1 5 B
1 6 B
2 7 C
2 8 C
2 9 D
3 10 A
3 11 C

Exemple le cas no 1 est lié aux questions 3-4-5-6. Une solution valide doit contenir ces 4 questions et leurs réponses.
La requête devrait extraire toutes les combinaisons valides soit:
No_Cas Solution
1 3A-4A-5B-6B
1 3A-4C-5B-6B
2 7C-8C-9D
3 10A-11C
J'ai l'impression qu'on pourrait y arriver dans un connect by mais je n'y parviens pas. Pour un connect by j'ai besoin d'un parent, alors j'ai généré une vue identique incluant le parent c'est-à-dire:
data
No_cas quest reponse parent
1 3 A null
1 4 A 3
1 4 C 3
1 5 B 4
1 6 B 5
etc.
Mais comment extraire les solutions valides?
18/12/2009, 09h58
xdescamp

Bonjour,

Tu pourrais donner la requête de ta vue ?
Je pense qu'en la modifiant un peu on doit pouvoir récupérer le résultat que tu cherches.

Voici une proposition, on peut peut-être la simplifier un peu :

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
with t as (select 1 as no_cas, 3 as quest, 'A' as reponse from dual union all
           select 1 as no_cas, 4 as quest, 'A' as reponse from dual union all
           select 1 as no_cas, 4 as quest, 'C' as reponse from dual union all
           select 1 as no_cas, 5 as quest, 'B' as reponse from dual union all
           select 1 as no_cas, 6 as quest, 'B' as reponse from dual union all
           select 2 as no_cas, 7 as quest, 'C' as reponse from dual union all
           select 2 as no_cas, 8 as quest, 'C' as reponse from dual union all
           select 2 as no_cas, 9 as quest, 'D' as reponse from dual union all
           select 3 as no_cas, 10 as quest, 'A' as reponse from dual union all
           select 3 as no_cas, 11 as quest, 'C' as reponse from dual ),
     v as (select t.no_cas, t.quest, t.reponse, t.quest||t.reponse as quest_reponse, ordre_quest.quest_suivante
           from t,
                (select no_cas, quest, lead (quest) over (partition by no_cas order by quest) quest_suivante
                 from (select distinct t.no_cas, t.quest
                       from t)) ordre_quest
           where t.no_cas = ordre_quest.no_cas
             and t.quest = ordre_quest.quest ),
     v_hier as (select no_cas, quest, reponse, quest_reponse, level niv,
                ltrim(sys_connect_by_path(quest_reponse, '-'), '-') combinaison
                from v
                connect by prior quest_suivante = quest
                       and prior no_cas = no_cas)
select no_cas, combinaison
from (
      select v_hier.no_cas, v_hier.quest, v_hier.reponse, v_hier.quest_reponse, v_hier.combinaison, v_hier.niv, max (v_hier.niv) over (partition by v_hier.no_cas) max_niv
      from v_hier)
where niv = max_niv;
 
    NO_CAS COMBINAISON
---------- --------------------
         1 3A-4A-5B-6B
         1 3A-4C-5B-6B
         2 7C-8C-9D
         3 10A-11C

Quelques explications :
- t est ta table de données,
- v est une "vue" qui permet de concaténer la question et sa réponse, et de trouver la question suivante avec "ordre_quest"; si la question suivante est toujours avec un +1, on peut modifier pour supprimer cette partie et modifier le "connect by" un peu plus loin
- v_hier est l'équivalent de la vue hiérarchique que tu as créée; j'y ai ajouté le "sys_connect_by_path qui permet de donner l'enchainement des enregistrements
- dans le select principal, j'utilise une fonction analytique pour trouver le nombre maximum de niveaux pour chaque "no_cas" et ensuite ne garder que les derniers.

J'espère que les explications sont à peu près claires et que ça correspond bien à ce que tu cherches à avoir comme résultat.

Xavier

18/12/2009, 16h09
PierreV01

Citation:

Envoyé par xdescamp

Voici une proposition, on peut peut-être la simplifier un peu : ........
NO_CAS COMBINAISON
---------- --------------------
1 3A-4A-5B-6B
1 3A-4C-5B-6B
2 7C-8C-9D
3 10A-11C[/code]
Quelques explications :
- t est ta table de données,
- v est une "vue" qui permet de concaténer la question et sa réponse, et de trouver la question suivante avec "ordre_quest"; si la question suivante est toujours avec un +1, on peut modifier pour supprimer cette partie et modifier le "connect by" un peu plus loin
- v_hier est l'équivalent de la vue hiérarchique que tu as créée; j'y ai ajouté le "sys_connect_by_path qui permet de donner l'enchainement des enregistrements
- dans le select principal, j'utilise une fonction analytique pour trouver le nombre maximum de niveaux pour chaque "no_cas" et ensuite ne garder que les derniers.

J'espère que les explications sont à peu près claires et que ça correspond bien à ce que tu cherches à avoir comme résultat.

Xavier

Salut,
Oui c'est bien ça et je l'ai testé avec un ensemble de données étendues. Comme tu le dis ça correspondait à ma vue hiérarchique mais je ne parvenais pas à voir comment extraire le niveau maximal. Bien sûr il fallait chercher le niveau max dans un autre select comme tu l'as fait pour ensuite faire les liens.

Merci.