Divertimento con SQL: CTEs ricorsivi in Postgres

Common Table Expressions (CTEs) sono un potente costrutto all’interno di SQL. Nella conversazione quotidiana, è possibile ascoltare le clausole CTE denominate WITH. Puoi pensare a CTEs come simile a una vista che si materializza solo mentre quella query è in esecuzione e non esiste al di fuori di quella query. I CTES possono essere elementi costitutivi molto utili per consentire alle query SQL di grandi dimensioni di essere più leggibili. Ma possono anche essere utilizzati in modo ricorsivo consentendo di creare query molto complesse senza dover passare a un linguaggio procedurale come plpgsql o plv8.

I CTE ricorsivi si permettono di essere chiamati fino a quando non viene soddisfatta una condizione. Saltiamo dentro ed esploriamo un CTE ricorsivo-uno di base, e ovviamente usando PostgreSQL-e poi sezioniamo il CTE ricorsivo un po ‘ più avanti per vedere a cosa possiamo usarlo:

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

Quando viene eseguito quanto sopra otterremo il seguente risultato:

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

Con quanto sopra potremmo anche farlo facilmente con un generate_series. Ma resta con noi e vedrai le cose più complesse che possiamo fare che non sono possibili con generate_series. Per prima cosa diamo un’occhiata più da vicino a come funziona.

La prima parte che noterai è WITH RECURSIVE. Questo dice a Postgres che il CTE può chiamarsi ricorsivamente. La prossima parte che noterai è che ci vogliono alcuni parametri. In questo caso (n), può anche richiedere più di uno se necessario.

Spostandoci ulteriormente nel CTE, abbiamo la prima query che viene eseguita, SELECT 10, che genera il primo valore. La seconda parte è dove inizia tutto il divertimento. UNION ALL specifica che restituiremo tutti i record prodotti dal ciclo. Quindi SELECT n+10 FROM tens WHERE n+10<= 100 continuerà a chiamare il CTE tens che viene creato fino a quando la condizione non viene soddisfatta.

Quindi queste sono le basi, ma la domanda interessante è: quando useresti un CTE ricorsivo? Quando si dispone di un albero o di una struttura gerarchica per i dati, i CTES ricorsivi possono rendere la vita molto più semplice del caricamento di tutti i dati e dell’esecuzione di un ciclo nel codice. Per le applicazioni che si occupano di e-commerce e categorie di shopping, CTE ricorsive sono di grande aiuto.

Proviamo a rendere i benefici dei CTE ricorsivi in Postgres un po ‘ più concreti, con un esempio. Per prima cosa creeremo una tabella di dipendenti, quindi caricheremo alcuni dipendenti di esempio. (Sì, vedrai alcuni dei nomi del nostro team qui a Citus Data in questa lista. Erano i primi nomi che mi sono venuti in mente.)

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); 

Ora ho intenzione di scrivere una query che mi dà tutti i rapporti che si arrotolano in una certa organizzazione all’interno dell’azienda. In questo caso ho intenzione di ottenere me stesso e tutti i miei rapporti, insieme con id manager di ogni persona:

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 prossima volta che devi eseguire un calcolo ricorsivo sui tuoi dati, considera di farlo direttamente in SQL invece di caricare tutti quei dati nella tua applicazione. Per ulteriori letture considera di dare un’occhiata ad alcune di queste utili risorse:

• PostgreSQL su Cte
• Risolvere il problema del commesso viaggiatore con un CTE
• di Ottenere un albero con tutti i figli

Come nota Cte, in questo momento, un’ottimizzazione recinzione in PostgreSQL, anche se ci sono speranze che in futuro cambierà. Le espressioni di tabella comuni sono uno strumento incredibilmente utile per la segnalazione. A volte la leggibilità dei CTEs supera l’impatto sulle prestazioni, ma considera i compromessi quando li usi

• divertimento con sql
• Postgres
• suggerimenti