Valmistautuminen ohjelmointihaastatteluihin · Oppitunti

SUM ja AVG NULL-arvojen kanssa

Miksi AVG ohittaa NULL-arvot ja miten tämä muuttaa haastattelijan odottamaa vastausta

Oppitunti 2/413 vaihetta

SUM ja AVG NULL-arvojen kanssa on ilmainen Valmistautuminen ohjelmointihaastatteluihin-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 Valmistautuminen ohjelmointihaastatteluihin-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.

AVG:n sudenkuoppa

Tässä on klassinen haastattelukysymys, johon huolimattomat ehdokkaat kompastuvat: "Palkkasarakkeessa on NULL-arvoja. Mitä AVG(salary) laskee, ja vastaako tulos liiketoiminnan tarpeita?"

Rehellinen vastaus paljastaa, ymmärrättekö, että aggregaattifunktiot ohittavat NULL-arvot, mikä muuttaa keskiarvon nimittäjää. Jos ymmärrätte tämän väärin tuotannossa, raportoitu keskiarvo on huomaamatta liian suuri.

Tehdään tästä toiminnasta täysin selkeä.

Esimerkkidata

Käyttäkää koko oppitunnin ajan tätä employees-taulua, jossa on NULL-arvoja salliva bonus-sarake:

Alice, bonus 100
Bob, bonus 200
Carol, bonus NULL
Dan, bonus 300

Rivejä on neljä, bonuksia kolme, joiden arvo ei ole NULL, ja yksi NULL-arvo. Suoritamme tälle datalle SUM- ja AVG-funktiot ja tarkastelemme, miten NULL-arvoa käsitellään.

SUM ohittaa NULL-arvot

SUM(bonus) laskee yhteen vain arvot, jotka eivät ole NULL: 100 + 200 + 300 = 600. NULL-rivi ei lisää summaa mitään, vaan se ohitetaan. Sitä ei varsinaisesti käsitellä nollana, koska se ei muuta laskettavien arvojen määrää.

Käytännössä NULL-arvoa käsitellään kuin arvo puuttuisi. SUM ei koskaan aiheuta virhettä NULL-arvojen vuoksi eikä palauta NULL-arvoa, elleivät kaikki syötearvot ole NULL.

SELECT SUM(bonus) AS total_bonus
FROM employees;
-- returns 600

AVG ohittaa myös NULL-arvot

AVG(bonus) on tässä ratkaiseva. Se laskee muiden kuin NULL-arvojen summan jaettuna muiden kuin NULL-arvojen määrällä: 600 / 3 = 200.

Nimittäjä on 3, ei 4. NULL-rivi jätetään pois sekä osoittajasta että jakajasta. Juuri tästä syystä AVG voi yllättää: keskiarvo lasketaan arvojen, ei kaikkien rivien perusteella.

SELECT AVG(bonus) AS avg_bonus
FROM employees;
-- 600 / 3 = 200, NOT 600 / 4 = 150

Miksi nimittäjällä on väliä

Oletetaan, että liiketoiminnassa NULL-bonus tarkoittaa "ei saanut bonusta" = 0. Tällöin todellisen keskiarvon pitäisi olla 600 / 4 = 150, mutta AVG(bonus) ilmoittaa tulokseksi 200.

Oikea haastatteluvastaus kuuluu: "AVG ohittaa NULL-arvot, joten se laskee keskiarvon niiden työntekijöiden perusteella, joilla on bonus. Jos NULL tarkoittaa nollaa, minun on ensin muutettava NULL-arvot nolliksi." Tämän eron nimeäminen on se, jolla saatte pisteen.

NULL-arvojen muuttaminen nolliksi COALESCE-funktiolla

Kun haluatte laskea keskiarvon kaikista riveistä ja käsitellä NULL-arvot nollina, ympäröikää sarake lausekkeella COALESCE(bonus, 0). Nyt jokaisella rivillä on numeerinen arvo, joten nimittäjäksi tulee 4.

Tulokseksi saadaan 600 / 4 = 150. Opetus: AVG(col) ja AVG(COALESCE(col, 0)) vastaavat eri liiketoimintakysymyksiin. Valitkaa käyttötapa tarkoituksella.

SELECT AVG(COALESCE(bonus, 0)) AS avg_over_all
FROM employees;
-- 600 / 4 = 150

AVG = SUM / COUNT – huomioitavaa

Hyödyllinen yhtälö on: AVG(col) on sama kuin SUM(col) / COUNT(col) — huomioikaa, että kyseessä on COUNT(col), ei COUNT(*), koska sekä AVG että tämä COUNT ohittavat NULL-arvot.

Jos kirjoitatte vahingossa SUM(col) / COUNT(*), saatte kaikkien rivien perusteella lasketun keskiarvon (tässä 150), joka poikkeaa AVG-funktion tuloksesta (200). Haastattelijat pyytävät joskus muodostamaan AVG-funktion itse nähdäkseen, valitsetteko oikean COUNT-funktion.

SELECT
  AVG(bonus)                       AS builtin_avg,   -- 200
  SUM(bonus) * 1.0 / COUNT(bonus)  AS manual_avg,    -- 200
  SUM(bonus) * 1.0 / COUNT(*)      AS over_all_rows  -- 150
FROM employees;

Kokonaislukujakoon liittyvä sudenkuoppa

Keskiarvojen manuaaliseen laskemiseen liittyy hienovarainen virhe: monissa tietokannoissa kahden kokonaisluvun jakaminen käyttää kokonaislukujakoa, joka katkaisee desimaaliosan. 7 / 2 voi tuottaa tulokseksi 3, ei 3,5.

AVG palauttaa yleensä desimaaliarvon, mutta jos muodostatte sen uudelleen käyttämällä SUM / COUNT-lauseketta kokonaislukusarakkeissa, voitte menettää tarkkuutta. Kertokaa arvo ensin luvulla 1.0 tai muuntakaa se ensin desimaalimuotoon.

SELECT
  SUM(bonus) / COUNT(bonus)        AS maybe_truncated,
  SUM(bonus) * 1.0 / COUNT(bonus)  AS precise
FROM employees;

Kun kaikki on NULL

Haastattelijoiden suosima reunatapaus: mitä tapahtuu, jos jokainen arvo on NULL tai suodatin ei löydä yhtäkään riviä?

SUM palauttaa NULL-arvon, ei nollaa, kun syötteissä ei ole yhtään muuta kuin NULL-arvoa.
AVG palauttaa myös NULL-arvon, koska nollalla jakaminen ei ole määriteltyä.
COUNT puolestaan palauttaa arvon 0.

Ympäröikää tulos lausekkeella COALESCE(SUM(col), 0), jos tarvitsette numeerisen oletusarvon.

SELECT COALESCE(SUM(bonus), 0) AS safe_total
FROM employees
WHERE 1 = 0;  -- no rows: returns 0, not NULL

Ryhmäkohtaiset keskiarvot

Samat NULL-säännöt pätevät myös GROUP BY-lausekkeen sisällä. Kunkin ryhmän AVG-funktio jakaa summan kyseisen ryhmän muiden kuin NULL-arvojen määrällä. Jos ryhmän kaikki bonusarvot ovat NULL, sen AVG-arvo on NULL.

Jos siis osastokohtaiset keskiarvot näyttävät yllättäviltä, epäilkää ensin NULL-arvoja, jotka pienentävät ryhmäkohtaisia nimittäjiä, ennen kuin epäilette liitosvirhettä.

SELECT department, AVG(bonus) AS avg_bonus
FROM employees
GROUP BY department;

Miten vastaus muotoillaan

Huoliteltu haastatteluvastaus voisi kuulua näin: "SUM ja AVG ohittavat molemmat NULL-arvot. AVG jakaa summan muiden kuin NULL-arvojen määrällä, joten NULL-arvot pienentävät käytännössä nimittäjää. Jos NULL-arvon pitäisi tarkoittaa nollaa, muutan sen COALESCE-funktiolla ennen aggregointia; muuten keskiarvo kuvaa vain rivejä, joilla on arvo."

Yksi lause osoittaa, että vastauksenne on oikea, huomioi liiketoiminnan tarpeet ja sisältää myös ratkaisun.

Pikatarkistus

Soveltakaa sääntöä esimerkkidataan.

Vastaa interaktiivisessa oppitunnissa

Kertaus

SUM- ja AVG-funktioiden tärkeimmät opit NULL-arvojen yhteydessä:

Kumpikin ohittaa NULL-arvot kokonaan.
AVG(col) = SUM(col) / COUNT(col) — nimittäjä ei sisällä NULL-arvoja.
Käyttäkää COALESCE(col, 0) -lauseketta, kun NULL tarkoittaa nollaa ja sen halutaan vaikuttavan laskentaan.
Kun kaikki syötteet ovat NULL-arvoja tai rivejä ei ole, SUM ja AVG palauttavat NULL-arvon (COUNT palauttaa 0).
Varokaa kokonaislukujakoa, kun muodostatte AVG-funktion itse.

Seuraavaksi käsittelemme MIN- ja MAX-funktioita sekä muiden kuin numeeristen tietojen aggregointia.

Aloita maksutta

Opi Valmistautuminen ohjelmointihaastatteluihin 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: 90
Oppitunnit: 360

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”SUM ja AVG NULL-arvojen kanssa” ilmainen?

Kyllä – oppitunnin ”SUM ja AVG NULL-arvojen kanssa” 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 Valmistautuminen ohjelmointihaastatteluihin-kurssin, päivitä CoddyKit PROhon. Valmistautuminen ohjelmointihaastatteluihin-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”SUM ja AVG NULL-arvojen kanssa”?

Miksi AVG ohittaa NULL-arvot ja miten tämä muuttaa haastattelijan odottamaa vastausta Harjoittelet Valmistautuminen ohjelmointihaastatteluihin-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni Valmistautuminen ohjelmointihaastatteluihin-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin Valmistautuminen ohjelmointihaastatteluihin-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 2/4.

Kuinka kauan ”SUM ja AVG NULL-arvojen kanssa”-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ä Valmistautuminen ohjelmointihaastatteluihin-oppitunnilla?

Kyllä. Jokainen Valmistautuminen ohjelmointihaastatteluihin-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

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)
SUM ja AVG NULL-arvojen kanssa
MIN, MAX ja ei-numeeristen arvojen yhdistäminen
Koostefunktiot ilman GROUP BY -lauseketta

← Takaisin: Valmistautuminen ohjelmointihaastatteluihin