Discussion :

[zptip]

Bonjour,

Je dois détecter des ruptures entre plusieurs ensembles. Dans une table, j'ai des ID qui peuvent appartenir à 2 types de groupe. Un ensemble est constitué par tous les ID qui partagent des valeurs sur l'un ou l'autre des groupes.

Un petit exemple pour être clair:

ID GROUPE_1 GROUPE_2
-------------------------------
1 100 1001
2 200 1002
3 200 1001
4 300 1002
5 400 1003
6 400 1004
7 500 1004

L'ID 1 et l'ID 3 sont du même ensemble (valeur 1001 sur groupe_2).
L'ID 2 appartient aussi à l'ensemble (valeur 200 sur groupe_1 avec l'ID3).
L'ID 4 appartient aussi à l'ensemble (valeur 1002 sur groupe_2 avec l'ID2).
Par contre ID 5, 6 et 7 appartiennent à un autre ensemble.

Je pensais utiliser un CONNECT BY mais je boucle systématiquement. Quelqu'un peut-il m'aider ?

Je suis sous Oracle 9.2 et travaille en PL/SQL.

Merci d'avance.

[lalystar]

Bonjour,


En PL/SQL, que penses tu de ca :

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
 
create table test_laly(id number, grp1 number, grp2 number);
 
insert into test_laly values(1,100,1001);
insert into test_laly values(2,200,1002);
insert into test_laly values(3,200,1001);
insert into test_laly values(4,300,1002);
insert into test_laly values(5,400,1003);
insert into test_laly values(6,400,1004);
insert into test_laly values(7,500,1004);
 
commit;
 
select * 
from   test_laly
start with grp1 = 100
connect by grp1 = prior grp1 or grp2 = prior grp2;
 
 
create global temporary table groupe(id number, grp1 number, grp2 number) on commit preserve rows;
 
create or replace procedure p_getGroupe(p_id number) as
begin
   delete Groupe;
 
   insert into groupe select * from test_laly where id = p_id;
 
   loop
      insert into groupe 
	  select *
	  from   TEST_LALY a
	  where  not exists (
	            select   1
				from	 GROUPE b
				where    b.id = a.id 
			 ) and
			 exists (
			    select   1
				from	 GROUPE b
				where	 b.grp1 = a.grp1 or 
						 b.grp2 = a.grp2
			 );
 
     exit when sql%rowcount = 0;
   end loop;	 			   
end;
/
 
exec p_getGroupe(1);
 
select * from Groupe;

Code :

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

1
2
3
4
5
6
7
8
9
 
tauexplo@TAUD02> select * from Groupe;
 
        ID       GRP1       GRP2
---------- ---------- ----------
         1        100       1001
         3        200       1001
         2        200       1002
         4        300       1002

En 10g, il y a une clause qui te permet d'éviter les boucles pour les CONNECT BY.


Laly.

[zptip]

Laly, merci de ta réponse.

Alors je n'ai qu'à passer en 10g pour éviter le bouclage

Je crois que je vais m'orienter vers ta proposition de PL. Etant donné le volume de données à traiter, j'aurai préféré utiliser un SQL en espérant diminuer la complexité mais je ne vois pas d'autres solutions.

Merci.

[plabrevo]

Une autre facon pour le faire en seule passe sous SQL directement est la suivante (en remplacant <numero de groupe> comme dans le precedent exemple):

Code :

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

1
2
3
4
5
 
SELECT * 
FROM   test_laly    a
WHERE  my_package.my_function(<numero de groupe>,a.grp1,a.grp2) = 1
/

En utilisant un package stocke dont une fonction effectuera une evaluation ligne par ligne de maniere a savoir si la ligne est candidate pour le groupe en question. Pour des raisons de perfs, le groupe complet est stocke en memoire durant le premier appel.

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
 
DROP PACKAGE my_package
/
DROP TYPE my_array_type
/
DROP TABLE test_laly
/
CREATE TABLE test_laly(id number, grp1 number, grp2 number);
 
INSERT INTO test_laly values(1,100,1001);
INSERT INTO test_laly values(2,200,1002);
INSERT INTO test_laly values(3,200,1001);
INSERT INTO test_laly values(4,300,1002);
INSERT INTO test_laly values(5,400,1003);
INSERT INTO test_laly values(6,400,1004);
INSERT INTO test_laly values(7,500,1004);
 
COMMIT;
 
CREATE OR REPLACE TYPE my_type AS 
OBJECT
(id             INTEGER
,grp1           INTEGER
,grp2           INTEGER)
/
CREATE TYPE my_array_type AS TABLE OF my_type
/
CREATE OR REPLACE PACKAGE my_package AS
/* $Header: $ */
/*======================================================================+
|                                                                       |
+======================================================================*/
TYPE t_array_integer          IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
 
g_cached_id_breakup              INTEGER;
 
g_cached_array                   my_array_type;
 
/*======================================================================+
|                                                                       |
+======================================================================*/
FUNCTION my_function 
(p_id_breakup                    INTEGER
,p_grp1                          INTEGER
,p_grp2                          INTEGER) 
RETURN INTEGER; 
 
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package  AS
/* $Header: $ */
/*======================================================================+
|                                                                       |
+======================================================================*/
 
/*======================================================================+
|                                                                       |
+======================================================================*/
PROCEDURE get_all
(p_id1                           INTEGER
,p_id2                           INTEGER
,o_array_id                  OUT t_array_integer
,o_array_grp1                OUT t_array_integer
,o_array_grp2                OUT t_array_integer)
 
IS
 
BEGIN
SELECT id
      ,grp1
      ,grp2
BULK COLLECT
INTO   o_array_id
      ,o_array_grp1
      ,o_array_grp2
FROM   test_laly
WHERE  grp1   = p_id1
   OR  grp2   = p_id2
MINUS
SELECT id
      ,grp1
      ,grp2
FROM   TABLE(CAST(g_cached_array AS my_array_type)) 
;
 
 
END get_all;
 
/*======================================================================+
|                                                                       |
+======================================================================*/
PROCEDURE rebuild_cache
(p_id_breakup                    INTEGER)
 
IS
 
i                                INTEGER;
l_new_array_id                   t_array_integer;
l_new_array_grp1                 t_array_integer;
l_new_array_grp2                 t_array_integer;
 
BEGIN
g_cached_array := my_array_type(NULL);
g_cached_array(1):= my_type(NULL,NULL,NULL);
 
SELECT id
      ,grp1
      ,grp2
INTO   g_cached_array(1).id
      ,g_cached_array(1).grp1
      ,g_cached_array(1).grp2
FROM   test_laly
WHERE  id = p_id_breakup
;
 
i := 0;
 
WHILE 1=1 LOOP
  i := i + 1;
 
  IF i > g_cached_array.COUNT THEN
    EXIT;
  END IF;
 
  get_all
    (p_id1        => g_cached_array(i).grp1
    ,p_id2        => g_cached_array(i).grp2
    ,o_array_id   => l_new_array_id
    ,o_array_grp1 => l_new_array_grp1
    ,o_array_grp2 => l_new_array_grp2);
 
  FOR j IN 1..l_new_array_id.COUNT LOOP
    g_cached_array.EXTEND(1);
    g_cached_array(g_cached_array.COUNT)      := my_type(NULL,NULL,NULL);
    g_cached_array(g_cached_array.COUNT).id   := l_new_array_id(j);
    g_cached_array(g_cached_array.COUNT).grp1 := l_new_array_grp1(j);
    g_cached_array(g_cached_array.COUNT).grp2 := l_new_array_grp2(j);
 
  END LOOP;
 
END LOOP;
END rebuild_cache;
 
/*======================================================================+
|                                                                       |
+======================================================================*/
FUNCTION my_function 
(p_id_breakup                    INTEGER
,p_grp1                          INTEGER
,p_grp2                          INTEGER) 
RETURN INTEGER 
 
IS
 
l_found                          BOOLEAN;
 
BEGIN 
IF p_id_breakup            <> g_cached_id_breakup
OR g_cached_id_breakup     IS NULL       THEN
 
  rebuild_cache
   (p_id_breakup => p_id_breakup);
 
  g_cached_id_breakup := p_id_breakup;
 
END IF;
 
l_found := FALSE;
 
FOR i IN 1..g_cached_array.COUNT LOOP
 
  IF g_cached_array(i).grp1 = p_grp1 
  OR g_cached_array(i).grp2 = p_grp2 THEN
 
    l_found := TRUE;
    EXIT;
 
  END IF;
END LOOP;
 
IF l_found THEN
  RETURN 1;
ELSE
  RETURN 0;
END IF;
END my_function ;
 
END my_package;
/
SELECT * 
FROM   test_laly    a
WHERE  my_package.my_function(1,a.grp1,a.grp2) = 1
/
SELECT * 
FROM   test_laly    a
WHERE  my_package.my_function(5,a.grp1,a.grp2) = 1
/
SELECT * 
FROM   test_laly    a
WHERE  my_package.my_function(2,a.grp1,a.grp2) = 1
/

Have fun.

[zptip]

Ces 2 solutions conduisent au même résultat. J'ai opté pour la solution de Plabrevo qui, sur ma base, était un peu plus rapide (en attendant que mon cher DBA passe en 10g).

Merci à tous les 2 pour votre aide précieuse et bonne année 2006 !