problème de groupage multiligne...

Version imprimable

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:

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:

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:

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.

L'original est ici

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

Voici une solution mais ...

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

07/03/2008, 10h12
pdelorme

Citation:

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.

Citation:

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:

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

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

Bref, sans StrAgg

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
With t As
(Select per_id, str, dense_rank() over (order by str) dr
  From (
Select f.per_id, Replace(Replace(XmlAgg(XmlElement("X",age||cheveux)),'<X>'),'</X>',',') 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

Citation:

Envoyé par mnitu

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

Intéressant mais ça ne marche malheureusement pas! Il faut que la concaténation respecte l'ordre des frères selon les colonnes age, cheveux sinon voici un contre exemple

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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, 15, 'Brun' from dual
  union all
  select 2, 12, 'Blond' 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 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            12   Blond                                                                                                       
1       Pierre   1            15   Brun                                                                                                       
2       Paul     4            15   Brun                                                                                                     
2       Paul     4            12   Blond                                                                                                     
3       Jacques  2            12   Blond                                                                                                        
3       Jacques  2            17   Chatain                                                                                                        
4       Pascal   3            12   Blond                                                                                                       
4       Pascal   3            17   Chatain                                                                                                       
4       Pascal   3            18   Chatain                                                                                                       
 
9 rows selected

J'ai juste interverti l'ordre des frèree de la personne numéro 2

Voici une modification qui marche de ta solution mnitu

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
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, 15, 'Brun' from dual
  union all
  select 2, 12, 'Blond' 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 *
  from frere
  order by per_id, age, cheveux
),
t1 as
(
  SELECT per_id, str, dense_rank() over (ORDER BY str) dr
    FROM (
  SELECT f.per_id, stragg(age||cheveux) str
    FROM t f
  GROUP BY f.per_id)
)
SELECT f.per_id, p.nom, t1.dr grouping_id, f.age, f.cheveux
  FROM frere f, personne p, t1
WHERE t1.per_id = f.per_id 
  AND p.id = f.per_id
 
 
PER_ID  NOM      GROUPING_ID  AGE  CHEVEUX                                                                                                                    
------  -------  -----------  ---  -------                                                                                                                        
1       Pierre   1            12   Blond                                                                                                       
1       Pierre   1            15   Brun                                                                                                       
2       Paul     1            15   Brun                                                                                                     
2       Paul     1            12   Blond                                                                                                     
3       Jacques  2            12   Blond                                                                                                        
3       Jacques  2            17   Chatain                                                                                                        
4       Pascal   3            12   Blond                                                                                                       
4       Pascal   3            17   Chatain                                                                                                       
4       Pascal   3            18   Chatain

Citation:

Envoyé par Michel SALAIS

Intéressant mais ça ne marche malheureusement pas! Il faut que la concaténation respecte l'ordre des frères selon les colonnes age...

Michel, pourquoi veut tu me casser la baraque ?

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
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, 15, 'Brun' from dual
  union all
  select 2, 12, 'Blond' 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 per_id, str, dense_rank() over (ORDER BY str) dr
  FROM (
    Select per_id, stragg(age||cheveux) str
      From(
        SELECT f.per_id, age, cheveux
          FROM frere f
          order by age, cheveux)
    GROUP BY 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         12 Blond
         1 Pierre            1         15 Brun
         2 Paul              1         15 Brun
         2 Paul              1         12 Blond
         3 Jacques           2         12 Blond
         3 Jacques           2         17 Chatain
         4 Pascal            3         12 Blond
         4 Pascal            3         17 Chatain
         4 Pascal            3         18 Chatain

07/03/2008, 13h49
Michel SALAIS

Citation:

Envoyé par mnitu

Michel, pourquoi veut tu me casser la baraque ?

Ah! Je jure que tout ce que je cherche est d'avoir une baraque plus solide :roll:
07/03/2008, 14h10
mnitu

Citation:

Envoyé par Michel SALAIS

Ah! Je jure que tout ce que je cherche est d'avoir une baraque plus solide :roll:

Merci Michel. C'était une blague :D
07/03/2008, 14h15
Michel SALAIS

Citation:

Envoyé par mnitu

Merci Michel. C'était une blague :D

tu n'as pas vu la tête d'ange que j'avais ?

Et pour s'amuser, voici encore une solution qui utilise une requête hiérarchique

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
with t as
(
  select f.*,
         row_number() over(partition by per_id order by age, cheveux) rn
  from frere f
),
t1 as
(
  select per_id, sys_connect_by_path(age || cheveux, ',') str
  from t
  start with rn = 1
  connect by per_id = prior per_id
   and rn = prior rn + 1
),
t2 as
(
  select per_id, max(str)  str
  from t1
  group by per_id
)
select
       p.id, p.nom,
       dense_rank() over (order by str) group_id,
       f.age, f.cheveux
from personne p, t2, frere f
where f.per_id = p.id
  and p.id = t2.per_id

12/03/2008, 14h42
pdelorme

merci à tous les deux pour votre bataille acharné pour répondre à ma question...
joli travail...
je vais maintenant essayer d'appliquer vos solutions à mon domaine fonctionnels... (qui n'a malheureusement rien à voir avec des histoire de famille)

P.