Les Common Table Expressions (CTE)
Par admin 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.