comment écrire la jointure externe ? [Résolu]

[DiverSIG]

Bonjour,

Je fais une requête sur ma base de données qui me renvoie des moyennes journalières de différentes valeurs stockées dans ma base :

Code :

1
2
3
4
5
6
7
8
9
 
SELECT str.str_date AS d, AVG(mss.mss_valeur) AS moy, STDDEV(mss.mss_valeur) AS ec, COUNT(str.str_date) AS c 
FROM m_stries mss, stries str 
WHERE mss.mss_parametre = 1 
AND EXTRACT(YEAR FROM str.str_date) = 1990 
AND mss.mss_individu = str.str_individu 
AND mss.mss_strie = str.str_strie 
GROUP BY str.str_date 
ORDER BY str.str_date

Dans le résultat que j'obtiens, je n'ai pas toutes les dates de l'année 1990 (normal, il n'y a pas de valeurs à moyenner pour toutes les dates).
Comme je veux une table résultat avec toutes les dates, j'ai crée dans ma base une table calendrier avec une variable qui contient les dates de tous les jours.
Et mon idée est de faire une jointure externe entre cette table et le résultat de ma requête, mais je ne vois pas comment l'écrire... parce que dans toutes les syntaxes que j'ai vues, le LEFT INNER JOIN travaille sur 2 tables 'en dur', et pas sur le résultat d'une requête.

J'ai essayé quelque chose comme ça :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT ca.cal_date, str.str_date AS d, AVG(mss.mss_valeur) AS moy, STDDEV(mss.mss_valeur) AS ec, COUNT(str.str_date) AS c 
FROM calendrier ca, m_stries mss, stries str
LEFT OUTER JOIN calendrier cal
ON cal.cal_date = str.str_date
WHERE mss.mss_parametre = 1 
AND EXTRACT(YEAR FROM ca.cal_date) = 1990 
AND mss.mss_individu = str.str_individu 
AND mss.mss_strie = str.str_strie 
GROUP BY ca.cal_date, str.str_date 
ORDER BY str.str_date

mais ça mouline, et ça finit en message d'erreur :

Code :

1
2
3
ERROR: could NOT WRITE block 68560 of TEMPORARY file: No space LEFT ON device
SQL state: 53100
Hint: Perhaps out of disk space?

une idée ?

Merci,
Nico

[Waldar]

Quel est votre SGBD ?

[DiverSIG]

PostgreSQL 8.4.8

Nico

[Waldar]

Essayez ainsi :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  SELECT ca.cal_date
       , str.str_date           AS d
       , AVG(mss.mss_valeur)    AS moy
       , STDDEV(mss.mss_valeur) AS ec
       , COUNT(str.str_date)    AS c
    FROM calendrier ca
         LEFT OUTER JOIN stries str
         INNER JOIN m_stries mss
           ON mss.mss_individu  = str.str_individu 
          AND mss.mss_strie     = str.str_strie
          AND mss.mss_parametre = 1 
           ON str.str_date      = ca.cal_date
   WHERE ca.cal_date >= date '1990-01-01'
     AND ca.cal_date <  date '1991-01-01'
GROUP BY ca.cal_date
       , str.str_date 
ORDER BY ca.cal_date ASC

[DiverSIG]

ça marche super, merci !
Nico

[StringBuilder]

Pour répondre à l'intervention de Waldar dans le topic du classement de ligue 1 :

En quoi cette syntaxe est nécessaire ?
Pourquoi ne pas utiliser celle-ci ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT ca.cal_date
       , str.str_date           AS d
       , AVG(mss.mss_valeur)    AS moy
       , STDDEV(mss.mss_valeur) AS ec
       , COUNT(str.str_date)    AS c
    FROM calendrier ca
         LEFT OUTER JOIN stries str ON str.str_date      = ca.cal_date
         LEFT OUTER JOIN m_stries mss
           ON mss.mss_individu  = str.str_individu 
          AND mss.mss_strie     = str.str_strie
          AND mss.mss_parametre = 1 
   WHERE ca.cal_date >= date '1990-01-01'
     AND ca.cal_date <  date '1991-01-01'
GROUP BY ca.cal_date
       , str.str_date 
ORDER BY ca.cal_date ASC

Je ne vois pas du tout quelle différence il peut y avoir entre les deux syntaxe (??)


Hmmmm ok, je vois un cas particulier où effectivement, ce n'est pas équivalent.

Si str contient un NULL dans la référence à mss, alors ta syntaxe vire la ligne aussi de str lors de la jointure avec ca.

Ceci dit, pour plus de clareté, je mettrait des parenthèse et j'indenterais (en effet, cette syntaxe s'apparente au fonctionnenent d'une sous requête (syntaxe ci-dessous) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT ca.cal_date
         , str.str_date           AS d
         , AVG(mss.mss_valeur)    AS moy
         , STDDEV(mss.mss_valeur) AS ec
         , COUNT(str.str_date)    AS c
    FROM calendrier ca
         LEFT OUTER JOIN (
                  stries str
                  INNER JOIN m_stries mss 
                  ON mss.mss_individu  = str.str_individu
                  AND mss.mss_strie     = str.str_strie
                  AND mss.mss_parametre = 1 
         )
         ON str.str_date      = ca.cal_date
WHERE ca.cal_date >= date '1990-01-01'
     AND ca.cal_date <  date '1991-01-01'
GROUP BY ca.cal_date
       , str.str_date 
ORDER BY ca.cal_date ASC

Avec une sous-requpête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT ca.cal_date
         , str.str_date           AS d
         , AVG(mss.mss_valeur)    AS moy
         , STDDEV(mss.mss_valeur) AS ec
         , COUNT(str.str_date)    AS c
    FROM calendrier ca
         LEFT OUTER JOIN (
                  SELECT st2.str_date
                  FROM stries st2
                  INNER JOIN m_stries mss 
                  ON mss.mss_individu  = st2.str_individu 
                  AND mss.mss_strie     = st2.str_strie
                  AND mss.mss_parametre = 1 
         ) str
         ON str.str_date      = ca.cal_date
   WHERE ca.cal_date >= date '1990-01-01'
     AND ca.cal_date <  date '1991-01-01'
GROUP BY ca.cal_date
       , str.str_date 
ORDER BY ca.cal_date ASC

PS : J'ai pas bien compris pourquoi AND mss.mss_strie = str.str_strie AND mss.mss_parametre = 1 était dans le second ON, pour moi ça doit être dans le premier, non ?

[CinePhil]

Pour une fois je suis d'accord avec toi StringBuilder !

Je trouve l'imbrication des jointures (à la manière d'Access par exemple) de la requête de Waldar beaucoup moins claire que lorsqu'on fait se succéder les jointures complètes.

Une remarques quand même sur la requête :
Plutôt que >= et <, il vaut mieux utiliser BETWEEN, en faisant attention aux bornes qui sont incluses avec BETWEEN.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT ca.cal_date, 
    str.str_date AS d, 
    AVG(mss.mss_valeur) AS moy, 
    STDDEV(mss.mss_valeur) AS ec
    COUNT(str.str_date) AS c 
FROM calendrier ca
LEFT OUTER JOIN stries str ON str.str_date = ca.cal_date
    INNER JOIN m_stries mss
        ON mss.mss_individu  = str.str_individu 
        AND mss.mss_strie = str.str_strie
        AND mss.mss_parametre = 1 
WHERE ca.cal_date BETWEEN '1990-01-01' AND '1990-12-31'
GROUP BY ca.cal_date, str.str_date 
ORDER BY ca.cal_date ASC

[StringBuilder]

J'ai édité mon post, il y a un cas où effectivement, la syntaxe de Waldar est nécessaire.
Mais dans ce cas, je trouve plus lisible de mettre entre parenthèses la seconde jointure et d'indenter : ça rappelle la syntaxe d'une jointure sur une sous-requête (et c'est d'ailleurs ni plus ni moins ce qu'on fait, de façon "propre" mais illisible).

[StringBuilder]

Voici un script qui illustre les différentes syntaxes :

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
 
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
 
 
CREATE TABLE c (id int NOT NULL identity PRIMARY KEY, vc varchar(10) NOT NULL);
CREATE TABLE b (id int NOT NULL identity PRIMARY KEY, c_id int NULL REFERENCES c (id), vb varchar(10) NOT NULL);
CREATE TABLE a (id int NOT NULL identity PRIMARY KEY, b_id int NULL REFERENCES b (id), va varchar(10) NOT NULL);
 
INSERT INTO c (vc) VALUES ('C1');
INSERT INTO c (vc) VALUES ('C2');
 
INSERT INTO b (c_id, vb) VALUES (1, 'B1');
INSERT INTO b (c_id, vb) VALUES (2, 'B2');
INSERT INTO b (c_id, vb) VALUES (NULL, 'B3');
 
INSERT INTO a (b_id, va) VALUES (1, 'A1');
INSERT INTO a (b_id, va) VALUES (2, 'A2');
INSERT INTO a (b_id, va) VALUES (3, 'A3');
INSERT INTO a (b_id, va) VALUES (NULL, 'A4');
 
SELECT a.va, b.vb, c.vc
FROM a
LEFT OUTER JOIN b ON b.id = a.b_id
INNER JOIN c ON c.id = b.c_id;
 
SELECT a.va, b.vb, c.vc
FROM a
LEFT OUTER JOIN b ON b.id = a.b_id
LEFT OUTER JOIN c ON c.id = b.c_id;
 
SELECT a.va, b.vb, c.vc
FROM a
LEFT OUTER JOIN 
(
	b
	INNER JOIN c ON c.id = b.c_id
)
	ON b.id = a.b_id;
 
SELECT a.va, b2.vb, b2.vc
FROM a
LEFT OUTER JOIN 
(
	SELECT b.id, b.vb, c.vc
	FROM b
	INNER JOIN c ON c.id = b.c_id
) b2
	ON b2.id = a.b_id;

La première : INNER JOIN entre B et C : vu que B ne référence pas toujours C, on perd des lignes de A : PAS BON

Code :

1
2
3
4
5
 
va         vb         vc
---------- ---------- ----------
A1         B1         C1
A2         B2         C2

La seconde : OUTER JOIN entre B et C : vu que A ne référence pas toujours B et que B ne référence pas toujours C, on a trop de lignes : on ne veux pas celles pour lesquelles B ne référence pas C

Code :

1
2
3
4
5
6
7
 
va         vb         vc
---------- ---------- ----------
A1         B1         C1
A2         B2         C2
A3         B3         NULL
A4         NULL       NULL

Les deux autres, qui sont équivalentes : Ca fait bien ce qu'on veut... Avec les parenthèses, on remarque à quel point elles sont similaires.

Code :

1
2
3
4
5
6
7
 
va         vb         vc
---------- ---------- ----------
A1         B1         C1
A2         B2         C2
A3         NULL       NULL
A4         NULL       NULL

[CinePhil]

C'est plus propre avec la sous-requête.

[StringBuilder]

Les plans d'exécution (sous SQL Server) sont cependant différents. Il faudra voir (j'imagine que le choix du SGBD a un impact) laquelle est la plus rapide. J'imagine que la syntaxe sans sous-requête est meilleure.

[CinePhil]

Mouais... des clés étrangères "NULLables" c'est pas terrible !

[StringBuilder]

Bah c'est pour ça que les jointures externes existent

On peut aussi reprendre dans l'autre sens (et ça n'a pas plus de sens) : les éléments de listes de valeurs qui n'ont pas de référence.

Moins ça arrive, mieux on se porte, mais bon, des fois c'est quand même utile

[CinePhil]

Envoyé par CinePhil

Mouais... des clés étrangères "NULLables" c'est pas terrible !

Envoyé par StringBuilder

Bah c'est pour ça que les jointures externes existent

Ça n'a rien à voir !
MCD :
personne -0,n----diriger----1,1- projet

Tables :
personne (prs_id...)
projet (prj_id, prj_id_chef...) <= clé étrangère non nulle.

Quelles sont les personnes qui n'ont jamais dirigé de projet ?

Code :

1
2
3
4
SELECT prs_id
FROM personne p
LEFT OUTER JOIN projet j ON j.prj_id_chef = p.prs_id
WHERE j.prj_id_chef IS NULL

Le NULL n'apparaîtrait dans le résultat qu'à cause de la jointure externe mais pas parce que la clé étrangère serait nullable !

[Waldar]

Envoyé par CinePhil

Je trouve l'imbrication des jointures (à la manière d'Access par exemple) de la requête de Waldar beaucoup moins claire que lorsqu'on fait se succéder les jointures complètes.

L'imbrication a du sens, il n'y a pas de hasard en jeu.
Ce n'est pas plus complexe à comprendre qu'une jointure externe.
Effectivement, deux parenthèses en plus aident à la lisibilité, mais elles ne changent rien en terme d'exécution de la requête. Chacun fait comme il souhaite, du moment que les résultats retournés sont corrects !

Cette syntaxe permet d'être plus concis dans l'écriture de la requête.

Envoyé par CinePhil

Une remarques quand même sur la requête :
Plutôt que >= et <, il vaut mieux utiliser BETWEEN, en faisant attention aux bornes qui sont incluses avec BETWEEN.

BETWEEN est transformé en >= et <=, on le voit quand on profile les requêtes.
Comme un IN est transformé en une succession de OR.
En fait, ça permet juste d'être plus concis dans l'écriture de la requête.

... tient, ça me rappelle quelque chose !

En l'occurrence BETWEEN a parfaitement sa place ici, j'ai du penser à la présence ou nom des heures minutes secondes, mais comme il s'agit d'une table calendrier la logique voudrait une ligne par jour.
Donc bonne correction !

Envoyé par CinePhil

C'est plus propre avec la sous-requête.

Là pour le coup, je ne suis pas d'accord.
Vous le comprenez certes mieux, mais je ne vois pas en quoi :

Code :

1
2
3
4
5
6
7
8
9
SELECT a.va, b2.vb, b2.vc
  FROM a
       LEFT OUTER JOIN 
       (
	  SELECT b.id, b.vb, c.vc
            FROM b
                 INNER JOIN c ON c.id = b.c_id
       ) b2
	 ON b2.id = a.b_id;

est plus propre que :

Code :

1
2
3
4
5
6
SELECT a.va, b.vb, b.vc
  FROM a
       LEFT OUTER JOIN b
       INNER JOIN c
         ON c.id = b.c_id
         ON b.id = a.b_id;

Il y a plus de select, plus d'alias, si vous voulez rajouter une colonne dans le select final il faut allez aussi le rajouter dans la sous-requête.

C'est exactement la même chose que de remonter certains critères de jointures dans le FROM pour les jointures externes.
Ce n'est jamais obligatoire, c'est juste une simplification syntaxique qui répond à des règles précises !

Et si c'est un peu ambigu au début il suffit de rajouter des parenthèses comme l'a fait StringBuilder :

Code :

1
2
3
4
5
6
7
8
9
SELECT a.va, b.vb, b.vc
  FROM a
       LEFT OUTER JOIN
       (
         b
         INNER JOIN c
           ON c.id = b.c_id
       )
         ON b.id = a.b_id;

On garde la facilité d'écriture et c'est visuellement plus parlant.

Dans le même registre je continue à mettre des parenthèses avec les prédicats OR et AND lorsqu'ils sont mélangés car je ne me souviens jamais de leur règle de priorité !

Envoyé par CinePhil

Mouais... des clés étrangères "NULLables" c'est pas terrible !

Ça arrive tout le temps ! Heureusement qu'elles sont nullables les clefs étrangères.
Pour reprendre votre exemple : on crée un nouveau projet, on lui affecte un responsable la semaine suivante.
Il n'y a pas de problème, et encore une fois, ça arrive tout le temps.

J'ai beaucoup cité Cinephil, rien de personnel bien entendu !