Distracție cu SQL: CTEs Recursive în Postgres

expresii comune de masă (CTEs) sunt un construct puternic în SQL. În conversația de zi cu zi, este posibil să auziți CTE denumite clauze WITH. Vă puteți gândi la CTEs ca fiind similar cu o vizualizare care se materializează numai în timp ce acea interogare rulează și nu există în afara acelei interogări. CTEs poate fi blocuri de construcție foarte utile pentru a permite interogări SQL mari pentru a fi mai ușor de citit. Dar, ele pot fi, de asemenea, utilizate recursiv, permițându-vă să creați câteva interogări foarte complexe, fără a fi nevoie să renunțați la un limbaj procedural precum plpgsql sau plv8.

CT-urile Recursive își permit să fie apelate până când este îndeplinită o anumită condiție. Să sărim direct și să explorăm un CTE recursiv—unul de bază și, desigur, folosind PostgreSQL—și apoi să disecăm CTE recursiv un pic mai departe pentru a vedea la ce îl putem folosi:

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

când se execută cele de mai sus, vom obține următorul rezultat:

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

cu cele de mai sus am putea face acest lucru cu ușurință și cu un generate_series. Dar rămâneți cu noi și veți vedea lucrurile mai complexe pe care le putem face, care nu sunt posibile cu generate_series. Mai întâi să aruncăm o privire mai atentă la modul în care funcționează.

prima parte pe care o veți observa este WITH RECURSIVE. Acest lucru îi spune lui Postgres că CTE se poate numi recursiv. Următoarea porțiune pe care o veți observa este că are câțiva parametri în ea. În acest caz (n), poate dura, de asemenea, mai mult de unul ar trebui să aveți nevoie.

Mergând mai departe în CTE, avem prima interogare care este executată, SELECT 10, care generează prima valoare. A doua porțiune este locul unde începe toată distracția. UNION ALL specifică faptul că vom returna toate înregistrările care sunt produse din buclă. Apoi SELECT n+10 FROM tens WHERE n+10<= 100 va continua să apeleze tens CTE care este creat până când condiția este îndeplinită.

deci acestea sunt elementele de bază, dar întrebarea interesantă este: când ați folosi un CTE recursiv? Când aveți un arbore sau o structură ierarhică a datelor dvs., CTEs recursive pot face viața mult mai ușoară decât încărcarea tuturor datelor și rularea unei bucle în codul dvs. Pentru aplicațiile care se ocupă de categoriile de comerț electronic și cumpărături, CTEs recursive sunt de mare ajutor.

să încercăm să facem beneficiile CTEs recursive în Postgres puțin mai concrete, cu un exemplu. Mai întâi vom crea un tabel de angajați, apoi vom încărca câțiva angajați de exemplu. (Da, veți vedea câteva nume din echipa noastră aici la Citus Data pe această listă. Au fost primele nume care mi-au venit în minte.)

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

acum voi scrie o interogare care îmi dă TOATE rapoartele care se rostogolesc într-o anumită organizație din cadrul companiei. În acest caz, am de gând să mă și toate rapoartele mele, împreună cu ID-ul de manager de fiecare persoană:

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) 

data viitoare când trebuie să faceți un calcul recursiv între datele dvs., luați în considerare să o faceți direct în SQL, spre deosebire de încărcarea tuturor acestor date în aplicația dvs. Pentru lecturi suplimentare ia în considerare a lua o privire la unele dintre aceste resurse utile:

• Docs PostgreSQL pe CTEs
• rezolvarea problemei comis-voiajor cu un CTE
• obtinerea unui copac cu toți copiii lui

ca o notă CTEs în acest moment sunt un gard de optimizare în PostgreSQL, deși există speranțe că schimbarea în viitor. Expresiile obișnuite ale tabelelor sunt un instrument incredibil de util pentru raportare. Uneori lizibilitatea CTEs depășește impactul asupra performanței, dar luați în considerare compromisurile atunci când le utilizați

• distracție cu sql
• Postgres
• sfaturi