SQL-työhaastatteluun valmistautuminen · Oppitunti

FROM-lausekkeen alikyselyt (johdetut taulut)

Kääri kysely virtuaalitaulukoksi ja opi, miksi alias on pakollinen

Oppitunti 2/413 vaihetta

FROM-lausekkeen alikyselyt (johdetut taulut) 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.

Mikä on johdettu taulu

FROM-osassa olevaa alikyselyä kutsutaan johdetuksi tauluksi (tai upotetuksi näkymäksi). Yhden arvon sijaan se palauttaa kokonaisen tulosjoukon, jota ulompi kysely käsittelee aivan kuin se olisi oikea taulu.

Siinä voi olla useita rivejä ja useita sarakkeita.
Sitä voi kysellä, siihen voi tehdä liitoksia ja sitä voi suodattaa kuten mitä tahansa taulua.

Haastattelijat käyttävät johdettuja tauluja testatakseen, osaatteko jakaa ongelman vaiheisiin.

Aliakset ovat pakollisia

Yleisin sudenkuoppa on tämä: johdetulla taululla täytyy olla alias. Ilman sitä useimmat tietokantamoottorit hylkäävät kyselyn.

MySQL: Every derived table must have its own alias.
Postgres: subquery in FROM must have an alias.

Antakaa sille nimi (tässä dept_avg), niin voitte viitata sen sarakkeisiin tämän nimen avulla.

SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS dept_avg;

Miksi johdetussa taulussa tehdään esikoostaminen

Tyypillinen haastattelutehtävä on näyttää jokainen työntekijä oman osastonsa keskimääräisen palkan rinnalla. Tietoriviä ja koostefunktiota ei voi yhdistää suoraan ilman ryhmittelyyn liittyviä ongelmia.

Selkeä ratkaisu on laskea osastokohtainen keskiarvo johdetussa taulussa ja liittää se sitten takaisin tietoriveihin. Johdettu taulu tiivistetään ensin yhdeksi riviksi osastoa kohden.

SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d ON e.dept_id = d.dept_id;

Koostetuloksen suodattaminen

Johdettujen taulujen avulla voit suodattaa laskettua koostetulosta ilman ulomman kyselyn HAVING-osan monimutkaisia ratkaisuja. Oletetaan, että haluamme vain osastot, joiden keskimääräinen palkka ylittää 60000.

Teemme koostamisen sisemmässä kyselyssä ja käytämme sen jälkeen johdetun sarakkeen tavallista WHERE-suodatusta ulommassa kyselyssä. Ulompi kysely näkee avg_salary-sarakkeen tavallisena sarakkeena.

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d
WHERE avg_salary > 60000;

Kaksi koostamisen tasoa

Johdetut taulut ovat parhaimmillaan, kun tarvitaan koostefunktiota koostetulokselle — klassinen haastattelukysymys on: mikä on osastokohtaisten keskipalkkojen keskiarvo?

AVG(AVG(...))-rakennetta ei voi käyttää suoraan sisäkkäin. Sisempi kysely tuottaa yhden keskiarvon osastoa kohden, ja ulompi kysely laskee näiden keskiarvojen keskiarvon.

SELECT AVG(avg_salary) AS avg_of_dept_avgs
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d;

Laskettujen sarakkeiden nimeäminen

Jokainen johdetussa taulussa oleva lauseke tarvitsee aliaksen, jos siihen halutaan viitata ulkopuolelta. Sisemmän kyselyn salary * 12 -lausekkeelle annettaisiin muuten tietokannan määrittämä nimi, johon ei voi luottaa.

Nimetkää lasketut sarakkeet aina aliaksilla — haastattelijat huomaavat, jos viittaatte aliaksettomaan lausekkeeseen ja oletatte sarakenimen, jota ei ehkä ole olemassa.

SELECT name, annual_salary
FROM (
  SELECT name, salary * 12 AS annual_salary
  FROM employees
) AS yearly
WHERE annual_salary > 100000;

Kahden johdetun taulun yhdistäminen

Voitte yhdistää useita johdettuja tauluja toisiinsa. Tässä vertaamme kunkin osaston työntekijämäärää sen kokonaispalkkasummaan yhdistämällä kaksi esikoostettua alikyselyä.

Kukin johdettu taulu vastaa yhteen alakysymykseen, ja liitos yhdistää vastaukset lopulliseksi raportiksi. Tämä vaiheittainen ajattelutapa on juuri sitä, mitä keskitason työhaastatteluissa arvostetaan.

SELECT c.dept_id, c.headcount, p.payroll
FROM (
  SELECT dept_id, COUNT(*) AS headcount
  FROM employees GROUP BY dept_id
) AS c
JOIN (
  SELECT dept_id, SUM(salary) AS payroll
  FROM employees GROUP BY dept_id
) AS p ON c.dept_id = p.dept_id;

Näkyvyysalue: ulompi kysely ei näe sisälle

Tärkeä sääntö on tämä: ulompi kysely voi viitata vain sarakkeisiin, jotka johdettu taulu paljastaa SELECT-listassaan. Pelkästään alikyselyn sisällä käytetyt sarakkeet eivät näy ulkopuolelle.

Jos sisempi kysely valitsee sarakkeet dept_id ja avg_salary, sarakkeet salary tai name eivät ole käytettävissä ulkopuolella — koostaminen kulutti ne. Haastattelijat testaavat tätä näkyvyysrajaa.

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
) AS d;

Johdettu taulu ja CTE

Johdettu taulu ja Common Table Expression (CTE) tuottavat usein saman suorituskykysuunnitelman. Haastattelija saattaa kysyä, miksi valitsisitte toisen:

Johdettu taulu: upotettu, sopii kertaluonteiseen käyttöön.
CTE (WITH): nimetään alussa, on selkeä ja uudelleenkäytettävä, jos siihen viitataan useita kertoja.

Monimutkaisessa sisäkkäisessä logiikassa CTE-putki etenee selkeästi ylhäältä alas, kun taas johdettu taulu luetaan sisältä ulospäin.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_salary > 60000;

LATERAL / korreloitu FROM-alikysely

Tavallisesti FROM-osan alikysely ei voi viitata ulomman kyselyn riveihin. LATERAL (Postgres) tai CROSS APPLY (SQL Server) poistaa tämän rajoituksen ja sallii johdetun taulun suorittamisen ulomman kyselyn jokaista riviä kohden.

Tämä mahdollistaa rivikohtaiset top-N-haut. Jo pelkkä tietoisuus tästä avainsanasta osoittaa edistyneen tason ymmärrystä myös keskitason haastattelussa.

SELECT d.dept_name, top_emp.name, top_emp.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT name, salary FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC LIMIT 1
) AS top_emp;

Haastatteluvastaus

Jos teiltä kysytään FROM-lauseen alikyselyistä, vastatkaa: "Johdettu taulu on FROM-lauseessa oleva alikysely, joka palauttaa tulosjoukon, jota ulompi kysely käyttää taulun tavoin. Sillä on oltava alias, ulompi kysely näkee vain sen valitsemat sarakkeet, ja se sopii erinomaisesti esikoostamiseen ennen liitosta tai aggregaatin aggregointiin."

Lisätkää vielä, että LATERAL sallii ulompien rivien viittaamisen, niin olette käsitelleet aiheen joka puolelta.

Pikatarkistus

Valitkaa lause, joka on aina pakollinen FROM-lauseen alikyselylle.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Johdetut taulut pähkinänkuoressa:

FROM-alkysely palauttaa virtuaalisen taulun — useita rivejä ja useita sarakkeita.
Sillä on oltava alias; ulompi kysely näkee vain sen valitsemat sarakkeet.
Käyttäkää sitä esikoostamiseen ennen liitosta, aggregaattien perusteella suodattamiseen tai aggregaatin aggregointiin.
CTE on selkeästi nimetty vaihtoehto; LATERAL/CROSS APPLY sallivat ulompien rivien viittaamisen.

Seuraavaksi: joukon jäsenyyttä testaavat alikyselyt IN-, ANY- ja ALL-operaattoreilla.

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 ”FROM-lausekkeen alikyselyt (johdetut taulut)” ilmainen?

Kyllä – oppitunnin ”FROM-lausekkeen alikyselyt (johdetut taulut)” 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 ”FROM-lausekkeen alikyselyt (johdetut taulut)”?

Kääri kysely virtuaalitaulukoksi ja opi, miksi alias on pakollinen 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 ”FROM-lausekkeen alikyselyt (johdetut taulut)”-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

Skalaarialikyselyt SELECT- ja WHERE-lausekkeissa
FROM-lausekkeen alikyselyt (johdetut taulut)
IN-, ANY- ja ALL-alikyselyt
EXISTS- ja IN-funktioiden suorituskyky

← Takaisin: SQL-työhaastatteluun valmistautuminen