SQL-työhaastatteluun valmistautuminen · Oppitunti

Kumulatiiviset summat ikkunakehyksillä

Luo juokseva summa SUM OVER -funktiolla järjestetyn kehyksen avulla

Oppitunti 1/413 vaihetta

Kumulatiiviset summat ikkunakehyksillä on ilmainen SQL-työhaastatteluun valmistautuminen-oppitunti CoddyKitissä. Tämä on oppitunti 1/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.

Kumulatiivisen summan kysymys

Lähes jokaisessa analyytikon työhaastattelussa esitetään jossain muodossa kysymys: ”Näyttäkää kumulatiivinen liikevaihto ajan mittaan.” Kumulatiivinen summa kasvaa rivi riviltä ja sisältää kaiken osion alusta nykyiseen riviin asti.

Ennen ikkunafunktioita hakijat ratkaisivat tämän hitaalla itseliitoksella tai korreloidulla alikyselyllä. Nykyaikainen ja odotettu vastaus on SUM(...) OVER (ORDER BY ...). Kehysversion tunteminen osoittaa, että ymmärrätte nykyaikaista, noin vuoden 2012 jälkeen vakiintunutta SQL:ää.

Järjestetyn ikkunasumman rakenne

Kumulatiivinen summa on yksinkertaisesti aggregaatti, joka on muutettu ikkunafunktioksi. Säilytätte SUM(amount)-funktion, mutta lisäätte siihen ORDER BY-lauseen sisältävän OVER-lauseen.

OVER-lauseen sisäinen ORDER BY tekee summasta kumulatiivisen: se käskee SQL:ää kerryttämään rivit kyseisessä järjestyksessä. Ilman ORDER BY-lauseketta SUM laskisi koko osion summan jokaiselle riville sen sijaan, että summa kasvaisi.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Miksi ORDER BY määrittää kehyksen

Tässä on yksityiskohta, jota haastattelijat mielellään testaavat: kun ikkunan aggregaattiin lisätään ORDER BY, SQL käyttää oletuskehyksenä määrittelyä RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Juuri tämä oletus tuottaa kumulatiivisen summan: mukaan otetaan jokainen rivi osion alusta nykyiseen riviin asti, nykyinen rivi mukaan lukien. Kun ymmärrätte tämän oletuksen, ymmärrätte, miksi kumulatiivinen summa toimii ”itsestään”.

Kehyksen määrittäminen eksplisiittisesti

Voitte kirjoittaa kehyksen myös itse. Nämä kaksi kyselyä palauttavat saman tuloksen, mutta eksplisiittinen versio osoittaa haastattelijalle, että tiedätte, mitä konepellin alla tapahtuu.

Määrittely ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW on turvallisin eksplisiittinen muoto kumulatiiviselle summalle, koska se laskee fyysiset rivit ja välttää RANGE-kehyksen arvoryhmittelyn aiheuttamat yllätykset, joita käsitellään seuraavassa oppitunnissa.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Käytännön esimerkki: päivittäinen myynti

Ajatellaan neljän päivän myyntiä: ma 100, ti 50, ke 200 ja to 75. Kumulatiivinen summa kasvaa vasemmalta oikealle.

Ma: 100
Ti: 100 + 50 = 150
Ke: 150 + 200 = 350
To: 350 + 75 = 425

Viimeinen rivi vastaa aina kokonaissummaa. Tämä on nopea järkevyystarkistus, jonka voitte mainita haastattelussa: kumulatiivisen summan viimeisen arvon on vastattava koko aineiston SUM(amount)-summaa.

Kumulatiivisen summan nollaus ryhmittäin PARTITION BY -lauseella

Oikeissa kysymyksissä halutaan yleensä kumulatiivinen summa asiakkaoittain tai alueittain, ei yhtä koko aineiston kattavaa summaa. Lisätkää PARTITION BY, jolloin kumulointi alkaa osion alusta uudelleen jokaisessa osiossa.

Ajattelumalli on seuraava: PARTITION BY jakaa rivit toisistaan riippumattomiin ryhmiin, ja ORDER BY sekä kehys käsitellään erikseen jokaisessa ryhmässä.

SELECT
  customer_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
  ) AS customer_running_total
FROM sales;

Tasatilanteiden ratkaisemisen ansa

Jos kahdella rivillä on sama ORDER BY-arvo (kaksi myyntiä samana päivänä), oletuskehys RANGE käsittelee niitä toisiaan vastaavina riveinä ja antaa niille saman kumulatiivisen summan, joka sisältää molemmat summat.

Jos tarvitsette tasatilanteissakin tiukasti rivi riviltä kasvavan summan, vaihtakaa kehykseksi ROWS ja lisätkää ORDER BY -lauseeseen yksilöllinen tasatilanteen ratkaisija, kuten sale_date, id. Haastattelijat lisäävät tarkoituksella päällekkäisiä päivämääriä nähdäkseen, huomaatteko tämän.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Kumulatiivinen lukumäärä

Kumulatiivinen logiikka ei rajoitu SUM-funktioon. Mikä tahansa aggregaatti toimii ikkunafunktiona, joten voitte muodostaa kumulatiivisen lukumäärän, kumulatiivisen keskiarvon tai kumulatiivisen maksimiarvon.

Tilausten kumulatiivinen lukumäärä on yleinen koontinäytön mittari: kuinka monta tilausta on vastaanotettu kunkin päivän tilanteeseen mennessä?

SELECT
  order_date,
  COUNT(*) OVER (
    ORDER BY order_date
  ) AS orders_to_date
FROM orders;

Vanha tapa: korreloitu alikysely

Haastattelijat saattavat joskus pyytää ratkaisemaan kumulatiivisen summan ilman ikkunafunktioita testatakseen osaamisen syvyyttä. Klassinen ikkunafunktioita edeltävä ratkaisu on korreloitu alikysely, joka laskee kaikki aiemmat rivit uudelleen.

Se toimii, mutta sen aikavaativuus on O(n²): jokaisella rivillä taulu käydään uudelleen läpi. Mainitkaa tämä osoittaaksenne, että ymmärrätte, miksi ikkunafunktiot korvasivat sen.

SELECT
  s.sale_date,
  s.amount,
  (SELECT SUM(s2.amount)
   FROM sales s2
   WHERE s2.sale_date <= s.sale_date) AS running_total
FROM sales s
ORDER BY s.sale_date;

Suodatus ja ikkunafunktion tulos

Yleinen jatkokysymys on: ”Näyttäkää vain päivät, joina kumulatiivinen summa ylitti 1000.” Ikkunafunktiota ei voi käyttää WHERE-lauseessa, koska kehys lasketaan vasta WHERE-lauseen suorittamisen jälkeen.

Ratkaisu on laskea kumulatiivinen summa CTE:ssä tai alikyselyssä ja suodattaa sitten ulommassa kyselyssä. Sama kyselyn sisään käärimistä koskeva sääntö pätee kaikkiin ikkunafunktioihin.

WITH t AS (
  SELECT
    sale_date,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales
)
SELECT *
FROM t
WHERE running_total >= 1000;

Haastattelun pääkohdat

Kun esitätte vastauksen kumulatiivisen summan laskemisesta, käykää läpi seuraavat kohdat saadaksenne täydet pisteet:

SUM OVER (ORDER BY ...) on kumulatiivinen muoto.
ORDER BY-lauseen lisääminen luo oletuskehyksen, joka ulottuu määrittelystä UNBOUNDED PRECEDING määrittelyyn CURRENT ROW.
Käyttäkää PARTITION BY -lausetta summan nollaamiseen ryhmittäin.
Lisätkää yksilöllinen tasatilanteen ratkaisija ja käyttäkää ROWS-kehystä päällekkäisten arvojen aiheuttaman ansan välttämiseksi.
Käärikää kysely CTE:hen, jotta voitte suodattaa tuloksen perusteella.

Pikatarkistus

Testatkaa oletuskehyksen ymmärtämistänne.

Vastaa interaktiivisessa oppitunnissa

Kertaus: kumulatiiviset summat

Kumulatiivinen summa on järjestetty ikkunan aggregaatti. SUM(amount) OVER (ORDER BY sale_date) kerryttää rivit osion alusta nykyiseen riviin asti implisiittisen UNBOUNDED PRECEDING–CURRENT ROW -kehyksen ansiosta.

Nollatkaa summa ryhmittäin käyttämällä PARTITION BY -lausetta, lisätkää tasatilanteiden käsittelyä varten tasatilanteen ratkaisija ja ROWS-kehys, ja käärikää kysely CTE:hen aina, kun kumulatiivisen arvon perusteella on suodatettava. Seuraavaksi tarkastelemme tämän oppitunnin vihjaamaa ROWS- ja RANGE-kehysten eroa.

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 ”Kumulatiiviset summat ikkunakehyksillä” ilmainen?

Kyllä – oppitunnin ”Kumulatiiviset summat ikkunakehyksillä” 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 ”Kumulatiiviset summat ikkunakehyksillä”?

Luo juokseva summa SUM OVER -funktiolla järjestetyn kehyksen avulla 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 1/4.

Kuinka kauan ”Kumulatiiviset summat ikkunakehyksillä”-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