Ensimmäisen CTE:n kirjoittaminen
WITH-syntaksin perusteet ja milloin CTE parantaa luettavuutta alikyselyyn verrattuna
Ensimmäisen CTE:n kirjoittaminen on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 1/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-työhaastatteluun valmistautuminen-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.
Mikä CTE oikeastaan on
Common Table Expression (CTE) on WITH-avainsanalla määritelty nimetty, väliaikainen tulosjoukko, joka on olemassa vain yhden kyselyn ajan. Haastattelijat pitävät CTE:istä, koska ne paljastavat, osaatteko jäsentää logiikan selkeästi.
Ajatelkaa CTE:tä alikyselylle annettuna nimenä, jonka avulla voitte viitata siihen taulun tavoin sitä seuraavassa päälausekkeessa. Se ei luo pysyvää objektia, vaan katoaa heti kyselyn valmistuttua.
WITH-perussyntaksi
Jokainen CTE alkaa WITH-avainsanalla, nimellä, AS-avainsanalla ja sulkeisiin sijoitetulla kyselyllä. Sulkevien sulkeiden jälkeen kirjoitetaan tavallinen lause, joka käyttää CTE:tä sen nimellä.
WITH cte_name AS ( ... )määrittelee lohkon.- Sulkeiden jälkeen tuleva kysely on pääkysely.
- CTE:n nimi toimii kuin taulu, josta voi tehdä SELECT-haun.
WITH recent_orders AS (
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
)
SELECT *
FROM recent_orders;Miksi ei vain alikyselyä
Sama logiikka voidaan kirjoittaa FROM-lauseen sisäisenä alikyselynä. Miksi haastattelijat sitten kysyvät CTE:istä?
- Luettavuus: nimetty vaihe etenee ylhäältä alas reseptin tavoin.
- Uudelleenkäyttö: samaan CTE:hen voi viitata useita kertoja sen sijaan, että alikysely kirjoitettaisiin uudelleen.
- Virheenkorjattavuus: voitte suorittaa SELECTin pelkästään CTE:stä ja tarkastella sen sisältöä.
Haastattelussa oikea vastaus on yleensä: käyttäkää CTE:tä, kun se tekee kyselystä helpommin luettavan ja ylläpidettävän.
Työstetty esimerkki: suodatus ja sen jälkeen aggregointi
Oletetaan, että kysymys kuuluu: etsi tänä vuonna tehtyjen tilausten kokonaistuotto. CTE:n avulla voitte eristää suodatusvaiheen ja tehdä aggregoinnin nimetyn tuloksen perusteella.
Pääkysely käsittelee recent_orders-tulosta ikään kuin se olisi oikea taulu, mikä pitää aggregoinnin selkeänä ja helposti ymmärrettävänä.
WITH recent_orders AS (
SELECT amount
FROM orders
WHERE order_date >= '2024-01-01'
)
SELECT SUM(amount) AS total_revenue
FROM recent_orders;Tulossarakkeiden nimeäminen
Voitte nimetä CTE:n tarjoamat sarakkeet uudelleen luettelemalla nimet heti CTE:n nimen jälkeen. Tämä on hyödyllistä, kun sisempi kysely tuottaa lausekkeita tai haluatte selkeämmät nimet kyselyn seuraaviin vaiheisiin.
Jos annatte sarakeluettelon, sen on vastattava sisemmän kyselyn palauttamien sarakkeiden määrää. Muussa tapauksessa tietokanta ilmoittaa virheestä.
WITH revenue (region, total) AS (
SELECT region, SUM(amount)
FROM orders
GROUP BY region
)
SELECT region, total
FROM revenue
ORDER BY total DESC;CTE on vain nimetty kysely
Yksi haastattelijoihin vaikutuksen tekevä ajattelumalli on tämä: CTE vastaa loogisesti määritelmänsä sijoittamista suoraan kyselyyn. Tietokanta voi päättää sijoittaa sen suoraan kyselyyn tai materialisoida sen, mutta semanttisesti tulos on sama kuin jos alikysely olisi liitetty kyselyyn sellaisenaan.
Tämä tarkoittaa, että kaikki tavallisessa SELECT-lauseessa sallitut rakenteet ovat sallittuja myös CTE:n sisällä: liitokset, GROUP BY, WHERE, ikkunafunktiot ja paljon muuta.
Syvempi esimerkki: CTE ja JOIN
CTE:t ovat parhaimmillaan, kun JOINin toinen puoli täytyy muotoilla valmiiksi. Tässä laskemme ensin asiakaskohtaisen tilausten määrän ja liitämme sen sitten takaisin asiakkaat-tauluun, jotta jokaisella asiakasrivillä näkyy kokonaismäärä.
Huomatkaa, miten pääkysely muistuttaa lähes englanninkielistä lausetta: asiakkaista ja niiden tilausmäärien liittämisestä.
WITH order_counts AS (
SELECT customer_id, COUNT(*) AS num_orders
FROM orders
GROUP BY customer_id
)
SELECT c.name, oc.num_orders
FROM customers c
JOIN order_counts oc
ON oc.customer_id = c.id;Missä CTE sijaitsee kyselyssä
WITH-lohkon on sijaittava sitä käyttävän lauseen edellä. CTE näkyy vain yhdessä lauseessa, joka seuraa välittömästi sen määritelmää.
- CTE:hen ei voi viitata myöhemmässä erillisessä kyselyssä.
- SELECT-lauseelle määriteltyä CTE:tä ei voi käyttää uudelleen toisessa myöhemmin suoritettavassa SELECT-lauseessa.
- CTE:n näkyvyysalue päättyy lauseen päättävään puolipisteeseen.
CTE:t toimivat INSERT-, UPDATE- ja DELETE-lauseiden kanssa
Usein esitetty jatkokysymys: CTE:t eivät rajoitu SELECT-lauseisiin. Useimmissa nykyaikaisissa tietokannoissa WITH-lauseen voi liittää myös tietoja muokkaaviin lauseisiin.
Näin rivijoukon voi laskea kerran ja käsitellä sitä sitten, mikä on paljon selkeämpää kuin sisäkkäinen alikysely WHERE-lauseessa.
WITH stale AS (
SELECT id
FROM sessions
WHERE last_seen < NOW() - INTERVAL '30 days'
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM stale);Yleisiä aloittelijan virheitä
Haastattelijat tarkkailevat erityisesti näitä virheitä:
- Pääkysely unohdetaan CTE:n jälkeen; pelkkä
WITH-lohko ei ole täydellinen lause. - CTE:n ja pääkyselyn väliin sijoitetaan puolipiste.
- Odotetaan, että CTE säilyy useiden lauseiden välillä.
- Valinnainen sarakenimien luettelo ei vastaa sisemmän kyselyn sarakkeita.
Miten CTE:istä puhutaan haastattelussa
Kun teitä pyydetään muotoilemaan sekava alikysely uudelleen, kertokaa ajattelustanne: Siirrän tämän suodatusta tekevän alikyselyn CTE:hen nimeltä recent_orders, jotta aggregointi on selkeä.
Kun osoitatte valitsevanne CTE:t selkeyden ja uudelleenkäytön vuoksi ettekä sokeasti, osoitatte keskitason osaamista. Mainitkaa, ettei CTE itsessään tee kyselystä nopeampaa, vaan sen tärkein hyöty on luettavuus.
Pikatarkistus
Testatkaa perustason CTE-syntaksin ja näkyvyysalueen ymmärtämistänne.
Kertaus: ensimmäinen CTE
Opitte, että CTE käyttää rakennetta WITH name AS ( ... ) väliaikaisen tulosjoukon nimeämiseen ja että siihen viitataan taulun tavoin seuraavassa lauseessa.
- CTE:t parantavat luettavuutta, uudelleenkäytettävyyttä ja vianjäljityksen helppoutta verrattuna suoraan upotettuihin alikyselyihin.
- Niiden näkyvyys rajoittuu yhteen lauseeseen, minkä jälkeen ne katoavat.
- Ne toimivat sekä SELECT- että INSERT/UPDATE/DELETE-käskyissä.
- Ne eivät itsessään paranna suorituskykyä.
Seuraavaksi: useiden CTE:iden ketjuttaminen putkeksi.
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
- 30
- Oppitunnit
- 120
Usein kysytyt kysymykset
Onko oppitunti ”Ensimmäisen CTE:n kirjoittaminen” ilmainen?
Kyllä – oppitunnin ”Ensimmäisen CTE:n kirjoittaminen” 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-työhaastatteluun valmistautuminen-kurssin, päivitä CoddyKit PROhon. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.
Mitä opin oppitunnilla ”Ensimmäisen CTE:n kirjoittaminen”?
WITH-syntaksin perusteet ja milloin CTE parantaa luettavuutta alikyselyyn verrattuna Harjoittelet SQL-työhaastatteluun valmistautuminen-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.
Tarvitsenko kokemusta aloittaakseni SQL-työhaastatteluun valmistautuminen-opiskelun?
Aiempi kokemus ei ole tarpeen. CoddyKitin SQL-työhaastatteluun valmistautuminen-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 1/4.
Kuinka kauan ”Ensimmäisen CTE:n kirjoittaminen”-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-työhaastatteluun valmistautuminen-oppitunnilla?
Kyllä. Jokainen SQL-työhaastatteluun valmistautuminen-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
- Ensimmäisen CTE:n kirjoittaminen
- Useiden CTE:iden ketjuttaminen
- CTE, alikysely vai väliaikainen taulu
- Sisäkkäisten kyselyjen uudelleenjärjestäminen CTE:iksi