Fun with SQL: rekursiiviset CTEs in Postgres

Common Table Expressions (Ctes) ovat voimakas konstruktio SQL: n sisällä. Päivittäisessä keskustelussa saatetaan kuulla CTEs-lausekkeita, joihin viitataan WITH lausekkeilla. Voit ajatella CTEs: n muistuttavan näkymää, joka toteutuu vain silloin, kun kyseinen kysely on käynnissä, eikä sitä ole olemassa kyseisen kyselyn ulkopuolella. CTEs voi olla erittäin hyödyllinen rakennuspalikoita, joiden avulla suuret SQL-kyselyt ovat luettavampia. Mutta, niitä voidaan myös käyttää rekursiivisesti, jolloin voit luoda joitakin hyvin monimutkaisia kyselyitä ilman pudota alas prosessuaalisen kielen kuten plpgsql tai plv8.

rekursiiviset Cteet antavat kutsua itseään, kunnes jokin ehto täyttyy. Let ’ s hypätä oikealle ja tutkia rekursiivinen CTE-perus-yksi, ja käyttämällä PostgreSQL tietenkin-ja sitten leikellään rekursiivinen CTE hieman pidemmälle nähdä, mitä voimme käyttää sitä:

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

kun edellä on ajaa saamme seuraavan tuloksen:

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

kanssa edellä voisimme myös helposti tehdä tämän generate_series. Mutta pysykää mukana ja näette monimutkaisempia asioita, joita voimme tehdä, jotka eivät ole mahdollisia generate_series: llä. Katsotaanpa ensin tarkemmin, miten se toimii.

ensimmäinen osa on WITH RECURSIVE. Tämä kertoo Postgres CTE voi rekursiivisesti kutsua itseään. Seuraava osa huomaat, että se vie joitakin parametreja siihen. Tässä tapauksessa (n), se voi myös ottaa enemmän kuin yksi sinun pitäisi.

siirryttäessä kauemmas CTE: hen meillä on ensimmäinen suoritettu kysely, SELECT 10, joka tuottaa ensimmäisen arvon. Toisessa osassa kaikki hauskuus alkaa. UNION ALL täsmentää, että palautamme kaikki Loopista tuotetut levyt. Silloin SELECT n+10 FROM tens WHERE n+10<= 100 soittaa jatkuvasti syntyvää tens CTE: tä, kunnes ehto täyttyy.

nämä ovat siis perusasiat, mutta kiinnostava kysymys kuuluu: milloin käyttäisit rekursiivista CTE: tä? Kun sinulla on puu tai hierarkkinen rakenne tietosi, rekursiivinen CTEs voi tehdä elämästä paljon helpompaa kuin ladata kaikki tiedot ja ajaa silmukan koodin. Sovelluksille, jotka käsittelevät verkkokauppa-ja ostoskategorioita, rekursiiviset CTEs ovat suuri apu.

yritetään konkretisoida rekursiivisen CTEs: n hyötyjä Postgresissä hieman esimerkin avulla. Ensin luodaan taulukko työntekijöistä,sitten ladataan esimerkkityöntekijöitä. (Kyllä, näet joitakin nimiä tiimimme täällä citus Data tässä luettelossa. Ne olivat ensimmäiset Nimet, jotka tulivat mieleen.)

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

nyt aion kirjoittaa kyselyn, joka antaa minulle kaikki raportit, jotka rullaavat tiettyyn organisaatioon yhtiön sisällä. Tässä tapauksessa aion saada itseni ja kaikki minun raportit, sekä jokaisen henkilön johtaja id:

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) 

seuraavan kerran sinun täytyy tehdä joitakin rekursiivinen laskenta poikki tiedot, harkitse tehdä se suoraan SQL vastakohtana lastaus kaikki tiedot sovellukseen. Tarkempaa luettavaa varten harkitse vilkaisemalla joitakin näistä hyödyllisistä resursseista:

• PostgreSQL docs On Ctes
• Solving the traveling salesman problem with a CTE
• Getting a tree with all it ’ s children

as a note CTEs at this time are an optimization aidan in PostgreSQL, joskin on olemassa toiveita, että tämä muuttuisi tulevaisuudessa. Yhteiset taulukon ilmaisut ovat uskomattoman hyödyllinen raportointiväline. Toisinaan CTEs: n luettavuus on suorituskykyvaikutusta suurempi, mutta ota huomioon vaihtokaupat, kun käytät niitä

• hauskaa SQL: llä
• Postgres
• tips