7.8. WITH-kyselyt (yleiset taulukkoilmaukset)

7.8.1. SELECT in WITH

SELECT in WITH:n perusarvo on monimutkaisten kyselyjen pilkkominen 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ää tuotekohtaiset myynnin kokonaissummat vain suurimmilla myyntialueilla. WITH-lausekkeessa määritellään kaksi apulausetta nimeltä regional_sales ja top_regions, joissa regional_sales-lausekkeen tulosta käytetään top_regions-lausekkeessa ja top_regions-lausekkeen tulosta käytetään ensisijaisessa SELECT-kyselyssä. Tämä esimerkki olisi voitu kirjoittaa ilman WITH-lauseketta, mutta silloin olisi tarvittu kaksi tasoa sisäkkäisiä ali-SELECT-lausekkeita. Näin sitä on hieman helpompi seurata.

Vapaaehtoinen RECURSIVE-modifikaattori muuttaa WITH:n pelkästä syntaktisesta kätevyydestä ominaisuudeksi, joka saa aikaan asioita, jotka eivät muuten ole mahdollisia tavallisessa SQL:ssä. RECURSIVE:n avulla WITH-kysely voi viitata omaan tulosteeseensa. Hyvin yksinkertainen esimerkki on tämä kysely, jossa summataan kokonaisluvut 1-100:

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 tulokseen. Tällainen kysely suoritetaan seuraavasti:

Ylläolevassa esimerkissä työskentelytaulussa on jokaisessa askeleen vaiheessa vain yksi rivi, ja se ottaa peräkkäisinä askeleina arvoja 1:stä 100:aan. 100. askeleessa ei ole tulosta WHERE-lausekkeen vuoksi, joten kysely päättyy.

Rekursiivisia kyselyjä käytetään tyypillisesti hierarkkisten tai puurakenteisten tietojen käsittelyyn. Hyödyllinen esimerkki on tämä kysely, jolla etsitään kaikki tuotteen suorat ja epäsuorat alaosat, kun on annettu vain taulukko, joka näyttää välittömät sulautumat:

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

Työskenneltäessä rekursiivisten kyselyjen kanssa on tärkeää varmistaa, että kyselyn rekursiivinen osa ei lopulta palauta yhtään tupelia, tai muuten kysely kiertää loputtomiin. Joskus UNIONin käyttäminen UNION ALLin sijasta voi saavuttaa tämän hylkäämällä rivit, jotka ovat päällekkäisiä aiempien tulostusrivien kanssa. Usein sykli ei kuitenkaan sisällä täysin päällekkäisiä tulosrivejä: voi olla tarpeen tarkistaa vain yksi tai muutama kenttä sen selvittämiseksi, onko sama kohta saavutettu aiemmin. Standardimenetelmä tällaisten tilanteiden käsittelyyn on laskea jo käydyistä arvoista joukko. Tarkastellaan esimerkiksi seuraavaa kyselyä, joka hakee taulukkograafia linkkikentän avulla:

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 tekee silmukan, jos linkkisuhteet sisältävät syklejä. Koska tarvitsemme ”syvyys”-tulosteen, pelkkä UNION ALL:n muuttaminen UNION:ksi ei poistaisi silmukointia. Sen sijaan meidän on tunnistettava, olemmeko päässeet samalle riville uudelleen seuratessamme tiettyä linkkipolkua. Lisäämme silmukoille alttiiseen kyselyyn kaksi saraketta polku ja sykli:

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;

Syklien estämisen lisäksi sarakkeen arvo on usein itsessään hyödyllinen, sillä se edustaa ”polkua”, joka on kuljettu tietyn rivin saavuttamiseksi.

Yleistapauksessa, jossa syklin tunnistamiseksi on tarkistettava useampi kuin yksi kenttä, käytetään rivien saraketta. Jos esimerkiksi pitäisi verrata 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 kyselyjen testaamiseen, kun et ole varma, voivatko ne tehdä silmukoita, on sijoittaa vanhemman kyselyn sisään LIMIT. Esimerkiksi tämä kysely silmukoituisi 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 WITH-kyselyssä vain niin monta riviä, kuin vanhemman kyselyn avulla todella haetaan. Tämän tempun käyttäminen tuotannossa ei ole suositeltavaa, koska muut järjestelmät saattavat toimia eri tavalla. Se ei myöskään yleensä toimi, jos laitat ulomman kyselyn lajittelemaan rekursiivisen kyselyn tulokset tai liittämään ne johonkin muuhun taulukkoon, koska tällaisissa tapauksissa ulompi kysely yrittää yleensä joka tapauksessa noutaa kaiken WITH-kyselyn tuloksen.

Hyödyllinen ominaisuus WITH-kyselyille on se, että ne evaluoidaan vain kerran vanhemman kyselyn suorituksen aikana, vaikka vanhemman kysely tai sisarelliset WITH-kyselyt viittasivat niihin useammin kuin kerran. Näin ollen kalliit laskutoimitukset, joita tarvitaan useissa paikoissa, voidaan sijoittaa WITH-kyselyyn turhan työn välttämiseksi. Toinen mahdollinen sovellus on estää sivuvaikutuksia sisältävien funktioiden ei-toivotut moninkertaiset evaluoinnit. Kolikon kääntöpuolena on kuitenkin se, että optimoija ei pysty yhtä hyvin työntämään rajoituksia emokyselystä alaspäin WITH-kyselyyn kuin tavalliseen alakyselyyn. WITH-kysely arvioidaan yleensä niin kuin se on kirjoitettu ilman sellaisten rivien tukahduttamista, jotka emokysely saattaa hylätä jälkikäteen. (Mutta kuten edellä mainittiin, evaluointi saatetaan lopettaa ennenaikaisesti, jos viittaus(t) kyselyyn vaativat vain rajoitetun määrän rivejä.)

Yllä olevissa esimerkeissä WITH-kyselyä käytetään vain SELECT-kyselyn kanssa, mutta se voidaan liittää samalla tavalla INSERT-, UPDATE- tai DELETE-kyselyyn. Kussakin tapauksessa se tarjoaa tehokkaasti väliaikaisen taulukon tai väliaikaiset taulukot, joihin voidaan viitata pääkäskyssä.