Sommes, Group by et valeurs nulles

Version imprimable

Bonjour,

J'ai une requête Oracle 10g assez classique avec une sommation et un group by
Code:

1 2 3 4 5 6 7 8 9 10 11 12 select sum(a.lasteffectif) as SUMEFF, c.DEPA_CODE, d.libelle as PAYS from etablissement a, commune c, pays d where a.commune_code = c.codeinsee and c.depa_code in ('01','02','03','04') and a.nationalite = d.codeiso2 group by c.depa_code, d.libelle order by d.libelle, c.depa_code
qui me renvoie très classiquement
Code:

1 2 3 4 5 6 7 8 39 03 AFRIQUE DU SUD 2821 01 ALLEMAGNE 894 02 ALLEMAGNE 17258 03 ALLEMAGNE 1068 04 ALLEMAGNE 101 03 AUTRICHE 469 04 AUTRICHE
Pour une utilisation très spécifique dans un moteur de templates HTML, j'ai absolument besoin que des null soient indiqués à la place de la somme là où il n'existe aucun couple (depa_code, pays). Plus précisément qu'il y ait toujours 4 lignes pour chaque pays, correspondant aux 4 départements.
En résumé, je veux ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 null 01 AFRIQUE DU SUD null 02 AFRIQUE DU SUD 39 03 AFRIQUE DU SUD null 04 AFRIQUE DU SUD 2821 01 ALLEMAGNE 894 02 ALLEMAGNE 17258 03 ALLEMAGNE 1068 04 ALLEMAGNE null 01 AUTRICHE null 02 AUTRICHE 101 03 AUTRICHE 469 04 AUTRICHE
J'ai commencé à regarder du côté des group by cube et autres group by rollup qui font des choses très sympathiques, mais pas ce que je cherche.

Est-ce que quelqu'un aurait une solution ? Peut-être que du côté des MODEL PARTITION DIMENSION ?

Merci de votre aide

23/11/2006, 17h02
nuke_y

Peut-être avec une jointure ouverte ou la fonction NVL.
23/11/2006, 17h08
antonomase

nvl transforme un null en chaîne, mais le null existe.
Là, il faut créer une ligne avec une partie à null.

Quant à la jointure ouverte, non, ça ne rajoute rien non plus.
23/11/2006, 17h49
orafrance

normalement la jointure externe fonctionne parfaitement. Qu'as-tu essayé pour que ça ne fonctionne pas ?
23/11/2006, 18h12
remi4444

y'a un truc qui me choque dans ta requête, tu fais un parcours de jointures

d -> a -> c

Or, ta table A contient des enregistrements facultatifs (là ou tu veux des null) donc si l'enregistrement n'existe pas, comment veux tu que le système fasse la jointure a -> c et donc determiner le bon DEPA_CODE. Fonctionnellement ce que tu demande n'est pas possible...

Il me semble qu'une commune doit appartenir à un pays unique non ? donc il doit bien y avoir cette information dans la table C...

si c'est le cas, il faut alors faire alors une jointure normale entre D et C puis une jointure externe entre C et A.

Si il n'y a pas de lien entre C et D, alors ça veux dire que tu dois faire un produit cartésien !! (pas de lien, c'est pas de lien... :roll: ). Dans ce cas une methode est de passer par une fonction qui elle, te fera un comptage individuellement, mais fonctionnellement, ça parait complètement abhérant... :aie:
23/11/2006, 18h20
nuke_y

Quand je parlais de jointure ouverte je voyais effectivement un produit cartésien, qui est pour moi le seul moyen de récupérer ça. J'y arrive mais ensuite c'est pour la somme que ça pose problème (elle est multipliée par toutes les lignes "en trop" du produit cartésien).

J'ai à peu près tout essayé avec cette syntaxe

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT sum(a.lasteffectif) AS SUMEFF,
       c.DEPA_CODE,
       d.libelle AS PAYS
FROM etablissement a,
     commune c,
     pays d
WHERE a.commune_code = c.codeinsee (+)
  AND c.depa_code IN ('01','02','03','04')
  AND a.nationalite = d.codeiso2
GROUP BY c.depa_code, d.libelle
ORDER BY d.libelle, c.depa_code

et avec celle-ci

Code:

1
2
3
4
5
6
7
8
9
10
 
SELECT sum(a.lasteffectif) AS SUMEFF,
       c.DEPA_CODE,
       d.libelle AS PAYS
FROM etablissement a left join commune c on a.commune_code = c.codeinsee,
     pays d
WHERE c.depa_code IN ('01','02','03','04')
  AND a.nationalite = d.codeiso2
GROUP BY c.depa_code, d.libelle
ORDER BY d.libelle, c.depa_code

sur la jointure etablissement-commune, sur la jointure etablissement-pays et sur les deux.
Cela ne rajoute jamais des lignes avec des sommes nulles.

J'ai trouvé cet exemple

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT times.time_id, product, quantity FROM inventory 
   PARTITION BY  (product) 
   RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) 
   WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') 
      AND TO_DATE('06/04/01', 'DD/MM/YY') 
   ORDER BY  2,1; 
 
TIME_ID   PRODUCT      QUANTITY
--------- ---------- ----------
01-APR-01 bottle             10
02-APR-01 bottle
03-APR-01 bottle
04-APR-01 bottle
05-APR-01 bottle
06-APR-01 bottle             10
06-APR-01 bottle              8
01-APR-01 can                10
01-APR-01 can                15
02-APR-01 can
03-APR-01 can

Mais encore une fois, c'est la récupération d'une colonne dont la valeur est nulle et pas le calcul d'une colonne sum.

23/11/2006, 18h50
antonomase

Citation:

Envoyé par remi4444

Il me semble qu'une commune doit appartenir à un pays unique non ? donc il doit bien y avoir cette information dans la table C...

Non. On a des établissements qui sont implantés dans des communes et ces établissements (entreprises) ont des nationalités qui n'ont rien à voir avec les communes. Exemple : IBM est une entreprise sur la commune de Puteaux et avec une nationalité US ; et dans la commune de Puteaux, tu as également Siemens qui a une nationalité allemande.

Je ne rentre pas dans le détail, mais le but fonctionnel de cette requête est d'obtenir des un tableau croisé des sommes d'effectifs des entreprises en fonction de la nationalité (en ligne) sur 4 départements fixes (en colonne). Et avec les outils utilisés destinés à des utilisateurs non informaticiens et qui possèdent donc forcément peu de fonctionnalités, je suis obligé de leur fournir le tableau indiqué dans mon premier post.

Ce n'est pas une jointure d -> a -> c, mais deux jointures : une a -> c et l'autre a -> d (à moins que je me sois gouré parce que j'ai simplifié la requête présentée ici par rapport à la requêtre originale).

Avec un traitement dans un langage tel que PHP, je peux récupérer le tableau, regarder la liste des départements différents et recréer les lignes manquantes. Mais le but est de le faire directement en SQL, si c'est possible. Peut-être les fonctions de data warehousing d'Oracle, mais je ne les maîtrise pas bien.

aié, j'ai trouvé :

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
SQL> DROP TABLE PAYS;
 
Table supprimée.
 
SQL> DROP TABLE DEPT;
 
Table supprimée.
 
SQL> CREATE TABLE PAYS (NOM_PAYS VARCHAR2(10));
 
Table créée.
 
SQL> CREATE TABLE DEPT (NOM_PAYS VARCHAR2(10), NUM_DEPT NUMBER(2), VALUE NUMBER(
5));
 
Table créée.
 
SQL> INSERT INTO PAYS VALUES ('FRANCE');
 
1 ligne créée.
 
SQL> INSERT INTO PAYS VALUES ('ALLEMAGNE');
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('FRANCE',1, 10);
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('FRANCE',2, 10);
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('FRANCE',3, 10);
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('FRANCE',4, 20);
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('ALLEMAGNE',2, 20);
 
1 ligne créée.
 
SQL> SELECT * FROM PAYS;
 
NOM_PAYS
----------
FRANCE
ALLEMAGNE
 
SQL> SELECT * FROM DEPT;
 
NOM_PAYS     NUM_DEPT      VALUE
---------- ---------- ----------
FRANCE              1         10
FRANCE              2         10
FRANCE              3         10
FRANCE              4         20
ALLEMAGNE           2         20
 
SQL> SELECT T1.NOM_PAYS, T2.NUM_DEPT, SUM(CASE WHEN T1.NOM_PAYS=T2.NOM_PAYS THEN VALUE ELSE 0 END) FROM PAYS T1, DEPT T2 GROUP BY T1.NOM_PAYS, T2.NUM_DEPT;
 
NOM_PAYS     NUM_DEPT SUM(CASEWHENT1.NOM_PAYS=T2.NOM_PAYSTHENVALUEELSE0END)
---------- ---------- -----------------------------------------------------
FRANCE              1                                                    10
FRANCE              2                                                    10
FRANCE              3                                                    10
FRANCE              4                                                    20
ALLEMAGNE           1                                                     0
ALLEMAGNE           2                                                    20
ALLEMAGNE           3                                                     0
ALLEMAGNE           4                                                     0
 
8 ligne(s) sélectionnée(s).

Evidemment, s'il ne faut ça ne correspond pas du tout à ton besoin...

d'accord, j'ai été trompé par le nom des tables... désolé....

donc tu veux bien un produit cartésien...

En fait tu veux:

(D x C) -> A

tu peux donc essayer:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT sum(a.lasteffectif) AS SUMEFF, V.DEPA_CODE, V.libelle AS PAYS FROM etablissement a, ( select c.DEPA_CODE, c.codeinsee, d.codeiso2, d.libelle as PAYS from commune c, pays d where c depa_code IN ('01','02','03','04') ) V WHERE V.codeinsee = a.commune_code (+) AND V.codeiso2 = a.nationalite (+) GROUP BY v.depa_code, v.libelle ORDER BY v.libelle, v.depa_code
ça devrais répondre à ton besoin.
Attention cependant au problème de performance car c'est le genre de vue qui se calcule entièrement meme si on ne veux en afficher qu'une toute petite partie dans le code.

Si tu veux en faire une vue et que tu as des problèmes de performance, je te conseille de la jouer "brutal" avec un appel de fonction.

exemple:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 create or replace function TA_SOMME (p_insee varchar2, p_iso2 varchar2) return number is l_sum number; BEGIN select SUM(lasteffectif) finto l_sum rom etablissement where commune_code = p_insee and nationalite = p_iso2; return l_sum; END; / CREATE OR REPACE VIEW TA_VUE as select TA_SOMME ( c.codeinsee, d.codeiso2) AS SUMEFF c.DEPA_CODE, d.libelle as PAYS from commune c, pays d where c depa_code IN ('01','02','03','04') ORDER BY d.libelle, c.depa_code;

23/11/2006, 19h25
antonomase

Merci à tous.
Y a les gosses qui gueulent parce qu'ils ont faim.

Je teste ça demain et je vous tiens au courant.

mais encore une fois, merci
23/11/2006, 20h33
antonomase

J'ai pas pu résister ...

@nuke_y
Super la syntaxe avec le case ; je ne connaissais pas du tout ce genre de syntaxe. C'est exactement ça et ça fonctionne.

@remi4444
je n'ai pas essayé ta syntaxe avec la vue, ça a l'air un peu plus compliqué. Mais je vais quand même essayer.

Je ne peux pas passer par des fonctions et du PL/SQL. En fait, c'est destiné à un outil où ce sont les utilisateurs eux-même qui se créent leurs propres pages HTML dynamiques à partir de requêtes SQL. Ces quelques utilisateurs avertis -mais non informaticiens- ont eu une formation SQL de base pour les requêtes courantes qui correspondent à 98% de leurs besoins et on était tombé sur LE cas tordu d'un tableau croisé. Il va donc déjà falloir que je leur explique ces syntaxes "avancées", mais je ne peux pas aller plus loin.

Encore merci à tous pour cette résolution aussi rapide