Discussion :

[Malette]

Rebonjour,

J'avance dans ce que je veux faire, mais malgré j'ai encore besoin de votre aide et cela me désole, En fait j'ai la table ci-dessous qui synthétise le résultat d'une plus grosse requête SQL qui serait trop longue à expliquer.

Je précise que je travaille sous Oracle, on ne sait jamais

Je reprends le même exemple que l'autre post:
Voici le résultat d'une requête sur le nombre de personne travaillant pour une zone géographique d'une société, par Département, par Société 'sous-traitante', comme ci dessous.
Le département est codé en branche c'est à dire 132 = marketing et 133 c'est le département communication, le niveau au-dessus dans l'abre c'est le niveau 13 qui est "Service Supports".

La représentation se fait de la facon suivante:

Code :

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

1
2
3
4
5
6
7
 
1
|
13
| \
|  \
2   3

Ma Table 'result':

Code :

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

1
2
3
4
5
6
7
8
9
zone_geo,dpt, societe, nb_pers
 
'Paris', '132', 'Société 1', 3
'Paris', '132', 'Société 2', 1
...
'Paris', '133', 'Société 1', 2
'Paris', '133', 'Société 2', 1
'Paris', '133', 'Société 3', 1
...

Ce que je souhaite faire dans une simple requête SQL c'est :

1) Quand il y'a moins de 5 personnes le code représentant le département remonte d'un niveau

En gros dans le concept cela s'apparente à ceci:

Code :

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

1
2
3
select zone_geo, case when sum(nb_pers)<5 then /*dpt tronché du dernier caractere, désolé je n'ai plus le bout de code trouvé en tête */ else dpt end, societe,nb_pers
from table result
group by zone_geo, case when sum(nb_pers)<5 then /*dpt tronché du dernier caractere*/ else dpt end, societe,nb_pers

Mais bien sûr cela ne marche pas, car cela reste du concept.

Auriez-vous une début de piste là dessus?
Je ne suis même pas sur de pouvoir faire ce genre dans une requete SQL simple, peut être est-il plus simple de passer par du PL/SQL et de stocker cette table dans une table temporaire et ensuite faire des traitements dessus...

2) Faire cette requête de façon récursive ou plutôt répétée jusqu'à arriver au top niveau c'est à dire '1'
J'ai recherché au niveau de la fonction connect by mais je ne pense pas que cela corresponde au besoin.
A priori cela m'orienterait sur du PL/SQL que je ne maitrise pas vraiment, il faudrait que je m'y penche plus pour voir si c'est réalisable...

Merci à vous pour vos indices.

[Waldar]

Vos exemples ne sont pas assez détaillés.

Présentez tous les cas (avec deux / trois entités à chaque fois) et à partir de ceux-ci ce que vous attendez vraiment en sortie.

Quelle est votre version d'Oracle ?

[Malette]

Avec plaisir, je travaille sous Oracle 9i

1) 1er exemple cas le plus simple
les valeurs en entrées donc :

Code :

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

1
2
3
4
5
6
zone_geo,dpt, societe, nb_pers
  'Paris', '132', 'Société 1', 3
'Paris', '132', 'Société 2', 1
'Paris', '133', 'Société 1', 2
'Paris', '133', 'Société 2', 1
'Paris', '133', 'Société 3', 1

Le résultat attendu pour la première itération aucun des département n'a le nombre minimum alors on leur squeeze le dernier charactère:

Code :

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

1
2
3
4
5
6
 
'Paris', '13', 'Société 1', 3
'Paris', '13', 'Société 2', 1
'Paris', '13', 'Société 1', 2
'Paris', '13', 'Société 2', 1
'Paris', '13', 'Société 3', 1

Le résultat est ok puisque le nombre de persones pour le dpt '13' est supérieur à 5

2) 2ème exemple plus complexe

Code :

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

1
2
3
4
5
6
7
zone_geo,dpt, societe, nb_pers
 
'Paris', '132', 'Société 1', 5
'Paris', '132', 'Société 2', 1
'Paris', '133', 'Société 1', 2
'Paris', '133', 'Société 2', 1
'Paris', '133', 'Société 3', 1

Résultat attendu première itération, pour le département 132 la somme de personnes est >5 on fait rien, pour le département 133 on a squeezé le dernier caractère car la somme <5 :

Code :

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

1
2
3
4
5
6
 
'Paris', '132', 'Société 1', 5
'Paris', '132', 'Société 2', 1
'Paris', '13', 'Société 1', 2
'Paris', '13', 'Société 2', 1
'Paris', '13', 'Société 3', 1

Deuxième itération car la somme pour le département 13 est < à 5:

Code :

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

1
2
3
4
5
6
 
'Paris', '132', 'Société 1', 5
'Paris', '132', 'Société 2', 1
'Paris', '1', 'Société 1', 2
'Paris', '1', 'Société 2', 1
'Paris', '1', 'Société 3', 1

On arrête là puisque c'est le niveau le plus haut, la condition mini est un caractère.

Ce type de répétition sur la requête n'est pas possible à mon avis sur une simple requête SQL, mais je serai curieux de voir comment on peut proceder pour la supprimer le dernier caractère d'un dpt selon la somme de personnes

Merci pour tout.

[Waldar]

En codant en dur le fait qu'il n'y ait que deux niveaux maximum, c'est relativement simple (j'ai supprimé la zone géo de l'exemple car elle n'était pas intéressante) :

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
WITH SR AS
(
select case
         when sum(nbp) over(partition by dpt) < 5
         then substr(dpt, 1, length(dpt)-1)
         else dpt
       end as dpt,
       soc, nbp
  from Result
)
select case
         when sum(nbp) over(partition by dpt) < 5
         then substr(dpt, 1, length(dpt)-1)
         else dpt
       end as dpt,
       soc, nbp 
  from SR

Premier jeu de test :

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
WITH Result AS
(
select '132' as dpt, 'Société 1' as soc, 3 as nbp from dual union all
select '132'       , 'Société 2'       , 1        from dual union all
select '133'       , 'Société 1'       , 2        from dual union all
select '133'       , 'Société 2'       , 1        from dual union all
select '133'       , 'Société 3'       , 1        from dual
)
  ,  SR AS
(
select case
         when sum(nbp) over(partition by dpt) < 5
         then substr(dpt, 1, length(dpt)-1)
         else dpt
       end as dpt,
       soc, nbp
  from Result
)
select case
         when sum(nbp) over(partition by dpt) < 5
         then substr(dpt, 1, length(dpt)-1)
         else dpt
       end as dpt,
       soc, nbp 
  from SR;
 
DPT	SOC		NBP
13	Société 2	1
13	Société 1	3
13	Société 1	2
13	Société 3	1
13	Société 2	1

Second jeu de test :

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
WITH Result AS
(
select '132' as dpt, 'Société 1' as soc, 5 as nbp from dual union all
select '132'       , 'Société 2'       , 1        from dual union all
select '133'       , 'Société 1'       , 2        from dual union all
select '133'       , 'Société 2'       , 1        from dual union all
select '133'       , 'Société 3'       , 1        from dual
)
  ,  SR AS
(
select case
         when sum(nbp) over(partition by dpt) < 5
         then substr(dpt, 1, length(dpt)-1)
         else dpt
       end as dpt,
       soc, nbp
  from Result
)
select case
         when sum(nbp) over(partition by dpt) < 5
         then substr(dpt, 1, length(dpt)-1)
         else dpt
       end as dpt,
       soc, nbp 
  from SR;
 
DPT	SOC		NBP
1	Société 2	1
1	Société 3	1
1	Société 1	2
132	Société 1	5
132	Société 2	1

[Malette]

Merci maître Waldar

Je pense que pour éviter de m'embeter trop, je vais hardcodé comme vous le nombre d'itérations (au max 6) et je vais rajouter dans le case la condition "and length(dpt)>1 pour éviter d'arriver à des cases vides pour le dpt.

Je verrai l'impact en terme de performance, à première vue cela n'a pas l'air trop lourd, mis à part pour la mise en forme de la requête .

Je ne connaissais pas non plus le sum over, bref on en apprend tous les jours.

Merci et à bientôt.