SQL-työhaastatteluun valmistautuminen · Oppitunti

Rivinumeron erotustemppu

Vähennä ROW_NUMBER sarjasta ryhmitelläksesi peräkkäiset arvot jaksoiksi

Oppitunti 2/413 vaihetta

Rivinumeron erotustemppu 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.

Tyylikkäin saarekeavain

Rivinumeroiden erotustekniikka on menetelmä, jonka haastattelijat haluavat useimmiten nähdä kokonaislukujen tai päivämäärien peräkkäisille jaksoille. Se tuottaa ryhmittelyavaimen yhdellä vähennyslaskulla ilman LAG-funktiota ja kumulatiivista summaa.

Ajatus on yksinkertainen: vähennetään arvosta ROW_NUMBER. Jokaisessa peräkkäisten arvojen jaksossa sekä arvo että rivinumero kasvavat joka askeleella täsmälleen yhdellä, joten niiden erotus pysyy vakiona koko jakson ajan. Tämä vakio on saarekkeen avain.

Miksi erotus pysyy vakiona

Ajatellaan kahta peräkkäisen jakson vierekkäistä riviä. Siirryttäessä riviltä seuraavalle arvo kasvaa yhdellä ja rivinumero kasvaa yhdellä. Kun ne vähennetään toisistaan, +1:t kumoavat toisensa, joten value - row_number ei muutu.

Kun vastaan tulee aukko, arvo kuitenkin kasvaa enemmän kuin yhdellä, vaikka rivinumero kasvaa edelleen vain yhdellä. Erotus siirtyy uuteen vakioarvoon. Juuri tämä muutos erottaa yhden saarekkeen seuraavasta.

Tarkastelu omilla tiedoillamme

Palautetaan mieleen kirjautumispäivät 1, 2, 3, 7, 8, 10. Asetetaan rivinumero ja erotus rinnakkain:

päivä 1, rn 1, erotus 0
päivä 2, rn 2, erotus 0
päivä 3, rn 3, erotus 0
päivä 7, rn 4, erotus 3
päivä 8, rn 5, erotus 3
päivä 10, rn 6, erotus 4

Erotukset (0,0,0,3,3,4) jakavat rivit täydellisesti kolmeksi saarekkeeksi. Sama erotus tarkoittaa samaa saareketta.

SELECT
  day_no,
  ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;

Saarekkeiksi kokoaminen

Kun erotus on ryhmittelyavain, lopullinen kysely on vakiomuotoinen kokoaminen. Käärikää erotus CTE:hen ja tehkää siitä GROUP BY:

Tämä palauttaa samat kolme saareketta kuin aiemmin, mutta SQL on lyhyempi ja selkeämpi kuin LAG- ja kumulatiivisen summan versio. Kokonaislukujen tai tasavälisten sarjojen tapauksessa tähän ratkaisuun kannattaa tarttua ensimmäisenä.

WITH keyed AS (
  SELECT
    day_no,
    day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
  FROM logins
)
SELECT
  MIN(day_no) AS start_day,
  MAX(day_no) AS end_day,
  COUNT(*)    AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;

Kompastuskivi: arvojen on edettävä yhden askeleen verran

Yksinkertainen erotusmenetelmä olettaa, että jono kasvaa täsmälleen yhdellä jokaisella askeleella. Tämä pätee tiheisiin kokonaislukuihin ja peräkkäisiin kalenteripäiviin, mutta menetelmä ei toimi, jos arvot kasvavat jollakin muulla kiinteällä määrällä tai jos arvoissa on duplikaatteja.

Parilliset arvot 2,4,6,8 näyttävät aukollisilta, kun arvoista vähennetään rivinumero.
Duplikaatit sekoittavat kohdistuksen, koska rivinumero kasvaa jatkuvasti mutta arvo ei.

Tämän rajoituksen ja sen korjaamisen ymmärtäminen erottaa ulkoa opetellun niksin todellisesta ymmärryksestä.

Kiinteäaskelisten jonojen korjaaminen

Jos arvot kasvavat tunnetulla vakiolla k yhden sijaan, normalisoi ne ensin: jaa arvo k:lla (tai käytä kokonaisluvuille value / k), jolloin jokainen askel on jälleen yksi, ja vähennä sitten rivinumero.

Esimerkiksi kahdella kasvaville parillisille luvuille käytä lauseketta day_no / 2 - ROW_NUMBER(). Normalisoitu arvo kasvaa nyt yhdellä jokaisella peräkkäisellä alkiolla, mikä palauttaa vakioerotuksen ominaisuuden.

SELECT
  val,
  (val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;

Soveltaminen päivämääriin

Päivämäärät ovat yleisin käytännön esimerkki. Kalenteripäivämääristä ei voi vähentää rivinumeroa suoraan, joten muunna päivämäärä ensin päivien lukumääräksi. Postgresissa vähennä päivämäärästä kiinteä ankkuripäivämäärä saadaksesi kokonaislukuna ilmaistun päivien määrän ja sovella sitten samaa niksiä.

Koska peräkkäisten kalenteripäivien ero on yksi, päivien lukumäärän ja rivinumeron erotus on jälleen vakio saman saarekkeen sisällä.

WITH keyed AS (
  SELECT
    login_date,
    (login_date - DATE '2000-01-01')
      - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
  FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*)        AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;

Päivämäärien erotus eri SQL-murteissa

Päivämäärän muuntaminen kokonaisluvuksi vaihtelee tietokantamoottorin mukaan, ja haastattelijat arvostavat eri murteiden tuntemusta:

Postgres: vähennä päivämäärävakio: login_date - DATE '2000-01-01' tuottaa kokonaisluvun.
MySQL: käytä funktiota DATEDIFF(login_date, '2000-01-01').
SQL Server: käytä funktiota DATEDIFF(day, '2000-01-01', login_date).

Joissakin moottoreissa vielä sujuvampi tapa on vähentää päivämäärästä suoraan ROW_NUMBER päivää intervallilaskennalla ja ryhmitellä tuloksena saadun ankkuripäivämäärän mukaan.

SELECT
  login_date,
  login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
               * INTERVAL '1 day') AS grp_date
FROM daily_logins;

Ryhmäkohtaisten ositusten lisääminen

Käyttäjäkohtaisia saarekkeita varten osita rivinumero ryhmäsarakkeen mukaan. Olennaista on, että ryhmittelyavaimeen on tämän jälkeen sisällytettävä myös ositussarake, koska kaksi eri käyttäjää voi sattumalta tuottaa saman erotusarvon.

Ryhmittele siis sekä user_id-sarakkeen että lasketun erotuksen mukaan. Jos user_id unohtuu lopullisesta GROUP BY-lausekkeesta, syntyy hienovarainen virhe, jonka haastattelijat mielellään huomaavat.

WITH keyed AS (
  SELECT user_id, day_no,
    day_no - ROW_NUMBER()
      OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;

Niksi vai LAG: kumpaa kannattaa käyttää

Työkalupakissasi on nyt kaksi toimivaa tekniikkaa. Valitse harkiten:

Rivinumeron erotus: lyhyin ja selkein vaihtoehto tasavälein etenevien arvojen jaksoille (tiheät kokonaisluvut, peräkkäiset päivämäärät). Ensisijainen valinta, kun vierekkäisyys tarkoittaa, että "ero on vakio".
LAG ja kumulatiivinen summa: joustavampi, kun vierekkäisyys ei perustu kiinteään numeeriseen askeleeseen, esimerkiksi kun "tila on sama kuin edellisellä rivillä" tai käytössä on epäsäännöllisiä mukautettuja sääntöjä.

Kerro haastattelussa, minkä vaihtoehdon valitsit ja miksi; perustelut tekevät paremman vaikutuksen kuin syntaksi.

Duplikaattien varovainen käsittely

Jos arvo voi toistua ja haluat silti yhden saarekkeen jokaista peräkkäistä jaksoa kohden, poista duplikaatit ensin komennolla DISTINCT tai ryhmittelyvaiheella, jotta rivinumero kohdistuu yksi yhteen arvojen kanssa. Vaihtoehtoisesti käytä DENSE_RANK-funktiota ROW_NUMBER-funktion sijaan, jolloin samat arvot saavat saman järjestysluvun.

Kysy aina haastattelijalta, voiko duplikaatteja esiintyä. Oikea varautumistapa riippuu siitä, tuleeko duplikaattien jatkaa jaksoa vai jättääkö ne sen sisällä huomiotta.

WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;

Pikatarkistus

Varmista, että ymmärrät, miksi tämä niksi toimii.

Vastaa interaktiivisessa oppitunnissa

Kertaus: erotusmenetelmä

Sinulla on nyt käytössäsi selkein saarekeavain:

Avainkaava: value - ROW_NUMBER() OVER (ORDER BY value) on vakio jokaisessa peräkkäisessä jaksossa.
Yhdistä rivit GROUP BY-lausekkeella erotuksen perusteella saadaksesi alkuarvon, loppuarvon ja pituuden.
Kiinteäaskelisissa jonoissa normalisoi arvot ensin (jaa ne askeleen pituudella).
Päivämäärät muunnetaan kokonaisluvuksi eli päivien lukumääräksi kyseisen SQL-murteen erotusfunktion avulla.
Ryhmäkohtaisesti: käytä rivinumerossa PARTITION BY-lausetta ja sisällytä ryhmäsarake lopulliseen GROUP BY-lausekkeeseen.
Varautu duplikaatteihin käyttämällä DISTINCT- tai DENSE_RANK-ratkaisua.

Seuraavaksi käännämme huomion saarekkeista tyhjiin kohtiin eli aukkojen etsimiseen.

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 ”Rivinumeron erotustemppu” ilmainen?

Kyllä – oppitunnin ”Rivinumeron erotustemppu” 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 ”Rivinumeron erotustemppu”?

Vähennä ROW_NUMBER sarjasta ryhmitelläksesi peräkkäiset arvot jaksoiksi 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 ”Rivinumeron erotustemppu”-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

Gaps-and-Islands-ongelman tunnistaminen
Rivinumeron erotustemppu
Sarjan aukkojen etsiminen
Jaksot päivämäärä- ja tilamuutosten avulla

← Takaisin: SQL-työhaastatteluun valmistautuminen