SQL-työhaastatteluun valmistautuminen · Oppitunti

Liukuvat keskiarvot liukuvalla ikkunalla

Laske N jakson liukuvat keskiarvot BETWEEN PRECEDING AND CURRENT ROW -määrittelyllä

Oppitunti 3/413 vaihetta

Liukuvat keskiarvot liukuvalla ikkunalla 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.

Liukuvan keskiarvon kysymys

Analyytikoilta kysytään tätä jatkuvasti: "Laske liukuva 7 päivän keskiarvo liikevaihdosta." Liukuva keskiarvo tasoittaa päivittäisten tietojen kohinaa laskemalla kunkin pisteen ja sen viimeaikaisten naapurien keskiarvon.

Haastattelutasoinen vastaus on ikkuna-AVG, jossa käytetään täsmällisesti määriteltyä liukuvaa kehystä. Keskeinen taito on valita kehyksen rajat niin, että ikkuna liikkuu järjestyksessä oikein.

Perusmalli

Liukuva keskiarvo on muotoa AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW). Kehys liukuu: kullakin rivillä se kattaa nykyisen rivin ja sitä edeltävät n riviä.

Päivittäisille riveille muodostettavassa 7 päivän ikkunassa siirrytään 6 riviä taaksepäin ja otetaan mukaan nykyinen rivi, jolloin rivejä on yhteensä 7. Tämä yhden rivin virhe on yleisin haastatteluissa havaittu virhe.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

Ikkunan koon oikea laskeminen

Ikkunan koko on preceding + 1 (luku 1 tarkoittaa nykyistä riviä). Siis:

3 rivin ikkuna: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
7 rivin ikkuna: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
30 rivin ikkuna: ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

Sanokaa tämä laskutoimitus haastattelussa ääneen, jotta haastattelijat näkevät toimivanne harkitusti ettekä arvaile.

Keskitetyt ja taaksepäin katsovat ikkunat

Edellinen esimerkki on taaksepäin katsova keskiarvo: se tarkastelee vain aiempia rivejä, joten se on kausaalinen ja turvallinen ennustamisen koontinäytöissä.

Keskitetty keskiarvo tarkastelee molempia suuntia, esimerkiksi ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING muodostaa 7 rivin keskitetyn ikkunan. Keskitetyt ikkunat tasoittavat tietoja symmetrisemmin, mutta niitä ei voi laskea reaaliajassa viimeisimmille riveille. Mainitkaa, kumpaa käyttötapaus edellyttää.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7
FROM daily_sales;

Ikkunan reunan vaikutus

Aivan tietojen alussa koko ikkunaa ei ole vielä olemassa. Seitsemän päivän taaksepäin katsovan keskiarvon ensimmäisellä rivillä käytettävissä on vain yksi rivi, joten AVG laskee keskiarvon vain tästä yhdestä arvosta.

Tämän vuoksi ensimmäisten rivien keskiarvo lasketaan niin sanotun "lämpenemisvaiheen" aikana harvemmista riveistä. Haastattelijat kysyvät, miten tämä käsitellään. Vaihtoehtoja on kaksi: hyväksykää osittainen ikkuna tai piilottakaa ensimmäiset rivit edellyttämällä täyttä lukumäärää.

Osittaisten ikkunoiden piilottaminen

Jos liiketoiminta haluaa arvon NULL, kunnes koko ikkuna on käytettävissä, käyttäkää COUNT(*)-funktiota saman kehyksen päällä ja jättäkää lyhyet ikkunat tyhjiksi CASE-lausekkeella.

Tämä on huolellinen yksityiskohta, joka osoittaa teidän pohtineen rajojen oikeellisuutta — seikka, jonka monet hakijat ohittavat.

SELECT
  sale_date,
  CASE WHEN COUNT(*) OVER w = 7
       THEN AVG(amount) OVER w
  END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Kehyksen uudelleenkäyttö WINDOW-lausekkeella

Edellisessä kyselyssä käytettiin nimettyä WINDOW-lauseketta. Kun sama kehys esiintyy useita kertoja, sen määrittäminen kerran muodossa WINDOW w AS (...) ja viittaaminen siihen muodossa OVER w pitää kyselyn toisteettomana ja luettavana.

Postgres, MySQL 8 ja SQL Server tukevat nimettyjä ikkunoita. Sellaisen käyttäminen haastattelussa osoittaa, että hallitsette ikkunafunktiot pelkkää kopiointia paremmin.

SELECT
  sale_date,
  AVG(amount) OVER w AS avg_7d,
  SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Kalenteripäivien ja rivien sudenkuoppa

Hienovarainen sudenkuoppa: jos taulukosta puuttuu päiviä, ROWS 6 PRECEDING kattaa viimeiset 7 tallennettua riviä, jotka voivat ulottua yli 7 kalenteripäivän ajalle.

Aidon kalenteriin perustuvan, aukot huomioivan 7 päivän keskiarvon laskemiseen käyttäkää siirtymällä varustettua RANGE-kehystä tai liittäkää taulukko ensin täydelliseen päivämääräsarjaan, jotta jokaiselle päivälle on rivi. Tämän eron mainitseminen on juuri edellisen oppitunnin ROWS- ja RANGE-kehysten välinen oivallus.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS calendar_avg_7d
FROM daily_sales;

Ryhmäkohtaiset liukuvat keskiarvot

Kuten kumulatiivisten summien tapauksessa, myös liukuvien keskiarvojen on yleensä nollauduttava kunkin entiteetin kohdalla. Lisätkää PARTITION BY, jotta jokainen tuote tai myymälä saa oman liukuvan ikkunansa, joka ei sekoitu muiden ryhmien tietoihin.

Kehys ja järjestys toimivat itsenäisesti kussakin osiossa, joten jokaisen ryhmän ensimmäiset rivit aloittavat oman lämpenemisvaiheensa oikein.

SELECT
  product_id,
  sale_date,
  AVG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS product_ma_7d
FROM daily_sales;

Tasoittaminen käytännössä

Miksi tätä tarvitaan? Päivittäinen liikevaihto vaihtelee jyrkästi, mutta liukuva keskiarvo paljastaa trendin. Yleinen analyytikon vastaus näyttää raaka-arvon ja tasoitetun käyrän rinnakkain, jotta koontinäytössä näkyvät sekä signaali että kohina.

Voitte myös verrata lyhyttä ja pitkää liukuvaa keskiarvoa (esimerkiksi 7 päivän ja 30 päivän keskiarvoja) havaitaksenne suunnan muutoksia — tämä vastaa SQL:ssä liukuvien keskiarvojen risteytystä.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma_7,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;

Haastattelun tarkistuslista

Vastataksenne liukuvia keskiarvoja koskevaan kysymykseen kattavasti mainitkaa:

AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW).
Ikkunan koko = preceding + 1.
Valinta taaksepäin katsovan ja keskitetyn ikkunan välillä.
Osittainen lämpenemisvaiheen ikkuna ja sen piilottaminen.
ROWS- ja RANGE-kehysten ero, kun päiviä puuttuu.
PARTITION BY nollaamaan ikkuna kunkin entiteetin kohdalla.

Pikatarkistus

Valitkaa kehys päivittäisille riveille laskettavaa 7 päivän taaksepäin katsovaa liukuvaa keskiarvoa varten.

Vastaa interaktiivisessa oppitunnissa

Yhteenveto: liukuvat keskiarvot

Liukuva keskiarvo on muotoa AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW), jossa ikkunan koko on edeltävien rivien määrä plus yksi. Valitkaa taaksepäin katsova tai keskitetty ikkuna käyttötapauksen mukaan, käsitelkää lämpenemisvaiheen rivit COUNT-tarkistuksella ja vaihtakaa RANGE-väliin, kun kalenterin aukot ovat merkityksellisiä.

Nollatkaa ikkuna entiteetin mukaan PARTITION BY-lausekkeella ja käyttäkää kehyksiä uudelleen nimetyn WINDOW-lausekkeen avulla. Seuraavaksi muutamme kumulatiiviset summat prosenteiksi laskemalla kumulatiivisen osuuden kokonaismäärästä.

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 ”Liukuvat keskiarvot liukuvalla ikkunalla” ilmainen?

Kyllä – oppitunnin ”Liukuvat keskiarvot liukuvalla ikkunalla” 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 ”Liukuvat keskiarvot liukuvalla ikkunalla”?

Laske N jakson liukuvat keskiarvot BETWEEN PRECEDING AND CURRENT ROW -määrittelyllä 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 ”Liukuvat keskiarvot liukuvalla ikkunalla”-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

Kumulatiiviset summat ikkunakehyksillä
ROWS- ja RANGE-kehykset
Liukuvat keskiarvot liukuvalla ikkunalla
Kumulatiivinen jakauma ja osuus kokonaismäärästä

← Takaisin: SQL-työhaastatteluun valmistautuminen