Yleiset taulukkolausekkeet (WITH)
Muokatkaa sisäkkäiset kyselyt luettaviksi WITH-lausekkeiksi, ketjuttakaa CTE-lausekkeita ja oppikaa PostgreSQL 12:n ja uudempien versioiden materialisointisäännöt.
Yleiset taulukkolausekkeet (WITH) on ilmainen SQL Academy-oppitunti CoddyKitissä. Tämä on oppitunti 3/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu SQL Academy-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL Academy-kurssilla on yhteensä 4 oppituntia.
Mikä on CTE?
Yhteinen taululauseke (CTE) on nimetty alikysely, joka määritellään WITH-lauseella:
WITH paid_orders AS (
SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, COUNT(*)
FROM paid_orders
GROUP BY user_id;Miksi käyttää CTE:itä?
Kolme merkittävää hyötyä:
- Luettavuus — jakaa 200-rivisen kyselyn nimetyiksi vaiheiksi
- Uudelleenkäyttö — samaan välitulokseen voi viitata useita kertoja
- Rekursio — vain CTE:t tukevat rekursiivisia kyselyitä (seuraava oppitunti)
CTE:iden ketjuttaminen
Määrittele useita CTE:itä yhdessä WITH-lauseessa ja käytä yhtä seuraavassa:
WITH last_30 AS (
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
),
per_user AS (
SELECT user_id, SUM(total) AS revenue FROM last_30 GROUP BY user_id
)
SELECT u.email, p.revenue
FROM users u
JOIN per_user p ON p.user_id = u.id
ORDER BY p.revenue DESC LIMIT 20;CTE:n uudelleenkäyttö
Jos samaa välitulosta käytetään kahdesti, CTE tekee tarkoituksesta selkeän:
WITH recent_users AS (
SELECT id FROM users WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT 'new orders' AS metric, COUNT(*) FROM orders
WHERE user_id IN (SELECT id FROM recent_users)
UNION ALL
SELECT 'new revenue', SUM(total) FROM orders
WHERE user_id IN (SELECT id FROM recent_users);CTE:n materialisointi (PostgreSQL ≤ 11)
Vanhemmat PG-versiot materialisoivat aina CTE:n tuloksen — tämä muodosti kyselysuunnittelijalle esteen. PG 12:sta alkaen kyselysuunnittelija upottaa CTE:t oletusarvoisesti, ellet määritä toisin:
-- Force the old materialise behaviour (rarely needed):
WITH x AS MATERIALIZED (SELECT ...) ...
-- Force inlining (default):
WITH x AS NOT MATERIALIZED (SELECT ...) ...Dataa muokkaavat CTE:t
CTE:t voivat käyttää komentoja INSERT/UPDATE/DELETE — tämä on kätevää rivien siirtämiseen taulujen välillä atomisesti:
WITH moved AS (
DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;CTE:t DML-komennoissa
RETURNING + WITH "etsi ja toimi" -toteutukseen:
WITH cancelled AS (
UPDATE orders SET status = 'cancelled'
WHERE created_at < NOW() - INTERVAL '14 days'
AND status = 'pending'
RETURNING id
)
INSERT INTO audit_log (event, order_id)
SELECT 'auto-cancel', id FROM cancelled;Suoritusjärjestys
Dataa muokkaavat CTE:t suoritetaan toisistaan riippumatta samassa tilannevedoksessa. Jokainen käsky näkee tilan ENNEN kaikkien muutosten aloittamista — yllättävää mutta ennustettavaa.
CTE:t, alikyselyt ja näkymät
Vertailu:
- Alikysely — määritellään suoraan kyselyssä; käytetään kerran
- CTE — nimetty; käytetään kyselyssä useita kertoja; poistuu kyselyn päätyttyä
- Näkymä — nimetty; säilytetään; uudelleenkäytettävissä eri kyselyissä
Älä käytä CTE:itä liikaa
Kaiken kääriminen CTE:hen tekee kyselyistä luettavia, mutta voi peittää kustannukset. Suurten välitulosten yhteydessä kyselysuunnittelija saattaa valita huonomman suunnitelman kuin vastaavalle JOIN-kyselylle.
Kertaus
CTE:t nimeävät välivaiheen kyselyitä.
- Parantavat luettavuutta
- Mahdollistavat uudelleenkäytön yhdessä kyselyssä
- Ovat välttämättömiä rekursiossa
- Upotetaan oletusarvoisesti nykyaikaisissa PG-versioissa
Pikatarkistus
Mikä avainsana aloittaa yhteisen taululausekkeen?
Opi SQL tekoälytuutorin avulla — ilmaiseksi
Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.
- Kurssit
- 46
- Oppitunnit
- 183
Usein kysytyt kysymykset
Onko oppitunti ”Yleiset taulukkolausekkeet (WITH)” ilmainen?
Kyllä – oppitunnin ”Yleiset taulukkolausekkeet (WITH)” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko SQL Academy-kurssin, päivitä CoddyKit PROhon. SQL Academy-kurssilla on yhteensä 4 oppituntia.
Mitä opin oppitunnilla ”Yleiset taulukkolausekkeet (WITH)”?
Muokatkaa sisäkkäiset kyselyt luettaviksi WITH-lausekkeiksi, ketjuttakaa CTE-lausekkeita ja oppikaa PostgreSQL 12:n ja uudempien versioiden materialisointisäännöt. Harjoittelet SQL Academy-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.
Tarvitsenko kokemusta aloittaakseni SQL Academy-opiskelun?
Aiempi kokemus ei ole tarpeen. CoddyKitin SQL Academy-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 3/4.
Kuinka kauan ”Yleiset taulukkolausekkeet (WITH)”-oppitunnin suorittaminen kestää?
Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.
Voinko kirjoittaa ja suorittaa koodia tällä SQL Academy-oppitunnilla?
Kyllä. Jokainen SQL Academy-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.
Kaikki tämän kurssin oppitunnit
- Skalaari-, rivi- ja taulualikyselyt
- Korreloivat ja korreloimattomat alikyselyt
- Yleiset taulukkolausekkeet (WITH)
- Rekursiiviset CTE:t hierarkioille