Dans ce cours, on va faire un peu de magie avec les requêtes ! On va créer plusieurs exemples en utilisant plusieurs CTE, histoire de voir comment ils peuvent s’imbriquer pour construire des requêtes bien complexes et à étapes multiples. Ces exemples te serviront dans la vraie vie, surtout pour des analyses costaudes.
Exemple 1 : Analyse du travail des étudiants
Imagine qu’on a une base de données d’université avec trois tables :
Table students :
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name TEXT NOT NULL
);
Table grades :
CREATE TABLE grades (
grade_id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(student_id),
course_id INT NOT NULL,
grade NUMERIC(3, 1) NOT NULL
);
Table courses :
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name TEXT NOT NULL
);
Objectif : choper la liste des étudiants qui ont une moyenne supérieure à 85, avec leur moyenne et les noms des cours qu’ils suivent.
Requête :
WITH high_achievers AS (
-- On trouve les étudiants avec une moyenne élevée
SELECT
student_id,
AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
HAVING AVG(grade) > 85
),
student_courses AS (
-- On trouve les cours auxquels chaque étudiant est inscrit
SELECT
s.student_id,
c.course_name
FROM grades g
JOIN courses c ON g.course_id = c.course_id
JOIN students s ON g.student_id = s.student_id
)
-- On fusionne les résultats
SELECT
s.student_name,
ha.avg_grade,
sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;
Explication :
- Dans le premier CTE (
high_achievers), on calcule la moyenne de chaque étudiant et on garde ceux qui ont plus de 85. - Dans le deuxième CTE (
student_courses), on fait le lien entre les étudiants et leurs cours. - Dans la requête principale, on assemble les données des deux CTE pour avoir la liste des étudiants, leur moyenne et les cours qu’ils suivent.
Exemple 2 : Rapport de ventes pour une boutique en ligne
Imaginons qu’on bosse pour un e-shop et qu’on a les tables suivantes :
Table orders (commandes) :
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
Table customers (clients) :
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL
);
Table order_items (articles dans les commandes) :
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_id INT NOT NULL,
quantity INT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
Objectif : sortir un rapport qui montre pour chaque client :
- Le nombre total de commandes.
- Le montant total de toutes ses commandes du dernier mois.
- La liste de tous les produits uniques qu’il a achetés.
Requête :
WITH recent_orders AS (
-- On prend les commandes du dernier mois
SELECT
order_id,
customer_id,
total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
-- On compte le nombre total de commandes et la somme pour chaque client
SELECT
ro.customer_id,
COUNT(ro.order_id) AS total_orders,
SUM(ro.total_amount) AS total_spent
FROM recent_orders ro
GROUP BY ro.customer_id
),
customer_products AS (
-- On chope les produits uniques achetés par chaque client
SELECT DISTINCT
ro.customer_id,
oi.product_id
FROM recent_orders ro
JOIN order_items oi ON ro.order_id = oi.order_id
)
-- On fusionne les résultats
SELECT
c.customer_name,
cs.total_orders,
cs.total_spent,
ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;
Explication :
- Dans le premier CTE (
recent_orders), on prend les commandes du dernier mois. - Dans le deuxième CTE (
customer_summary), on calcule le nombre total de commandes et le montant total pour chaque client. - Dans le troisième CTE (
customer_products), on récupère les produits uniques achetés par chaque client. - Dans la requête finale, on assemble tout et on utilise
ARRAY_AGG()pour faire la liste des produits uniques.
Exemple 3 : Analyse de la hiérarchie des employés
On a une table des employés :
Table employees :
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name TEXT NOT NULL,
manager_id INT NULL
);
Objectif : construire la hiérarchie des employés à partir du CEO. Indiquer le niveau de chaque employé dans la hiérarchie.
Requête :
WITH RECURSIVE employee_hierarchy AS (
-- On commence avec les employés sans manager (le CEO)
SELECT
employee_id,
employee_name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- On ajoute tous les subordonnés du niveau courant
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- On affiche la hiérarchie
SELECT
employee_id,
employee_name,
manager_id,
level
FROM employee_hierarchy
ORDER BY level, employee_id;
- Dans la requête récursive, on commence avec le CEO (employés sans manager).
- À chaque étape, on ajoute les subordonnés du niveau courant, en augmentant le
levelde un. - Dans la requête principale, on sélectionne toute la hiérarchie, triée par niveau et par identifiant d’employé.
Conseils utiles et erreurs classiques
- Trop de CTE : évite d’en abuser là où un sous-requête suffit. Les CTE peuvent parfois ralentir la requête à cause de la matérialisation des données.
- Noms des CTE : donne des noms clairs et courts à tes CTE pour garder tes requêtes lisibles.
- Ordre d’exécution : rappelle-toi que les CTE s’exécutent dans l’ordre où tu les déclares.
- Groupement des données : utilise
GROUP BYseulement quand c’est nécessaire, pour éviter des opérations inutiles.
Tous ces exemples montrent comment les CTE permettent de découper des tâches complexes en étapes, ce qui rend les requêtes plus lisibles et plus faciles à maintenir. Maintenant, t’es armé pour attaquer des analyses bien velues avec PostgreSQL !
GO TO FULL VERSION