SQL-työhaastatteluun valmistautuminen · Oppitunti

Avaimen viimeisimmän rivin säilyttäminen

Malli, jossa kullekin asiakkaalle haetaan viimeisin tietue osioimalla avaimen mukaan ja järjestämällä päivämäärän mukaan

Oppitunti 4/413 vaihetta

Avaimen viimeisimmän rivin säilyttäminen on ilmainen SQL-työhaastatteluun valmistautuminen-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 SQL-työhaastatteluun valmistautuminen-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.

Kysymys: uusin rivi avainta kohti

"Palauttakaa kunkin asiakkaan uusin tilaus." "Hakekaa jokaisen laitteen viimeisin tila." Tämä uusin rivi avainta kohti -ongelma on yksi SQL-haastatteluiden yleisimmistä tehtävistä, koska se tulee jatkuvasti vastaan oikeassa analytiikkatyössä.

Kyseessä on erityinen top-1-ryhmäkohtainen ongelma: osioikaa rivit avaimen perusteella, järjestäkää aikaleiman mukaan laskevasti ja säilyttäkää ensimmäinen rivi. Tässä oppitunnissa harjoitellaan tätä mallia ja sen vaihtoehtoja.

Miksi MAX ei yksin riitä

Houkutteleva ensimmäinen vastaus on MAX(order_date) ryhmiteltynä asiakkaan mukaan. Se antaa viimeisimmän päivämäärän, mutta ei kyseisen tilauksen muita tietoja, kuten tilauksen tunnistetta, summaa tai tilaa.

Jos haastattelija haluaa koko uusimman rivin, MAX yhdessä GROUP BY -lauseen kanssa vaatii ylimääräisen liitoksen takaisin taulukkoon avaimen ja suurimman päivämäärän perusteella. Ratkaisu on pitkä ja voi epäonnistua tasatilanteissa. Ikkunafunktiot ovat selkeämpiä.

-- Gives the date, not the full row
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;

ROW_NUMBER-malli

Osioikaa rivit avaimen perusteella, järjestäkää ne aikaleiman mukaan laskevasti, niin uusin rivi saa arvon rn = 1. Säilyttäkää vain nämä rivit, ja saatte koko uusimman tietueen kutakin avainta kohti.

Tämä on ensisijainen vastaus. Se palauttaa täsmälleen yhden rivin avainta kohti myös silloin, kun aikaleimat ovat samat, mikä yleensä sisältyy ilmaukseen "uusin rivi".

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1;

Aikaleimojen tasatilanteiden ratkaiseminen

Kahdella saman asiakkaan tilauksella voi olla sama order_date (sama päivä tai täsmälleen sama aikaleima). Ilman tasatilanteen ratkaisevaa avainta ei ole määritelty, kumpi saa arvon rn = 1, ja valinta voi muuttua suorituskerroista toiseen.

Lisätkää yksilöllinen toissijainen avain, kuten order_id DESC, jotta uusin rivi määräytyy yksiselitteisesti. Haastattelijat selvittävät erityisesti, huomasitteko tämän reunatapauksen.

ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_date DESC, order_id DESC
) AS rn

Uusin rivi vai kaikki tasatulokset

Päättäkää, mitä "uusin" tarkoittaa, kun aikaleimat ovat samat:

Jos haluatte täsmälleen yhden rivin avainta kohti → käyttäkää ROW_NUMBER-funktiota ja tasatilanteen ratkaisevaa avainta.
Jos haluatte kaikki rivit, joilla on sama suurin aikaleima → käyttäkää sen sijaan ehtoa RANK() = 1, joka palauttaa kaikki uusimmat tasatuloksen saaneet rivit.

Tämän tarkentavan kysymyksen esittäminen osoittaa, että ymmärrätte merkityksen ettekä vain syntaksia.

WITH ranked AS (
  SELECT *,
    RANK() OVER (
      PARTITION BY customer_id ORDER BY order_date DESC
    ) AS rnk
  FROM orders
)
SELECT * FROM ranked WHERE rnk = 1;

Korreloitu alikysely vaihtoehtona

Ennen kuin ikkunafunktiot olivat yleisesti käytettävissä, uusin rivi avainta kohti haettiin korreloidulla alikyselyllä: rivi säilytetään vain, jos samalla avaimella ei ole toista riviä, jolla on suurempi päivämäärä.

Ratkaisu toimii, mutta sisempi kysely suoritetaan jokaiselle riville, joten suurissa taulukoissa se on hitaampi ja tasatilanteiden käsittely on hankalaa. Mainitkaa se osoittaaksenne osaamisenne laajuuden, mutta suosikaa suorituskyvyn vuoksi ikkunafunktioratkaisua.

SELECT o.*
FROM orders o
WHERE o.order_date = (
  SELECT MAX(o2.order_date)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

Postgresin DISTINCT ON -oikotie

PostgreSQL tarjoaa tiiviin idiomin: DISTINCT ON (key) säilyttää kunkin avaimen ensimmäisen rivin ORDER BY -lauseen määrittämän järjestyksen perusteella. ORDER BY -lauseen on aloitettava samoilla avainsarakkeilla ja jatkuttava sitten tasatilanteen ratkaisevalla sarakkeella tai aikaleimalla.

Ratkaisu on PostgreSQL:ssä elegantti ja nopea, mutta ei siirrettävä. Mainitkaa se murrekohtaisena lisävaihtoehtona ja pitäkää ROW_NUMBER siirrettävänä oletusratkaisuna.

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, order_date, amount
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

Ehdollinen uusin rivi

Oikeissa kysymyksissä on usein suodattimia: "kunkin asiakkaan viimeisin valmistunut tilaus". Käyttäkää suodatinta ennen rankingia, jotta vain ehdon täyttävät rivit numeroidaan.

Sijoittakaa ehto sisemmän kyselyn WHERE-lauseeseen, koska se suoritetaan ennen ikkunafunktiota, ja valitkaa ulommassa kyselyssä rn = 1. Suodattaminen rankingin jälkeen palauttaisi väärän rivin.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
  WHERE status = 'completed'
)
SELECT * FROM ranked WHERE rn = 1;

Esimerkki: laitteen viimeisin tila

status_log-taulukkoon tallennetaan device_id, status ja logged_at. Hakeaksenne kunkin laitteen nykyisen tilan osioikaa rivit device_id:n perusteella, järjestäkää ne logged_at DESC -järjestyksessä ja säilyttäkää rn = 1.

Tämä on perusta koontinäytöille, jotka näyttävät monien kohteiden "nykyisen tilan" vain lisäyksiä sisältävästä tapahtumalokista. Sama malli toimii myös viimeisimmän hinnan, sijainnin ja version hakemiseen.

WITH latest AS (
  SELECT device_id, status, logged_at,
    ROW_NUMBER() OVER (
      PARTITION BY device_id ORDER BY logged_at DESC
    ) AS rn
  FROM status_log
)
SELECT device_id, status, logged_at
FROM latest
WHERE rn = 1;

Suorituskykyä koskevia huomioita

Seuraavat näkökohdat osoittavat edistyneen osaamisen:

Indeksi muodossa (customer_id, order_date DESC) auttaa tietokantamoottoria lukemaan kunkin avaimen uusimman rivin tehokkaasti.
Ikkunaratkaisu käy taulukon läpi kerran, mutta korreloitu alikysely ei.
PostgreSQL:n DISTINCT ON voi käyttää samaa indeksiä ja on usein nopein yhden taulukon vaihtoehto.
Paljon lisäyksiä sisältävissä tapahtumalokeissa kannattaa harkita aineistotaulukkoa, johon "uusin" tila päivitetään asteittain.

Yleiset virheet

Varokaa näitä:

MAX(date) -funktion käyttäminen niin, että palautetaan vain päivämäärä eikä koko riviä.
Tasatilanteen ratkaisevan avaimen unohtaminen, jolloin tulos ei ole yksiselitteinen päivämäärien ollessa samoja.
Ehdon käyttäminen suodattamiseen ranking-funktion jälkeen, jolloin voidaan valita rivi, joka olisi pitänyt sulkea pois.
"Yksi uusin rivi" -tapauksen (ROW_NUMBER) sekoittaminen "kaikki uusimmat tasatuloksen saaneet rivit" -tapaukseen (RANK).

Pikatarkistus

Valitkaa oikea kysely, joka palauttaa uusimman rivin avainta kohti.

Vastaa interaktiivisessa oppitunnissa

Kertaus: Uusin rivi avainta kohti

Malli: PARTITION BY key, ORDER BY timestamp DESC (sekä yksilöllinen tasatilanteen ratkaiseva avain), säilytä rn = 1.

MAX(date) antaa päivämäärän, ei koko riviä.
Lisätkää aina tasatilanteen ratkaiseva avain, jotta tulos määräytyy yksiselitteisesti.
Käyttäkää ehtoa RANK() = 1, jos haluatte kaikki viimeisimmän aikaleiman tasatuloksen saaneet rivit.
Suodatusehdot kuuluvat sisempään kyselyyn ennen rankingia.
Postgresin DISTINCT ON on tiivis ja nopea murrekohtainen vaihtoehto.

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 ”Avaimen viimeisimmän rivin säilyttäminen” ilmainen?

Kyllä – oppitunnin ”Avaimen viimeisimmän rivin säilyttäminen” 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 ”Avaimen viimeisimmän rivin säilyttäminen”?

Malli, jossa kullekin asiakkaalle haetaan viimeisin tietue osioimalla avaimen mukaan ja järjestämällä päivämäärän mukaan 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 4/4.

Kuinka kauan ”Avaimen viimeisimmän rivin säilyttäminen”-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

Ryhmän Top-N-rivit ROW_NUMBER-funktiolla
Tasatilanteiden käsittely Top-N-tuloksissa
Rivien turvallinen deduplikointi
Avaimen viimeisimmän rivin säilyttäminen

← Takaisin: SQL-työhaastatteluun valmistautuminen