Les Common Table Expressions (CTE)
Par footcow le dimanche, 8 mai 2022, 22:46 - PostgreSQL - Lien permanent
Les CTE sont un moyen pratique de définir des sous-requêtes réutilisables dans une requête principale. Les CTE sont similaires aux vues, mais contrairement aux vues, elles ne sont pas stockées en permanence dans la base de données. Au lieu de cela, elles sont définies dans la requête elle-même et ne peuvent être référencées que dans la requête qui les contient.
L'objectif principal des CTE est de simplifier la requête et de la rendre plus facile à comprendre en divisant la requête en parties plus petites. Les CTE sont particulièrement utiles pour les requêtes complexes impliquant des jointures multiples, des filtres et des regroupements.
Voici un exemple concret d'utilisation de CTE : supposons que vous ayez une table de ventes avec des colonnes pour les dates de vente, le produit vendu et le chiffre d'affaires de chaque vente. Vous voulez créer une requête qui renvoie le chiffre d'affaires total pour chaque produit, mais vous voulez également inclure une colonne qui montre le pourcentage de chiffre d'affaires que chaque produit représente pour l'ensemble des ventes.
Voici la requête que vous pouvez utiliser avec une CTE pour accomplir cela :
WITH total_sales AS (
SELECT SUM(sales_amount) as total_sales
FROM sales
)
SELECT product, SUM(sales_amount) as product_sales,
(SUM(sales_amount) / total_sales.total_sales) * 100 as percentage_total_sales
FROM sales, total_sales
WHERE sales.date >= '2022-01-01'
GROUP BY product, total_sales.total_sales
ORDER BY percentage_total_sales DESC;
Dans cet exemple, la CTE est la première partie de la requête qui calcule le chiffre d'affaires total de toutes les ventes. Cette CTE est ensuite utilisée dans la requête principale pour calculer le chiffre d'affaires pour chaque produit, ainsi que le pourcentage de chiffre d'affaires qu'il représente pour l'ensemble des ventes.
Il est également possible d'enchainer plusieurs CTE dans une même requête :
WITH
cte1 AS (
SELECT id, name
FROM table1
WHERE id > 100
),
cte2 AS (
SELECT id, value
FROM table2
WHERE value > 50
)
SELECT cte1.name, cte2.value
FROM cte1
JOIN cte2 ON cte1.id = cte2.id;
Dans cet exemple, simple, nous créons deux CTE, cte1 et cte2. cte1 sélectionne toutes les lignes de table1 avec un id supérieur à 100 et renvoie les colonnes id et name. cte2 sélectionne toutes les lignes de table2 avec une valeur supérieure à 50 et renvoie les colonnes id et value.
Ensuite, nous effectuons une jointure entre cte1 et cte2 sur la colonne id pour récupérer les noms de table1 et les valeurs de table2 qui correspondent aux mêmes identifiants.
L'utilisation de plusieurs CTE dans la même requête peut être utile pour diviser une requête complexe en plusieurs parties plus gérables et réutilisables. Cela peut également améliorer la lisibilité de la requête en la rendant plus modulaire.
En résumé, les CTE sont une fonctionnalité utile pour les requêtes complexes qui nécessitent des sous-requêtes réutilisables. Elles peuvent simplifier la requête, la rendre plus facile à comprendre et à maintenir, et vous permettre de diviser une requête en parties plus petites pour une meilleure organisation.
