requetes hiérarchiques et somme [9i] [Résolu]

[bguihal]

Voila je suis en 9i et je fait des requetes hiérarchique sur une table, jusque la pas de soucci.
Mon arbres est constitué d'elements qui ont tous un délai, j'aimerai cumulé ces délais sur mes branches les plus hautes.
il s'agit donc de trouver un chemin critique.
exemple :

Code :

1
2
3
            0     
          1   2     
        11     21

si tous les element ont 1 en délai, j'aimerai une requete qui me ramenne :
sur le 11 et le 21 : 1 sur le 1et 2 : 2 et sur le 0 : 3

es ce possible en 1 seul requete en 9i ?

merci.

[ojo77]

Désolé ... erreur de compréhension de ma part

[laurentschneider]

• test data

Code :

1
2
3
4
5
6
CREATE TABLE t AS (
        SELECT  0 child,NULL parent FROM dual union ALL
        SELECT 1,0 FROM dual union ALL
        SELECT 2,0 FROM dual union ALL
        SELECT 11,1 FROM dual union ALL
        SELECT 21,2 FROM dual )

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT child, max(l) FROM
(SELECT child, level l  
FROM t 
connect BY prior parent=child 
)
GROUP BY child
ORDER BY 2
 
     CHILD     MAX(L)
---------- ----------
        21          1
        11          1
         1          2
         2          2
         0          3

[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
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> 
SQL> WITH DATA AS
  2  (
  3    SELECT 0 AS id, NULL AS parent_id, 0 AS delai FROM dual union ALL
  4    SELECT 1 AS id, 0 AS parent_id, 1 AS delai FROM dual union ALL
  5    SELECT 2 AS id, 0 AS parent_id, 2 AS delai FROM dual union ALL
  6    SELECT 11 AS id, 1 AS parent_id, 2 AS delai FROM dual union ALL
  7    SELECT 21 AS id, 2 AS parent_id, 3 AS delai FROM dual
  8  )
  9  SELECT id, parent_id, (SELECT Sum(delai)
 10                           FROM DATA b
 11                          WHERE id IN (SELECT id
 12                                         FROM DATA c
 13                                        connect BY prior id = parent_id
 14                                        start WITH id = a.id)
 15                        ) Sum_delai
 16    FROM DATA a
 17  /
 
        ID  PARENT_ID  SUM_DELAI
---------- ---------- ----------
         0                     8
         1          0          3
         2          0          5
        11          1          2
        21          2          3
 
SQL>

Mais dpv perf ça peut être vite la Berezina.

[bguihal]

en fait c'est le chemin crique qu'il me faut, pas le total de tous :

Code :

1
2
3
4
5
6
7
     ID  PARENT_ID  SUM_DELAI
---------- ---------- ----------
         0                     8
         1          0          3
         2          0          5
        11          1          2
        21          2          3

donc pour le 0 ce serai 0+1+2 (coté element 1) soit 3
et 0+2+3 (coté element 2) soit 5
donc un total de 5.
quand t'on a une seul branche t'a requete marche tres bien.

pour laurentschneider merci, mais mon probleme est la somme des délais.

[mnitu]

Maintenant c'est Waterloo

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
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> 
SQL> WITH DATA AS
  2  (
  3    SELECT 0 AS id, NULL AS parent_id, 0 AS delai FROM dual union ALL
  4    SELECT 1 AS id, 0 AS parent_id, 1 AS delai FROM dual union ALL
  5    SELECT 2 AS id, 0 AS parent_id, 2 AS delai FROM dual union ALL
  6    SELECT 11 AS id, 1 AS parent_id, 2 AS delai FROM dual union ALL
  7    SELECT 21 AS id, 2 AS parent_id, 3 AS delai FROM dual union ALL
  8    SELECT 100 AS id, NULL AS parent_id, 0 AS delai FROM dual union ALL
  9    SELECT 101 AS id, 100 AS parent_id, 7 AS delai FROM dual union ALL
 10    SELECT 102 AS id, 100 AS parent_id, 1 AS delai FROM dual union ALL
 11    SELECT 1011 AS id, 101 AS parent_id, 4 AS delai FROM dual union ALL
 12    SELECT 1021 AS id, 102 AS parent_id, 8 AS delai FROM dual
 13  ),
 14  Calc_Data AS
 15  (
 16    SELECT id, parent_id, (SELECT Sum(delai)
 17                             FROM DATA b
 18                            WHERE id IN (SELECT id
 19                                           FROM DATA c
 20                                          connect BY prior id = parent_id
 21                                          start WITH id = a.id)
 22                           ) Sum_delai
 23      FROM DATA a
 24  )
 25  SELECT id, parent_id,
 26         Case When parent_id IS NULL Then (SELECT Max(Sum_delai)
 27                                             FROM Calc_Data b
 28                                            WHERE parent_id = a.id)
 29         Else Sum_Delai
 30         End delai
 31    FROM Calc_data a
 32  /
 
        ID  PARENT_ID      DELAI
---------- ---------- ----------
         0                     5
         1          0          3
         2          0          5
        11          1          2
        21          2          3
       100                    11
       101        100         11
       102        100          9
      1011        101          4
      1021        102          8
 
10 rows selected
 
SQL>

[laurentschneider]

avec 2 petites fonctions (en 10g il y aurait le connect_by_root)

Code :

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION eval (str VARCHAR2)
   RETURN NUMBER
IS
   n   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'begin :n := ' || str || ';end;' USING OUT n;
 
   RETURN n;
END;
/

Code :

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION root (str VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN
   RETURN SUBSTR (str, 2, INSTR (str || '/', '/', 2) - 2);
END;
/

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH t
     AS (SELECT 0 id, NULL parent, 0 delai FROM DUAL
         UNION ALL
         SELECT 1, 0, 1 FROM DUAL
         UNION ALL
         SELECT 2, 0, 2 FROM DUAL
         UNION ALL
         SELECT 11, 1, 2 FROM DUAL
         UNION ALL
         SELECT 21, 2, 3 FROM DUAL)
  SELECT root, MAX (PATH)
    FROM (    SELECT root (SYS_CONNECT_BY_PATH (id, '/')) root,
                     eval (SYS_CONNECT_BY_PATH (delai, '+')) PATH
                FROM t
          CONNECT BY parent = PRIOR id)
GROUP BY root;

Code :

1
2
3
4
5
6
7
ROOT MAX(PATH)
---- ---------
1            3
11           2
21           3
0            5
2            5

[bguihal]

mnitu : joli
(y a l'element 0 si on ajout un délai c'est pas pris en compte, mais je chipote, je vais trouver) un grand merci a toi !

laurentschneider : oui on doit passer en 11 d'ici quelques mois, mais mon projet est déja en cours.

[mnitu]

Code :

1
2
 
Case When parent_id IS NULL Then delai + (SELECT Max(Sum_delai)...

Mais côté perf ça va seulement pour des petits volumétries.

[bguihal]

4Millions d'enregistrements : oui ça va c'est pas trop gros, et de toutes façon je filtre ! encore merci a toi.

[bguihal]

En fait ce code ne peut pas convenir a mes besoins, en effet la separation des branches ne se fait pas forcement sur le niveau le plus haut, mais peu se produire plus "bas" dans l'arbre.
par exemple :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 0 AS id, NULL AS parent_id, 5 AS delai FROM dual union ALL
      SELECT 1 AS id, 0 AS parent_id, 10 AS delai FROM dual union ALL
      SELECT 11 AS id, 1 AS parent_id, 5 AS delai FROM dual union ALL
      SELECT 111 AS id, 11 AS parent_id, 5 AS delai FROM dual union ALL
      SELECT 3 AS id, 0 AS parent_id, 9 AS delai FROM dual union ALL
     SELECT 31 AS id, 3 AS parent_id, 0 AS delai FROM dual union ALL
      SELECT 311 AS id, 31  AS parent_id, 1 AS delai FROM dual union ALL
     SELECT 312 AS id, 31 AS parent_id, 1 AS delai FROM dual union ALL
     SELECT 313 AS id, 31 AS parent_id, 1 AS delai FROM dual union ALL
     SELECT 5 AS id, 0 AS parent_id, 10 AS delai FROM dual  union ALL
      SELECT 51 AS id, 5 AS parent_id, 5 AS delai FROM dual union ALL
       SELECT 511 AS id, 51 AS parent_id, 5 AS delai FROM dual

et pour le coup, l'element 3 ne doit pas avoir un délai de 12 mais bien de 10.

et au niveau du code cela donne :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
   SELECT id, parent_id,
           Case When parent_id IS NULL Then delai +  (SELECT Max(Sum_delai)
                                              FROM  (  SELECT id, parent_id, (SELECT Sum(delai)
                              FROM adv_dev_cde.simu_nomenclature b
                             WHERE id IN (SELECT id
                                            FROM adv_dev_cde.simu_nomenclature c
                                           connect BY prior id = parent_id
                                           start WITH id = a.id)
                            ) Sum_delai,delai
       FROM adv_dev_cde.simu_nomenclature a) b
                                            WHERE parent_id = a.id)
          Else Sum_Delai
         End delai
   FROM  (
     SELECT id, parent_id, (SELECT Sum(delai)
                              FROM adv_dev_cde.simu_nomenclature b
                             WHERE id IN (SELECT id
                                            FROM adv_dev_cde.simu_nomenclature c
                                           connect BY prior id = parent_id
                                           start WITH id = a.id)
                            ) Sum_delai,delai
       FROM adv_dev_cde.simu_nomenclature a
   ) a;

[laurentschneider]

tu as essayé mon code???

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH t AS (
SELECT 0 AS id, NULL AS parent_id, 5 AS delai FROM dual union ALL
      SELECT 1 AS id, 0 AS parent_id, 10 AS delai FROM dual union ALL
      SELECT 11 AS id, 1 AS parent_id, 5 AS delai FROM dual union ALL
      SELECT 111 AS id, 11 AS parent_id, 5 AS delai FROM dual union ALL
      SELECT 3 AS id, 0 AS parent_id, 9 AS delai FROM dual union ALL
     SELECT 31 AS id, 3 AS parent_id, 0 AS delai FROM dual union ALL
      SELECT 311 AS id, 31  AS parent_id, 1 AS delai FROM dual union ALL
     SELECT 312 AS id, 31 AS parent_id, 1 AS delai FROM dual union ALL
     SELECT 313 AS id, 31 AS parent_id, 1 AS delai FROM dual union ALL
     SELECT 5 AS id, 0 AS parent_id, 10 AS delai FROM dual  union ALL
      SELECT 51 AS id, 5 AS parent_id, 5 AS delai FROM dual union ALL
       SELECT 511 AS id, 51 AS parent_id, 5 AS delai FROM dual
       )

Code :

1
2
3
4
5
6
       SELECT root, MAX (PATH)
    FROM (    SELECT root (SYS_CONNECT_BY_PATH (id, '/')) root,
                     eval (SYS_CONNECT_BY_PATH (delai, '+')) PATH
                FROM t
          CONNECT BY parent_id = PRIOR id)
GROUP BY root;

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ROOT MAX(PATH)              
---- ---------------------- 
1    20                     
3    10                     
11   10                     
312  1                      
511  5                      
31   1                      
311  1                      
313  1                      
51   10                     
0    25                     
5    20                     
111  5

[bguihal]

c'est gentil laurentschneider , mais je suis en 9i, donc j'ai pas ces fonctions la.
et pas de possibilité d'évoluer pour le moment.
je vais faire une requete + simple + une fonction, je la posterai quand je l'aurai fini.

[mnitu]

Sys_Connect_By_Path ou Key_Path.

[laurentschneider]

je t'ai posté le code des fonctions, relis ma réponse stpl

[bguihal]

ok merci beaucoup, j'avais trouvé avec une fonction, mais la tienne est beaucoup plus efficace (-de code).