Requête récursive Oracle 11g

Version imprimable

Bonjour à tous,

Je souhaiterais s'il vous plaît avoir vos avis et vos lumières sur une requête assez complexe, du moins pour moi.
Merci déjà aux personnes qui auront la patience de me lire, je m'aperçois après relecture que c'est assez touffu!

L'environnement est Oracle 11gR2. Je ne peux pas modifier le modèle de données, une appli de saisie/consultation se base directement dessus (pas de vues intermédiaires), donc je peux seulement ajouter des tables au besoin. (ce point reste peut être négociable avec l'appli si la modif est minime et vraiment bénéfique)

Je vais tacher d'expliquer brievement la problématique, en espérant être assez clair.
Je m'excuse de ne pas présenter tel quel le modèle, comme pour à peu près tout le monde en entreprise, ça m'est interdit.
Ceci dit je crois que c'est pas plus mal pour la compréhension :mrgreen:

Départements :
t_departement (departement_id, nom_departement, activite_departement, site_departement)

Projets :
Un projet appartient à un seul département et peut être composé de sous-projets (auto-référencement).
On peut en théorie avoir jusqu'à 5 niveaux de sous-projet. Un projet principal a un parent_id null.
t_projet (projet_id, parent_projet_id, departement_id, reference_projet, code_projet, projet_type1_id, projet_type2_id, projet_type3_id)

Un projet est d'un certain type (qui va lui donner quelques attributs spécifiques), on a une sorte d'héritage mal modélisé (sur les trois clé étrangères dans t_projet deux seront forcément non renseignées, et un type_projet ne peut être référencé qu'une seule fois :/ )
t_projet_type1 (projet_type1_id, nom_projet_type1, ...)
t_projet_type2 (projet_type2_id, nom_projet_type2, ...)
t_projet_type3 (projet_type3_id, nom_projet_type3, ...)

Chaque projet_type a 0-n contrats rattachés, qui correspondent à un montant sur une période (qui peut être passée ou future) :
t_contrat_type1 (contrat_type1_id, projet_type1_id, debut_contrat_type1, fin_contrat_type1, montant_contrat_type1)
t_contrat_type2 (contrat_type2_id, projet_type2_id, debut_contrat_type2, fin_contrat_type2, montant_contrat_type2)
t_contrat_type3 (contrat_type3_id, projet_type3_id, debut_contrat_type3, fin_contrat_type3, montant_contrat_type3)

Chaque projet ou sous projet a un budget sur une période, les périodes pour un projet_id ne peuvent pas se chevaucher :
t_budget (t_budget_id, projet_id, debut_budget, fin_budget, montant_budget)

Une table calendrier (simplifiée) :
t_calendrier (jour)

On souhaite pouvoir fournir les indicateurs suivants à une date d donnée (présente, passée ou future) :
1) i1 : Dépense courante d'un projet ou sous-projet : c'est la somme des montant_contrat qui lui sont rattachés et qui sont actifs à cette date (debut_contrat <= d < fin_contrat)
2) i2 : Budget restant pour un projet : c'est la différence entre le montant_budget (actif à la date d) qui lui est rattaché et la dépense courante. Pour un sous-projet, ce sera le minimum entre son budget restant et celui de ses projets parents.
3) i3 : Dépense courante d'un projet ou sous-projet, sous projets inclus : c'est la somme des montant_contrat qui lui sont rattachés, ou qui sont rattachés à l'un de ces sous-projet, et qui sont actifs à cette date (debut_contrat <= d < fin_contrat)
4) i4 : Budget restant pour un projet, sous-projets inclus : c'est la différence entre le montant_budget qui lui est rattaché et la dépense courante de lui et tous ses sous-projets. Pour un sous-projet, ce sera le min entre son budget restant et celui de ses predecesseurs.

L'utilisateur doit saisir au minimum une date (ou une période, auquel cas on lui renverra la valeur pour chaque jour de cette période). Il peut aussi filtrer sur un pays, un département, un projet, une activité... classique quoi.
L'outil de restitution utilisé est BO.

Donc l'idée serait de sortir une (ou plusieurs) vue côté Oracle avec la structure suivante :
v_indicateurs (projet_id, jour, i1, i2, i3, i4)

Les volumétries à terme sont de l'ordre de 10 000 projets et 40 000 sous projets. En moyenne 15 contrats par projet.
Stocker tous les résultats à l'avance dans un modèle type datawarehouse parait compliqué, car les données sont susceptibles de changer tous les jours, il faudrait donc recalculer chaque jour tous les indicateurs pour tous les projets et toutes les dates du calendrier...
Je penche plutôt pour du calcul à la volée, mais j'ai quelques inquiétudes côté performances.

Suis-je passé à côté de quelque chose, auriez vous des idées quant à la solution à adopter?
Voici un début de requête, où i2 est incomplet car on doit pouvoir le comparer avec celui de ces predecesseur et ne garder que le plus petit. Sans parler de i3 et i4. Avez-vous une piste pour ce faire svp? Je me penche sur la récursivité, mais je ne suis pas très à l'aise avec et ne sais pas si j'arriverai à mes fins.
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 SELECT p.projet_id, cal.jour, sum(coalesce(ct1.montant_contrat1, ct2.montant_contrat2, ct3.montant_contrat3,0)) AS i1, avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat1, ct2.montant_contrat2, ct3.montant_contrat3,0)) AS i2 FROM t_projet p CROSS JOIN t_calendrier cal LEFT JOIN t_budget b ON b.projet_id = p.projet_id AND cal.jour BETWEEN b.debut_budget AND b.fin_budget LEFT JOIN t_projet_type1 pt1 JOIN t_contrat_type1 ct1 ON pt1.projet_type1_id = ct1.projet_type1_id ON pt1.projet_type1_id = p.projet_type1_id AND cal.jour >= ct1.debut_contrat1 AND cal.jour < ct1.fin_contrat1 LEFT JOIN t_projet_type2 pt2 JOIN t_contrat_type2 ct2 ON pt2.projet_type2_id = ct2.projet_type2_id ON pt2.projet_type2_id = p.projet_type2_id AND cal.jour >= ct2.debut_contrat2 AND cal.jour < ct2.fin_contrat2 LEFT JOIN t_projet_type3 pt3 JOIN t_contrat_type3 ct3 ON pt3.projet_type3_id = ct3.projet_type3_id ON pt3.projet_type3_id = p.projet_type3_id AND cal.jour >= ct3.debut_contrat1 AND cal.jour < ct3.fin_contrat1 -- Version avec UNION SELECT p.projet_id, cal.jour, sum(coalesce(ct1.montant_contrat1,0)) AS i1, avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat1,0)) AS i2 FROM t_projet p CROSS JOIN t_calendrier cal LEFT JOIN t_budget b ON b.projet_id = p.projet_id AND cal.jour BETWEEN b.debut_budget AND b.fin_budget JOIN t_projet_type1 pt1 ON pt1.projet_type1_id = p.projet_type1_id LEFT JOIN t_contrat_type1 ct1 ON pt1.projet_type1_id = ct1.projet_type1_id AND cal.jour >= ct1.debut_contrat1 AND cal.jour < ct1.fin_contrat1 UNION ALL SELECT p.projet_id, cal.jour, sum(coalesce(ct2.montant_contrat2,0)) AS i1, avg(coalesce(b.montant_budget,0))-sum(coalesce(ct2.montant_contrat2,0)) AS i2 FROM t_projet p CROSS JOIN t_calendrier cal LEFT JOIN t_budget b ON b.projet_id = p.projet_id AND cal.jour BETWEEN b.debut_budget AND b.fin_budget JOIN t_projet_type2 pt2 ON pt2.projet_type2_id = p.projet_type2_id LEFT JOIN t_contrat_type2 ct2 ON pt2.projet_type2_id = ct2.projet_type2_id AND cal.jour >= ct2.debut_contrat1 AND cal.jour < ct2.fin_contrat1 UNION ALL SELECT p.projet_id, cal.jour, sum(coalesce(ct3.montant_contrat3,0)) AS i1, avg(coalesce(b.montant_budget,0))-sum(coalesce(ct3.montant_contrat3,0)) AS i2 FROM t_projet p CROSS JOIN t_calendrier cal LEFT JOIN t_budget b ON b.projet_id = p.projet_id AND cal.jour BETWEEN b.debut_budget AND b.fin_budget JOIN t_projet_type3 pt3 ON pt3.projet_type3_id = p.projet_type3_id LEFT JOIN t_contrat_type3 ct3 ON pt3.projet_type3_id = ct3.projet_type3_id AND cal.jour >= ct3.debut_contrat1 AND cal.jour < ct3.fin_contrat1
Si y'a besoin de précisions supplémentaires n'hésitez pas, et merci à toute personne qui saura me donner un coup de main!

22/01/2013, 18h49
Waldar

Un petit jeu de données ce serait super !

Bonjour,

Voici un jeu de données, j'ai enlevé le type3 qui n'apporte rien à la logique :

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
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
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
-- creation tables
CREATE TABLE t_departement
(
  departement_id number,
  nom_departement varchar2(32),
  activite_departement number,
  site_departement number,
  constraints pk_departement primary key (departement_id)
);
 
CREATE TABLE t_projet_type1
(
  projet_type1_id number,
  nom_projet_type1 varchar2(32),
  constraints pk_projet_type1 primary key (projet_type1_id)
);
 
CREATE TABLE t_projet_type2
(
  projet_type2_id number,
  nom_projet_type2 varchar2(32),
  constraints pk_projet_type2 primary key (projet_type2_id)
);
 
CREATE TABLE t_contrat_type1
(
  contrat_type1_id number,
  projet_type1_id number,
  debut_contrat_type1 date,
  fin_contrat_type1 date,
  montant_contrat_type1 number,
  constraints pk_contrat_type1 foreign key (projet_type1_id) references t_projet_type1 (projet_type1_id)
);
 
CREATE TABLE t_contrat_type2
(
  contrat_type2_id number,
  projet_type2_id number,
  debut_contrat_type2 date,
  fin_contrat_type2 date,
  montant_contrat_type2 number,
  constraints pk_contrat_type2 foreign key (projet_type2_id) references t_projet_type2 (projet_type2_id)
);   
 
CREATE TABLE t_projet
(
  projet_id number,
  parent_projet_id number,
  departement_id number,
  reference_projet varchar2(32),
  code_projet varchar2(32),
  projet_type1_id number,
  projet_type2_id number,
  constraints pk_projet primary key (projet_id),
  constraints fk_departement foreign key (departement_id) references t_departement (departement_id),
  constraints fk_projet_type1 foreign key (projet_type1_id) references t_projet_type1 (projet_type1_id),
  constraints fk_projet_type2 foreign key (projet_type2_id) references t_projet_type2 (projet_type2_id)
);
 
 
CREATE TABLE t_budget
(
  budget_id number,
  projet_id number,
  debut_budget date,
  fin_budget date,
  montant_budget number,
  constraints pk_budget primary key (budget_id),
  constraints fk_projet foreign key (projet_id) references t_projet (projet_id)
);
 
CREATE TABLE t_calendrier
(
  jour date,
  CONSTRAINT pk_calendrier PRIMARY KEY (JOUR)
);
 
 
-- insertion donnees test
 
INSERT INTO t_calendrier
(
SELECT 
  TRUNC(SYSDATE - ROWNUM,'DD') AS jour
FROM all_objects 
WHERE TRUNC(SYSDATE - ROWNUM,'DD')>= TO_DATE('20110101','YYYYMMDD')
UNION ALL
SELECT 
  TRUNC(SYSDATE + ROWNUM-1,'DD') AS jour
FROM all_objects 
WHERE TRUNC(SYSDATE + ROWNUM-1,'DD')<= TO_DATE('20140101','YYYYMMDD')
);
 
 
-- dep
insert into t_departement values (1,'OGD',1,3);
insert into t_departement values (2,'DSP',1,1);
insert into t_departement values (3,'CS-PIT',2,3);
 
-- proj type1
insert into t_projet_type1 values (1,'projet type1 - 1');
insert into t_projet_type1 values (2,'projet type1 - 2');
insert into t_projet_type1 values (3,'projet type1 - 3');
insert into t_projet_type1 values (4,'projet type1 - 4');
insert into t_projet_type1 values (5,'projet type1 - 5');
insert into t_projet_type1 values (6,'projet type1 - 6');
insert into t_projet_type1 values (7,'projet type1 - 7');
insert into t_projet_type1 values (8,'projet type1 - 8');
 
-- proj type2
insert into t_projet_type2 values (1,'projet type2 - 1');
insert into t_projet_type2 values (2,'projet type2 - 2');
insert into t_projet_type2 values (3,'projet type2 - 3');
insert into t_projet_type2 values (4,'projet type2 - 4');
insert into t_projet_type2 values (5,'projet type2 - 5');
insert into t_projet_type2 values (6,'projet type2 - 6');
 
 
-- projet
insert into t_projet values (1,null,2,'122C','1',1,null);
insert into t_projet values (2,1,2,'122C-1','1.1',null,1);
insert into t_projet values (3,1,2,'122C-2','1.2',2,null);
insert into t_projet values (4,3,2,'122C-21','1.2.1',3,null);
 
insert into t_projet values (5,null,2,'233D','2',null,2);
insert into t_projet values (6,5,2,'233D-1','2.1',null,3);
insert into t_projet values (7,6,2,'233D-11','2.1.1',4,null);
insert into t_projet values (8,7,2,'233D-111','2.1.1.1',null,4);
insert into t_projet values (9,7,2,'233D-112','2.1.1.2',5,null);
insert into t_projet values (10,5,2,'233D-2','2.2',null,5);
insert into t_projet values (11,10,2,'233D-21','2.2.1',null,6);
 
insert into t_projet values (12,null,1,'344E','1',6,null);
insert into t_projet values (13,12,1,'344E-1','1.1',7,null);
insert into t_projet values (14,13,1,'344E-11','1.1.1',8,null);
 
 
-- budget
insert into t_budget values (1,1,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/04/2012','dd/mm/yyyy'),100000);
insert into t_budget values (2,1,to_date('16/04/2012','dd/mm/yyyy'),to_date('01/12/2012','dd/mm/yyyy'),120000);
insert into t_budget values (3,1,to_date('02/12/2012','dd/mm/yyyy'),to_date('01/04/2013','dd/mm/yyyy'),100000);
insert into t_budget values (4,1,to_date('02/04/2013','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),90000);
 
insert into t_budget values (5,2,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/04/2012','dd/mm/yyyy'),20000);
insert into t_budget values (6,2,to_date('16/04/2012','dd/mm/yyyy'),to_date('01/12/2012','dd/mm/yyyy'),12000);
insert into t_budget values (7,2,to_date('02/12/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),10000);
 
insert into t_budget values (8,3,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/04/2012','dd/mm/yyyy'),40000);
insert into t_budget values (9,3,to_date('16/04/2012','dd/mm/yyyy'),to_date('01/12/2012','dd/mm/yyyy'),32000);
insert into t_budget values (10,3,to_date('02/12/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),40000);
 
insert into t_budget values (11,4,to_date('01/01/2012','dd/mm/yyyy'),to_date('11/05/2012','dd/mm/yyyy'),30000);
insert into t_budget values (12,4,to_date('12/05/2012','dd/mm/yyyy'),to_date('01/02/2013','dd/mm/yyyy'),32000);
insert into t_budget values (13,4,to_date('02/02/2013','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),30000);
 
 
insert into t_budget values (14,5,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/02/2012','dd/mm/yyyy'),200000);
insert into t_budget values (15,5,to_date('16/02/2012','dd/mm/yyyy'),to_date('01/10/2012','dd/mm/yyyy'),220000);
insert into t_budget values (16,5,to_date('02/10/2012','dd/mm/yyyy'),to_date('01/05/2013','dd/mm/yyyy'),180000);
insert into t_budget values (17,5,to_date('02/05/2013','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),150000);
 
insert into t_budget values (18,6,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/03/2012','dd/mm/yyyy'),80000);
insert into t_budget values (19,6,to_date('16/03/2012','dd/mm/yyyy'),to_date('01/10/2012','dd/mm/yyyy'),82000);
insert into t_budget values (20,6,to_date('02/10/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),75000);
 
insert into t_budget values (21,7,to_date('01/01/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),25000);
 
insert into t_budget values (22,8,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/09/2012','dd/mm/yyyy'),8000);
insert into t_budget values (23,8,to_date('16/09/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),6000);
 
insert into t_budget values (24,9,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/09/2012','dd/mm/yyyy'),10000);
insert into t_budget values (25,9,to_date('16/09/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),9000);
 
insert into t_budget values (26,10,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/07/2012','dd/mm/yyyy'),40000);
insert into t_budget values (27,10,to_date('16/07/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),30000);
 
insert into t_budget values (28,11,to_date('01/01/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),25000);
 
 
insert into t_budget values (29,12,to_date('01/01/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),100000);
 
insert into t_budget values (30,13,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/05/2012','dd/mm/yyyy'),60000);
insert into t_budget values (31,13,to_date('16/05/2012','dd/mm/yyyy'),to_date('01/10/2012','dd/mm/yyyy'),68000);
insert into t_budget values (32,13,to_date('02/10/2012','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),65000);
 
insert into t_budget values (33,14,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/08/2012','dd/mm/yyyy'),30000);
insert into t_budget values (34,14,to_date('16/08/2012','dd/mm/yyyy'),to_date('01/01/2013','dd/mm/yyyy'),32000);
insert into t_budget values (35,14,to_date('02/01/2013','dd/mm/yyyy'),to_date('31/12/2013','dd/mm/yyyy'),35000);
 
 
-- contrat t1
insert into t_contrat_type1 values (1,1,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/06/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type1 values (2,1,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/05/2012','dd/mm/yyyy'),5000);
insert into t_contrat_type1 values (3,1,to_date('01/09/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (4,1,to_date('01/07/2012','dd/mm/yyyy'),to_date('18/08/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type1 values (5,1,to_date('01/01/2013','dd/mm/yyyy'),to_date('25/03/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type1 values (6,1,to_date('01/02/2013','dd/mm/yyyy'),to_date('15/05/2013','dd/mm/yyyy'),6000);
insert into t_contrat_type1 values (7,1,to_date('01/02/2013','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),3000);
insert into t_contrat_type1 values (8,1,to_date('01/06/2013','dd/mm/yyyy'),to_date('25/09/2013','dd/mm/yyyy'),2000);
insert into t_contrat_type1 values (9,1,to_date('01/05/2013','dd/mm/yyyy'),to_date('26/11/2013','dd/mm/yyyy'),5000);
 
insert into t_contrat_type1 values (10,2,to_date('01/04/2012','dd/mm/yyyy'),to_date('15/12/2012','dd/mm/yyyy'),7000);
insert into t_contrat_type1 values (11,2,to_date('01/07/2012','dd/mm/yyyy'),to_date('15/05/2013','dd/mm/yyyy'),3000);
insert into t_contrat_type1 values (12,2,to_date('01/09/2012','dd/mm/yyyy'),to_date('07/11/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type1 values (13,2,to_date('01/02/2013','dd/mm/yyyy'),to_date('15/05/2013','dd/mm/yyyy'),6000);
 
insert into t_contrat_type1 values (14,3,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/06/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (15,3,to_date('01/03/2012','dd/mm/yyyy'),to_date('22/08/2012','dd/mm/yyyy'),2000);
insert into t_contrat_type1 values (16,3,to_date('01/08/2012','dd/mm/yyyy'),to_date('07/03/2013','dd/mm/yyyy'),10000);
insert into t_contrat_type1 values (17,3,to_date('01/10/2012','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),5000);
 
insert into t_contrat_type1 values (18,4,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/07/2012','dd/mm/yyyy'),5000);
insert into t_contrat_type1 values (19,4,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/05/2012','dd/mm/yyyy'),6000);
insert into t_contrat_type1 values (20,4,to_date('01/06/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (21,4,to_date('01/01/2013','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (22,4,to_date('01/04/2013','dd/mm/yyyy'),to_date('07/11/2013','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (23,4,to_date('10/05/2012','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),8000);
 
insert into t_contrat_type1 values (24,5,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/07/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type1 values (25,5,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/09/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (26,5,to_date('01/06/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),7000);
insert into t_contrat_type1 values (27,5,to_date('01/04/2012','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),3000);
insert into t_contrat_type1 values (28,5,to_date('01/04/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),2000);
insert into t_contrat_type1 values (29,5,to_date('10/06/2012','dd/mm/yyyy'),to_date('18/10/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (30,5,to_date('01/10/2012','dd/mm/yyyy'),to_date('15/07/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type1 values (31,5,to_date('01/03/2013','dd/mm/yyyy'),to_date('15/05/2013','dd/mm/yyyy'),6000);
insert into t_contrat_type1 values (32,5,to_date('01/06/2013','dd/mm/yyyy'),to_date('07/11/2013','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (33,5,to_date('01/01/2013','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),9000);
insert into t_contrat_type1 values (34,5,to_date('01/04/2013','dd/mm/yyyy'),to_date('07/11/2013','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (35,5,to_date('10/05/2012','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),8000);
 
insert into t_contrat_type1 values (36,6,to_date('01/01/2012','dd/mm/yyyy'),to_date('15/06/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (37,6,to_date('01/03/2012','dd/mm/yyyy'),to_date('22/08/2012','dd/mm/yyyy'),2000);
insert into t_contrat_type1 values (38,6,to_date('01/05/2012','dd/mm/yyyy'),to_date('07/03/2013','dd/mm/yyyy'),6000);
insert into t_contrat_type1 values (39,6,to_date('01/09/2012','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),5000);
 
insert into t_contrat_type1 values (40,7,to_date('01/05/2012','dd/mm/yyyy'),to_date('15/12/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (41,7,to_date('01/03/2012','dd/mm/yyyy'),to_date('22/10/2012','dd/mm/yyyy'),2000);
insert into t_contrat_type1 values (42,7,to_date('01/07/2012','dd/mm/yyyy'),to_date('07/03/2013','dd/mm/yyyy'),7000);
insert into t_contrat_type1 values (43,7,to_date('01/10/2012','dd/mm/yyyy'),to_date('18/08/2013','dd/mm/yyyy'),5000);
 
insert into t_contrat_type1 values (44,8,to_date('01/05/2012','dd/mm/yyyy'),to_date('15/12/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (45,8,to_date('01/03/2012','dd/mm/yyyy'),to_date('22/10/2012','dd/mm/yyyy'),2000);
insert into t_contrat_type1 values (46,8,to_date('01/07/2012','dd/mm/yyyy'),to_date('07/04/2013','dd/mm/yyyy'),7000);
insert into t_contrat_type1 values (47,8,to_date('01/10/2012','dd/mm/yyyy'),to_date('18/09/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type1 values (48,8,to_date('01/05/2012','dd/mm/yyyy'),to_date('15/10/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type1 values (49,8,to_date('01/03/2012','dd/mm/yyyy'),to_date('22/09/2012','dd/mm/yyyy'),2000);
 
-- contrat t2
insert into t_contrat_type2 values (1,1,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/07/2012','dd/mm/yyyy'),2000);
insert into t_contrat_type2 values (2,1,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/05/2012','dd/mm/yyyy'),5000);
insert into t_contrat_type2 values (3,1,to_date('01/09/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type2 values (4,1,to_date('01/07/2012','dd/mm/yyyy'),to_date('18/08/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type2 values (5,1,to_date('01/01/2013','dd/mm/yyyy'),to_date('25/03/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type2 values (6,1,to_date('01/02/2013','dd/mm/yyyy'),to_date('15/10/2013','dd/mm/yyyy'),6000);
 
insert into t_contrat_type2 values (7,2,to_date('01/09/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type2 values (8,2,to_date('01/07/2012','dd/mm/yyyy'),to_date('18/08/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type2 values (9,2,to_date('01/01/2013','dd/mm/yyyy'),to_date('25/03/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type2 values (10,2,to_date('01/02/2013','dd/mm/yyyy'),to_date('15/10/2013','dd/mm/yyyy'),6000);
insert into t_contrat_type2 values (11,2,to_date('01/02/2013','dd/mm/yyyy'),to_date('15/10/2013','dd/mm/yyyy'),4000);
 
insert into t_contrat_type2 values (12,3,to_date('01/01/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type2 values (13,3,to_date('01/03/2012','dd/mm/yyyy'),to_date('18/08/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type2 values (14,3,to_date('01/01/2013','dd/mm/yyyy'),to_date('25/03/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type2 values (15,3,to_date('01/02/2013','dd/mm/yyyy'),to_date('15/10/2013','dd/mm/yyyy'),6000);
 
insert into t_contrat_type2 values (16,4,to_date('01/02/2012','dd/mm/yyyy'),to_date('15/07/2012','dd/mm/yyyy'),5000);
insert into t_contrat_type2 values (17,4,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/05/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type2 values (18,4,to_date('01/09/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type2 values (19,4,to_date('01/07/2012','dd/mm/yyyy'),to_date('18/08/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type2 values (20,4,to_date('01/01/2013','dd/mm/yyyy'),to_date('25/03/2013','dd/mm/yyyy'),2000);
insert into t_contrat_type2 values (21,4,to_date('01/02/2013','dd/mm/yyyy'),to_date('15/10/2013','dd/mm/yyyy'),9000);
 
insert into t_contrat_type2 values (22,5,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/07/2012','dd/mm/yyyy'),2000);
insert into t_contrat_type2 values (23,5,to_date('01/03/2012','dd/mm/yyyy'),to_date('15/05/2012','dd/mm/yyyy'),5000);
insert into t_contrat_type2 values (24,5,to_date('01/09/2012','dd/mm/yyyy'),to_date('07/11/2012','dd/mm/yyyy'),4000);
insert into t_contrat_type2 values (25,5,to_date('01/07/2012','dd/mm/yyyy'),to_date('18/08/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type2 values (26,5,to_date('01/01/2013','dd/mm/yyyy'),to_date('25/03/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type2 values (27,5,to_date('01/02/2013','dd/mm/yyyy'),to_date('15/10/2013','dd/mm/yyyy'),6000);
insert into t_contrat_type2 values (28,5,to_date('01/07/2012','dd/mm/yyyy'),to_date('18/08/2012','dd/mm/yyyy'),3000);
insert into t_contrat_type2 values (29,5,to_date('01/03/2013','dd/mm/yyyy'),to_date('25/05/2013','dd/mm/yyyy'),5000);
insert into t_contrat_type2 values (30,5,to_date('01/04/2013','dd/mm/yyyy'),to_date('15/11/2013','dd/mm/yyyy'),6000);

Et la requête modifiée en conséquence :

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
SELECT 
      p.projet_id,
      cal.jour,
      sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i1,
      avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i2,
      count(ct1.contrat_type1_id) + count(ct2.contrat_type2_id) AS contrat_actifs
FROM t_projet p
CROSS JOIN t_calendrier cal
LEFT JOIN t_budget b
     ON b.projet_id = p.projet_id
     AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
LEFT JOIN t_projet_type1 pt1
     JOIN t_contrat_type1 ct1
          ON pt1.projet_type1_id = ct1.projet_type1_id
  ON pt1.projet_type1_id = p.projet_type1_id
  AND cal.jour >= ct1.debut_contrat_type1 AND cal.jour < ct1.fin_contrat_type1
LEFT JOIN t_projet_type2 pt2
     JOIN t_contrat_type2 ct2
          ON pt2.projet_type2_id = ct2.projet_type2_id
  ON pt2.projet_type2_id = p.projet_type2_id
  AND cal.jour >= ct2.debut_contrat_type2 AND cal.jour < ct2.fin_contrat_type2
WHERE cal.jour between to_date('01/05/2012','dd/mm/yyyy') and to_date('01/10/2012','dd/mm/yyyy')
GROUP BY 
      p.projet_id,
      cal.jour
ORDER BY 1,2

En espérant n'avoir rien omis, à bientôt!

24/01/2013, 15h13
Snipah

Un petit up pour signaler que la cible est Oracle 11gR2, donc possibilité d'utiliser des CTE récursives si besoin, je vais creuser de ce côté.
Et du coup ce post aurait peut être sa place dans la section SQL, si un modérateur juge le déplacement justifié!

Au passage, j'ai fait un test avec une fonction récursive pour calculer les indicateurs et présenter une première ébauche aux utilisateurs, et sans surprise, 20 minutes pour 8000 projets/sous-projets sur 60 jours, ça rame bien comme il faut :mrgreen:

Je reste à l'écoute de toute suggestion sur la manière d'aborder ce problème.
24/01/2013, 18h55
Waldar

Petite question sur le i2, actuellement l'indicateur ne gère pas le cumul des montants alloués et ça me paraît surprenant.
Est-ce volontaire ?
25/01/2013, 10h12
Snipah

Oui c'est volontaire, c'est bien le budget alloué au projet (actif à la date voulue, il y en a 0 ou 1) moins la somme des contrats rattachés au projet.
C'est vrai que c'était pas très clair dans ma description initiale!

Hello,

Étant incessamment sous peu en congés je n'aurai pas le temps de regarder toute la demande.
Néanmoins voici une piste pour gérer tous les problèmes de récursion.

Vous pourriez ajouter une vue dans le modèle avec le résultat de cette 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
    select connect_by_root projet_id as projet_id_init
         , projet_id
         , level as lvl
         , sys_connect_by_path(projet_id, '/') as chemin
      from t_projet
start with projet_id in (select parent_projet_id from t_projet where parent_projet_id is not null)
connect by parent_projet_id = prior projet_id;
 
PROJET_ID_INIT PROJET_ID LVL CHEMIN
-------------- --------- --- ------
             1         1   1 /1
             1         2   2 /1/2
             1         3   2 /1/3
             1         4   3 /1/3/4
             3         3   1 /3
             3         4   2 /3/4
             5         5   1 /5
             5         6   2 /5/6
             5         7   3 /5/6/7
             5         8   4 /5/6/7/8
             5         9   4 /5/6/7/9
             5        10   2 /5/10
             5        11   3 /5/10/11
             6         6   1 /6
             6         7   2 /6/7
             6         8   3 /6/7/8
             6         9   3 /6/7/9
             7         7   1 /7
             7         8   2 /7/8
             7         9   2 /7/9
            10        10   1 /10
            10        11   2 /10/11
            12        12   1 /12
            12        13   2 /12/13
            12        14   3 /12/13/14
            13        13   1 /13
            13        14   2 /13/14

Avec une jointure bien sentie sur le reste de la requête vous devriez pouvoir rapprocher facilement les montants d'un projet à ses parents.
Compte-tenu que les opérations de récursions sont coûteuses, il vaut mieux le faire sur le moins de lignes possibles.

Bonjour Waldar, et merci de vous être penché sur ma demande.

En partant de votre proposition j'arrive à ceci :

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
CREATE OR REPLACE VIEW projet_tree
AS
    SELECT connect_by_root projet_id AS projet_id_init
         , projet_id
         , projet_type1_id,projet_type2_id
         , level AS lvl
         , sys_connect_by_path(projet_id, '/') AS chemin
      FROM t_projet
connect BY parent_projet_id = prior projet_id
;
 
 
WITH t
AS
(
    SELECT 
          p.projet_id,
          cal.jour,
          sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i1,
          sum(CASE 
                    WHEN pt.projet_id = pt.projet_id_init 
                         THEN coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)
                    ELSE 0
              END
             ) AS i2,
          avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i3,
          avg(coalesce(b.montant_budget,0))
          - sum(CASE 
                    WHEN pt.projet_id = pt.projet_id_init 
                         THEN coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)
                    ELSE 0
              END
             ) AS i4,
          count(ct1.contrat_type1_id) + count(ct2.contrat_type2_id) AS contrat_actifs
    FROM t_projet p
    CROSS JOIN t_calendrier cal
    JOIN projet_tree pt
         ON P.projet_id = pt.projet_id_init
    LEFT JOIN t_budget b
         ON b.projet_id = p.projet_id
         AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
    LEFT JOIN t_projet_type1 pt1
         JOIN t_contrat_type1 ct1
              ON pt1.projet_type1_id = ct1.projet_type1_id
      ON pt1.projet_type1_id = pt.projet_type1_id 
      AND cal.jour >= ct1.debut_contrat_type1 AND cal.jour < ct1.fin_contrat_type1
    LEFT JOIN t_projet_type2 pt2
         JOIN t_contrat_type2 ct2
              ON pt2.projet_type2_id = ct2.projet_type2_id
      ON pt2.projet_type2_id = pt.projet_type2_id
      AND cal.jour >= ct2.debut_contrat_type2 AND cal.jour < ct2.fin_contrat_type2
    GROUP BY 
          p.projet_id,
          cal.jour
) 
SELECT 
    t1.projet_id,
    t1.jour,
    avg(t1.i1) AS i1,
    avg(t1.i2) AS i2,
    avg(t1.i3) AS i3,
    avg(t1.i4) AS i4,
    min(t2.i3) AS i5,
    min(t2.i4) AS i6
FROM t t1
JOIN projet_tree pt
     ON t1.projet_id = pt.projet_id
JOIN t t2
     ON pt.projet_id_init = t2.projet_id
     AND t2.jour = t1.jour
--WHERE t1.jour = to_date('01/04/2013','dd/mm/yyyy')
GROUP BY 
    t1.projet_id,
    t1.jour;

J'ai ôté la clause START WITH de la requête récursive, pour les jointures que j'ai appliquées j'avais besoin de tout l'arbre. Vous auriez fait d'une autre façon?

Au final, on a :
i1) dépenses du projet sous projets inclus
i2) dépenses du projet
i3) budget restant, dépenses des sous projets inclus
i4) budget restant
i5) budget restant, dépenses des sous projets inclus, capé par le i5 des projets parents
i6) budget restant, capé par le i6 des projets parents

La première partie (t dans la clause WITH) calcule les indicateurs i1 à i4.
La seconde (clause SELECT) récupère pour chaque projet les indicateurs des projet parents (et les siens), et prend le min pour calculer i5 et i6.

Une seconde version qui n'effectue qu'une fois le travail de récursivité :

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
WITH tree AS
(
    SELECT connect_by_root projet_id AS projet_id_init
         , projet_id
         , projet_type1_id,projet_type2_id
         , level AS lvl
         , sys_connect_by_path(projet_id, '/') AS chemin
      FROM t_projet
connect BY parent_projet_id = prior projet_id 
),
t AS
(
    SELECT 
          p.projet_id,
          cal.jour,
          sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i1,
          sum(CASE 
                    WHEN pt.projet_id = pt.projet_id_init 
                         THEN coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)
                    ELSE 0
              END
             ) AS i2,
          avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i3,
          avg(coalesce(b.montant_budget,0))
          - sum(CASE 
                    WHEN pt.projet_id = pt.projet_id_init 
                         THEN coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)
                    ELSE 0
              END
             ) AS i4,
          count(ct1.contrat_type1_id) + count(ct2.contrat_type2_id) AS contrat_actifs
    FROM t_projet p
    CROSS JOIN t_calendrier cal
    JOIN tree pt
         ON P.projet_id = pt.projet_id_init
    LEFT JOIN t_budget b
         ON b.projet_id = p.projet_id
         AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
    LEFT JOIN t_projet_type1 pt1
         JOIN t_contrat_type1 ct1
              ON pt1.projet_type1_id = ct1.projet_type1_id
      ON pt1.projet_type1_id = pt.projet_type1_id 
      AND cal.jour >= ct1.debut_contrat_type1 AND cal.jour < ct1.fin_contrat_type1
    LEFT JOIN t_projet_type2 pt2
         JOIN t_contrat_type2 ct2
              ON pt2.projet_type2_id = ct2.projet_type2_id
      ON pt2.projet_type2_id = pt.projet_type2_id
      AND cal.jour >= ct2.debut_contrat_type2 AND cal.jour < ct2.fin_contrat_type2
    GROUP BY 
          p.projet_id,
          cal.jour
) 
SELECT 
    t1.projet_id,
    t1.jour,
    avg(t1.i1) AS i1,
    avg(t1.i2) AS i2,
    avg(t1.i3) AS i3,
    avg(t1.i4) AS i4,
    min(t2.i3) AS i5,
    min(t2.i4) AS i6
FROM t t1
JOIN tree pt
     ON t1.projet_id = pt.projet_id
JOIN t t2
     ON pt.projet_id_init = t2.projet_id
     AND t2.jour = t1.jour
--WHERE t1.jour = to_date('01/04/2013','dd/mm/yyyy')
GROUP BY 
    t1.projet_id,
    t1.jour;

J'attends d'être au travail pour comparer les performances des deux solutions sur une volumétrie conséquente.
Bien évidemment, si quelqu'un a des remarques ou des améliorations à proposer, je reste à l'écoute!

Merci encore Waldar, et bonnes vacances :mrgreen:

Je repasse pour poster la requête que j'ai finalement retenue :

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
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
SELECT 
    t1.projet_id,
    t1.jour,
    avg(t1.i1) AS i1,
    avg(t1.i2) AS i2,
    avg(t1.i3) AS i3,
    avg(t1.i4) AS i4,
    min(t2.i3) AS i5,
    min(t2.i4) AS i6
FROM
(
    SELECT 
          p.projet_id,
          cal.jour,
          sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i1,
          sum(CASE 
                    WHEN pt.projet_id = pt.projet_id_init 
                         THEN coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)
                    ELSE 0
              END
             ) AS i2,
          avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i3,
          avg(coalesce(b.montant_budget,0))
          - sum(CASE 
                    WHEN pt.projet_id = pt.projet_id_init 
                         THEN coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)
                    ELSE 0
              END
             ) AS i4,
          count(ct1.contrat_type1_id) + count(ct2.contrat_type2_id) AS contrat_actifs
    FROM t_projet p
    CROSS JOIN t_calendrier cal
    JOIN projet_tree pt
         ON P.projet_id = pt.projet_id_init
    LEFT JOIN t_budget b
         ON b.projet_id = p.projet_id
         AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
    LEFT JOIN t_projet_type1 pt1
         JOIN t_contrat_type1 ct1
              ON pt1.projet_type1_id = ct1.projet_type1_id
      ON pt1.projet_type1_id = pt.projet_type1_id 
      AND cal.jour >= ct1.debut_contrat_type1 AND cal.jour < ct1.fin_contrat_type1
    LEFT JOIN t_projet_type2 pt2
         JOIN t_contrat_type2 ct2
              ON pt2.projet_type2_id = ct2.projet_type2_id
      ON pt2.projet_type2_id = pt.projet_type2_id
      AND cal.jour >= ct2.debut_contrat_type2 AND cal.jour < ct2.fin_contrat_type2
    GROUP BY 
          p.projet_id,
          cal.jour
) t1
JOIN projet_tree pt
     ON t1.projet_id = pt.projet_id
JOIN 
(
    SELECT 
          p.projet_id,
          cal.jour,
          sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i1,
          sum(CASE 
                    WHEN pt.projet_id = pt.projet_id_init 
                         THEN coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)
                    ELSE 0
              END
             ) AS i2,
          avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)) AS i3,
          avg(coalesce(b.montant_budget,0))
          - sum(CASE 
                    WHEN pt.projet_id = pt.projet_id_init 
                         THEN coalesce(ct1.montant_contrat_type1, ct2.montant_contrat_type2,0)
                    ELSE 0
              END
             ) AS i4,
          count(ct1.contrat_type1_id) + count(ct2.contrat_type2_id) AS contrat_actifs
    FROM t_projet p
    CROSS JOIN t_calendrier cal
    JOIN projet_tree pt
         ON P.projet_id = pt.projet_id_init
    LEFT JOIN t_budget b
         ON b.projet_id = p.projet_id
         AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
    LEFT JOIN t_projet_type1 pt1
         JOIN t_contrat_type1 ct1
              ON pt1.projet_type1_id = ct1.projet_type1_id
      ON pt1.projet_type1_id = pt.projet_type1_id 
      AND cal.jour >= ct1.debut_contrat_type1 AND cal.jour < ct1.fin_contrat_type1
    LEFT JOIN t_projet_type2 pt2
         JOIN t_contrat_type2 ct2
              ON pt2.projet_type2_id = ct2.projet_type2_id
      ON pt2.projet_type2_id = pt.projet_type2_id
      AND cal.jour >= ct2.debut_contrat_type2 AND cal.jour < ct2.fin_contrat_type2
    GROUP BY 
          p.projet_id,
          cal.jour
) t2
     ON pt.projet_id_init = t2.projet_id
     AND t2.jour = t1.jour
--WHERE t1.jour = to_date('01/04/2013','dd/mm/yyyy')
GROUP BY 
    t1.projet_id,
    t1.jour;

J'ai enlevé le WITH car il ne voulait pas utiliser l'index sur t_calendrier lors de la jointure entre t1 et t2, ce qui était assez catastrophique en terme de perfs.
J'ai également transformé la vue projet_tree en vue matérialisée, ça m'arrange pas vraiment, mais c'est quasiment du simple au double en temps d'execution.
Je n'ai pas constaté de différence de performances entre la CTE récursive et le CONNECT BY.
au passage voilà la CTE :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH projet_tree(projet_id_init,projet_id,projet_type1_id,projet_type2_id) 
AS
 (SELECT projet_id AS projet_id_init,
         projet_id,
         projet_type1_id,
         projet_type2_id
    FROM t_projet
  UNION ALL
  SELECT d.projet_id_init AS projet_id_init,
         a.projet_id,
         a.projet_type1_id,
         a.projet_type2_id
    FROM t_projet a
   INNER JOIN projet_tree d
      ON a.parent_projet_id = d.projet_id)
SELECT * FROM projet_tree;

Sur un echantillon de 8000 projets/sous-projets, la requete ramène les données pour une journée en 10 secondes, pour 1 mois en 25 secondes et 1 an en trois minutes.
Sur un temps suffisamment long (5 ans), le temp (1go) est saturé avant la fin de l'execution.

Si vous avez des idées d'amélioration, elles sont les bienvenues, chaque seconde gagnée est importante!