SQL-työhaastatteluun valmistautuminen · Oppitunti

N:nneksi suurin arvo DENSE_RANK-funktiolla

Yleistää ratkaisu N:nneksi suurimpaan erilliseen arvoon ja käsittelee kaksoiskappaleet

Oppitunti 2/413 vaihetta

N:nneksi suurin arvo DENSE_RANK-funktiolla 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.

Yleistys N:nneksi suurimpaan arvoon

Kun toiseksi suurin palkka osataan löytää, haastattelija kysyy heti: "Entä N:nneksi suurin?" Selkein ja parhaiten perusteltava vastaus käyttää DENSE_RANK-funktiota.

Kaava on aina sama: järjestetään eri palkka-arvot laskevaan järjestykseen rankkaamalla ja suodatetaan sitten rivi, jonka sijoitus on N. Koska logiikka ei muutu N:n mukana, yksi ratkaisu vastaa koko tähän kysymysjoukkoon.

Rakennamme ratkaisun vaiheittain, käsittelemme tasatilanteet ja duplikaatit sekä tarkastelemme, miksi DENSE_RANK on oikea rankkausfunktio "eri arvo" -semantiikkaan.

Perusmalli

Tässä on uudelleenkäytettävä pohja N:nneksi suurimman arvon hakemiseen. Korvaa vakio sillä N:n arvolla, jota haastattelija pyytää.

DENSE_RANK lasketaan sisemmässä kyselyssä, koska ikkunafunktio ei voi olla WHERE-lausekkeessa. Sen jälkeen ulkopuolella suodatetaan ehdolla rnk = N. Kolmanneksi suurinta palkkaa varten suodattimeksi asetetaan rnk = 3.

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

Miten DENSE_RANK numeroi eri arvot

DENSE_RANK antaa yhtä suurille arvoille saman sijan eikä jätä sen jälkeen aukkoja. Tämä vastaa täsmälleen haastatteluissa tarkoitettua "N:nneksi suurinta eri arvoa".

Kun palkat ovat 800, 800, 600, 600, 400:

800 -> sija 1
600 -> sija 2
400 -> sija 3

Kolmanneksi suurin arvo on siis 400, vaikka rivejä on viisi. Duplikaatit yhdistyvät automaattisesti yhdeksi sijaksi.

Miksi RANK antaa väärän vastauksen

Kun tilalle vaihdetaan RANK, vastaus menee rikki. RANK jättää aukkoja tasatilanteiden lukumäärän perusteella.

Kun palkat ovat 800, 800, 600, 600, 400:

800, 800 -> sija 1 (molemmat)
600, 600 -> sija 3 (aukko, sijaa 2 ei ole)
400 -> sija 5

Suodatus ehdolla rnk = 3 palauttaa arvon 600, mutta rnk = 2 ei palauta mitään. Ellei haastattelija nimenomaisesti halua kilpailusijoitusta, DENSE_RANK on oikea valinta "N:nneksi suurimmalle eri palkka-arvolle".

Miksi ROW_NUMBER on myös tässä väärä

ROW_NUMBER antaa jokaiselle riville yksilöllisen numeron ja ohittaa tasatilanteet kokonaan. Kun palkat ovat 800, 800, 600, 600, 400, tuloksena ovat numerot 1, 2, 3, 4, 5.

Siksi rn = 3 palauttaa arvon 600, mutta rn = 2 palauttaa duplikaatin 800 eikä toiseksi suurinta eri arvoa. ROW_NUMBER vastaa kysymykseen "N:nnes rivi", ei kysymykseen "N:nnes eri arvo".

Käytä ROW_NUMBER-funktiota vain, kun kysymys todella koskee tiettyä riviä, esimerkiksi duplikaattien poistossa tai ryhmäkohtaisessa top-N-kyselyssä, jossa säilytetään täsmälleen yksi rivi.

SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employee;

N:n turvallinen parametrisoiminen

Oikeassa koodissa sijoitusta ei määritettäisi kiinteästi. N välitetään parametrina, johon sijoitusta verrataan. Ikkunan määrittely pysyy samana; vain ulompi suodatin tehdään parametrisoitavaksi.

Tässä voidaan myös palauttaa kaikki sijalla N olevat tasapalkat. Koska DENSE_RANK antaa saman sijan tasapalkoille, WHERE rnk = N voi palauttaa useita rivejä, jos useilla työntekijöillä on sama N:nneksi suurin eri palkka-arvo. Tämä on usein toivottu toimintatapa.

SELECT id, salary
FROM (
  SELECT id, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = :n;

Korreloidun laskennan yleistys

Ikkunafunktioita edeltävä lähestymistapa yleistyy myös: palkka on N:nneksi suurin eri palkka-arvo, kun sitä suurempia eri palkka-arvoja on täsmälleen N - 1.

Kolmanneksi suurinta varten edellytetään täsmälleen kahta sitä suurempaa eri palkka-arvoa. Tämä toimii vanhemmissa tietokantajärjestelmissä, joissa ei ole ikkunafunktioita, mutta skaalautuu huonosti, koska sisempi laskenta suoritetaan uudelleen jokaiselle ulomman kyselyn riville.

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

MySQL-funktiomuoto, jota haastatteluissa kysytään

LeetCode-tyylisessä "N:nneksi suurin palkka" -tehtävässä pyydetään usein tallennettua funktiota, joka palauttaa yhden arvon. Funktion runko on yksinkertaisesti DENSE_RANK-pohja, joka on kääritty palauttamaan yksi palkka.

Tarkkaa funktiosyntaksia ei tarvitse opetella ulkoa haastattelua varten, mutta on hyvä tuntea, että eri palkka-arvoihin kohdistuva LIMIT N-1, 1 on MySQL:n tiivis idiomi.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 2;  -- N = 3, so OFFSET N-1

Esimerkki: neljänneksi suurin palkka

Palkat: 1000, 900, 900, 700, 500, 500, 300.

Eri palkat laskevassa järjestyksessä DENSE_RANK-funktion avulla:

1000 -> sija 1
900 -> sija 2
700 -> sija 3
500 -> sija 4
300 -> sija 5

Neljänneksi suurin palkka on 500. Huomaa, että molemmilla 500-arvoilla on sija 4, joten suodatus ehdolla rnk = 4 palauttaa molemmat 500 ansaitsevat työntekijät, jos myös heidän tunnisteensa valitaan.

Suorituskykyä koskevia huomioita

Miten lähestymistavat vertautuvat suurilla aineistoilla?

DENSE_RANK: yksi järjestämisvaihe aineistolle ja sen jälkeen suodatus. Tehokas ratkaisu, jossa kyselysuunnittelija voi hyödyntää salary-sarakkeen indeksiä järjestämiseen.
Korreloitu laskenta: mahdollisesti O(n²), koska sisempi aggregaatti suoritetaan jokaiselle riville. Vältä tätä suurissa tauluissa.
LIMIT/OFFSET: nopea pienillä N-arvoilla, mutta joutuu silti järjestämään aineiston, ja suuret offset-arvot käyvät läpi ja hylkäävät monia rivejä.

Kun aloitat DENSE_RANK-funktiosta, valinta osuu harvoin väärään.

Mainitsemisen arvoiset reunatapaukset

Vahvat ehdokkaat tuovat reunatapaukset esiin jo ennen kuin niistä kysytään:

N on suurempi kuin eri palkka-arvojen määrä: suodatin ei täsmää yhteenkään riviin, joten tulos on tyhjä. Oppitunnissa 4 käsitellään, miten palautetaan pakotetusti yksi NULL-arvo.
Tasatilanteet sijalla N: DENSE_RANK palauttaa kaikki samalla sijalla olevat työntekijät. Päätä, onko tämä haluttu toimintatapa.
N = 1: pohja toimii edelleen ja palauttaa maksimiarvon.

Pikatesti

Sovella N:nneksi suurimman arvon hakemiseen tarkoitettua pohjaa.

Vastaa interaktiivisessa oppitunnissa

Kertaus

N:nneksi suurimman palkan hakemiseen on yksi ensisijainen ratkaisu: rankkaa eri palkat alikyselyssä komennolla DENSE_RANK() OVER (ORDER BY salary DESC) ja suodata sitten ehdolla WHERE rnk = N.

DENSE_RANK tarkoittaa "N:nneksi suurinta eri arvoa": tasapalkoilla on sama sija eikä aukkoja synny.
RANK synnyttää aukkoja; ROW_NUMBER laskee rivejä eikä arvoja.
Korreloitu laskenta = N-1 yleistää saman idean ilman ikkunafunktioita, mutta skaalautuu huonosti.

Mainitse aina reunatapaus, jossa "N ylittää käytettävissä olevien arvojen määrän". Ratkaisemme sen seuraavaksi.

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 ”N:nneksi suurin arvo DENSE_RANK-funktiolla” ilmainen?

Kyllä – oppitunnin ”N:nneksi suurin arvo DENSE_RANK-funktiolla” 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 ”N:nneksi suurin arvo DENSE_RANK-funktiolla”?

Yleistää ratkaisu N:nneksi suurimpaan erilliseen arvoon ja käsittelee kaksoiskappaleet 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 ”N:nneksi suurin arvo DENSE_RANK-funktiolla”-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

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: SQL-työhaastatteluun valmistautuminen