SQL-työhaastatteluun valmistautuminen · Oppitunti

Kumulatiivinen jakauma ja osuus kokonaismäärästä

Laske juoksevat prosenttiosuudet ja kokonaisosuudet osioiden sisällä

Oppitunti 4/413 vaihetta

Kumulatiivinen jakauma ja osuus kokonaismäärästä 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.

Prosenttiosuus kokonaismäärästä

Raportointiin liittyvä vakiokysymys haastatteluissa: "Minkä prosenttiosuuden kokonaisliikevaihdosta kukin luokka muodostaa?" sekä sen kumulatiivinen vastine "Mikä on kumulatiivinen osuus kokonaismäärästä?"

Temppu on jakaa kunkin rivin arvo koko osion kattavalla ikkuna-aggregaatilla. Olennaista on ymmärtää, että kokonaismäärän voi laskea ikkunafunktiolla ilman itseliitosta.

Window-SUM ilman ORDER BY -lauseketta = kokonaismäärä

Tässä on keskeinen ratkaisu: SUM(amount) OVER (), jossa OVER on tyhjä eikä mukana ole ORDER BY -lauseketta, palauttaa koko tulosjoukon summan jokaisella rivillä.

Koska ORDER BY puuttuu, kyseessä ei ole kumulatiivinen kehys, joten oletuskehys on koko osio. Jokaisella rivillä oleva sama kokonaismäärä on juuri se nimittäjä, jota tarvitsette prosenttiosuuden laskemiseen kokonaismäärästä.

SELECT
  category,
  amount,
  SUM(amount) OVER () AS grand_total
FROM category_sales;

Prosenttiosuuden laskeminen kokonaismäärästä

Jakakaa rivin arvo ikkunalla lasketulla kokonaismäärällä ja kertokaa tulos sadalla. Muuntakaa arvo desimaaliksi, jotta kokonaislukujako ei katkaise desimaaleja nollaksi.

Tämä yhden käsittelyn kysely korvaa vanhan mallin, jossa kokonaismäärä laskettiin alikyselyssä ja liitettiin takaisin tietoriveihin. Se on lyhyempi, nopeampi ja selkeämpi.

SELECT
  category,
  amount,
  ROUND(
    100.0 * amount / SUM(amount) OVER (),
    2
  ) AS pct_of_total
FROM category_sales;

Kokonaislukujakolaskun sudenkuoppa

Klassinen haastattelun kompakysymys: monissa tietokannoissa kokonaislukusarakkeiden amount / total suorittaa kokonaislukujen jaon, jolloin yhtä pienempi tulos muuttuu nollaksi.

Korjatkaa tämä kertomalla ensin 100.0:lla (numeerisella vakiolla) tai muuntamalla toinen operandiksi: amount::numeric / total. Jos tämä unohtuu, tuloksena on pelkkiä nollia sisältävä sarake, minkä haastattelijat huomaavat heti.

SELECT
  category,
  amount * 1.0 / SUM(amount) OVER () AS share,
  CAST(amount AS DECIMAL) / SUM(amount) OVER () AS share_alt
FROM category_sales;

Prosenttiosuus ryhmän kokonaismäärästä

Lisätkää PARTITION BY, jotta kunkin rivin osuus lasketaan suhteessa sen ryhmään eikä koko taulukkoon. Esimerkiksi kunkin tuotteen prosenttiosuus oman alueensa myynnistä.

Nimittäjä SUM(amount) OVER (PARTITION BY region) nollautuu nyt alueittain, joten kunkin alueen prosenttiosuuksien summa on 100.

SELECT
  region,
  product,
  amount,
  ROUND(
    100.0 * amount / SUM(amount) OVER (PARTITION BY region),
    2
  ) AS pct_of_region
FROM regional_sales;

Kumulatiivinen prosenttiosuus kokonaismäärästä

Yhdistämällä kumulatiivisen osoittajan kiinteään nimittäjään saadaan kumulatiivinen osuus kokonaismäärästä: kuinka suuri osa kokonaismäärästä on kertynyt kullakin rivillä.

Osoittajassa käytetään ORDER BY-lauseketta (kumulatiivinen laskenta), nimittäjässä tyhjää OVER ()-lauseketta (kokonaismäärä). Viimeinen rivi saavuttaa aina 100 prosentin osuuden.

SELECT
  sale_date,
  amount,
  ROUND(
    100.0 * SUM(amount) OVER (ORDER BY sale_date)
          / SUM(amount) OVER (),
    2
  ) AS running_pct
FROM daily_sales;

CUME_DIST: kumulatiivinen jakauma

SQL:ssä on sisäänrakennettu funktio kumulatiivisen jakauman laskemiseen: CUME_DIST(). Se palauttaa niiden rivien osuuden, joiden ORDER BY -arvo on pienempi tai yhtä suuri kuin nykyisen rivin arvo; tulos on välillä (0, 1].

Toisin kuin summan manuaalinen kumulatiivinen osuus, CUME_DIST kuvaa rivin sijaintia ja vastaa kysymykseen "kuinka suuri osuus riveistä sijoittuu tämän arvon kohdalle tai sen alapuolelle?" Se on hyödyllinen prosenttipisteitä muistuttavassa raportoinnissa.

SELECT
  score,
  CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM exam_results;

PERCENT_RANK ja sen ero

Läheinen vastine on PERCENT_RANK(), joka määritellään muodossa (rank - 1) / (total_rows - 1) ja jonka arvot ovat välillä 0–1.

Haastattelussa olennainen ero on tämä: CUME_DIST sisällyttää nykyisen rivin osoittajaan ("kohdalla tai sen alapuolella"), kun taas PERCENT_RANK on suhteellinen sijoitus, joka alkaa ensimmäisen rivin arvosta 0. Ne tuottavat eri arvoja, ja niiden sekoittaminen on yleinen lipsahdus.

SELECT
  score,
  CUME_DIST()    OVER (ORDER BY score) AS cd,
  PERCENT_RANK() OVER (ORDER BY score) AS pr
FROM exam_results;

Pareto- eli 80/20-analyysi

Kumulatiivinen prosenttiosuus kokonaismäärästä mahdollistaa Pareto-analyysin: "mitkä tärkeimmät asiakkaat muodostavat 80 prosenttia liikevaihdosta?" Järjestäkää rivit summan mukaan laskevaan järjestykseen, laskekaa kumulatiivinen osuus ja suodattakaa sitten kohdat, joissa kumulatiivinen osuus ylittää ensimmäisen kerran 80 prosenttia.

Koska ikkunafunktioiden tuloksia ei voi käyttää WHERE-lausekkeessa, sijoittakaa laskenta CTE:hen ja tehkää suodatus ulommassa kyselyssä — sama sääntö pätee kaikkiin ikkunafunktioihin.

WITH ranked AS (
  SELECT
    customer_id,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue DESC)
      / SUM(revenue) OVER () AS running_share
  FROM customer_revenue
)
SELECT *
FROM ranked
WHERE running_share <= 0.80;

Pyöristys ja täsmäytys

Huomioikaa, että kunkin prosenttiosuuden pyöristäminen kahteen desimaaliin voi saada sarakkeen summaksi 99.99 tai 100.01 täsmälleen 100:n sijaan. Haastattelija voi kysyä, miten varmistatte, että osat summautuvat kokonaisuudeksi.

Yleisiä vastauksia ovat, että pyöristätte vain esitystä varten, säilytätte täyden tarkkuuden laskentaa varten tai teette suurimman jäännöksen menetelmään perustuvan korjauksen yhdelle riville. Ongelman nimeäminen on tärkeämpää kuin korjaus.

Haastattelun tärkeimmät kohdat

Tärkeimmät asiat, jotka kannattaa sanoa ääneen:

SUM(x) OVER () ilman ORDER BY:tä = jokaisella rivillä näkyvä kokonaissumma.
Kertokaa arvo luvulla 100.0 kokonaislukujakolaskun välttämiseksi.
PARTITION BY ryhmäkohtaisten osuuksien laskemiseen.
Kumulatiivinen osoittaja jaettuna kokonaissummalla = kumulatiivinen osuus.
CUME_DIST ja PERCENT_RANK jakauman tarkasteluun; tietäkää, miten ne eroavat toisistaan.
Käyttäkää CTE:tä Pareto- tai kynnysarvosuodatukseen.

Pikatarkistus

Miten saatte koko tulosjoukon kokonaissumman jokaiselle riville?

Vastaa interaktiivisessa oppitunnissa

Kertaus: jakauma ja prosenttiosuus kokonaismäärästä

Prosenttiosuus kokonaismäärästä lasketaan jakamalla rivin arvo luvulla SUM(x) OVER (), joka palauttaa kokonaissumman jokaiselle riville. Kertokaa arvo aina luvulla 100.0 kokonaislukujakolaskun välttämiseksi ja lisätkää PARTITION BY ryhmäkohtaisten osuuksien laskemiseen. Kumulatiivinen osoittaja jaettuna kokonaissummalla tuottaa kumulatiivisen osuuden, joka päättyy 100 prosenttiin ja muodostaa Pareto-analyysin perustan.

Sijaintiin perustuvaa jakaumaa varten käyttäkää funktioita CUME_DIST ja PERCENT_RANK ja muistakaa pyöristykseen ja täsmäytykseen liittyvä huomautus. Tähän päättyvät kumulatiiviset summat ja liukuvat keskiarvot.

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 ”Kumulatiivinen jakauma ja osuus kokonaismäärästä” ilmainen?

Kyllä – oppitunnin ”Kumulatiivinen jakauma ja osuus kokonaismäärästä” 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 ”Kumulatiivinen jakauma ja osuus kokonaismäärästä”?

Laske juoksevat prosenttiosuudet ja kokonaisosuudet osioiden sisällä 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 ”Kumulatiivinen jakauma ja osuus kokonaismäärästä”-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