SQL-työhaastatteluun valmistautuminen · Oppitunti

Useiden CTE:iden ketjuttaminen

Rakenna nimettyjen vaiheiden putki, jossa vaiheet viittaavat toisiinsa

Oppitunti 2/413 vaihetta

Useiden CTE:iden ketjuttaminen on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 2/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.

Miksi CTE:itä ketjutetaan

Todelliset työhaastattelutehtävät harvoin ratkeavat yhdellä askeleella. CTE:iden ketjuttamisen avulla voitte rakentaa nimettyjen vaiheiden putken, jossa kukin vaihe muuntaa edellisen vaiheen tulostetta. Tämä vastaa tapaa, jolla kokenut insinööri pilkkoo vaikean kyselyn hallittaviksi osiksi.

Sen sijaan, että alikyselyitä sisäkkäistettäisiin kolme tasoa syvälle, kukin vaihe kirjoitetaan kerran, nimetään ja myöhemmät vaiheet voivat viitata siihen.

Pilkuilla eroteltu syntaksi

Useita CTE:itä määritettäessä WITH kirjoitetaan kerran, minkä jälkeen kukin nimetty lohko erotetaan pilkulla. WITH-avainsanaa ei toisteta.

Yksi WITH kyselyn alussa.
Kunkin CTE-määrittelyn välissä on pilkku.
Lopullisen pääkyselyn edellä ei ole pilkkua.

WITH a AS (
    SELECT customer_id FROM orders
),
b AS (
    SELECT customer_id FROM a
)
SELECT *
FROM b;

Myöhemmät CTE:t voivat viitata aiempiin

Ketjutuksen voima on siinä, että CTE voi lukea mistä tahansa ennen sitä määritellystä CTE:stä. Näkyvyys kulkee vain eteenpäin, joten voitte rakentaa riippuvuuksien ketjun.

Aiempi CTE ei voi nähdä myöhempää CTE:tä, joten järjestyksellä on merkitystä. Järjestäkää vaiheet raakadatasta kohti lopullista muotoa.

WITH filtered AS (
    SELECT *
    FROM events
    WHERE event_type = 'purchase'
),
per_user AS (
    SELECT user_id, COUNT(*) AS purchases
    FROM filtered
    GROUP BY user_id
)
SELECT *
FROM per_user;

Esimerkki: kolmivaiheinen putki

Kysymys: mikä on yli 1 000 dollaria käyttäneiden asiakkaiden keskimääräinen kulutus? Jakakaa ongelma kolmeen vaiheeseen: laskekaa kunkin asiakkaan kokonaiskulutus, suodattakaa paljon kuluttaneet ja laskekaa sitten heidän keskiarvonsa.

Kunkin CTE:n nimi kertoo sen tarkoituksen, joten tarkistaja ymmärtää työnkulun heti.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
),
big_spenders AS (
    SELECT customer_id, total
    FROM spend
    WHERE total > 1000
)
SELECT AVG(total) AS avg_big_spend
FROM big_spenders;

Määrittelyjärjestyksellä on merkitystä

Koska näkyvyys kulkee vain eteenpäin, toisesta CTE:stä riippuvainen CTE on lueteltava riippuvuutensa jälkeen. Jos viittaatte nimeen, jota ei ole vielä määritelty, tietokanta antaa 'relation does not exist' -virheen.

Hyvä tapa on käydä CTE-luettelo ylhäältä alas ja varmistaa, että jokainen käytetty nimi on jo esiintynyt aiemmalla rivillä.

Yhteen CTE:hen viittaaminen useasta muusta

Yksi CTE voi syöttää tietoja useille seuraaville CTE:ille. Tässä ketjutus voittaa sisäkkäiset alikyselyt: laskette perustuloksen kerran ja haarautatte käsittelyn siitä.

Tässä sekä active että recent lukevat base-CTE:stä, joten logiikkaa ei tarvitse toistaa.

WITH base AS (
    SELECT * FROM users WHERE deleted = false
),
active AS (
    SELECT id FROM base WHERE last_login > NOW() - INTERVAL '7 days'
),
recent AS (
    SELECT id FROM base WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT (SELECT COUNT(*) FROM active) AS active_cnt,
       (SELECT COUNT(*) FROM recent) AS recent_cnt;

Kahden CTE:n yhdistäminen

Ketjutetut CTE:t yhdistetään usein pääkyselyssä. Laskekaa kumpikin puoli erikseen ja yhdistäkää ne sitten. Näin kukin laskenta pysyy erillään ja liitos on yksinkertainen.

Alla laskemme tilausten ja hyvitysten määrät toisistaan riippumatta ja yhdistämme ne sitten asiakaskohtaisesti.

WITH orders_cte AS (
    SELECT customer_id, COUNT(*) AS orders
    FROM orders GROUP BY customer_id
),
refunds_cte AS (
    SELECT customer_id, COUNT(*) AS refunds
    FROM refunds GROUP BY customer_id
)
SELECT o.customer_id, o.orders, COALESCE(r.refunds, 0) AS refunds
FROM orders_cte o
LEFT JOIN refunds_cte r ON r.customer_id = o.customer_id;

Luettavuus sisäkkäisyyden sijaan

Verratkaa kolme tasoa syvää sisäkkäistä alikyselyä kolmen CTE:n putkeen. Sisäkkäinen versio pakottaa lukijan purkamaan rakenteen mielessään sisältä ulospäin. CTE-versio etenee suoritusjärjestyksessä ylhäältä alas.

Haastattelijat arvostavat CTE-lähestymistapaa, koska sitä he haluaisivat ylläpitää tuotannossa. Kunkin vaiheen nimeäminen on dokumentaatiota, joka ei vanhene.

Yleinen ketjutusvirhe

Aloittelijat lisäävät usein pilkun viimeisen CTE:n jälkeen, juuri ennen pääkyselyä. Tämä ylimääräinen pilkku on syntaksivirhe.

Pilkut tulevat vain CTE-määrittelyjen väliin.
Loppusuljetta seuraa suoraan pääkysely ilman pilkkua.

Toinen sudenkuoppa on unohtaa, että jokainen CTE tarvitsee sulkeiden sisään oman kokonaisen SELECT-kyselynsä.

Suoritetaanko kukin vaihe erikseen

Hienovarainen haastattelussa esiin tuleva näkökulma: loogisesti putki koostuu erillisistä vaiheista, mutta optimoija saattaa sisällyttää ne suoraan yhteen ja yhdistää ne yhdeksi suorityssuunnitelmaksi. Useimmissa tietokantamoottoreissa välituloksia ei siis tarvitse materialisoida.

Ketjutus auttaa teitä hahmottamaan kyselyn ilman, että se välttämättä heikentää suorituskykyä. Mainitkaa tämä osoittaaksenne ymmärtävänne kokonaisuuden.

Vaiheiden nimeäminen putken tavoin

Hyvät vaihenimet tekevät kyselystä itseään dokumentoivaa koodia. Suosikaa nimiä, jotka kuvaavat kunkin vaiheen tulostetta, eivät sen toimintoa.

spend ja big_spenders ovat parempia kuin step1 ja step2.
Lukijan pitäisi pystyä päättelemään koko työnkulku pelkkien CTE-nimien perusteella.
Johdonmukainen nimeäminen eri vaiheissa tekee pääkyselyn liitoksesta ilmeisen.

Haastattelussa vaiheiden selkeä nimeäminen osoittaa, että kirjoitatte ylläpidettävää tuotanto-SQL:ää.

Pikatarkistus

Tarkistakaa, miten ketjutetut CTE:t viittaavat toisiinsa.

Vastaa interaktiivisessa oppitunnissa

Kertaus: CTE:iden ketjuttaminen

Opitte rakentamaan putkia: yksi WITH, pilkuilla erotellut CTE-määrittelyt ja vain eteenpäin kulkeva näkyvyys, jossa kukin vaihe voi lukea aiemmista vaiheista.

Järjestäkää CTE:t raakadatasta kohti lopullista tulosta.
Uudelleenkäyttäkää yhtä perustason CTE:tä useissa seuraavissa vaiheissa.
Pääkyselyn edellä ei ole ylimääräistä pilkkua.
Ketjutus parantaa luettavuutta ilman, että se välttämättä heikentää suorituskykyä.

Seuraavaksi: CTE:iden vertaaminen alikyselyihin ja väliaikaisiin tauluihin.

Aloita maksutta

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

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Useiden CTE:iden ketjuttaminen” ilmainen?

Kyllä – oppitunnin ”Useiden CTE:iden ketjuttaminen” 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 ”Useiden CTE:iden ketjuttaminen”?

Rakenna nimettyjen vaiheiden putki, jossa vaiheet viittaavat toisiinsa 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 2/4.

Kuinka kauan ”Useiden CTE:iden ketjuttaminen”-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

← Takaisin: SQL-työhaastatteluun valmistautuminen