Amusant avec SQL: Les CTE récursifs dans Postgres

Les expressions de table communes (CTE) sont une construction puissante dans SQL. Dans la conversation quotidienne, vous pouvez entendre les CTE appelées clauses WITH. Vous pouvez considérer les CTE comme similaires à une vue qui n’est matérialisée que pendant l’exécution de cette requête et n’existe pas en dehors de cette requête. Les CTE peuvent être des blocs de construction très utiles pour permettre à vos requêtes SQL volumineuses d’être plus lisibles. Mais, ils peuvent également être utilisés de manière récursive vous permettant de créer des requêtes très complexes sans avoir à passer à un langage procédural comme plpgsql ou plv8.

Les CTE récursifs se permettent d’être appelés jusqu’à ce qu’une condition soit remplie. Allons directement explorer un CTE récursif — un CTE basique, et en utilisant PostgreSQL bien sûr — et ensuite disséquons un peu plus le CTE récursif pour voir à quoi nous pouvons l’utiliser:

WITH RECURSIVE tens (n) AS ( SELECT 10 UNION ALL SELECT n+10 FROM tens WHERE n+10<= 100 ) SELECT n FROM tens; 

Lorsque ce qui précède est exécuté, nous obtiendrons le résultat suivant:

 n ----- 10 20 30 40 50 60 70 80 90 100 (10 rows) 

Avec ce qui précède, nous pourrions également facilement le faire avec un generate_series. Mais restez avec nous et vous verrez les choses les plus complexes que nous pouvons faire qui ne sont pas possibles avec generate_series. Regardons d’abord de plus près comment cela fonctionne.

La première partie que vous remarquerez est WITH RECURSIVE. Cela indique à Postgres que le CTE peut s’appeler récursivement. La partie suivante que vous remarquerez est qu’elle prend certains paramètres. Dans ce cas (n), il peut également en prendre plus d’un si vous en avez besoin.

En allant plus loin dans le CTE, nous avons la première requête exécutée, SELECT 10, qui génère la première valeur. La deuxième partie est l’endroit où tout le plaisir commence. Le UNION ALL spécifie que nous allons renvoyer tous les enregistrements produits à partir de la boucle. Ensuite, SELECT n+10 FROM tens WHERE n+10<= 100 continuera à appeler le CTE tens créé jusqu’à ce que la condition soit remplie.

Ce sont donc les bases, mais la question intéressante est: quand utiliseriez-vous un CTE récursif? Lorsque vous avez une arborescence ou une structure hiérarchique pour vos données, les CTE récursifs peuvent rendre la vie beaucoup plus facile que de charger toutes vos données et d’exécuter une boucle dans votre code. Pour les applications qui traitent du commerce électronique et des catégories d’achats, les CTE récursifs sont d’une grande aide.

Essayons de rendre un peu plus concrets les avantages des CTE récursifs dans Postgres, avec un exemple. Nous allons d’abord créer une table d’employés, puis nous allons charger quelques exemples d’employés. (Oui, vous verrez certains des noms de notre équipe ici à Citus Data sur cette liste. Ce sont les premiers noms qui me sont venus à l’esprit.)

CREATE TABLE employees ( id serial, name varchar(255), manager_id int ); INSERT INTO employees VALUES (1, 'Umur', null); INSERT INTO employees VALUES (2, 'Craig', 1); INSERT INTO employees VALUES (3, 'Daniel', 2); INSERT INTO employees VALUES (4, 'Claire', 1); INSERT INTO employees VALUES (5, 'Lindsay', 2); INSERT INTO employees VALUES (6, 'Will', 2); INSERT INTO employees VALUES (7, 'Burak', 2); INSERT INTO employees VALUES (8, 'Eren', 2); INSERT INTO employees VALUES (9, 'Katie', 3); INSERT INTO employees VALUES (10, 'Teresa', 4); 

Maintenant, je vais écrire une requête qui me donne tous les rapports qui s’enroulent dans une certaine organisation au sein de l’entreprise. Dans ce cas, je vais me procurer moi-même et tous mes rapports, ainsi que l’identifiant du manager de chaque personne:

WITH RECURSIVE managertree AS ( SELECT id, name, manager_id FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN managertree mtree ON mtree.id = e.manager_id ) SELECT * FROM managertree; id | name | manager_id ----+---------+------------ 2 | Craig | 1 3 | Daniel | 2 5 | Lindsay | 2 6 | Will | 2 7 | Burak | 2 8 | Eren | 2 9 | Katie | 3 (7 rows) 

La prochaine fois que vous devrez effectuer un calcul récursif sur vos données, envisagez de le faire directement en SQL plutôt que de charger toutes ces données dans votre application. Pour en savoir plus, pensez à jeter un coup d’œil à certaines de ces ressources utiles:

• docs PostgreSQL sur CTEs
• Résoudre le problème du vendeur itinérant avec un CTE
• Obtenir un arbre avec tous ses enfants

En tant que note, les CTEs sont actuellement une clôture d’optimisation dans PostgreSQL, bien qu’il y ait des espoirs que cela change à l’avenir. Les expressions de table courantes sont un outil incroyablement utile pour les rapports. Parfois, la lisibilité des CTE l’emporte sur l’impact sur les performances, mais considérez les compromis lors de leur utilisation

• fun avec sql
• Postgres
• conseils