Valmistautuminen ohjelmointihaastatteluihin · Oppitunti

Toiseksi suurin palkka viidellä tavalla

Vertaa alikysely-, LIMIT/OFFSET- ja ikkunafunktioratkaisuja

Oppitunti 1/413 vaihetta

Toiseksi suurin palkka viidellä tavalla on ilmainen Valmistautuminen ohjelmointihaastatteluihin-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 Valmistautuminen ohjelmointihaastatteluihin-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.

Kysymys, jonka kaikki saavat

"Etsi toiseksi suurin palkka" on yleisin SQL-haastattelukysymys. Haastattelijat pitävät siitä, koska siihen on useita oikeita vastauksia ja useita hienovaraisia sudenkuoppia.

Oletetaan, että employee-taulukossa on sarakkeet id ja salary. Tehtävänne on palauttaa toiseksi suurin eri palkka-arvo.

Jos palkat ovat 300, 200, 200 ja 100, vastaus on 200, ei toinen rivi.
Jos toiseksi suurinta eri palkka-arvoa ei ole, odotettu vastaus on yleensä NULL.

Seuraavissa osioissa ratkaisemme tehtävän viidellä eri tavalla ja tarkastelemme, milloin kukin niistä on parhaimmillaan.

CREATE TABLE employee (
  id     INT PRIMARY KEY,
  salary INT
);

Tapa 1: MAX-arvoista, jotka ovat pienempiä kuin MAX

Kaikkein intuitiivisin ratkaisu: toiseksi suurin palkka on suurin palkka, joka on aidosti pienempi kuin kaikkien palkkojen suurin arvo.

Tämä on lähes englannin kielen kaltainen ja toimii kaikissa SQL-murteissa. Sisempi alikysely etsii suurimman arvon, ja ulompi MAX etsii sitä pienemmän suurimman arvon.

Lisähyöty: jos toiseksi suurinta eri palkka-arvoa ei ole, ulompi MAX käsittelee nolla riviä ja palauttaa automaattisesti arvon NULL. Tämä automaattisesti saatava NULL on juuri se, mitä haastattelijat haluavat.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Miksi alikysely käsittelee duplikaatit

Huomaa, että Tavassa 1 ei käytetä lainkaan DISTINCT-määrettä, mutta duplikaatit käsitellään silti oikein.

Jos kolme henkilöä ansaitsee 200 ja eniten ansaitseva 300, sisempi kysely palauttaa arvon 300. Ulompi suodatin pitää mukana kaikki alle 300 olevat rivit, ja niiden MAX on 200 riippumatta siitä, kuinka monta 200-arvoa joukossa on.

Tämä on keskeinen oivallus: aggregaatit käsittelevät duplikaatit puolestasi. Monet ehdokkaat tekevät ratkaisusta turhaan monimutkaisen käyttämällä DISTINCT-määrettä, vaikka aggregaatti tekee jo tarvittavan työn.

Tapa 2: LIMIT ja OFFSET

MySQL:ssä ja PostgreSQL:ssä eri palkka-arvot voidaan järjestää laskevaan järjestykseen ja ohittaa ensimmäinen.

OFFSET 1 ohittaa suurimman arvon.
LIMIT 1 ottaa mukaan vain seuraavan arvon.

DISTINCT on tässä välttämätön. Muuten useat samat suurimmat palkat saisivat OFFSET 1:n osumaan maksimiarvon toistoon aidon toiseksi suurimman arvon sijaan.

Ansa: jos toista eri arvoa ei ole, tämä palauttaa nolla riviä, ei arvoa NULL. Korjaamme tämän reunatapauksen oppitunnissa 4.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Tapa 3: FETCH SQL Serveriä ja Oraclea varten

SQL Server ja nykyaikaiset Oracle-versiot eivät tue syntaksia LIMIT ... OFFSET. Niissä käytetään ANSI-standardin mukaista syntaksia OFFSET ... FETCH.

Logiikka on sama kuin Tavassa 2: järjestetään eri palkat laskevaan järjestykseen, ohitetaan yksi rivi ja otetaan yksi rivi. Eri tietokantajärjestelmien syntaksin tunteminen osoittaa haastattelijalle käytännön kokemusta.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

Tapa 4: DENSE_RANK-ikkunafunktio

Nykyaikainen ja skaalautuva ratkaisu käyttää ikkunafunktiota. DENSE_RANK antaa korkeimmalle palkalle sijan 1, seuraavalle eri palkka-arvolle sijan 2 ja antaa yhtä suurille palkoille saman sijan ilman aukkoja.

Sijoitus lasketaan alikyselyssä, minkä jälkeen ulompi kysely suodatetaan sijalle 2. Muista, ettei ikkunafunktiota voi suodattaa suoraan WHERE-lausekkeessa, joten alikyselyyn kääriminen on pakollista.

SELECT salary AS second_highest
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

Miksi DENSE_RANK eikä RANK tai ROW_NUMBER

Rankkausfunktion valinta vaikuttaa siihen, miten eri arvot käsitellään:

ROW_NUMBER antaa jokaiselle riville yksilöllisen numeron. Jos kaksi henkilöä ansaitsee 300, heidän rivinsä ovat 1 ja 2, jolloin sija 2 olisi suurimman palkan toisto. Väärin.
RANK jättää tasatilanteiden jälkeen aukkoja: kaksi 300-arvoa saavat sijan 1, minkä jälkeen seuraava palkka hyppää sijalle 3. Sijalla 2 sitä ei löydy. Väärin.
DENSE_RANK antaa tasapalkoille saman sijan eikä jätä aukkoja, joten sija 2 on aina toiseksi suurin eri palkka-arvo. Oikein.

Tapa 5: korreloitu alikyselylaskenta

Klassinen ennen ikkunafunktioita käytetty tekniikka on seuraava: palkka on N:nneksi suurin, jos sitä suurempia eri palkka-arvoja on täsmälleen N − 1.

Toiseksi suurinta palkkaa varten halutaan, että sitä suurempia eri palkka-arvoja on täsmälleen yksi. Ratkaisu on elegantti, mutta suurilla tauluilla se voi olla hidas, koska sisempi laskenta suoritetaan jokaiselle ulomman kyselyn riville.

Ratkaisu yleistyy helposti N:nneksi suurimpaan arvoon muuttamalla laskennaksi N - 1, minkä vuoksi haastattelijat haluavat usein nähdä tämän tekniikan.

SELECT salary AS second_highest
FROM employee e
WHERE 1 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employee e2
  WHERE e2.salary > e.salary
);

Kokonainen esimerkki alusta loppuun

Oletetaan palkoiksi 500, 500, 350, 350, 100.

Tapa 1: MAX on 500, ja suurin arvo alle 500:n on 350. Vastaus on 350.
Tapa 4 (DENSE_RANK): 500 -> sija 1, 350 -> sija 2, 100 -> sija 3. Sijalla 2 on 350.
Tapa 5: palkkaa 350 suurempi eri palkka-arvo on täsmälleen yksi (500). Ehto täyttyy. Vastaus on 350.

Kaikki viisi menetelmää antavat saman tuloksen: toiseksi suurin eri palkka-arvo on 350, vaikka mukana on duplikaatteja.

Mikä menetelmä kannattaa valita

Haastatteluvinkit:

Esitä kysymys ensin: "Tarvitaanko eri palkka-arvot ja arvo NULL, jos sellaista ei ole?" Tarkentava kysymys antaa pisteitä.
DENSE_RANK on vahvin oletusvastaus, sillä se yleistyy siististi N:nneksi suurimpaan arvoon ja ryhmäkohtaisiin kyselyihin.
MAX alle MAXin on paras yhden rivin ratkaisu ja palauttaa automaattisesti arvon NULL.
LIMIT/OFFSET on tiivis, mutta tietokantajärjestelmäkohtainen ja palauttaa reunatapauksessa nolla riviä.

Menetelmien erojen tuominen esiin ääneen erottaa keskitason vastauksen junioritason vastauksesta.

Yleiset vältettävät virheet

Varo näitä haastattelijoiden asettamia ansoja:

ROW_NUMBER-funktion käyttäminen DENSE_RANK-funktion sijaan, jolloin suurin palkka saadaan kahdesti.
DISTINCT-määreen unohtaminen LIMIT/OFFSET-versiossa, kun suurin palkka esiintyy useita kertoja.
Oletus, että ORDER BY salary DESC LIMIT 1,1 palauttaa eri palkka-arvon. Se ei palauta.
Toisen rivin palauttaminen toisen arvon sijaan.

Pikatesti

Testaa, ymmärrätkö rankkausfunktion valinnan.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Nyt tunnet viisi tapaa löytää toiseksi suurimman palkan:

MAX alle MAXin – siirrettävä eri tietokantajärjestelmiin ja palauttaa automaattisesti arvon NULL.
LIMIT/OFFSET ja OFFSET/FETCH – tiiviitä ja tietokantajärjestelmäkohtaisia.
DENSE_RANK – skaalautuva oletusratkaisu, joka käsittelee tasapalkat oikein.
Korreloitu laskenta – elegantti ratkaisu, joka yleistyy N:nneksi suurimpaan arvoon.

Keskeiset opit: selvitä, tarvitaanko eri arvoja, suosi tasatilanteissa DENSE_RANK-funktiota ja muista, mitkä menetelmät palauttavat arvon NULL ja mitkä eivät yhtään riviä, kun toista arvoa ei ole.

Aloita maksutta

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

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Toiseksi suurin palkka viidellä tavalla” ilmainen?

Kyllä – oppitunnin ”Toiseksi suurin palkka viidellä tavalla” 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 ”Toiseksi suurin palkka viidellä tavalla”?

Vertaa alikysely-, LIMIT/OFFSET- ja ikkunafunktioratkaisuja 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 1/4.

Kuinka kauan ”Toiseksi suurin palkka viidellä tavalla”-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

Toiseksi suurin palkka viidellä tavalla
N:nneksi suurin arvo DENSE_RANK-funktiolla
Osaston suurimman ansion saaja
NULL-arvon palauttaminen, kun N:nnettä arvoa ei ole

← Takaisin: Valmistautuminen ohjelmointihaastatteluihin