LATERAL
Transformer des champs, contenus dans chaque enregistrement d'une table, en enregistrements
Fonction :
La fonction LATERAL permet de transformer les champs d’une table en enregistrements d’une nouvelle table, qui sera alors requêtable plus facilement.
Ceci est particulièrement intéressant sur les tables contenant plusieurs champs (= plusieurs colonnes) qui portent le même type d’informations.
Syntaxe :
La fonction LATERAL doit être indiquée après la table de la clause FROM.
Elle est suivie :
- du mot clef VALUES et de la liste des champs de la tables qui seront extraits pour être transformés en enregistrements.
- et du mot clef AS qui permettra de renommer chaque champ extrait.
AS doit être suivi du préfixe à utiliser pour identifier les nouveaux champs ainsi renommés.
LATERAL(VALUES(mon premier champ à extraire, mon second champ à extraire)
AS mon prefixe(mon premier nouveau champ, mon second nouveau champ) )
Exemples :
Voici la table CDM_FOOT_2 contenant la liste des pays ayant gagné la coupe du monde de football au moins une fois, avec 5 champs ANNEE_CHAMPION et 5 champs PAYS_ORGANISATEURS contenant le même type d’informations :
Pour extraire le PAYS qui a remporté la coupe du monde en Suède il va falloir tester les 5 champs Pays_Organisateur, de même pour extraire le pays ayant remporté la coupe du monde en 1938 il faudra tester les 5 champs Année_Champion :
SELECT PAYS FROM CDM_FOOT_2
WHERE PAYS_ORGANISATEUR_1 = « SUEDE »
OR PAYS_ORGANISATEUR_2 = « SUEDE »
OR PAYS_ORGANISATEUR_3 = « SUEDE »
OR PAYS_ORGANISATEUR_4 = « SUEDE »
OR PAYS_ORGANISATEUR_5 = « SUEDE »;
SELECT PAYS FROM CDM_FOOT_2
WHERE ANNEE_CHAMPION_1 = 1938
OR ANNEE_CHAMPION_2 = 1938
OR ANNEE_CHAMPION_3 = 1938
OR ANNEE_CHAMPION_4 = 1938
OR ANNEE_CHAMPION_5 = 1938;
La fonction LATERAL va permettre de transformer les champs 5 champs ANNEE_CHAMPION_x et les 5 champs PAYS_ORGANISATEUR_x en enregistrements d’une table fictive qui pourra être requêtée.
SELECT L.ANNEE, L.PAYS_ORGANISATEUR, A.PAYS as PAYS_VAINQUEUR, A.CONTINENT, A.NOMBRE_COUPES
FROM CDM_FOOT_2 A,
LATERAL(VALUES (A.ANNEE_CHAMPION_1, A.PAYS_ORGANISATEUR_1),
(A.ANNEE_CHAMPION_2, A.PAYS_ORGANISATEUR_2),
(A.ANNEE_CHAMPION_3, A.PAYS_ORGANISATEUR_3),
(A.ANNEE_CHAMPION_4, A.PAYS_ORGANISATEUR_4),
(A.ANNEE_CHAMPION_5, A.PAYS_ORGANISATEUR_5)
) AS L(ANNEE, PAYS_ORGANISATEUR)
WHERE L.ANNEE <> 0
ORDER BY L.ANNEE;
Dans cet exemple on voit que la clause WHERE peut cibler les données de la table fictive, ici pour permettre de ne sélectionner que les champs ANNEE_CHAMPION renseignée.
De même la clause ORDER BY peut porter, elle aussi, sur les données de la table fictive pour ordonner les résultats.
Il est ainsi plus facile de faire sa sélection sur l’année ou sur le pays organisateur sans avoir à tester les 5 champs ANNEE_CHAMPION ou les 5 champs PAYS_ORGANISATEUR :
SELECT A.PAYS as PAYS_VAINQUEUR, L.PAYS_ORGANISATEUR, L.ANNEE, A.NOMBRE_COUPES
FROM CDM_FOOT_2 A,
LATERAL(VALUES (A.ANNEE_CHAMPION_1, A.PAYS_ORGANISATEUR_1),
(A.ANNEE_CHAMPION_2, A.PAYS_ORGANISATEUR_2),
(A.ANNEE_CHAMPION_3, A.PAYS_ORGANISATEUR_3),
(A.ANNEE_CHAMPION_4, A.PAYS_ORGANISATEUR_4),
(A.ANNEE_CHAMPION_5, A.PAYS_ORGANISATEUR_5)
) AS L(ANNEE, PAYS_ORGANISATEUR)
WHERE L.PAYS_ORGANISATEUR = ‘SUEDE‘;
SELECT A.PAYS as PAYS_VAINQUEUR, L.ANNEE, L.PAYS_ORGANISATEUR
FROM CDM_FOOT_2 A,
LATERAL(VALUES (A.ANNEE_CHAMPION_1, A.PAYS_ORGANISATEUR_1),
(A.ANNEE_CHAMPION_2, A.PAYS_ORGANISATEUR_2),
(A.ANNEE_CHAMPION_3, A.PAYS_ORGANISATEUR_3),
(A.ANNEE_CHAMPION_4, A.PAYS_ORGANISATEUR_4),
(A.ANNEE_CHAMPION_5, A.PAYS_ORGANISATEUR_5)
) AS L(ANNEE, PAYS_ORGANISATEUR)
WHERE L.ANNEE = 1938;
Pour obtenir le même résultat sans utiliser la fonction LATERAL, il aurait fallut utiliser la requête suivante (qui est quand même plus complexe, plus difficile à maintenir et surtout compliquée à rendre dynamique) :
SELECT PAYS,
1938 AS ANNEE,
CASE WHEN ANNEE_CHAMPION_1 = 1938 THEN PAYS_ORGANISATEUR_1
WHEN ANNEE_CHAMPION_2 = 1938 THEN PAYS_ORGANISATEUR_2
WHEN ANNEE_CHAMPION_3 = 1938 THEN PAYS_ORGANISATEUR_3
WHEN ANNEE_CHAMPION_4 = 1938 THEN PAYS_ORGANISATEUR_4
WHEN ANNEE_CHAMPION_5 = 1938 THEN PAYS_ORGANISATEUR_5
END AS PAYS_ORGANISATEUR
FROM CDM_FOOT_2
WHERE ANNEE_CHAMPION_1 = 1938
OR ANNEE_CHAMPION_2 = 1938
OR ANNEE_CHAMPION_3 = 1938
OR ANNEE_CHAMPION_4 = 1938
OR ANNEE_CHAMPION_5 = 1938;
