Apprends à exploiter les puissantes fonctionnalités de SQL, telles que les agrégats, les jointures, les sous-requêtes, les fonctions de fenêtre et les CTEs, afin d’interroger, de manipuler et d’obtenir des informations sur les données relationnelles. Que tu sois un débutant désireux d’apprendre les bases du langage SQL ou un expert souhaitant renforcer ses compétences en matière d’analyse de données, ce tuto te permettra d’améliorer tes requêtes SQL pour l’analyse de données.
Tu peux également consulter le Bootcamp SQL + Python (avec postgreSQL).
Suis les exemples clairs qui démontrent chaque concept afin que tu puisses appliquer ces techniques de manipulation de données dans tes propres projets. À la fin de ce tutoriel, tu auras acquis les compétences SQL nécessaires pour extraire des données business dans ton entreprise qui changeront la donne.
Qu’est-ce que SQL ?
SQL signifie Structured Query Language (langage de requête structuré). Il s’agit d’un langage de programmation utilisé pour gérer et manipuler des bases de données relationnelles. Les bases de données relationnelles sont des collections de données organisées en tables avec des lignes et des colonnes. Le langage SQL permet d’extraire des données de ces tables, de les modifier et d’en insérer de nouvelles.
Syntaxe SQL de base
La syntaxe de base du langage SQL se compose de commandes utilisées pour créer, modifier et interroger des bases de données. La syntaxe se compose de commandes telles que SELECT, INSERT, UPDATE, DELETE et CREATE. Les commandes SQL ne sont pas sensibles à la case, mais il est courant de les écrire en majuscules. La suite de l’histoire commence maintenant.
Récupération des données depuis une seule table
En SQL, l’instruction SELECT est utilisée pour extraire des données d’une table. La clause FROM spécifie la table à utiliser. La clause WHERE permet de filtrer les données, la clause ORDER BY permet de trier les données et la clause LIMIT permet de limiter le nombre de lignes renvoyées.
SELECT & FROM
L’instruction SELECT spécifie les colonnes à extraire et est suivie des noms de colonnes à sélectionner. Si tu souhaites récupérer toutes les colonnes, tu peux utiliser le symbole * à la place des noms de colonnes.
La clause FROM spécifie la ou les tables dont tu souhaites extraire les données et est utilisée après l’instruction SELECT. En combinant l’instruction SELECT et la clause FROM, tu peux récupérer des colonnes spécifiques ou toutes les colonnes d’une ou de plusieurs tables.
SELECT first_name, last_name FROM employees
WHERE
La clause WHERE est utilisée pour filtrer les données en fonction de conditions spécifiques. Tu peux utiliser différents opérateurs tels que =, <>, <, >, <=, >=, LIKE, IN, BETWEEN, IS NULL et IS NOT NULL pour définir les conditions.
SELECT * FROM employees WHERE salary > 50000 AND department = 'IT';
ORDER BY
La clause ORDER BY est utilisée pour trier les données par ordre croissant ou décroissant sur la base d’une ou de plusieurs colonnes. Tu peux spécifier le mot-clé ASC pour l’ordre croissant et le mot-clé DESC pour l’ordre décroissant. Si aucun mot-clé n’est fourni, la valeur par défaut est l’ordre croissant.
SELECT * FROM employees ORDER BY last_name ASC, first_name DESC;
LIMIT
La clause LIMIT est utilisée pour limiter le nombre de lignes renvoyées. Elle permet de limiter le nombre de lignes retournées à un certain nombre.
SELECT id, name, salary FROM employees LIMIT 30;
Joins – Récupérer des données dans plusieurs tables
En SQL, l’opération JOIN est utilisée pour extraire des données de plusieurs tables. Il existe quatre types d’opérations JOIN : INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL OUTER JOIN.
INNER JOIN
Un INNER JOIN ne renvoie que les lignes qui ont des valeurs correspondantes dans les deux tables. Il s’agit du type d’opération JOIN le plus courant. Dans ce cas, le jeu de résultats ne comprendra que les enregistrements pour lesquels il existe une correspondance dans les deux tables.
SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
LEFT JOIN
Un LEFT JOIN renvoie toutes les lignes de la table de gauche (table1) et les lignes correspondantes de la table de droite (table2). S’il n’y a pas de correspondance, des valeurs NULL sont renvoyées pour les colonnes de la table de droite.
SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;
RIGHT JOIN
Une RIGHT JOIN fonctionne de la même manière qu’une LEFT JOIN, mais elle renvoie toutes les lignes de la table de droite (table2) et les lignes correspondantes de la table de gauche (table1). S’il n’y a pas de correspondance, des valeurs NULL sont renvoyées pour les colonnes de la table de gauche.
SELECT employees.name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
FULL OUTER JOIN
Un FULL OUTER JOIN combine les résultats du LEFT JOIN et du RIGHT JOIN. Elle renvoie toutes les lignes des deux tables, avec des valeurs NULL pour les colonnes non appariées.
SELECT employees.name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
Bonus – SELF JOIN
Un SELF JOIN est un type de jointure SQL dans lequel une table est jointe à elle-même. Elle est utilisée pour combiner les lignes d’une même table sur la base d’une colonne liée. Les self joins sont utiles lorsque tu souhaite comparer des lignes d’une même table ou trouver des relations entre les lignes d’une même table. Pour effectuer une self join, tu dois utiliser des alias pour différencier les deux instances de la même table.
SELECT e1.employee_name AS Employee, e2.employee_name AS Manager FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id;
Filtrer et trier les données
En SQL, la clause GROUP BY est utilisée pour regrouper les données, et la clause HAVING est utilisée pour filtrer les données après le regroupement. WHERE et ORDER BY Nous les avons déjà abordés dans la section précédente👆.
Clause GROUP BY
La clause GROUP BY est utilisée pour regrouper des données sur la base d’une ou plusieurs colonnes. Elle est utilisée avec les fonctions d’agrégation telles que COUNT, SUM, AVG, MIN et MAX.
SELECT department, COUNT(*) as employee_count FROM employees GROUP BY department;
Clause HAVING
La clause HAVING est une fonctionnalité utile de SQL qui permet de filtrer les résultats d’une requête en fonction d’une condition appliquée à des données agrégées. Elle est souvent utilisée en conjonction avec la clause GROUP BY, qui regroupe les lignes ayant des valeurs similaires. La clause HAVING est particulièrement utile lorsque tu souhaites récupérer des groupes spécifiques en fonction d’un certain critère, tel que la somme ou la moyenne d’une colonne.
SELECT product_id, SUM(quantity_sold) as total_units_sold FROM sales GROUP BY product_id HAVING total_units_sold > 100;
Opérateur LIKE avec caractères génériques
L’opérateur LIKE est utilisé en conjonction avec des caractères génériques pour filtrer les données sur la base de modèles. Le caractère générique % représente un nombre quelconque de caractères, tandis que le caractère générique _ représente un seul caractère.
SELECT * FROM employees WHERE last_name LIKE 'Smi%';
Fonction d’agrégation
Les fonctions agrégées sont utilisées pour effectuer des calculs sur un ensemble de valeurs et renvoient une seule valeur comme résultat. Elles sont très utiles lorsque tu dois analyser et résumer des données dans ta base de données.
Voici quelques fonctions agrégées courantes en SQL :
- COUNT() : Cette fonction renvoie le nombre de lignes correspondant à une condition spécifiée.
- SUM() : Cette fonction renvoie la somme totale d’une colonne numérique.
- AVG() : Cette fonction renvoie la valeur moyenne d’une colonne numérique.
- MIN() : Cette fonction renvoie la plus petite valeur d’une colonne sélectionnée.
- MAX() : Cette fonction renvoie la plus grande valeur d’une colonne sélectionnée.
Laisse moi te montrer quelques exemples d’utilisation de ces fonctions d’agrégation en SQL :
COUNT()
Supposons que tu disposes d’un tableau intitulé ’employees’ et que tu souhaites connaître le nombre total d’employés. Tu peux utiliser la fonction COUNT() comme suit :
SELECT COUNT(*) FROM employees;
SUM()
Si tu souhaites calculer le salaire total de tous les employés, tu peux utiliser la fonction SUM() :
SELECT SUM(salary) FROM employees;
AVG()
Pour trouver le salaire moyen de tous les employés, tu peux utiliser la fonction AVG() :
SELECT AVG(salary) FROM employees;
MIN()
Si tu souhaites trouver l’employé dont le salaire est le plus bas, tu peux utiliser la fonction MIN() :
SELECT MIN(salary) FROM employees;
MAX()
De même, pour trouver l’employé ayant le salaire le plus élevé, tu peux utiliser la fonction MAX() :
SELECT MAX(salary) FROM employees;
Tu peux également utiliser les fonctions d’agrégation avec la clause GROUP BY pour regrouper les résultats par une ou plusieurs colonnes. Par exemple, si tu souhaites connaître le salaire total de chaque département, tu peux procéder comme suit :
SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;
Sous-requêtes
Les sous-requêtes sont une fonctionnalité puissante de SQL qui nous permet d’effectuer des requêtes complexes en les décomposant en parties plus petites et plus faciles à gérer. En termes simples, une sous-requête est une requête intégrée à une autre requête, souvent utilisée pour filtrer ou modifier les résultats de la requête externe. Elles peuvent être utilisées dans différentes parties d’une requête SQL, telles que les clauses SELECT, WHERE et JOIN.
Entrons dans les détails des sous-requêtes à l’aide de quelques exemples :
Sous-requête basique
Une sous-requête de base est une requête placée entre parenthèses et utilisée dans la clause SELECT, WHERE ou JOIN d’une autre requête. Elle renvoie un ensemble de résultats qui peut être utilisé comme valeur ou comme condition pour la requête externe.
Exemple :
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Sous-requête corrélée
Une sous-requête corrélée est une sous-requête qui fait référence à une valeur de la requête externe. Elle est utilisée pour filtrer ou modifier les résultats de la requête externe en fonction de la valeur d’une colonne spécifique de la requête externe.
Exemple :
SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE name = 'IT');
Sous-requête imbriquée
Une sous-requête imbriquée est une sous-requête utilisée à l’intérieur d’une autre sous-requête. Cela peut s’avérer utile lorsque tu dois effectuer des calculs ou des comparaisons complexes sur la base de plusieurs niveaux de données.
Exemple de sous-requête imbriquée :
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM (SELECT * FROM employees WHERE department_id = 1));
Common Table Expressions (CTE)
Les expressions de table commune (Common Table Expressions ou CTE) sont une fonctionnalité puissante de SQL qui permet de créer des ensembles de résultats temporaires auxquels il est possible de faire référence dans une instruction SELECT, INSERT, UPDATE ou DELETE. Les CTEs sont particulièrement utiles pour simplifier les requêtes complexes, en les divisant en parties plus petites et plus faciles à gérer. Ils peuvent également être utilisés pour créer des requêtes récursives pour des données hiérarchiques.
Pour créer un CTE, tu utilises le mot-clé WITH suivi du nom du CTE et du nom des colonnes entre parenthèses. Ensuite, tu fournis la requête qui définit le CTE. Voyons un exemple simple :
WITH customer_total_orders (customer_id, total_orders) AS ( SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id ) SELECT * FROM customer_total_orders WHERE total_orders > 10;
Les CTEs peuvent également être enchaînés, ce qui permet de référencer un CTE dans un autre. En voici un exemple :
WITH customer_total_orders (customer_id, total_orders) AS ( SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id ), top_customers (customer_id) AS ( SELECT customer_id FROM customer_total_orders WHERE total_orders > 10 ) SELECT c.*, t.total_orders FROM customers c JOIN top_customers tc ON c.customer_id = tc.customer_id JOIN customer_total_orders t ON c.customer_id = t.customer_id;
Enfin, les CTEs peuvent être utilisés pour créer des requêtes récursives. Par exemple, si tu disposes d’une table stockant des données hiérarchiques, telles que les employés et leurs responsables, tu peux utiliser un CTE récursif pour récupérer l’ensemble de la hiérarchie :
WITH RECURSIVE employee_hierarchy (employee_id, manager_id, level) AS ( SELECT employee_id, manager_id, 1 as level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.manager_id, eh.level + 1 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id ) SELECT * FROM employee_hierarchy;
Fonction de fenêtre
Les fonctions de fenêtre en SQL sont un outil puissant qui nous permet d’effectuer des calculs sur un ensemble de lignes liées à la ligne actuelle. Elles sont appelées « fonctions de fenêtre » parce qu’elles offrent une « fenêtre » sur les données environnantes. Voici les fonctions de fenêtre les plus couramment utilisées :
ROW_NUMBER()
ROW_NUMBER() attribue un numéro unique à chaque ligne du jeu de résultats. Cette fonction est utile lorsque tu souhaites attribuer un ordre séquentiel aux lignes, comme pour le classement ou la pagination.
Exemple :
SELECT name, age, ROW_NUMBER() OVER (ORDER BY age) as row_number FROM people;
RANK()
RANK() attribue un rang unique à chaque ligne de l’ensemble de résultats, le même rang étant attribué aux lignes ayant des valeurs égales. Les lignes ayant des valeurs égales obtiennent le même rang, et le rang suivant est ignoré.
Exemple :
SELECT name, score, RANK() OVER (ORDER BY score DESC) as rank FROM exam_results;
DENSE_RANK()
DENSE_RANK() est similaire à RANK(), mais elle ne saute aucun numéro de rang. Elle attribue un rang unique à chaque ligne de l’ensemble de résultats, le même rang étant attribué aux lignes ayant des valeurs égales.
Exemple :
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank FROM exam_results;
SUM()
SUM() calcule la somme cumulative de la valeur d’une colonne spécifique sur l’ensemble des lignes du cadre de la fenêtre. Cette fonction est utile lorsque tu souhaites trouver des totaux en cours.
Exemple :
SELECT date, sales, SUM(sales) OVER (ORDER BY date) as cumulative_sales FROM daily_sales;
AVG()
AVG() calcule la moyenne de la valeur d’une colonne spécifique sur l’ensemble des lignes du cadre de la fenêtre. Cette fonction est utile lorsque tu souhaites trouver la moyenne mobile.
Exemple :
SELECT date, temperature, AVG(temperature) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_average FROM daily_temperatures;
Conclusion de SQL pour l’analyse de données
SQL est un langage de programmation puissant et polyvalent qui peut t’aider à effectuer des analyses de données sur des bases de données relationnelles. Dans cet article, tu as appris les bases de la syntaxe SQL, comment joindre les données de plusieurs tables, comment filtrer et trier les données, comment utiliser les fonctions d’agrégation, les sous-requêtes, les expressions de table courantes et les fonctions de fenêtre. En appliquant ces compétences, tu peux interroger et manipuler les données de différentes manières et obtenir des informations sur tes données.
J’espère que cet article t’a plu et qu’il te sera utile pour tes projets d’analyse de données. Si tu as des questions ou des commentaires, n’hésite pas à m’en faire part dans les commentaires ci-dessous. Merci d’avoir lu cet article !
