PostgreSQL: Dokumentaatio: 13: 7.8. Kyselyillä (yhteiset taulukon lausekkeet)

SELECT: n perusarvo WITH on hajottaa monimutkaiset kyselyt yksinkertaisempiin osiin. Esimerkki on:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

joka näyttää per tuote myynti yhteensä vain top myynti alueilla. WITH lausekkeessa määritellään kaksi apulausetta, joiden nimet ovat regional_sales ja top_regions, joissa top_regions: ssä käytetään tulostetta regional_sales ja top_regions: ssä primäärissä SELECT. Tämä esimerkki olisi voitu kirjoittaa ilman WITH, mutta olisimme tarvinneet kaksi sisäkkäistä alatasoaSELECTs. tätä tietä on hieman helpompi seurata.

valinnainen RECURSIVE modifioija muuttuu WITH pelkästä syntaktisesta mukavuudesta ominaisuudeksi, joka saa aikaan asioita, jotka eivät muuten ole mahdollisia tavallisessa SQL: ssä. Käyttämällä RECURSIVE, WITH – kysely voi viitata omaan tulosteeseensa. Hyvin yksinkertainen esimerkki on tämä kysely kokonaislukujen summaamiseksi 1: stä 100: aan:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

rekursiivisen WITH kyselyn yleinen muoto on aina ei-rekursiivinen termi, sitten UNION (tai UNION ALL), sitten rekursiivinen termi, jossa vain rekursiivinen termi voi sisältää viittauksen kyselyn omaan ulostuloon. Tällainen kysely suoritetaan seuraavasti:

yllä olevassa esimerkissä työpöydässä on vain yksi rivi kussakin vaiheessa, ja se ottaa arvot 1: stä 100: aan peräkkäisissä portaissa. 100. vaiheessa ei ole ulostuloa WHERE – lausekkeen takia, joten kysely päättyy.

rekursiivisia kyselyitä käytetään tyypillisesti hierarkkisen tai puurakenteisen tiedon käsittelyyn. Hyödyllinen esimerkki on tämä kysely, jolla etsitään tuotteen kaikki suorat ja epäsuorat osat, Kun otetaan huomioon vain taulukko, joka näyttää välittömät sulkeumat:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

rekursiivisten kyselyjen kanssa työskenneltäessä on tärkeää olla varma, että kyselyn rekursiivinen osa ei lopulta palauta tupleja, tai muuten kysely loopataan loputtomiin. Joskus käyttämällä UNION sijasta UNION ALL voidaan tämä saavuttaa hylkäämällä rivit, jotka toistavat edelliset lähtörivit. Usein syklissä ei kuitenkaan ole lähtörivejä, jotka ovat täysin päällekkäisiä: voi olla tarpeen tarkistaa vain yksi tai muutama kenttä nähdäkseen, onko sama piste saavutettu aiemmin. Vakiomenetelmä tällaisten tilanteiden käsittelemiseksi on laskea joukko jo käyneitä arvoja. Tarkastellaan esimerkiksi seuraavaa kyselyä, joka etsii taulua graph käyttäen link kenttää:

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link)SELECT * FROM search_graph;

tämä kysely loopataan, jos link suhteet sisältävät syklejä. Koska tarvitsemme” syvyyden ” ulostulon, pelkkä UNION ALL muuttaminen UNION: ksi ei poistaisi silmukkaa. Sen sijaan meidän on tunnustettava, olemmeko päässeet samalle riville uudelleen seuratessamme tiettyä linkkien polkua. Loop-alttiiseen kyselyyn lisätään kaksi saraketta path ja cycle :

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

sen lisäksi, että estetään syklit, array arvo on usein hyödyllinen sellaisenaan edustaa “polku” otettu saavuttaa tietyn rivin.

siinä yleisessä tapauksessa, jossa on tarkistettava useampi kuin yksi kenttä syklin tunnistamiseksi, käytetään rivejä. Esimerkiksi jos piti vertailla kenttiä f1 ja f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

hyödyllinen kikka kyselyiden testaamiseen, kun et ole varma, voivatko ne luupata, on sijoittaa LIMIT vanhempaan kyselyyn. Esimerkiksi tämä kysely loop ikuisesti ilman LIMIT:

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

tämä toimii, koska PostgreSQL: n toteutus arvioi vain niin monta riviä WITH – kyselystä kuin ne itse asiassa hakevat. Tämän kikan käyttäminen tuotannossa ei ole suositeltavaa, koska muut järjestelmät saattavat toimia toisin. Se ei myöskään yleensä toimi, jos teet ulomman kyselyn lajittelemalla rekursiivisen kyselyn tulokset tai liittämällä ne johonkin toiseen taulukkoon, koska tällöin ulompi kysely yrittää yleensä hakea kaikki WITH kyselyn tulokset joka tapauksessa.

WITH kyselyiden hyödyllinen ominaisuus on se, että ne arvioidaan yleensä vain kerran per vanhemman kyselyn suoritus, vaikka niihin viitattaisiinkin useammin kuin kerran vanhemman kyselyn tai sisaruksen WITH kyselyiden perusteella. Näin ollen kalliit laskelmat, joita tarvitaan useassa paikassa, voidaan sijoittaa WITH – kyselyyn, jotta vältyttäisiin turhalta työltä. Toinen mahdollinen sovellus on estää ei-toivottuja useita arviointeja toimintoja sivuvaikutuksia. Kolikon toinen puoli on kuitenkin se, että optimoija ei pysty työntämään rajoituksia kantakyselystä kertovaan WITH-kyselyyn, koska se saattaa vaikuttaa kaikkiin WITH – kyselyn ulostulon käyttötarkoituksiin, kun sen pitäisi vaikuttaa vain yhteen. Moninkertaistettu WITH – kysely arvioidaan kirjallisena ilman sellaisten rivien suppenemista, jotka kantakysely saattaa myöhemmin hylätä. (Mutta, kuten edellä mainittiin, arviointi saattaa loppua aikaisin, jos viittaus(t) kyselyyn vaatii vain rajoitetun määrän rivejä.)

Jos kuitenkin WITH kysely on ei-rekursiivinen ja sivuvaikutukseton (eli se on SELECT, jossa ei ole haihtuvia funktioita), se voidaan taittaa kantakyselyyn, mikä mahdollistaa kahden kyselytason yhteisen optimoinnin. Oletusarvon mukaan näin käy, jos pääkysely viittaa WITH – kyselyyn vain kerran, mutta ei, jos se viittaa WITH – kyselyyn useammin kuin kerran. Voit ohittaa tämän päätöksen määrittämällä MATERIALIZED pakottaaksesi erillisen laskutoimituksen WITH kyselystä tai määrittämällä NOT MATERIALIZED pakottaaksesi sen yhdistymään kantakyselyyn. Viimeksi mainittu valinta uhkaa kaksinkertaistaa WITH – kyselyn laskennan, mutta se voi silti tuottaa nettosäästön, jos WITH – kyselyn jokainen käyttö tarvitsee vain pienen osan WITH – kyselyn koko tuloksesta.

yksinkertainen esimerkki näistä säännöistä on

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

tämä WITH kysely taitetaan, jolloin saadaan sama suoritussuunnitelma kuin

SELECT * FROM big_table WHERE key = 123;

erityisesti, jos key on indeksi, sitä käytetään todennäköisesti vain niiden rivien hakemiseen, joilla on key = 123. Toisaalta

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

WITH kysely toteutuu tuottaen big_table: stä väliaikaisen kopion, joka sitten liitetään itseensä — ilman mitään indeksiä. Tämä kysely suoritetaan paljon tehokkaammin, jos se kirjoitetaan nimellä

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

niin, että kantakyselyn rajoituksia voidaan soveltaa suoraan skannauksiin, joiden arvo on big_table.

esimerkki, jossa NOT MATERIALIZED voi olla ei-toivottu, on

WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

tässä WITH kyselyn toteutuminen varmistaa, että very_expensive_function arvioidaan vain kerran taulukkoriviä kohti, ei kahdesti.

yllä olevat esimerkit osoittavat vain WITH olevan käytössä SELECT, mutta se voidaan liittää samalla tavalla INSERT, UPDATE tai DELETE. Kussakin tapauksessa se tarjoaa tehokkaasti väliaikaisia tauluja, joihin voidaan viitata pääkomennossa.