Revision SQL

Author

Romain

Packages utiles

library(sqldf)    
library(questionr)
library(gt)

Syntaxe générale

sqldf(" 
  SELECT #|①
    AGREG(VAR6) AS NOM6,
    CASE WHEN VAR8='yes' THEN 1 ELSE 0 END AS NOM8,
    CASE WHEN VAR9<10 THEN 'petit'
         WHEN VAR9 BETWEEN 10 AND 50 THEN 'moyen'
         WHEN VAR9>50 THEN 'gros'
         ELSE NULL
         END AS NOM9
  FROM #|②
    TABLE1 AS t1 
    LEFT JOIN TABLE2 AS t2 ON t1.id=t2.index #|③
  WHERE#|④
    t1.VAR1='caractères' 
    AND t2.VAR2=99
    AND (VAR3='modalite1' OR VAR4='modalite2')
    AND VAR5 IN ('modalite3','modalite4')
    AND VAR10 LIKE '%US%'
  GROUP BY
    NOM8,NOM9
  HAVING #|⑤
    NOM6>150
  ORDER BY
    NOM6 Desc
  LIMIT
    10
      ")

①.On selectionne les variables à afficher

②.On précise d’où viennent les variables

③. LEFT JOIN ou INNER JOIN. POur plus dinformations sur les jointures possibles voir https://cartman34.fr/informatique/sgbd/differences-entre-inner-left-right-et-outer-join-en-sql.html

④. Ici on va mettre les conditions qsur les variables. Attention. Le WHERE s’execute avant le select donc il faut utiliser les noms natifs des variables, pas les surnoms.

⑤. C’est le WHERE pour les variables agrégées

Les agrégats

🧮 Fonctions d’agrégation SQL (pour sqldf)

Les fonctions d’agrégation permettent de résumer les données au sein de chaque groupe (GROUP BY).

Fonction	Rôle	Exemple d’usage	Résultat
`SUM(colonne)`	Somme des valeurs numériques	`SUM(VAR6)`	Total des valeurs de `VAR6` par groupe
`AVG(colonne)`	Moyenne arithmétique	`AVG(VAR6)`	Moyenne de `VAR6` par groupe
`COUNT(*)`	Nombre total de lignes, nulle ou pas	`COUNT(*)`	Nombre d’observations dans le groupe par modalité en cas de GROUP BY
`COUNT(colonne)`	Nombre de valeurs non nulles	`COUNT(VAR6)`	Nombre d’observations non nulle pour VAR6
`MIN(colonne)`	Valeur minimale	`MIN(VAR6)`	Plus petite valeur du groupe
`MAX(colonne)`	Valeur maximale	`MAX(VAR6)`	Plus grande valeur du groupe
`GROUP_CONCAT(colonne, ', ')`	Concatène les valeurs d’un groupe	`GROUP_CONCAT(VAR3, ', ')`	Liste des modalités de `VAR3` séparées par des virgules
`TOTAL(colonne)`	Variante de `SUM` qui renvoie `0` si tout est `NULL`	`TOTAL(VAR6)`	Évite les `NULL` dans la somme

Notes : - Ces fonctions s’utilisent dans le bloc SELECT après GROUP BY. - On peut combiner plusieurs agrégations : sql SELECT SUM(VAR6) AS total, AVG(VAR6) AS moyenne, COUNT(*) AS n FROM table GROUP BY categorie

L’ordre d’execution

🧠 Ordre logique d’exécution d’une requête SQL

Même si on écrit une requête SQL de haut en bas (SELECT ... FROM ... WHERE ...),
le moteur SQL ne l’exécute pas dans cet ordre.
Voici la séquence réelle :

1️⃣ FROM — charge les tables et exécute les jointures.
2️⃣ WHERE — filtre les lignes selon les conditions.
3️⃣ GROUP BY — regroupe les lignes restantes en catégories.
4️⃣ HAVING — filtre les groupes (après agrégation).
5️⃣ SELECT — calcule les fonctions d’agrégation (SUM, AVG, COUNT, etc.) et affiche les résultats.
6️⃣ ORDER BY — trie les lignes finales selon un critèr (Desc pour une decroissance).

💡 En résumé : > Le WHERE filtre avant le regroupement,
> le HAVING filtre après,
> et les agrégations du SELECT sont calculées dans les groupes formés par le GROUP BY.

Exemple

chargement des données

CREDITS <- read.csv(file = "../DISNEY/credits.csv", 
                   header = TRUE, 
                   sep = ',', 
                   dec = ',', 
                   stringsAsFactors = TRUE)

TITRE <- read.csv2(file = "../DISNEY/titles.csv", 
                   header = TRUE, 
                   sep = ',', 
                   dec = ',', 
                   stringsAsFactors = TRUE)

description des variables

names(CREDITS)

[1] "person_id" "id"        "name"      "character" "role"

str(CREDITS)

'data.frame':   30689 obs. of  5 variables:
 $ person_id: int  35549 57832 57833 25096 27185 22459 57834 57841 57860 27567 ...
 $ id       : Factor w/ 1733 levels "tm1","tm100013",..: 1161 1161 1161 1161 1161 1161 1161 1161 1161 1161 ...
 $ name     : Factor w/ 19851 levels "'Weird Al' Yankovic",..: 13068 9659 5606 14115 15443 15361 751 7366 8319 8939 ...
 $ character: Factor w/ 18236 levels "","'Adolph Hitler'",..: 4426 5843 8826 15883 6546 8341 455 13391 4602 16479 ...
 $ role     : Factor w/ 2 levels "ACTOR","DIRECTOR": 1 1 1 1 1 1 1 1 1 1 ...

names(TITRE)

 [1] "id"                   "title"                "type"                
 [4] "description"          "release_year"         "age_certification"   
 [7] "runtime"              "genres"               "production_countries"
[10] "seasons"              "imdb_id"              "imdb_score"          
[13] "imdb_votes"           "tmdb_popularity"      "tmdb_score"

str(TITRE)

'data.frame':   1854 obs. of  15 variables:
 $ id                  : Factor w/ 1854 levels "tm1","tm100013",..: 1190 990 986 1169 1050 1082 1022 501 1151 1018 ...
 $ title               : Factor w/ 1814 levels "'Twas the Night",..: 920 1353 263 393 436 111 1236 1600 453 774 ...
 $ type                : Factor w/ 2 levels "MOVIE","SHOW": 1 1 1 1 1 1 1 1 1 1 ...
 $ description         : Factor w/ 1845 levels "","'Toon star Roger is worried that his wife Jessica is playing pattycake with someone else, so the studio hires d"| __truncated__,..: 977 1538 472 561 1667 387 23 1524 1580 1142 ...
 $ release_year        : int  1947 1950 1950 1941 1941 1942 1938 1935 1938 1937 ...
 $ age_certification   : Factor w/ 12 levels "","G","PG","PG-13",..: 2 2 2 2 2 2 2 1 2 1 ...
 $ runtime             : int  96 68 74 64 119 70 83 9 8 9 ...
 $ genres              : Factor w/ 718 levels "['action', 'animation', 'comedy', 'drama', 'family', 'fantasy', 'scifi']",..: 493 583 536 147 166 148 539 144 352 352 ...
 $ production_countries: Factor w/ 85 levels "['AE']","['AR', 'BR']",..: 83 83 83 83 83 83 83 83 83 83 ...
 $ seasons             : Factor w/ 20 levels "","1.0","10.0",..: 1 1 1 1 1 1 1 1 1 1 ...
 $ imdb_id             : Factor w/ 1377 levels "","tt0032455",..: 11 13 14 5 2 7 504 1 1 1 ...
 $ imdb_score          : Factor w/ 66 levels "","1.6","2.5",..: 53 43 47 46 51 47 50 1 1 1 ...
 $ imdb_votes          : Factor w/ 1153 levels "","10.0","100.0",..: 825 254 248 153 1147 191 346 1 1 1 ...
 $ tmdb_popularity     : Factor w/ 1708 levels "","0.6","0.603",..: 833 521 1696 1363 1235 1458 1556 1648 511 1330 ...
 $ tmdb_score          : Factor w/ 651 levels "","10.0","2.0",..: 454 222 364 350 453 353 383 272 241 282 ...

sélection de données

Exemple 1

On veut faire apparaitre le nombre de films Disney dans lesquels le personnage “Mickey Mouse” est apparu chaque année?

sqldf(" 
  SELECT
    release_year,
    COUNT(TITRE.title) AS Nb_films
  FROM
    TITRE
    INNER JOIN CREDITS ON TITRE.id=CREDITS.id
  WHERE
    CREDITS.character='Mickey Mouse' -- LIKE '%Mickey%' renverrait également les voix de Mickey
    AND TITRE.type='MOVIE'
  GROUP BY
    release_year
")

  release_year Nb_films
1         1939        1
2         1940        1
3         1941        1
4         1947        1
5         1948        3

On peut améliorer l’output avec gt

sqldf(" 
  SELECT
    release_year,
    COUNT(TITRE.title) AS Nb_films
  FROM
    TITRE
    INNER JOIN CREDITS ON TITRE.id=CREDITS.id
  WHERE
    CREDITS.character='Mickey Mouse' -- LIKE '%Mickey%' renverrait également les voix de Mickey
    AND TITRE.type='MOVIE'
  GROUP BY
    release_year
") %>% 
  gt %>% tab_header(title="Nombre de films avec Mickey",subtitle = "par année")

Nombre de films avec Mickey
par année
release_year	Nb_films
1939	1
1940	1
1941	1
1947	1
1948	3

Exemple 2

Nombre de programme de plus de 30 minutes par type (movie/show) et nationalité de la (co)production (USA oui/non) et durée moyenne. Ordonner par nombre décroissant de programmes.

sqldf("
  SELECT
    Type,
    COUNT(TITLE) AS NB,
    round(AVG(runtime),2) AS durée_moyenne,
    CASE WHEN production_countries LIKE '%US%' THEN 'yes'
         ELSE 'no'
         END AS prod_US
  FROM
    TITRE
  WHERE
    runtime >30
  GROUP BY
    Type, prod_US
  ORDER BY
    NB Desc
       ") %>% 
  gt() %>% tab_header(title="Nombre de programme et durée moyenne par type et nationalité de production",subtitle = "Pour les programmes de plus de 30 minutes")

Nombre de programme et durée moyenne par type et nationalité de production
Pour les programmes de plus de 30 minutes
type	NB	durée_moyenne	prod_US
MOVIE	965	87.74	yes
SHOW	166	45.82	yes
MOVIE	98	64.44	no
SHOW	64	44.59	no

La fonction “LIKE” ‘%mot%’ MEMO

La commande LIKE permet d’effectuer une recherche partielle dans une chaîne de caractères.
Le symbole % agit comme un joker (wildcard) qui représente “n’importe quelle suite de caractères”.

Syntaxe	Signification	Exemple	Résultat
`LIKE 'King'`	Mot exact	`"King"`	Ne renvoie que King
`LIKE '%King%'`	Contient “King” n’importe où	`"The King in the North"`, `"Mockingbird"`	✅ les deux
`LIKE 'King%'`	Commence par “King”	`"King Robert"`, `"Kingsguard"`	✅
`LIKE '%King'`	Se termine par “King”	`"The Mad King"`	✅
`LIKE '% King %'`	“ King ” entouré d’espaces	`"The Mad King dies"`	✅, mais ❌ `"Mockingbird"`
`NOT LIKE '%King%'`	N’inclut pas “King”	Exclut tous les titres contenant ce motif	❌
`LIKE 'K_ng'`	`_` = 1 caractère exactement	`"King"` ✅, `"Kng"` ❌	1 seul caractère manquant

💡 Astuce : pour ignorer la casse (minuscules/majuscules), on peut combiner avec LOWER() :

WHERE LOWER(episodeTitle) LIKE ‘%king%’

La fonction LAG MEMO

La fonction `LAG()`

La fonction LAG() est une fonction de fenêtre (window function) qui permet d’accéder à la valeur d’une ligne précédente dans un jeu de données, sans regrouper les observations.
Elle est très utile pour calculer des évolutions temporelles ou des écarts entre observations successives (par exemple entre deux années, deux mois, ou deux épisodes).

Syntaxe	Description	Exemple	Résultat
`LAG(colonne)`	Renvoie la valeur précédente de la colonne	`LAG(année)`	Valeur de l’année précédente
`LAG(colonne, 2)`	Décale de 2 lignes en arrière	`LAG(total, 2)`	Valeur deux lignes avant
`LAG(colonne, décalage, valeur_par_défaut)`	Définit une valeur par défaut si aucune donnée précédente n’existe	`LAG(total, 1, 0)`	Renvoie `0` à la première ligne au lieu de `NULL`
`LAG(colonne) OVER (ORDER BY variable)`	Spécifie l’ordre de calcul	`LAG(SUM(MONTANT)) OVER (ORDER BY ANNEE)`	Compare la somme à l’année précédente
`LEAD(colonne)`	Inverse de `LAG()` : renvoie la valeur suivante	`LEAD(total)`	Valeur de la ligne suivante

💡 Remarque : si aucune valeur précédente n’existe, LAG() renvoie NULL par défaut.
Le troisième argument (valeur_par_défaut) permet de remplacer ce NULL par une autre valeur.

Exemple d’utilisation simple

SELECT ANNEE, SUM(MONTANT) AS Total, LAG(SUM(MONTANT),1,0) OVER (ORDER BY ANNEE) AS Total_prec FROM VENTES GROUP BY ANNEE;

ANNEE	Total	Total_prec
1996	15.2	0
1997	21.5	15.2
1998	28.7	21.5

💡 Astuce : le 0 dans LAG(...,1,0) évite d’avoir une valeur NULL à la première ligne.

Exemple avec pourcentage d’évolution

SELECT ANNEE, SUM(MONTANT) AS Total, LAG(SUM(MONTANT),1) OVER (ORDER BY ANNEE) AS Total_prec, ROUND(((SUM(MONTANT)/LAG(SUM(MONTANT),1) OVER (ORDER BY ANNEE))-1)*100,1) AS Pct_Evol FROM VENTES GROUP BY ANNEE;

ANNEE	Total	Total_prec	Pct_Evol
1996	15.2	NULL	NULL
1997	21.5	15.2	+41.4
1998	28.7	21.5	+33.5

💡 Astuce : pour comparer des valeurs futures plutôt que passées, on utilise LEAD() à la place de LAG().

Les requêtes imbriquées MEMO

Les requêtes imbriquées (ou sous-requêtes) permettent de créer des tables temporaires à l’intérieur d’une même instruction SQL.
Elles sont souvent introduites par la clause WITH, qui définit une ou plusieurs CTE (Common Table Expressions).
Chaque sous-requête agit comme une table virtuelle réutilisable dans la requête principale.

Syntaxe	Description	Exemple	Résultat
`WITH table_temp AS (SELECT ... )`	Crée une table temporaire disponible pour la requête suivante	`WITH PXMOY AS (SELECT AVG(prix) FROM PRIX)`	Table contenant la moyenne des prix
`WITH t1 AS (...), t2 AS (...)`	Définit plusieurs sous-requêtes séparées par des virgules	`WITH CARTES AS(...), PRIX AS(...) SELECT ...`	Plusieurs tables intermédiaires utilisées ensuite
`SELECT * FROM table_temp`	Utilise la table temporaire définie plus haut	`SELECT * FROM PXMOY`	Résultat de la sous-requête
`JOIN ... ON 1=1`	Fusionne toutes les lignes d’une table avec une table à une seule valeur	`LEFT JOIN PXMOY ON 1=1`	Attribue la même moyenne à toutes les lignes

💡 Remarques :
- Les requêtes imbriquées rendent les requêtes complexes plus lisibles.
- On peut chaîner plusieurs CTE dans un seul bloc WITH.
- Chaque sous-requête définie doit avoir un nom unique.

Exemple 1 : Cartes dont le prix total dépasse la moyenne

WITH
PXMOY AS (
SELECT AVG(BOIS+RESINE+GALET+BAIE) AS prix_moyen
FROM PRIX
),
PRIX_HAUT AS (
SELECT NOM, (BOIS+RESINE+GALET+BAIE) AS px, prix_moyen
FROM CARTE AS t1
LEFT JOIN PRIX AS t2 ON t1.IDENTIFIANT = t2.IDENTIFIANT
LEFT JOIN PXMOY ON 1 = 1
)
SELECT NOM, px, prix_moyen
FROM PRIX_HAUT
WHERE px > prix_moyen;

NOM	px	prix_moyen
Université	12	7.8
Palais	11	7.8

💡 Astuce : la jointure ON 1=1 permet d’appliquer une valeur unique (ici la moyenne) à toutes les lignes.

Exemple 2 : Cartes ayant le même coût total que “Université”

WITH
PX_UNIV AS (
SELECT NOM, (BOIS+RESINE+GALET+BAIE) AS px_universite
FROM PRIX
LEFT JOIN CARTE ON PRIX.IDENTIFIANT = CARTE.IDENTIFIANT
WHERE NOM = ‘UNIVERSITE’
),
COUT AS (
SELECT CARTE.NOM,
CASE WHEN (BOIS+RESINE+GALET+BAIE) = px_universite THEN ‘YES’ ELSE ‘NO’ END AS CRITERE
FROM PRIX
LEFT JOIN CARTE ON PRIX.IDENTIFIANT = CARTE.IDENTIFIANT
LEFT JOIN PX_UNIV ON 1 = 1
WHERE CRITERE = ‘YES’
AND TYPE = ‘CONSTRUCTION’
AND TOP_UNIQUE = 1
)
SELECT COUT.NOM
FROM COUT
WHERE NOM != ‘UNIVERSITE’;

NOM
Tour
Palais

Exemple 3 : Cartes de la même couleur que la plus coûteuse

WITH
COULEUR_CHER AS (
SELECT COULEUR AS couleur_cible, (BOIS+RESINE+GALET+BAIE) AS px_total
FROM PRIX
LEFT JOIN CARTE ON PRIX.IDENTIFIANT = CARTE.IDENTIFIANT
ORDER BY px_total DESC
LIMIT 1
),
CIBLE AS (
SELECT CARTE.NOM, BOIS+RESINE+GALET+BAIE AS px, CARTE.COULEUR
FROM PRIX
LEFT JOIN CARTE ON PRIX.IDENTIFIANT = CARTE.IDENTIFIANT
LEFT JOIN COULEUR_CHER ON 1 = 1
WHERE COULEUR = couleur_cible
)
SELECT *
FROM CIBLE;

NOM	px	COULEUR
Palais	11	ROUGE
Place du Marché	8	ROUGE

Exemple 4 : Trouver la créature la plus “rentable”

WITH
crea_rentable AS (
SELECT CARTE.NOM, CARTE.IDENTIFIANT AS id, BOIS+RESINE+GALET+BAIE AS px
FROM CARTE
LEFT JOIN PRIX ON CARTE.IDENTIFIANT = PRIX.IDENTIFIANT
LEFT JOIN CHAINAGE ON CARTE.NOM = CHAINAGE.NOM
WHERE TYPE = ‘CREATURE’
ORDER BY px DESC
LIMIT 1
),
chaineurs AS (
SELECT CHAINAGE.NOM
FROM CHAINAGE
LEFT JOIN crea_rentable ON 1 = 1
WHERE IDENTIFIANT_CHAINAGE = id
)
SELECT *
FROM chaineurs;

NOM
Palais
Chapelle

Exemple 5 : Carte la plus chère dans chaque couleur

WITH
FUSION AS (
SELECT CARTE.NOM AS nom, COULEUR.NOM_COULEUR AS couleur, COULEUR.EFFET AS effet, BOIS+RESINE+GALET+BAIE AS px
FROM CARTE
LEFT JOIN PRIX ON CARTE.IDENTIFIANT = PRIX.IDENTIFIANT
LEFT JOIN COULEUR ON CARTE.COULEUR = COULEUR.IDENTIFIANT
)
SELECT nom, couleur, effet, px
FROM FUSION
WHERE (couleur, px) IN (
SELECT couleur, MAX(px)
FROM FUSION
GROUP BY couleur
);

nom	couleur	effet	px
Université	VIOLET	Gain de PV	12
Palais	ROUGE	Gain de ressources	11
Port	BLEU	Pioche de carte	10
Chapelle	GRIS	Gain de PV	9

💡 Astuce finale :
Les requêtes imbriquées (ou CTE) sont particulièrement puissantes pour :
- factoriser des calculs intermédiaires (moyenne, total, top 1, etc.) ;
- rendre les requêtes longues plus lisibles ;
- éviter de répéter plusieurs fois la même sous-requête.