SQL-työhaastatteluun valmistautuminen · Oppitunti

Pivotointi ehdollisella aggregoinnilla

Siirrettävä CASE-inside-SUM-malli rivien muuntamiseen sarakkeiksi.

Oppitunti 1/413 vaihetta

Pivotointi ehdollisella aggregoinnilla 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.

Haastattelun lähtöasetelma

Yksi yleisimmistä raportointiin liittyvistä haastattelutehtävistä on: muuta rivit sarakkeiksi. Käytettävissä on pitkä taulu, kuten sales(region, quarter, amount), ja haastattelija haluaa leveän raportin, jossa on yksi sarake neljännestä kohti.

Siirrettävä ja SQL-murteesta riippumaton vastaus, jonka haastattelija haluaa kuulla, on ehdollinen aggregointi: CASE-lauseke sijoitettuna aggregointifunktion, kuten SUM-funktion, sisään. Kun hallitsette tämän, osaatte tehdä pivotoinnin missä tahansa tietokannassa, myös sellaisissa, joissa ei ole PIVOT-avainsanaa.

Pitkä ja leveä esitysmuoto

Ennen pivotointia on hyvä nimetä esitysmuodot. Pitkä esitysmuoto tallentaa yhden faktan riville: jokainen alueen ja vuosineljänneksen yhdistelmä on oma rivinsä. Leveä esitysmuoto levittää luokan sarakkeisiin.

Pitkä: helppo lisätä, vaikea lukea rinnakkain.
Leveä: erinomainen ihmislukijalle tarkoitetussa raportissa.

Pivotointi muuntaa pitkän esitysmuodon leveäksi. Haastattelijat pitävät tästä, koska se testaa aggregoinnin ymmärtämistä eikä vain syntaksin muistamista.

-- Long form (the input)
region | quarter | amount
-------+---------+-------
East   | Q1      | 100
East   | Q2      | 150
West   | Q1      | 200
West   | Q2      | 250

Perusmalli

Temppu on seuraava: kirjoittakaa jokaista tulossaraketta varten CASE-lauseke, joka palauttaa arvon, kun rivi vastaa kyseistä saraketta, ja muuten NULL-arvon. Käärikää se aggregointifunktion sisään, jotta ryhmä tiivistyy yhdeksi riviksi avainta kohti.

Tämän voi lukea näin: summaa amount-arvo, mutta vain Q1-riveiltä. Koska SUM ohittaa NULL-arvot, ehtoa vastaamattomat rivit eivät vaikuta summaan.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

Miksi SUM ohittaa NULL-arvot

Tämä malli toimii yhden asian vuoksi, jota haastattelijat usein testaavat: aggregointifunktiot ohittavat NULL-arvot. Ilman ELSE-haaraa oleva CASE palauttaa NULL-arvon, kun mikään haara ei täsmää, joten SUM(CASE WHEN ... THEN amount END) lisää vain valitut rivit.

Jos kirjoittaisitte sen sijaan ELSE 0, se toimisi myös SUM-funktion kanssa (nollan lisääminen ei muuta summaa), mutta rikkoisi funktioiden AVG, MIN ja COUNT toiminnan.

-- Both produce the same SUM result:
SUM(CASE WHEN quarter = 'Q1' THEN amount END)
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END)

Käytännön esimerkki: neljännesvuosiraportti

Tässä on koko kysely esimerkkidataa vasten. Jokaisesta alueesta tulee yksi rivi ja jokaisesta neljänneksestä yksi sarake.

GROUP BY region tiivistää neljä syöteriviä kahdeksi tulosriviksi. Ilman sitä saisitte yhden rivin kutakin syöteriviä kohden, ja useimmissa soluissa olisi NULL-arvoja.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2
FROM sales
GROUP BY region;

-- Result:
-- region | q1  | q2
-- East   | 100 | 150
-- West   | 200 | 250

Oikean aggregaattifunktion valitseminen

CASE-lausekkeen ympärille sijoitettavan aggregaattifunktion on vastattava kysymystä:

SUM, kun kunkin solun tulee sisältää arvojen summa.
MAX tai MIN, kun kullakin alueen ja neljänneksen yhdistelmällä on täsmälleen yksi arvo ja haluatte vain tuoda sen näkyviin.
COUNT, kun kunkin solun tulee laskea ehtoon täsmäävät rivit.

Haastattelijat kysyvät usein COUNT-versiota: kuinka monta tilausta kutakin tilaa kohden kuukaudessa?

SELECT
  month,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY month;

MAX yhden arvon soluille

Kun jokaisella avain- ja luokkayhdistelmällä on yksi arvo (kyseessä on todellinen ristiintaulukointi, ei summa), käyttäkää MAX- tai MIN-funktiota. Molemmat palauttavat ainoan ei-NULL-arvon ja ohittavat ehtojen täyttymättömistä haaroista tulevat NULL-arvot.

Tämä on turvallinen valinta, kun muotoilette attribuutteja uudelleen sen sijaan, että laskisitte rahasummia, esimerkiksi kun muunnatte avain-arvo-asetustaulun yhdeksi riviksi entiteettiä kohden.

-- Turn key/value rows into one wide row per user
SELECT
  user_id,
  MAX(CASE WHEN attr = 'city'  THEN value END) AS city,
  MAX(CASE WHEN attr = 'plan'  THEN value END) AS plan
FROM user_attributes
GROUP BY user_id;

NULL-tulossolujen käsittely

Jos alueella ei ollut myyntiä toisella neljänneksellä, sen q2-solun arvoksi tulee NULL. Haastattelija voi pyytää näyttämään sen sijaan arvon 0. Käärikää koko aggregaattifunktio COALESCE-funktion sisään.

Sijoittakaa COALESCE aggregaattifunktion ulkopuolelle, ei CASE-lausekkeen sisään, jotta korvaatte arvon vain silloin, kun koko ryhmässä ei ole ehtoon täsmääviä rivejä.

SELECT
  region,
  COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN amount END), 0) AS q1,
  COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN amount END), 0) AS q2
FROM sales
GROUP BY region;

Loppusumman lisääminen

Yleinen jatkokysymys on lisätä kaikkien pivot-sarakkeiden yhteissumma. Sarakkeita ei tarvitse summata nimien perusteella. Tavallinen SUM(amount) samassa ryhmässä antaa rivin kokonaissumman, koska se ohittaa CASE-suodatuksen kokonaan.

Tämä osoittaa haastattelijalle, että ymmärrätte jokaisen SELECT-lauseen aggregaatin laskettavan itsenäisesti saman ryhmän riveistä.

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(amount) AS total
FROM sales
GROUP BY region;

Suodatetun aggregaation pikaratkaisu

PostgreSQL ja SQL-standardi tukevat rakennetta FILTER (WHERE ...), joka on selkeämpi tapa kirjoittaa ehdollista aggregointia. Se on helpompi lukea ja välttää CASE-lauseen toistuvan rungon.

Mainitkaa tämä haastattelussa osoittaaksenne tuntevanne useampia tapoja, mutta muistakaa, että MySQL ja SQL Server eivät tue sitä, joten CASE on edelleen eri tietokannoissa toimiva ratkaisu.

-- Postgres / standard SQL
SELECT
  region,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY region;

Suuri rajoitus

Ehdollisessa aggregoinnissa on yksi rajoitus, johon haastattelijat kiinnittävät huomiota: jokainen tulossarake on lueteltava käsin. Jos neljänneksiä tai luokkia ei tunneta etukäteen, tämä staattinen kysely ei voi mukautua niihin.

Tätä ongelmaa kutsutaan dynaamiseksi pivotoinniksi, ja se edellyttää muodostettua SQL:ää. Kun luokat ovat kiinteä ja ennalta tunnettu joukko, ehdollinen aggregointi on kuitenkin selkeä ja eri tietokannoissa toimiva valinta.

Pikatesti

Testatkaa, miten hyvin hallitsette ehdollisen aggregoinnin mallin.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Ehdollinen aggregointi on eri tietokannoissa toimiva pivotointiratkaisu, jonka jokainen haastattelija hyväksyy:

Yksi CASE kutakin tulossaraketta kohden, aggregaattifunktion ympäröimänä.
SUM summille, MAX/MIN yhden arvon soluille ja COUNT lukumäärille.
Toimii, koska aggregaattifunktiot ohittavat ehtoon täsmäämättömistä haaroista tulevan NULL-arvon.
Käyttäkää COALESCE-funktiota tyhjien solujen muuttamiseen arvoksi 0.
Rajoitus: sarakkeet on kovakoodattava, mikä johtaa seuraavaksi dynaamisiin pivotointeihin.

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 ”Pivotointi ehdollisella aggregoinnilla” ilmainen?

Kyllä – oppitunnin ”Pivotointi ehdollisella aggregoinnilla” 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 ”Pivotointi ehdollisella aggregoinnilla”?

Siirrettävä CASE-inside-SUM-malli rivien muuntamiseen sarakkeiksi. 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 ”Pivotointi ehdollisella aggregoinnilla”-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

Pivotointi ehdollisella aggregoinnilla
Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi
Sarakkeiden muuttaminen riveiksi
Dynaamiset pivotit tuntemattomilla sarakkeilla

← Takaisin: SQL-työhaastatteluun valmistautuminen