Norme SQL - Jointure partitionnée . . . étude

**SQLpro** · 24/09/2023, 20h33

Bonjour,

dans le cadre du nouveau livre que j'écris sur SQL, je rajoute un paragraphe sur les jointures partitionnées. J'ai cherché un exemple simpe.. Mais peut être est-il trop simpliste. Dites moi ce que vous en pensez et si vous avec des exemples plus complexes qui diffèreraient de mon analyse...

En PJ : l'extrait du livre Jointures partitionnées SQLpro F Brouard.pdf (le script SQL de l'exemple figure ci-dessous)

A +

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
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
-- tables et données pour notre exemple
CREATE TABLE T_CHARGEMENT_CHG 
(CHG_ID            INT IDENTITY PRIMARY KEY,
 CHG_DATE          DATE NOT NULL,
 CHG_ENTREPOT      VARCHAR(32) NOT NULL,
 CHG_NOMBRE        SMALLINT NOT NULL);
 
INSERT INTO T_CHARGEMENT_CHG  VALUES (  '2023-09-17', 'Marseille', 11 ) ;
INSERT INTO T_CHARGEMENT_CHG  VALUES (  '2023-09-15', 'Marseille', 10 ) ;
INSERT INTO T_CHARGEMENT_CHG  VALUES (  '2023-09-16', 'Paris', 20 ) ;
INSERT INTO T_CHARGEMENT_CHG  VALUES (  '2023-09-17', 'Lyon', 31 ) ;
INSERT INTO T_CHARGEMENT_CHG  VALUES (  '2023-09-16', 'Lyon', 30 ) ;
 
CREATE TABLE T_ENTREPOT_ETP
(ETP_ENTREPOT varchar(32));
 
INSERT INTO T_ENTREPOT_ETP ( ETP_ENTREPOT ) VALUES ( 'Marseille' );
INSERT INTO T_ENTREPOT_ETP ( ETP_ENTREPOT ) VALUES ( 'Paris' );
INSERT INTO T_ENTREPOT_ETP ( ETP_ENTREPOT ) VALUES ( 'Lyon' );
 
--> les requêtes suivantes :
SELECT S.CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM   T_ENTREPOT_ETP AS L
       RIGHT OUTER JOIN T_CHARGEMENT_CHG S 
          ON S.CHG_ENTREPOT = L.ETP_ENTREPOT;
 
SELECT S.CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM   T_ENTREPOT_ETP AS L
       LEFT OUTER JOIN T_CHARGEMENT_CHG S 
          ON S.CHG_ENTREPOT = L.ETP_ENTREPOT;
 
SELECT S.CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM   T_ENTREPOT_ETP AS L
       INNER JOIN T_CHARGEMENT_CHG S 
          ON S.CHG_ENTREPOT = L.ETP_ENTREPOT;
 
--> donnent toutes le même résultat qui ne fait pas apparaître 
--  toutes les dates combinées aux entreprots...
/*
 
CHG_DATE   ETP_ENTREPOT                     CHG_NOMBRE
---------- -------------------------------- ----------
2023-09-17 Marseille                        11
2023-09-15 Marseille                        10
2023-09-16 Paris                            20
2023-09-17 Lyon                             31
2023-09-16 Lyon                             30
*/
--> Il y manque 4 lignes :
/*
 
2023-09-16 Marseille                        NULL
2023-09-15 Paris                            NULL
2023-09-17 Paris                            NULL
2023-09-15 Lyon                             NULL
*/
 
--> la jointure partitionnée résous élégamment ce problème :
SELECT S.CHG_DATE, L.ETP_ENTREPOT,S.CHG_NOMBRE 
FROM   T_CHARGEMENT_CHG AS S PARTITION BY (CHG_DATE) 
       RIGHT OUTER JOIN T_ENTREPOT_ETP L 
          ON S.CHG_ENTREPOT = L.ETP_ENTREPOT;
 
/*
CHG_DATE   ETP_ENTREPOT     CHG_NOMBRE
---------- ---------------- -----------
2019-01-15 Boston           NULL
2019-01-15 London           10
2019-01-15 Paris            NULL
2019-01-16 Boston           30
2019-01-16 London           NULL
2019-01-16 Paris            20
2019-01-17 Boston           31
2019-01-17 London           11
2019-01-17 Paris            NULL
*/
 
--> équivalence avec expression de chacun des valeurs du domaine
SELECT CAST('2023-09-15' AS DATE) AS CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM   T_ENTREPOT_ETP AS L
       RIGHT OUTER JOIN T_CHARGEMENT_CHG AS S
          ON S.CHG_ENTREPOT = L.ETP_ENTREPOT AND S.CHG_DATE = '2023-09-15'
UNION ALL
SELECT CAST('2023-09-16' AS DATE)  AS CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM   T_CHARGEMENT_CHG AS S
       RIGHT OUTER JOIN T_ENTREPOT_ETP AS L
          ON S.CHG_ENTREPOT = L.ETP_ENTREPOT AND S.CHG_DATE = '2023-09-16'
UNION ALL
SELECT CAST('2023-09-17' AS DATE)  AS CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM   T_CHARGEMENT_CHG AS S
       RIGHT OUTER JOIN T_ENTREPOT_ETP AS L
          ON S.CHG_ENTREPOT = L.ETP_ENTREPOT  AND S.CHG_DATE = '2023-09-17'
ORDER BY 1, 2
 
--> 4 formes d'équivalence avec plusieurs jointures dont un produit cartésien
SELECT CE.*, C.CHG_NOMBRE
FROM   (SELECT *
        FROM   (SELECT DISTINCT CHG_DATE 
                FROM   T_CHARGEMENT_CHG) AS T1 
               CROSS JOIN (SELECT DISTINCT ETP_ENTREPOT 
                           FROM   T_ENTREPOT_ETP) AS T2
        ) AS CE 
        LEFT OUTER JOIN (SELECT CHG_DATE, CHG_ENTREPOT, CHG_NOMBRE 
                         FROM   T_CHARGEMENT_CHG) AS C 
           ON CE.ETP_ENTREPOT = C.CHG_ENTREPOT 
              AND CE.CHG_DATE = C.CHG_DATE 
ORDER BY 1, 2;
 
WITH T AS
(SELECT C.CHG_DATE, E.ETP_ENTREPOT, C.CHG_NOMBRE
 FROM   T_CHARGEMENT_CHG AS C  
        INNER JOIN T_ENTREPOT_ETP AS E 
           ON C.CHG_ENTREPOT = E.ETP_ENTREPOT)
SELECT * 
FROM   T
UNION  ALL
SELECT DISTINCT C.CHG_DATE, E.ETP_ENTREPOT, NULL
FROM   T_CHARGEMENT_CHG AS C  
       CROSS JOIN T_ENTREPOT_ETP AS E
WHERE  NOT EXISTS(SELECT * 
                  FROM   T 
                  WHERE  T.CHG_DATE = C.CHG_DATE 
                         AND E.ETP_ENTREPOT = T.ETP_ENTREPOT)
ORDER  BY 1, 2;
 
WITH 
T AS (SELECT DISTINCT CHG_DATE, X.CHG_ENTREPOT 
      FROM   T_CHARGEMENT_CHG 
             CROSS JOIN (SELECT CHG_ENTREPOT 
                         FROM   T_CHARGEMENT_CHG) AS X)
SELECT T.CHG_DATE, T.CHG_ENTREPOT, S.CHG_NOMBRE 
FROM   T
       LEFT OUTER JOIN T_CHARGEMENT_CHG AS S
          ON T.CHG_DATE = S.CHG_DATE 
             AND T.CHG_ENTREPOT = S.CHG_ENTREPOT
          LEFT OUTER JOIN T_ENTREPOT_ETP AS L
             ON S.CHG_ENTREPOT = L.ETP_ENTREPOT
ORDER BY 1, 2;
 
WITH
T AS
(
SELECT DISTINCT C.CHG_DATE, E.ETP_ENTREPOT
FROM   T_CHARGEMENT_CHG AS C  
       CROSS JOIN T_ENTREPOT_ETP AS E
)
SELECT T.CHG_DATE, T.ETP_ENTREPOT, C.CHG_NOMBRE
FROM   T
       LEFT OUTER JOIN T_CHARGEMENT_CHG AS C  
          ON T.CHG_DATE = C.CHG_DATE 
             AND T.ETP_ENTREPOT = C.CHG_ENTREPOT
ORDER BY 1, 2;

**escartefigue** · 25/09/2023, 10h41

Bonjour Frederic

quelques remarques

tant qu'à faire, j'aurais ajouté une colonne identifiant d'entrepôt dans la table des entrepôts et remplacé la colonne libellé d'entrepôt par cet identifiant dans la table des chargements
dans le résultat communiqué de l'extrait du livre page 27, on trouve des lignes mentionnant "boston" et "london" alors que les entrepôts et chargements concernent Lyon, Marseille et Paris