SQL-työhaastatteluun valmistautuminen · Oppitunti

Jaksot päivämäärä- ja tilamuutosten avulla

Ryhmittele peräkkäiset saman tilan jaksot, kuten tilauspalvelun tiloja koskevassa yleisessä tehtävässä

Oppitunti 4/413 vaihetta

Jaksot päivämäärä- ja tilamuutosten avulla 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.

Muuttuvan arvon määrittämät saarekkeet

Liiketoiminnan kannalta merkityksellisin aukkojen ja saarekkeiden muunnelma ryhmittelee peräkkäiset rivit, joilla on sama tila, ja tiivistää meluisan tapahtumalokin selkeiksi tilajaksoiksi. Klassinen tehtävänanto kuuluu: "Palauta tilauslokista yksi rivi jokaista jatkuvaa ajanjaksoa kohden, jonka käyttäjä pysyi samassa tilassa."

Tässä vierekkäisyys ei tarkoita, että "arvot eroavat yhdellä". Se tarkoittaa, että tila ei ole muuttunut edellisestä rivistä. Uusi saareke alkaa heti, kun tila vaihtuu. Tässä LAG-pohjainen tekniikka on puhdasta rivinumeron niksaa parempi.

Tilausesimerkki

Tarkastellaan yhden käyttäjän päivämäärän mukaan järjestettyä sub_events-taulua:

2026-01-01 active
2026-02-01 active
2026-03-01 paused
2026-04-01 active
2026-05-01 active

Haluttu tulos on kolme tilajaksoa: active tammi–helmi, paused maalis, active huhti–touko. Huomaa, että kaksi active-jaksoa ovat erillisiä saarekkeita, koska paused-jakso katkaisee ne. Sama tila mutta epäperäkkäiset rivit tarkoittavat eri saarekkeita.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

Tilan vaihtumisen merkitseminen

Vertaa LAG-funktion avulla kunkin rivin tilaa edellisen rivin tilaan. Kun tilat eroavat (tai edellinen arvo on ensimmäisellä rivillä NULL), alkaa uusi saareke. Tuota muutokselle arvo 1 ja muulloin arvo 0.

Järjestä rivit käyttäjän sisällä yksiselitteisesti päivämäärän mukaan. Aineistomme muutosliput ovat 1,0,1,1,0, jotka merkitsevät kolmen jakson rajat.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Kumulatiivinen summa jaksotunnukseksi

Kuten aiemmin, muutoslippujen kumulatiivinen summa tuottaa ryhmäavaimen, joka on vakio jokaisessa tilajaksossa: riveillemme arvot ovat 1,1,2,3,3. Jokainen yksilöllinen avain vastaa yhtä jatkuvaa jaksoa.

Rivinumeron erotusmenetelmä ei toimi tässä, koska tila ei ole luvuksi koodattu arvo, joka kasvaa yhdellä. LAG:n ja kumulatiivisen summan yhdistelmä on oikea työkalu, kun vierekkäisyys tarkoittaa "sama arvo".

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Tilajaksojen yhdistäminen

Ryhmittele nyt user_id-sarakkeen, status-sarakkeen ja kumulatiivisen summan avaimen mukaan ilmoittaaksesi kunkin jakson aikavälin. status-sarakkeen sisällyttäminen GROUP BY-lausekkeeseen on turvallista, koska sen arvo on jakson sisällä vakio, ja sen ansiosta voit valita sen ilman aggregaattifunktiota.

Tuloksena on täsmälleen kolme riviä: active 01-01–02-01, paused 03-01–03-01 ja active 04-01–05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

Tapahtumista puoliavoimiin väleihin

Hienovarainen mutta tärkeä haastatteluhuomio: tapahtumapäivä ilmaisee, milloin tila alkoi, ja jakso päättyy tosiasiassa seuraavan tilan alkaessa, ei viimeisen saman tilan tapahtumapäivänä. Oikea jakson loppu on usein seuraavan jakson alku, mikä mallinnetaan puoliavoimena välinä [start, next_start).

Laske seuraavan jakson alku LEAD-funktiolla tiivistetyistä jaksoista ja jätä viimeinen jakso avoimeksi (NULL tai 'current').

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Peräkkäisten samojen tilojen käsittely

Entä jos lokissa on tarpeettomia rivejä, kuten active, active, active, eikä niiden välillä tapahdu muutosta? Toistojen muutoslippu on 0, joten juokseva summa pitää ne automaattisesti samassa saarekkeessa. Tämä on haluttu toimintatapa: peräkkäiset samanlaiset tilat tiivistyvät yhdeksi jaksoksi.

Toistojen luonnollinen yhdistyminen on muutoslippumenetelmän keskeinen etu, ja siitä kannattaa mainita haastattelijalle.

Milloin ajan aukkojen tulee katkaista jakso

Joskus pelkkä sama tila ei riitä, vaan suuri aukko ajassa katkaisee jakson, vaikka tila pysyisi samana. Esimerkiksi tammikuun active ja uudelleen kuuden kuukauden hiljaisuuden jälkeen ilmenevä active voidaan tulkita kahdeksi eri jaksoksi.

Laajenna muutoslippua toisella ehdolla: aloita uusi saareke, kun tila muuttuu tai edellisestä tapahtumasta kulunut aika ylittää raja-arvon. Näin molemmat vierekkäisyyssäännöt yhdistyvät siististi.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Erillisten tilanvaihtojen laskeminen

Luonteva jatkokysymys kuuluu: “Kuinka monta kertaa tämä käyttäjä vaihtoi tilaa?” Se on yksinkertaisesti muutoslippujen määrästä vähennettynä ensimmäinen lippu, joka merkitsee alkutilaa eikä tilanvaihtoa.

Toisin sanoen tulos on jaksojen määrä miinus 1. Juoksevan summan avain sisältää tämän tiedon jo valmiiksi, joten vastaus saadaan samalla menetelmällä, jolla muodostit jaksot.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Miksi itseliitokset eivät tässä toimi yhtä hyvin

Itseliitoksella toteutettu tilajaksojen ratkaisu joutuisi yhdistämään jokaisen rivin naapuriinsa, tunnistamaan muutokset ja kokoamaan rajat yhteen. Tämä on virhealtis monivaiheinen prosessi, joka vaikeutuu huomattavasti kolmen tai useamman jakson tapauksessa.

LAG-flag-runningsum-groupby-putki käsittelee minkä tahansa määrän jaksoja yhdellä läpikäynnillä ilman liitoksia. Tämän eron sanoittaminen — lineaarinen yhden läpikäynnin ratkaisu verrattuna neliölliseen itseliitokseen — on juuri sellaista senioritason päättelyä, jota haastattelijat arvostavat.

Uudelleenkäytettävä mallipohja

Opettele tämä neliosainen mallipohja ulkoa; se ratkaisee koko status-island-tehtävien perheen vaihtamalla vain CASE-lausekkeen vierekkäisyystestin:

flag: CASE ja LAG uuden saarekkeen tunnistamiseen.
key: lipun juokseva SUM osioituna ja järjestettynä.
collapse: GROUP BY osiosarakkeen, tilan ja avaimen perusteella.
interval (valinnainen): LEAD puoliavoimien jakson loppujen laskemiseen.

Sama runko toimii peräkkäisille kokonaisluvuille, päivämäärille ja tiloille; vain CASE-ehto vaihtuu.

Pikatarkistus

Varmistakaa, että ymmärrätte status-island-ryhmittelysäännön.

Vastaa interaktiivisessa oppitunnissa

Kertaus: tila- ja päivämääräsaarekkeet

Osaatte nyt ratkaista monipuolisimman aukkojen ja saarekkeiden muunnelman:

Vierekkäisyys tarkoittaa, että tila ei muutu edelliseen riviin verrattuna; lippu muuttuu LAG-funktion avulla.
Muodostakaa muutoslipuista juoksevan summan avulla jaksoittainen ryhmäavain.
Tiivistäkää tiedot komennolla GROUP BY user_id, status, key saadaksenne jaksojen aikavälit.
Käyttäkää LEAD-funktiota puoliavoimien välien loppuihin ja laajentakaa lippua katkaisemaan jakso myös suurten ajan aukkojen kohdalla.
Toistuvat samanlaiset rivit tiivistyvät automaattisesti; tilanvaihtojen määrät saadaan samoista lipuista.
Yksi uudelleenkäytettävä mallipohja kattaa kokonaisluvut, päivämäärät ja tilat; vain CASE muuttuu.

Tähän päättyy aukkojen ja saarekkeiden kurssi, joka on luotettava senioritason osaamisen osoitus SQL-haastatteluissa.

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 ”Jaksot päivämäärä- ja tilamuutosten avulla” ilmainen?

Kyllä – oppitunnin ”Jaksot päivämäärä- ja tilamuutosten avulla” 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 ”Jaksot päivämäärä- ja tilamuutosten avulla”?

Ryhmittele peräkkäiset saman tilan jaksot, kuten tilauspalvelun tiloja koskevassa yleisessä tehtävässä 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 ”Jaksot päivämäärä- ja tilamuutosten avulla”-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