SQL-työhaastatteluun valmistautuminen · Oppitunti

Osaston suurimman ansion saaja

Q: Onko oppitunti ”Osaston suurimman ansion saaja” ilmainen?

Kyllä – oppitunnin ”Osaston suurimman ansion saaja” 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.

Q: 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 3/4.

Q: Kuinka kauan ”Osaston suurimman ansion saaja”-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.

Q: 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.

Yhdistä osiointi ja sijoittaminen ryhmiteltyjen Top-N-palkkaongelmien ratkaisemiseksi

Oppitunti 3/413 vaihetta

Osaston suurimman ansion saaja on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 3/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.

Globaalista ryhmäkohtaiseen rankkaukseen

Seuraava haastetaso on: "Etsi parhaiten palkattu työntekijä kustakin osastosta." Tässä yhdistyvät rankkaus ja ryhmittely, ja kyseessä on varma keskitason haastattelukysymys.

Oletetaan, että taulussa employee ovat sarakkeet id, name, department_id ja salary. Tavoitteena on löytää kustakin osastosta yksi parhaiten ansaitseva työntekijä tai tasatilanteessa useampi, ei vain koko aineiston suurinta palkkaa.

Keskeinen uusi työkalu on PARTITION BY, joka aloittaa rankkauksen uudelleen jokaisessa osastossa.

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BY aloittaa rankkauksen uudelleen

Kun ikkunaan lisätään PARTITION BY department_id, tietokanta laskee rankkauksen toisistaan riippumatta jokaisessa osastossa.

Jokainen osasto aloittaa omalta sijalta 1. Siksi osaston 1 parhaiten ansaitseva ja osaston 5 parhaiten ansaitseva saavat molemmat sijan 1. Ilman osiointia vain koko aineiston suurin palkka saisi sijan 1.

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

Suodatus sijalle 1

Jos halutaan pitää mukana vain parhaiten ansaitsevat työntekijät, rankattu kysely kääritään alikyselyksi ja suodatetaan sijalle 1. Kuten aina, ikkunafunktio on laskettava alikyselyssä tai CTE:ssä ennen kuin sitä voidaan käyttää suodatuksessa.

DENSE_RANK- tai RANK-funktion käyttäminen tässä tarkoittaa, että jos kaksi työntekijää saa osastossa saman korkeimman palkan, molemmat palautetaan. Tämä on yleensä oikea tulkinta ilmaukselle "parhaiten ansaitseva työntekijä".

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk = 1;

ROW_NUMBER, kun halutaan täsmälleen yksi

Joskus haastattelija haluaa täsmälleen yhden rivin osastoa kohden, vaikka palkoissa olisi tasatulos. Käyttäkää silloin ROW_NUMBER-funktiota ja lisätkää deterministinen tasatilanteen ratkaisija, kuten pienin id.

Ilman tasatilanteen ratkaisijaa tasatilanteet ratkaistaan mielivaltaisesti, joten tulos ei ole deterministinen. Lisäämällä , id ASC valinta toistuu aina samalla tavalla.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

DENSE_RANK vs. ROW_NUMBER vs. RANK tässä tilanteessa

Valinta tehdään tehtävänannon tarkan sanamuodon perusteella:

DENSE_RANK = 1: kaikki työntekijät, jotka jakavat osastonsa korkeimman palkan.
RANK = 1: sama kuin DENSE_RANK korkeimman sijoituksen osalta (aukot merkitsevät vain sijoituksen 1 alapuolella).
ROW_NUMBER = 1: täsmälleen yksi työntekijä osastoa kohden; tasatilanteet ratkaistaan määrittämällänne ORDER BY -järjestyksellä.

Haastattelijat arvioivat erityisesti sitä, kerrotteko, minkä vaihtoehdon valitsitte ja miksi.

Ikkunafunktioita edeltänyt korreloitu menetelmä

Ennen ikkunafunktioita tavanomainen ratkaisu oli korreloitu alikysely: säilytetään rivi vain, jos kukaan samassa osastossa ei ansaitse enempää.

Tämä palauttaa luontevasti kaikki korkeimman palkan jakavat työntekijät. Ratkaisu on siirrettävä eri tietokantajärjestelmiin, mutta se voi olla hidas, koska sisempi MAX arvioidaan jokaiselle ulomman kyselyn riville, ellei optimoija muokkaa kyselyä.

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

GROUP BY -liitosmenetelmä

Toinen helposti siirrettävä menetelmä on laskea osastokohtainen enimmäispalkka GROUP BY-lauseella ja liittää sitten taulu takaisin saadaksenne vastaavat työntekijät.

Menetelmä on tehokas ja selkeä. Liitos palauttaa kaikki työntekijät, joiden palkka on sama kuin osaston enimmäispalkka, joten tasatilanteet säilyvät.

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

Osastokohtaiset N parasta

Menetelmä laajenee kyselyksi "osaston 3 parhaiten palkattua työntekijää" ilman uusia ideoita. Muuttakaa vain suodatin käsittelemään väliä.

DENSE_RANK-funktion kanssa rnk <= 3 palauttaa kolme korkeinta toisistaan poikkeavaa palkkatasoa, joten tasatilanteissa rivejä voi tulla enemmän kuin kolme. ROW_NUMBER-funktion kanssa rn <= 3 palauttaa täsmälleen kolme riviä osastoa kohden.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

Esimerkki

Osasto 1: Ana 120, Bob 120, Cara 90. Osasto 2: Dan 200, Eve 150.

DENSE_RANK = 1: osastolta 1 Ana (120) ja Bob (120); osastolta 2 Dan (200). Yhteensä kolme riviä.
ROW_NUMBER = 1 with id tiebreaker: toinen henkilöistä Ana/Bob (se, jolla on pienempi id) sekä Dan. Yhteensä kaksi riviä.

Sama data tuottaa eri määrän rivejä käytetystä funktiosta riippuen. Valitkaa ratkaisu tehtävänannon mukaan.

Osastojen sisällyttäminen ja nimien liittäminen

Haastattelijat lisäävät usein department-taulun ja pyytävät mukaan osaston nimen. Liittäkää se vasta rankkauksen jälkeen.

Tehkää rankkaus employee-taulussa ja liittäkää hakutaulu lopuksi, jotta osiointi tapahtuu edelleen oikealla tarkkuustasolla.

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

Vältettävät sudenkuopat

Yleisiä osastokohtaisen rankkauksen virheitä:

PARTITION BY unohtuu ja ranking tehdään maailmanlaajuisesti, jolloin palautetaan vain koko yrityksen parhaiten ansaitseva työntekijä.
ROW_NUMBER-funktiota käytetään, vaikka tehtävänanto edellyttää kaikkien tasatulosten näyttämistä, jolloin saman kärkisijan jakavat työntekijät putoavat huomaamatta pois.
Ikkunafunktiota yritetään käyttää suoraan WHERE-lauseessa sen sijaan, että kysely käärittäisiin ulompaan kyselyyn.
department-taulu liitetään ennen rankkausta, jolloin osioinnin tarkkuustaso muuttuu vahingossa.

Pikatarkistus

Valitkaa vaatimukseen sopiva rankkausfunktio.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Osastokohtaisesti parhaiten ansaitseva työntekijä saadaan käyttämällä maailmanlaajuista rankkausmallia sekä PARTITION BY department_id -määrettä:

DENSE_RANK = 1 palauttaa kaikki osaston korkeimman palkan jakavat työntekijät.
ROW_NUMBER = 1 ja tasatilanteen ratkaisija palauttavat täsmälleen yhden työntekijän osastoa kohden.
Siirrettäviä vaihtoehtoja ovat osastokohtainen korreloitu MAX tai GROUP BY-lauseella laskettu enimmäispalkka, joka liitetään takaisin tauluun.

Laajentakaa ratkaisu N parhaan tuloksen hakuun muuttamalla = 1 muotoon <= N. Kertokaa ääneen, miten käsittelette tasatilanteet.

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 ”Osaston suurimman ansion saaja” ilmainen?

Kyllä – oppitunnin ”Osaston suurimman ansion saaja” 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 ”Osaston suurimman ansion saaja”?

Yhdistä osiointi ja sijoittaminen ryhmiteltyjen Top-N-palkkaongelmien ratkaisemiseksi 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 3/4.

Kuinka kauan ”Osaston suurimman ansion saaja”-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