problème de groupage multiligne...

**pdelorme** · 06/03/2008, 10h51

Bonjour,

j'ai un problème de groupage de donnée un peut particulier.

vu la complexité de ma problématique métier, j'ai préféré vous donner un exemple (peut être un peut stupide) mais beaucoup plus simple à expliquer (et sans doute à comprendre)

Question : comment grouper les personnes ayant des freres de même age et de même couleur de cheveux. une personne pouvant avoir plusieurs freres.

j'ai 2 tables PERSONNE et FRERE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
PERSONNE:
PER_ID  Nom
1       Pierre
2       Paul
3       Jacques
 
FRERE:
PER_ID  Age    Cheveux
1       12     Blond
1       15     Brun
2       12     Blond
2       15     Brun
3       12     Chatain
3       17     Brun

j'aimerais editer un état Crystal Report ou les valeurs de FRERE sont groupé sur les PERSONNES ayant des valeurs de FRERE identiques

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Pierre, Paul
-> Age    Cheveux
-> 12     Blond
-> 15     Brun
 
Jacques
-> Age    Cheveux
-> 12     Chatain
-> 17     Brun

ici l'important est que PIERRE et PAUL soient identifié comme des PERSONNE ayant les mêmes valeurs de FRERE.
La complexité viens du fait toutes les lignes de FRERE doivent être identiques (en valeur et en nombre) pour PIERRE et PAUL se qui empêche l'utilisation d'un GROUP BY standard.

l'idéal serais une requette permetant de générer le résultat suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
ID Nom       id_goupe Age       cheveux
1  PIERRE    GROUP1   12        Blond
1  PIERRE    GROUP1   15        Brun
2  PAUL      GROUP1   12        Blond
2  PAUL      GROUP1   15        Brun
3  JACQUES   GROUP2   12        Chatain
3  JACQUES   GROUP2   17        Brun

De sorte que je puisse alors faire un group by simple sur id_groupe...

j'ai deux pistes:
- la division euclidienne SQL... un collègue me dit de chercher par la. mais je m'y perd.
- définir une fonction d'agrégation spéciale qui calcule une clé unique sur FRERE à partir de plusieurs lignes... et d'utiliser cette clé comme id_groupe... mais la encore j'ai du mal a déterminer la fonction d'agrégation qui va bien.

des idées...

merci,

Patrice.

**mnitu** · 06/03/2008, 14h07

L'original est ici

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
 
SQL*Plus: Release 9.2.0.5.0 - Production on Je Mar 6 14:07:07 2008
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connecté à :
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production
SQL> select * from t;
 
C1         C2
---------- ----------
1          12Blond
1          15Brun
2          12Blond
2          15Brun
3          12Chatain
3          17Brun
 
6 ligne(s) sélectionnée(s).
 
SELECT c1,Replace(Replace(XMLAgg(XMLElement(X,c2) order by c1),'<X>'),'</X>',' ') c2
FROM
(SELECT DISTINCT MIN(substr(str,1,
                 CASE WHEN instr(str,',')>0
                      THEN instr(str,',')-1
	              ELSE length(str) END)) OVER (PARTITION BY c1) c1,c2
  FROM
  (SELECT c1, c2, path,ltrim(sys_connect_by_path(c1,','),',') str
     FROM
     (SELECT c1,c2,rn,ltrim(sys_connect_by_path(c1,','),',') path
        FROM(SELECT c1,c2,row_number() over (PARTITION BY c2 ORDER BY c1) rn
              FROM t )
        START WITH rn = 1
        CONNECT BY c2 = PRIOR c2
        AND PRIOR rn = rn -1
     )
     CONNECT BY PRIOR substr(path,instr(path,',',-1)+1) =
	              substr(path,0,instr(path,',',1)-1)
     AND c1 > PRIOR c1
  )
)
GROUP BY c1;
 
C1
--------------------------------------------------------------------------------
C2
--------------------------------------------------------------------------------
1
12Blond 15Brun
 
3
12Chatain 17Brun

**Michel SALAIS** · 06/03/2008, 22h22

Voici une solution mais ...

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
with personne as
(
  select 1 as id, 'Pierre' as nom from dual
  union all
  select 2, 'Paul' from dual
  union all
  select 3, 'Jacques' from dual
  union all
  select 4, 'Pascal' from dual
),
frere as
(
  select 1 as per_id, 12 as age, 'Blond' as cheveux from dual
  union all
  select 1, 15, 'Brun' from dual
  union all
  select 2, 12, 'Blond' from dual
  union all
  select 2, 15, 'Brun' from dual
  union all
  select 3, 12, 'Blond' from dual
  union all
  select 3, 17, 'Chatain' from dual
  union all
  select 4, 12, 'Blond' from dual
  union all
  select 4, 17, 'Chatain' from dual
  union all
  select 4, 18, 'Chatain' from dual
),
t as
(
  select f.per_id id, f.age, f.cheveux
       , count(*) over (partition by f.per_id) frere_count
  from frere f
),
t1 as
(
  select distinct f1.id id1, f2.id id2
  from t f1, t f2
  where f1.frere_count = f2.frere_count
    and f1.frere_count = 
    (
      select count(*)
      from t f3, t f4
      where f3.id = f1.id
        and f4.id = f2.id
        and f3.age = f4.age
        and f3.cheveux = f4.cheveux
 
    )
),
t2 as
(
  select id1 id, min(id2) group_id
  from t1
  group by id1
)
select p.id, p.nom, t2.group_id, f.age, f.cheveux
from personne p, frere f, t2
where p.id = t2.id
  and p.id = f.per_id
 
 
ID  NOM      GROUP_ID  AGE  CHEVEUX                                                                                                                    
--  -------  --------  ---  -------                                                                                                                        
1   Pierre   1         12   Blond                                                                                                              
1   Pierre   1         15   Brun                                                                                                              
2   Paul     1         12   Blond                                                                                                            
2   Paul     1         15   Brun                                                                                                            
3   Jacques  3         12   Blond                                                                                                               
3   Jacques  3         17   Chatain                                                                                                               
4   Pascal   4         12   Blond                                                                                                              
4   Pascal   4         17   Chatain                                                                                                              
4   Pascal   4         18   Chatain

**pdelorme** · 07/03/2008, 10h12

Envoyé par Michel SALAIS

Voici une solution mais ...

Salut,
La solution à l'air élégante mais je n'ai pas réussi à la faire fonctionner...
l'instruction WITH ne semble pas fonctionner sur 9i.

P.

**Michel SALAIS** · 07/03/2008, 10h36

Envoyé par pdelorme

Salut,
La solution à l'air élégante mais je n'ai pas réussi à la faire fonctionner...
l'instruction WITH ne semble pas fonctionner sur 9i.

P.

Si, si ...
WITH a été intoduit à partir d'Oracle 9i. Par contre je l'ai testé après sur Oracle 10g et je n'ai pas réussi ! En fait ça touche apparemment un bug (J'ai l'erreur ora-07445 dans le fichier d'alerte). Pour moi il n'y avait pas de problème sur une base en 9.2.0.8.

La solution était de créer réellement les tables et de passer la requê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
57
58
59
60
61
62
63
 
create table personne as
  select 1 as id, 'Pierre' as nom from dual
  union all
  select 2, 'Paul' from dual
  union all
  select 3, 'Jacques' from dual
  union all
  select 4, 'Pascal' from dual
/
 
create table frere as
  select 1 as per_id, 12 as age, 'Blond' as cheveux from dual
  union all
  select 1, 15, 'Brun' from dual
  union all
  select 2, 12, 'Blond' from dual
  union all
  select 2, 15, 'Brun' from dual
  union all
  select 3, 12, 'Blond' from dual
  union all
  select 3, 17, 'Chatain' from dual
  union all
  select 4, 12, 'Blond' from dual
  union all
  select 4, 17, 'Chatain' from dual
  union all
  select 4, 18, 'Chatain' from dual
/
 
with t as
(
  select f.per_id id, f.age, f.cheveux
       , count(*) over (partition by f.per_id) frere_count
  from frere f
),
t1 as
(
  select distinct f1.id id1, f2.id id2
  from t f1, t f2
  where f1.frere_count = f2.frere_count
    and f1.frere_count = 
    (
      select count(*)
      from t f3, t f4
      where f3.id = f1.id
        and f4.id = f2.id
        and f3.age = f4.age
        and f3.cheveux = f4.cheveux
 
    )
),
t2 as
(
  select id1 id, min(id2) group_id
  from t1
  group by id1
)
select p.id, p.nom, t2.group_id, f.age, f.cheveux
from personne p, frere f, t2
where p.id = t2.id
  and p.id = f.per_id

**mnitu** · 07/03/2008, 12h12

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
 
With t As
(Select per_id, str, dense_rank() over (order by str) dr
  From (
Select f.per_id, stragg(age||cheveux) str
  From frere f
Group BY f.per_id)
)
Select f.per_id, p.nom, t.dr grouping_id, f.age, f.cheveux
  From frere f, personne p, t
Where t.per_id = f.per_id 
  And p.id = f.per_id
/
    PER_ID NOM     GROUPING_ID        AGE CHEVEUX
---------- ------- ----------- ---------- -------
         1 Pierre            1         15 Brun
         1 Pierre            1         12 Blond
         2 Paul              1         15 Brun
         2 Paul              1         12 Blond
         3 Jacques           2         17 Chatain
         3 Jacques           2         12 Blond
         4 Pascal            3         18 Chatain
         4 Pascal            3         17 Chatain
         4 Pascal            3         12 Blond

problème de groupage multiligne...

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager