Korreloitujen alikyselyjen muuttaminen liitoksiksi
Muunna korreloitu logiikka suorituskyvyn parantamiseksi liitoksiksi tai ikkunafunktioiksi
Korreloitujen alikyselyjen muuttaminen liitoksiksi on ilmainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti CoddyKitissä. Tämä on oppitunti 4/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 Valmistautuminen ohjelmointihaastatteluihin-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.
Miksi kirjoittaa uudelleen lainkaan
Korreloidut alikyselyt ovat helppolukuisia, mutta ne voivat olla hitaita: sisempi kysely saatetaan suorittaa kerran jokaista ulomman kyselyn riviä kohti. Haastattelijat pyytävät usein kirjoittamaan alikyselyn uudelleen JOINiksi tai ikkunafunktioksi suorituskyvyn parantamiseksi.
Tavoitteena on saada sama tulos yhdellä datan läpikäynnillä toistuvien sisempien kyselyiden sijaan.
Kahden tai kolmen uudelleenkirjoitusmallin tunteminen ja sen tietäminen, milloin kukin niistä säilyttää oikeellisuuden, on keskeinen keskitason taito.
Malli 1: EXISTS INNER JOINiksi
Korreloitu EXISTS, joka tarkistaa vähintään yhden osuman, voidaan usein muuttaa INNER JOIN -liitokseksi.
Varokaa kuitenkin: liitos voi tuottaa ulomman kyselyn rivien kaksoiskappaleita, jos useat sisemmän kyselyn rivit täsmäävät. Lisätkää DISTINCT tai käyttäkää aggregointia palauttaaksenne yhden rivin ulompaa avainta kohti.
-- Correlated EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);
-- Join rewrite (DISTINCT avoids dupes from fan-out)
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;Fan-out-ongelma
Yleisin uudelleenkirjoitusvirhe on fan-outin unohtaminen. EXISTS palauttaa jokaisen asiakkaan kerran riippumatta hänen tilaustensa määrästä. Naiivi JOIN palauttaa yhden rivin jokaista tilausta kohti, mikä kasvattaa laskettuja määriä virheellisesti.
Jos seuraava käsittelyvaihe tekee COUNT(*)- tai SUM(amount)-laskennan yhdistetystä tuloksesta ilman huolellista ryhmittelyä, luvut ovat väärin.
Kysykää aina: voiko JOIN monistaa rivejä? Jos voi, käyttäkää DISTINCT- tai GROUP BY -rakennetta rivien palauttamiseksi yhdeksi kutakin ulompaa avainta kohti.
Malli 2: NOT EXISTS LEFT JOIN / IS NULL -muotoon
Anti-join-uudelleenkirjoitus on varma haastatteluklassikko. Korreloitu NOT EXISTS muutetaan LEFT JOIN -liitokseksi, jossa oikean puolen arvo on NULL.
Ilman osumaa jäävät ulommat rivit saavat oikealle puolelle NULL-arvot. Kun suodatatte nämä NULL-arvot, mukaan jäävät täsmälleen rivit, joille ei ole osumaa.
-- Correlated NOT EXISTS
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);
-- LEFT JOIN / IS NULL rewrite
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;Valitkaa testattavaksi NOT NULL -sarake
LEFT JOIN / IS NULL -uudelleenkirjoituksessa testatkaa oikean puolen saraketta, joka ei ole koskaan NULL aidossa osumassa, mieluiten liitosavainta tai pääavainta.
Jos testaatte NULL-arvon sallivaa saraketta, ette voi erottaa aitoa osuman puuttumista eli rivin puuttumista siitä, että täsmäävällä rivillä kyseisen sarakkeen arvo on NULL. Tämä virhe palauttaa vääriä rivejä.
Liitosavaimen, tässä tapauksessa o.customer_id:n, tai o.order_id:n käyttäminen takaa, että NULL tarkoittaa "täsmäävää riviä ei ole".
Malli 3: skalaarinen aggregaatti JOIN + GROUP BY -rakenteeksi
SELECT-lauseen korreloitu aggregaatti voidaan muuttaa liitokseksi ryhmiteltyyn alikyselyyn eli johdettuun tauluun.
Laskekaa ryhmäkohtainen aggregaatti kerran ja liittäkää se sitten takaisin tietoriveihin. Sisempi kysely suoritetaan vain kerran eikä jokaiselle riville erikseen.
-- Correlated scalar aggregate
SELECT e1.name,
(SELECT MAX(e2.salary) FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS dept_max
FROM employees e1;
-- Join + GROUP BY rewrite
SELECT e.name, m.dept_max
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS dept_max
FROM employees GROUP BY dept_id) m
ON m.dept_id = e.dept_id;Malli 4: ikkunafunktiolla uudelleenkirjoittaminen
Usein siistein uudelleenkirjoitus on ikkunafunktio. MAX(salary) OVER (PARTITION BY dept_id) korvaa korreloidun aggregaatin kokonaan, eikä liitosta tarvita.
Se laskee ryhmän arvon yhdellä läpikäynnillä ja säilyttää kaikki tietorivit. Tämä on yleensä se vastaus, jonka haastattelijat haluavat analytiikkakyselyissä eniten nähdä.
SELECT name,
dept_id,
salary,
MAX(salary) OVER (PARTITION BY dept_id) AS dept_max
FROM employees;Suurimman N:n rivit ryhmittäin: uudelleenkirjoitus
Ryhmän parhaan rivin valitseva korreloitu alikysely, kuten salary = MAX per dept, voidaan kirjoittaa siististi uudelleen ROW_NUMBER-funktion avulla.
Osioikaa tiedot ryhmän mukaan, järjestäkää ne mittarin perusteella ja säilyttäkää sijan 1 saaneet rivit. Käyttäkää RANK-funktiota sen sijaan, jos haluatte kaikki tasatuloksissa olevat parhaat rivit.
SELECT name, dept_id, salary
FROM (
SELECT name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id
ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn = 1;Milloin ei pidä kirjoittaa uudelleen
Uudelleenkirjoitus ei aina paranna tilannetta. Säilyttäkää korreloitu alikysely, kun:
- Ulkoinen joukko on pieni, joten rivikohtainen kustannus on mitätön.
- Korreloitu sarake on hyvin indeksoitu ja optimoija muuttaa kyselyn jo valmiiksi tehokkaaksi semi-joiniksi.
- Ylläpidettävän koodin luettavuus on tärkeämpää kuin pienet optimoinnit.
Nykyaikaiset optimoijat muuttavat EXISTS-rakenteen usein automaattisesti semi-joiniksi. Sanokaa, että mittaisitte suorituksen EXPLAIN-komennolla ennen kuin oletatte uudelleenkirjoituksen parantavan suorituskykyä.
Vastaavuuden varmistaminen
Varmistakaa jokaisen uudelleenkirjoituksen jälkeen, että se palauttaa samat rivit ja saman kardinaliteetin kuin alkuperäinen kysely.
- Tarkistakaa, että rivimäärät täsmäävät.
- Tarkistakaa, ettei JOINin fan-out ole tuottanut kaksoiskappaleita.
- Tarkistakaa, että NULL-arvot ja tyhjien ryhmien reunatapaukset toimivat edelleen oikein.
Nopea tapa on suorittaa molemmat versiot ja käyttää EXCEPT-operaattoria molempiin suuntiin. Tyhjä tulos tarkoittaa, että versiot vastaavat toisiaan. Haastattelijat arvostavat sitä, että varmistatte asian oletusten sijaan.
SELECT customer_id FROM query_a
EXCEPT
SELECT customer_id FROM query_b;
-- and the reverse; both empty => equivalentINin uudelleenkirjoittaminen JOINiksi
Korreloimaton IN-alikysely voidaan usein kirjoittaa uudelleen JOINiksi, mutta sama fan-out-varoitus pätee. IN poistaa jäsenyydestä kaksoiskappaleet, mutta JOIN ei.
Jos sisempi luettelo sisältää kaksoiskappaleina olevia avaimia, JOIN toistaa ulommat rivit. Käyttäkää DISTINCT-rakennetta sisemmällä puolella tai lopputuloksessa, jotta semantiikka vastaa IN-rakennetta.
-- IN subquery
SELECT c.name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);
-- Join rewrite, de-duplicated to match IN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;Pikatarkistus
Valitkaa oikea JOIN-uudelleenkirjoitus korreloidulle NOT EXISTS -anti-joinille.
Kertaus: korreloitujen alikyselyiden uudelleenkirjoittaminen JOINeiksi
Keskeiset opit:
EXISTS→INNER JOIN(lisätkää DISTINCT fan-outin aiheuttamien kaksoiskappaleiden välttämiseksi).NOT EXISTS→LEFT JOIN ... WHERE key IS NULL(testatkaa saraketta, joka ei voi olla NULL).- Korreloitu skalaarinen aggregaatti → liittäkää
JOIN-rakenteella ryhmitelty johdettu taulu tai, vielä parempi, käyttäkää ikkunafunktiota. - Ryhmän parhaat rivit →
ROW_NUMBER(tai tasatuloksiinRANK). - Varmistakaa vastaavuus ja tarkistakaa suorituskyky komennolla
EXPLAINennen kuin oletatte uudelleenkirjoituksen olevan nopeampi.
Molempien muotojen ja fan-out-ansan tunteminen on juuri sitä, mitä keskitason haastatteluissa testataan.
Opi Valmistautuminen ohjelmointihaastatteluihin 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
- 90
- Oppitunnit
- 360
Usein kysytyt kysymykset
Onko oppitunti ”Korreloitujen alikyselyjen muuttaminen liitoksiksi” ilmainen?
Kyllä – oppitunnin ”Korreloitujen alikyselyjen muuttaminen liitoksiksi” 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 Valmistautuminen ohjelmointihaastatteluihin-kurssin, päivitä CoddyKit PROhon. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.
Mitä opin oppitunnilla ”Korreloitujen alikyselyjen muuttaminen liitoksiksi”?
Muunna korreloitu logiikka suorituskyvyn parantamiseksi liitoksiksi tai ikkunafunktioiksi Harjoittelet Valmistautuminen ohjelmointihaastatteluihin-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.
Tarvitsenko kokemusta aloittaakseni Valmistautuminen ohjelmointihaastatteluihin-opiskelun?
Aiempi kokemus ei ole tarpeen. CoddyKitin Valmistautuminen ohjelmointihaastatteluihin-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 4/4.
Kuinka kauan ”Korreloitujen alikyselyjen muuttaminen liitoksiksi”-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ä Valmistautuminen ohjelmointihaastatteluihin-oppitunnilla?
Kyllä. Jokainen Valmistautuminen ohjelmointihaastatteluihin-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
- Korreloidun alikyselyn rakenne
- Ryhmäkohtaiset koostearvot ilman GROUP BY -lauseketta
- Korreloidut EXISTS- ja NOT EXISTS -lausekkeet
- Korreloitujen alikyselyjen muuttaminen liitoksiksi