En tant que data scientist ou data analyst, les données sont naturellement au cœur de votre métier. Vous pouvez les collecter à partir de nombreuses sources. Très souvent, elles résident dans une base de données SQL ; dans ce cas, une bonne maîtrise des commandes de requête SQL est essentielle pour réussir. Cet article vous présente les commandes de base, puis des opérations plus avancées qui vous seront utiles au quotidien.
Pour illustrer ces commandes, nous utiliserons la base de données open source « DVD rental ». Vous trouverez le diagramme entité-relation (ERD) ici.
Dans le cadre de cet article, nous avons installé la base en local et exécuté toutes les requêtes avec pgAdmin4, donc sur postgreSQL. La plupart de ces commandes fonctionneront également sur d’autres serveurs SQL. L’installation de postgreSQL et la mise en place de la base sortent du périmètre de cet article ; nous vous invitons à consulter d’autres ressources, comme le guide du débutant PostgreSQL de DataCamp.
Récupération simple de données
SELECT FROM
Le cas le plus simple consiste à extraire tout le contenu d’une table. Si nous voulons lister toutes les catégories de films de la table « category », nous pourrions saisir :
SELECT *
FROM category
Ce qui renvoie (seulement les 10 premières lignes affichées) :
Nous avons indiqué le nom de la table après FROM. Comme nous voulons tout le contenu, nous avons utilisé * après SELECT pour tout sélectionner.
DISTINCT
Parfois, seuls les valeurs uniques nous intéressent. Par exemple, si l’on souhaite connaître toutes les classifications MPAA présentes dans la table « film ». DISTINCT permet de ne renvoyer que les valeurs uniques :
SELECT DISTINCT(rating)
FROM film
Ce qui renvoie :
Récupération de données avec conditions simples
WHERE
Il arrive aussi que l’on ne souhaite récupérer que les enregistrements répondant à certaines conditions. On peut les préciser grâce à la clause WHERE, qui filtre les lignes. Par exemple, si l’on veut uniquement les films classés PG-13 :
SELECT title
FROM film
WHERE rating = 'PG-13'
Ce qui renvoie (seulement les 10 premières lignes affichées) :
On peut également lister les films dont le prix de location est supérieur ou égal à 4,5 :
SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5
Ce qui renvoie (seulement les 10 premières lignes affichées) :
ORDER BY
Trier les résultats aide souvent à mieux comprendre les données. Reprenons la liste des films PG-13, mais cette fois triée par durée décroissante, avec la durée correspondante. Avec ORDER BY, c’est simple :
SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
Ce qui renvoie (seulement les 10 premières lignes affichées) :
LIMIT
Parfois, on ne souhaite afficher qu’un nombre limité de lignes. Ici, nous voulons seulement les 10 films PG-13 les plus longs ; utilisons la clause LIMIT.
SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10
Ce qui renvoie :
Agrégations
GROUP BY & COUNT( )
Les agrégations servent à résumer un jeu de données pour en tirer des insights. Elles s’utilisent souvent avec GROUP BY. Exemple : pour savoir combien de locations chaque client a effectuées, on peut compter les locations et trier par nombre décroissant.
SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC
Ce qui renvoie (seulement les 10 premières lignes affichées) :
SUM( )
Et combien chaque client a-t-il dépensé ? On peut utiliser l’agrégat SUM. Ici, nous voulons afficher d’abord le plus gros dépensier.
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Ce qui renvoie (seulement les 10 premières lignes affichées) :
AVG( ) & HAVING
Après un regroupement, on peut filtrer les résultats agrégés avec HAVING. Par exemple : quelles catégories de classification MPAA ont des films d’une durée moyenne supérieure à 115 minutes ?
SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115
Ce qui renvoie :
MIN( ) & alias
Un regroupement n’est pas toujours nécessaire avec une agrégation. Pour connaître la durée du film le plus court, utilisons simplement MIN. Nous voulons aussi nommer la colonne « shortest_movie_length », via un alias avec AS.
SELECT MIN(length) AS shortest_movie_length
FROM film
Ce qui renvoie (seulement les 10 premières lignes affichées) :
Vous pouvez aussi définir des alias pour les noms de tables. C’est utile avec des noms longs, notamment lors des jointures (voir ci-dessous). Attention toutefois aux alias trop obscurs, qui nuisent à la lisibilité.
D’autres fonctions d’agrégation sont très utiles ; vous pouvez en savoir plus dans le cours Introduction to SQL.
Jointures
INNER JOIN (JOIN)
On atteint vite les limites d’une requête sur une seule table. Il est fréquent de devoir croiser plusieurs tables : c’est l’objet des JOIN. On joint des tables à partir d’une clé commune. Par exemple, pour chercher les films dont la langue n’est pas l’anglais :
SELECT film.title, language.name
FROM film
JOIN language
ON film.language_id = language.language_id
WHERE language.name != 'English'
(Notez que, dans cette base, il n’y a pas de films dans d’autres langues que l’anglais. Cette requête renverra donc un résultat vide.)
On peut aussi joindre plusieurs tables. Ci-dessous, nous cherchons les catégories et leur note moyenne de location, triées de la plus élevée à la plus basse.
SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
ON film.film_id = film_category.film_id
JOIN category
ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Ce qui renvoie (seulement les 10 premières lignes affichées) :
Il existe plusieurs types de jointures : INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. Ici, JOIN équivaut par défaut à INNER JOIN : seules les lignes présentes dans les deux tables sont renvoyées. Pour approfondir, consultez le cours Joining Data in SQL.
Changement de types de données
CAST( )
Dans l’exemple ci-dessus où nous calculions des montants en dollars, vous avez peut‑être remarqué que SQL ne voit que des nombres. On peut les convertir en montants monétaires avec CAST :
SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Ce qui renvoie (seulement les 10 premières lignes affichées) :
L’argent n’est pas le seul type possible : on peut convertir en flottants, texte, voire en date et heure.
ROUND( )
De même, on peut arrondir des nombres. Dans l’exemple d’une moyenne de prix de location, les résultats comportent beaucoup de décimales. Utilisons ROUND pour arrondir à deux décimales :
SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
ON film.film_id = film_category.film_id
JOIN category
ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Ce qui renvoie (seulement les 10 premières lignes affichées) :
Conditions complexes
Instruction CASE
En combinant plusieurs clauses et fonctions, on peut écrire des requêtes très puissantes. Parfois, les bases ne suffisent pas. Par exemple, ajoutons une colonne catégorisant la durée des films : « Short Film » si moins de 50 minutes, « Long Film » si plus de 150 minutes, et « Medium Film » sinon. On utilisera une instruction CASE :
SELECT title, length,
CASE
WHEN length < 50 THEN 'Short Film'
WHEN length > 150 THEN 'Long Film'
ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film
Ce qui renvoie (seulement les 10 premières lignes affichées) :
Comme vous le voyez, le résultat dépend des conditions définies, à la manière des instructions « if-else » dans les langages de programmation.
Sous-requêtes
Nous pouvons vouloir connaître le ou les films les plus longs et leurs titres. Pour cela, utilisons WHERE avec une condition plus élaborée :
SELECT title, length
FROM film
WHERE length = (SELECT max(length)
FROM film)
Ce qui renvoie :
Les requêtes complexes s’appuient souvent sur des sous‑requêtes. Dans cet exemple, nous voulons la liste des films dont le prix de location est supérieur à la moyenne des films de la catégorie comédie :
SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
ON film.film_id=cat_id.film_id
JOIN category AS cat
ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')
Ce qui renvoie (seulement les 10 premières lignes affichées) :
Ici, nous commençons par calculer la moyenne du prix de location des comédies. La valeur obtenue (dans les parenthèses ci‑dessus) est ensuite utilisée comme condition dans la clause WHERE pour récupérer les titres concernés.
Common Table Expressions (CTEs)
Pour des cas plus complexes, les Common Table Expressions (CTE) permettent de matérialiser temporairement un résultat sur lequel s’appuyer. Par exemple, calculons le nombre moyen de locations traitées par chaque membre du personnel par semaine.
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id
Ce qui renvoie :
Ici, nous stockons le nombre de locations par semaine dans une table temporaire « weekly_rentals », puis nous requêtons dessus pour obtenir le résultat final.
Fonctions de fenêtre
Les fonctions de fenêtre constituent un ensemble de compétences SQL important pour passer au niveau supérieur. Bien qu’elles soient de niveau intermédiaire à avancé, elles sont souvent jugées indispensables par les data analysts et data scientists. Il vaut donc la peine de s’y familiariser.
OVER( ) & PARTITION BY. Supposons que nous voulions afficher, pour chaque membre du personnel, son nombre hebdomadaire de locations et le total global, avec l’identifiant et le numéro de semaine. Réutilisons la CTE de l’exemple précédent :
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals
Ce qui renvoie (seulement les 10 premières lignes affichées) :
La présence de OVER indique une fonction de fenêtre. Ici, la fonction (SUM) s’applique à toutes les lignes. On peut également utiliser PARTITION BY pour appliquer la fonction sur des « partitions » spécifiques. Disons que l’on veut maintenant le total de locations par semaine plutôt que le total global (en plus des autres colonnes). Là encore, nous réutilisons la CTE générée précédemment.
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals
Ce qui renvoie (seulement les 10 premières lignes affichées) :
La colonne supplémentaire contient le total hebdomadaire plutôt que le total cumulé.
Nous n’avons présenté ici que quelques exemples simples de fonctions de fenêtre, essentielles pour exercer comme data analyst ou data scientist dans de nombreuses entreprises. Pour aller plus loin, consultez les ressources proposées et le cours Intermediate SQL. Vous pouvez aussi vous entraîner sur une base open source et tester ces commandes en cherchant des questions pertinentes à explorer.