SQL faire des regroupements

Version imprimable

Je voudrais regrouper des éléments qui ont des codes en commun, mais je n'y arrive pas.

Voici un exemple :

Ma table :
liste_objet	Code_n1	Code_n2	Code_n3
objet_1 	A		
objet_2 	A	Z	
objet_3 	B	W	
objet_4 	C	Z	
objet_5 	D	Y	
objet_6 	D	U	O
objet_7 	E	Z	P
objet_8 	F	X	P
objet_9 	G	Y	
objet_10	H	U	
Les regroupements que je voudrais faire :
Le regroupement n° 1 doit contenir les objets suivants :
objet_1			
objet_2	car lié à l'objet_1 par le code_n1		
objet_4	car lié à l'objet_2 par le code_n2		
objet_7	car lié à l'objet_4 par le code_n2		
objet_8	car lié à l'objet_7 par le code_n3	
Le regroupement n° 2 doit contenir les objets suivants :
objet_5			
objet_6 	car lié à l'objet_5 par le code_n1		
objet_9 	car lié à l'objet_5 par le code_n2		
objet_10	car lié à l'objet_6 par le code_n2		
Le regroupement n°3 doit contenir les objets suivants :
objet_3	lié à aucun autre objet		
Table finale souhaitée :
liste_objet Groupement
objet_1 	g1		
objet_2 	g1		
objet_3 	g3		
objet_4 	g1		
objet_5 	g2		
objet_6 	g2		
objet_7 	g1		
objet_8 	g1		
objet_9 	g2		
objet_10	g2		
Est ce que quelqu'un peu m'aider ?

27/08/2012, 14h57
al1_24
Le modèle de ta table d'origine étant dénormalisé, les traitements vont être complexes.
La bonne structure serait :
```
liste_objet	Code	Niveau
objet_1 	A	1
objet_2 	A	1
objet_2 	Z	2
objet_3 	B	1
objet_3 	W	2
objet_4 	C	1
objet_4 	Z	2
```

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
With tp as (select 'objet_1' OBJ ,'A' C1,null C2, null C3 from dual union all
            select 'objet_2'     ,'A'   ,'Z'    , null    from dual union all
            select 'objet_3'     ,'B'   ,'W'    , null    from dual union all
            select 'objet_4'     ,'C'   ,'Z'    , null    from dual union all
            select 'objet_5'     ,'D'   ,'Y'    , null    from dual union all
            select 'objet_6'     ,'D'   ,'U'    , 'O'     from dual union all
            select 'objet_7'     ,'E'   ,'Z'    , 'P'     from dual union all
            select 'objet_8'     ,'F'   ,'X'    , 'P'     from dual union all
            select 'objet_9'     ,'G'   ,'Y'    , null    from dual union all
            select 'objet_10'    ,'H'   ,'U'    , null    from dual ),
t as (select rownum rn, tp.* from tp ),
t2 as (select distinct OBJ
            , C1
            , C2
            , C3
            , prior C1  PC1
            , prior C2  PC2
            , prior C3  PC3
            , prior OBJ POB
       from t 
       connect by (C1=prior C1 
                or C2=prior C2 
                or C3=prior C3 )
              and rn>prior RN ),
t3 as (select rownum GN, OBJ
       from ( select OBJ, max(PC1), max(PC2), max(PC3) from t2
              HAVING max(PC1) is null and max(PC1) is null and max(PC3) is null 
              group by OBJ, C1, C2, C3
              order by OBJ )), 
t4 as (select OBJ, CONNECT_BY_ROOT OBJ TOBJ
       from t2
       start with obj in ( select OBJ from t3 ) 
       connect by prior OBJ=POB)
select distinct t4.obj
     , t3.gn
from t3
   , t4 
where t3.obj = t4.tobj
order by gn

Qui donne

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
OBJ                                    GN
------------------------------ ----------
objet_1                                 1
objet_2                                 1
objet_4                                 1
objet_7                                 1
objet_8                                 1
objet_3                                 2
objet_10                                3
objet_5                                 3
objet_6                                 3
objet_9                                 3
 
10 ligne(s) sélectionnée(s).

MAIS, ça ne fonctionne pas dans tous les cas (je ne suis pas certain de cette dernière assertion)

28/08/2012, 09h09
Emilie_8

regroupement avec la bonne structure de table
Citation:
Envoyé par al1_24

Le modèle de ta table d'origine étant dénormalisé, les traitements vont être complexes.
La bonne structure serait :

liste_objet Code Niveau objet_1 A 1 objet_2 A 1 objet_2 Z 2 objet_3 B 1 objet_3 W 2 objet_4 C 1 objet_4 Z 2
==> J'ai possibilité de mettre la table avec la bonne structure comme décrite ci dessus.
Comment faire alors le regroupement que je souhaite (sachant que la table contient 5000 lignes) ?
28/08/2012, 09h16
Emilie_8

Regroupement d'élément - suite

Merci ojo77 pour ce code.
Malheureusement, j'ai l'impression que ma requête n’aboutit pas.
Ma table contient bcp de lignes (1800 dans la forme décrite initialement) et ça bloque dès la création de la table t2
:cry:

Citation:

Envoyé par Emilie_8

==> J'ai possibilité de mettre la table avec la bonne structure comme décrite ci dessus.
Comment faire alors le regroupement que je souhaite (sachant que la table contient 5000 lignes) ?

même requête que précédemment proposée avec une seule ligne dans les regroupement connect by prior

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
t2 AS (SELECT DISTINCT OBJ
            , C1
            , prior C1  PC1
            , prior OBJ POB
       FROM t 
       connect BY C1=prior C1 
              AND rn>prior RN ),
t3 AS (SELECT rownum GN, OBJ
       FROM ( SELECT OBJ, max(PC1) FROM t2
              HAVING max(PC1) IS NULL 
              GROUP BY OBJ, C1
              ORDER BY OBJ )), 
t4 AS (SELECT OBJ, CONNECT_BY_ROOT OBJ TOBJ
       FROM t2
       start WITH obj IN ( SELECT OBJ FROM t3 ) 
       connect BY prior OBJ=POB)
SELECT DISTINCT t4.obj
     , t3.gn
FROM t3
   , t4 
WHERE t3.obj = t4.tobj
ORDER BY gn

28/08/2012, 09h24
ojo77

Citation:

Envoyé par Emilie_8

Merci ojo77 pour ce code.
Malheureusement, j'ai l'impression que ma requête n’aboutit pas.
Ma table contient bcp de lignes (1800 dans la forme décrite initialement) et ça bloque dès la création de la table t2
:cry:

en effet le connect by est exponentiel et en olus j'en fais 2 :aie: