Écriture de requêtes avancées en PostgreSQL

Introduction

Dans ce laboratoire, vous améliorerez vos compétences en écriture de requêtes PostgreSQL en explorant des techniques avancées. Vous apprendrez à utiliser des sous-requêtes dans la clause WHERE pour filtrer les données en fonction des résultats d'une autre requête.

Le laboratoire vous guide dans la définition et l'utilisation des expressions de table communes (CTE, Common Table Expressions) pour une meilleure lisibilité et modularité des requêtes. De plus, vous appliquerez des fonctions de fenêtrage (window functions) comme ROW_NUMBER pour effectuer des calculs sur des ensembles de lignes. Enfin, vous maîtriserez le regroupement et le filtrage des données à l'aide des clauses GROUP BY et HAVING pour extraire des informations significatives de vos ensembles de données.

Écrire une sous-requête dans une clause WHERE

Dans cette étape, vous apprendrez à utiliser une sous-requête dans la clause WHERE d'une requête SQL. Une sous-requête, également appelée requête interne ou requête imbriquée, est une requête intégrée à l'intérieur d'une autre requête. Les sous-requêtes sont utilisées pour renvoyer des données qui seront utilisées dans la requête principale comme condition pour restreindre davantage les données à extraire.

Comprendre les sous-requêtes dans la clause WHERE

Une sous-requête dans la clause WHERE est généralement utilisée pour comparer la valeur d'une colonne au résultat de la sous-requête. La sous-requête est exécutée en premier, et son résultat est ensuite utilisé par la requête externe.

Syntaxe de base :

SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

Ici, la sous-requête (SELECT column_name FROM another_table WHERE condition) renvoie un ensemble de valeurs. La requête externe sélectionne ensuite les lignes de table_name où column_name se trouve dans cet ensemble.

Scénario :

Supposons que vous ayez deux tables : employees (employés) et departments (départements). La table employees contient des informations sur les employés, notamment leur employee_id (identifiant d'employé), employee_name (nom d'employé) et department_id (identifiant de département). La table departments contient des informations sur les départements, notamment leur department_id et department_name (nom de département).

Nous voulons trouver tous les employés qui travaillent dans le département 'Sales' (Ventes).

Étape 1 : Créer les tables et insérer des données

Tout d'abord, connectez-vous à la base de données PostgreSQL en utilisant l'utilisateur postgres :

sudo -u postgres psql

Ensuite, créez la table departments :

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(50)
);

Insérez des exemples de données dans la table departments :

INSERT INTO departments (department_name) VALUES
('Sales'),
('Marketing'),
('Engineering');

Maintenant, créez la table employees :

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INTEGER REFERENCES departments(department_id)
);

Insérez des exemples de données dans la table employees :

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', 2);

Étape 2 : Écrire la sous-requête

Maintenant, écrivons la requête pour trouver tous les employés qui travaillent dans le département 'Sales' en utilisant une sous-requête dans la clause WHERE.

SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

Explication :

La sous-requête (SELECT department_id FROM departments WHERE department_name = 'Sales') sélectionne le department_id de la table departments où le department_name est 'Sales'. Dans ce cas, elle renverra 1.
La requête externe sélectionne ensuite le employee_name de la table employees où le department_id se trouve dans l'ensemble renvoyé par la sous-requête (qui est juste 1).

Étape 3 : Exécuter la requête et afficher les résultats

Exécutez la requête dans votre terminal psql. Vous devriez voir la sortie suivante :

 employee_name
---------------
 Alice
 Charlie
(2 rows)

Cela montre qu'Alice et Charlie sont les employés qui travaillent dans le département 'Sales'.

Étape 4 : Utilisation de EXISTS avec des sous-requêtes

Une autre façon d'utiliser des sous-requêtes dans la clause WHERE est avec l'opérateur EXISTS. L'opérateur EXISTS teste l'existence de lignes dans une sous-requête. Il renvoie true si la sous-requête renvoie des lignes, et false sinon.

Voici un exemple d'utilisation de EXISTS pour obtenir le même résultat :

SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.department_name = 'Sales'
);

Cette requête obtient le même résultat que la précédente, mais utilise l'opérateur EXISTS au lieu de IN.

Explication :

La sous-requête SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Sales' vérifie s'il existe un département avec le nom 'Sales' qui a le même department_id que l'employé actuel.
Si la sous-requête renvoie des lignes (ce qui signifie qu'il existe un département 'Sales' avec le même department_id), l'opérateur EXISTS renvoie true, et le nom de l'employé est sélectionné.

Exécutez la requête dans votre terminal psql. Vous devriez voir la même sortie qu'avant :

 employee_name
---------------
 Alice
 Charlie
(2 rows)

Vous avez maintenant utilisé avec succès une sous-requête dans la clause WHERE pour filtrer les données en fonction d'une condition dans une autre table. Vous avez également appris à utiliser l'opérateur EXISTS avec une sous-requête.

Définir et utiliser une CTE

Dans cette étape, vous apprendrez à définir et à utiliser une expression de table commune (CTE, Common Table Expression) dans PostgreSQL. Une CTE est un ensemble de résultats nommé temporaire que vous pouvez référencer dans une seule instruction SELECT, INSERT, UPDATE ou DELETE. Les CTE sont utiles pour décomposer des requêtes complexes en parties plus simples et plus lisibles.

Comprendre les CTE

Les CTE sont définies à l'aide de la clause WITH. Elles n'existent que pendant la durée de l'exécution de la requête.

Syntaxe de base :

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE condition;

Ici, cte_name est le nom que vous donnez à la CTE. L'instruction SELECT à l'intérieur des parenthèses définit l'ensemble de résultats de la CTE. L'instruction SELECT externe interroge ensuite la CTE comme s'il s'agissait d'une table ordinaire.

Scénario :

En continuant avec les tables employees (employés) et departments (départements) de l'étape précédente, utilisons une CTE pour trouver les noms des employés et les noms de leurs départements correspondants.

Étape 1 : Vérifier les tables et les données

Assurez-vous que les tables employees et departments existent et contiennent les données de l'étape précédente. Vous pouvez le vérifier en exécutant les requêtes suivantes dans votre terminal psql :

SELECT * FROM departments;
SELECT * FROM employees;

Si les tables ou les données sont manquantes, reportez-vous à l'étape précédente pour les créer et insérer les données.

Étape 2 : Définir une CTE

Maintenant, définissons une CTE appelée EmployeeDepartments qui joint les tables employees et departments pour récupérer les noms des employés et les noms de leurs départements.

WITH EmployeeDepartments AS (
    SELECT
        e.employee_name,
        d.department_name
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT employee_name, department_name
FROM EmployeeDepartments;

Explication :

La clause WITH EmployeeDepartments AS (...) définit la CTE nommée EmployeeDepartments.
L'instruction SELECT à l'intérieur des parenthèses joint la table employees (alias e) avec la table departments (alias d) sur la colonne department_id.
L'instruction SELECT externe récupère ensuite le employee_name et le department_name de la CTE EmployeeDepartments.

Étape 3 : Exécuter la requête et afficher les résultats

Exécutez la requête dans votre terminal psql. Vous devriez voir la sortie suivante :

 employee_name | department_name
---------------+-----------------
 Alice         | Sales
 Bob           | Marketing
 Charlie       | Sales
 David         | Engineering
 Eve           | Marketing
(5 rows)

Cela montre les noms de tous les employés et les noms de leurs départements correspondants.

Étape 4 : Utilisation des CTE pour le filtrage

Vous pouvez également utiliser des CTE pour filtrer les données. Par exemple, trouvons tous les employés qui travaillent dans le département 'Sales' en utilisant la CTE EmployeeDepartments.

WITH EmployeeDepartments AS (
    SELECT
        e.employee_name,
        d.department_name
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT employee_name
FROM EmployeeDepartments
WHERE department_name = 'Sales';

Explication :

Cette requête est similaire à la précédente, mais elle ajoute une clause WHERE à l'instruction SELECT externe pour filtrer les résultats afin d'inclure uniquement les employés qui travaillent dans le département 'Sales'.

Exécutez la requête dans votre terminal psql. Vous devriez voir la sortie suivante :

 employee_name
---------------
 Alice
 Charlie
(2 rows)

Cela montre qu'Alice et Charlie sont les employés qui travaillent dans le département 'Sales'.

Vous avez maintenant défini et utilisé avec succès une CTE pour joindre des tables et filtrer des données. Les CTE peuvent grandement améliorer la lisibilité et la maintenabilité des requêtes SQL complexes.

Appliquer des fonctions de fenêtrage (par exemple, ROW_NUMBER)

Dans cette étape, vous apprendrez à appliquer des fonctions de fenêtrage (window functions) dans PostgreSQL. Les fonctions de fenêtrage effectuent des calculs sur un ensemble de lignes de table qui sont liées à la ligne actuelle. Elles sont similaires aux fonctions d'agrégation, mais contrairement à ces dernières, les fonctions de fenêtrage ne regroupent pas les lignes en une seule ligne de sortie. Au lieu de cela, elles fournissent une valeur pour chaque ligne dans l'ensemble de résultats.

Comprendre les fonctions de fenêtrage

Les fonctions de fenêtrage utilisent la clause OVER() pour définir la fenêtre de lignes pour le calcul. La clause OVER() peut inclure les clauses PARTITION BY et ORDER BY pour définir plus précisément la fenêtre.

Syntaxe de base :

SELECT
    column1,
    column2,
    WINDOW_FUNCTION(column3) OVER (
        [PARTITION BY column4]
        [ORDER BY column5]
    ) AS alias_name
FROM
    table_name;

WINDOW_FUNCTION : Le nom de la fonction de fenêtrage (par exemple, ROW_NUMBER, RANK, SUM, AVG).
OVER() : Spécifie la fenêtre sur laquelle la fonction opère.
PARTITION BY : Divise les lignes en partitions, et la fonction de fenêtrage est appliquée à chaque partition indépendamment.
ORDER BY : Définit l'ordre des lignes dans chaque partition.
alias_name : L'alias pour le résultat calculé de la fonction de fenêtrage.

Scénario :

En continuant avec les tables employees (employés) et departments (départements) des étapes précédentes, utilisons la fonction de fenêtrage ROW_NUMBER() pour attribuer un rang unique à chaque employé au sein de son département respectif en fonction de son employee_name (nom d'employé).

Étape 1 : Appliquer la fonction de fenêtrage ROW_NUMBER()

Maintenant, écrivons une requête qui utilise la fonction de fenêtrage ROW_NUMBER() pour attribuer un rang à chaque employé au sein de son département.

SELECT
    employee_name,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
FROM
    employees;

Explication :

ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) : C'est la fonction de fenêtrage.
- ROW_NUMBER() : Attribue un entier séquentiel unique à chaque ligne dans la fenêtre.
- PARTITION BY department_id : Divise les lignes en partitions en fonction du department_id (identifiant de département). Cela signifie que le classement sera effectué séparément pour chaque département.
- ORDER BY employee_name : Spécifie l'ordre dans lequel les lignes de chaque partition sont classées. Dans ce cas, les employés sont classés par ordre alphabétique de leur employee_name.
employee_rank : C'est l'alias donné au résultat de la fonction de fenêtrage.

Étape 2 : Exécuter la requête et afficher les résultats

Exécutez la requête dans votre terminal psql. Vous devriez voir la sortie suivante :

 employee_name | department_id | employee_rank
---------------+---------------+---------------
 Alice         |             1 |             1
 Charlie       |             1 |             2
 Bob           |             2 |             1
 Eve           |             2 |             2
 David         |             3 |             1
(5 rows)

Cela montre le nom de chaque employé, son department_id et son rang au sein de son département. Par exemple, Alice est classée 1 dans le département 1, et Charlie est classé 2 dans le département 1.

Étape 3 : Utilisation des fonctions de fenêtrage avec les CTE

Vous pouvez également utiliser des fonctions de fenêtrage dans les CTE pour rendre vos requêtes plus organisées. Réécrivons la requête précédente en utilisant une CTE.

WITH RankedEmployees AS (
    SELECT
        employee_name,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
    FROM
        employees
)
SELECT
    employee_name,
    department_id,
    employee_rank
FROM
    RankedEmployees;

Cette requête produit le même résultat que la précédente, mais elle utilise une CTE pour encapsuler le calcul de la fonction de fenêtrage.

Exécutez la requête dans votre terminal psql. Vous devriez voir la même sortie qu'avant.

Grouper et filtrer avec GROUP BY et HAVING

Dans cette étape, vous apprendrez à utiliser les clauses GROUP BY et HAVING dans PostgreSQL pour grouper les lignes et filtrer les résultats groupés. La clause GROUP BY regroupe les lignes qui ont les mêmes valeurs dans les colonnes spécifiées en lignes récapitulatives, par exemple pour trouver le nombre d'employés dans chaque département. La clause HAVING est utilisée pour filtrer ces lignes groupées en fonction d'une condition spécifiée.

Comprendre GROUP BY et HAVING

GROUP BY : Cette clause regroupe les lignes avec les mêmes valeurs dans une ou plusieurs colonnes en une ligne récapitulative. Vous utilisez généralement des fonctions d'agrégation (par exemple, COUNT, SUM, AVG, MIN, MAX) pour calculer les valeurs de chaque groupe.
HAVING : Cette clause filtre les groupes créés par la clause GROUP BY. Elle est similaire à la clause WHERE, mais elle opère sur des groupes plutôt que sur des lignes individuelles.

Syntaxe de base :

SELECT
    column1,
    aggregate_function(column2)
FROM
    table_name
WHERE
    condition
GROUP BY
    column1
HAVING
    aggregate_function(column2) condition;

column1 : La colonne à grouper.
aggregate_function(column2) : Une fonction d'agrégation appliquée à column2 pour chaque groupe.
WHERE : Filtre les lignes avant le groupement.
GROUP BY : Regroupe les lignes en fonction des valeurs de column1.
HAVING : Filtre les groupes après le groupement, en fonction du résultat de la fonction d'agrégation.

Scénario :

En continuant avec les tables employees (employés) et departments (départements), utilisons GROUP BY et HAVING pour trouver les départements qui ont plus d'un employé.

Étape 1 : Grouper par département et compter les employés

Tout d'abord, écrivons une requête pour grouper les employés par département et compter le nombre d'employés dans chaque département.

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name;

Explication :

SELECT d.department_name, COUNT(e.employee_id) AS employee_count : Cela sélectionne le nom du département et le nombre d'ID d'employés pour chaque département.
FROM employees e JOIN departments d ON e.department_id = d.department_id : Cela joint les tables employees et departments sur la colonne department_id (identifiant de département).
GROUP BY d.department_name : Cela regroupe les lignes par nom de département, de sorte que la fonction COUNT() compte le nombre d'employés dans chaque département.

Étape 2 : Exécuter la requête et afficher les résultats

Exécutez la requête dans votre terminal psql. Vous devriez voir la sortie suivante :

 department_name | employee_count
-----------------+----------------
 Engineering     |              1
 Marketing       |              2
 Sales           |              2
(3 rows)

Cela montre le nombre d'employés dans chaque département.

Étape 3 : Filtrer avec HAVING

Maintenant, ajoutons une clause HAVING pour filtrer les résultats afin d'inclure uniquement les départements qui ont plus d'un employé.

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name
HAVING
    COUNT(e.employee_id) > 1;

Explication :

Cette requête est la même que la précédente, mais elle ajoute une clause HAVING :
- HAVING COUNT(e.employee_id) > 1 : Cela filtre les groupes pour n'inclure que les départements où le nombre d'ID d'employés est supérieur à 1.

Étape 4 : Exécuter la requête et afficher les résultats

Exécutez la requête dans votre terminal psql. Vous devriez voir la sortie suivante :

 department_name | employee_count
-----------------+----------------
 Marketing       |              2
 Sales           |              2
(2 rows)

Cela montre uniquement les départements qui ont plus d'un employé (Marketing et Sales).

Vous avez maintenant utilisé avec succès les clauses GROUP BY et HAVING pour grouper les lignes et filtrer les résultats groupés en fonction d'une condition.

Résumé

Dans ce labo, vous avez appris à écrire des requêtes PostgreSQL avancées. Vous avez commencé par utiliser des sous-requêtes (subqueries) dans la clause WHERE pour filtrer les données en fonction des résultats d'une autre requête. Cela impliquait de comprendre la syntaxe et l'application des sous-requêtes pour comparer les valeurs des colonnes à un ensemble de valeurs renvoyées par la requête interne.

Vous avez ensuite appris à définir et à utiliser les expressions de table communes (Common Table Expressions - CTE) pour améliorer la lisibilité et la modularité des requêtes. Les CTE vous permettent de décomposer des requêtes complexes en parties plus simples et plus faciles à gérer.

Ensuite, vous avez appliqué des fonctions de fenêtrage (window functions) comme ROW_NUMBER pour effectuer des calculs sur des ensembles de lignes. Les fonctions de fenêtrage sont similaires aux fonctions d'agrégation, mais elles fournissent une valeur pour chaque ligne dans l'ensemble de résultats, au lieu de regrouper les lignes en une seule ligne de sortie.

Enfin, vous avez maîtrisé le groupement et le filtrage des données à l'aide des clauses GROUP BY et HAVING pour extraire des informations significatives de vos ensembles de données. La clause GROUP BY regroupe les lignes qui ont les mêmes valeurs dans les colonnes spécifiées en lignes récapitulatives, tandis que la clause HAVING filtre ces lignes groupées en fonction d'une condition spécifiée.