PricingLoginStart free
SQL-työhaastatteluun valmistautuminen · Oppitunti

Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi

SQL Serverin PIVOT ja Postgresin crosstab sekä niiden rajoitukset.

Oppitunti 2/413 vaihetta

Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi on ilmainen SQL-työhaastatteluun valmistautuminen-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 SQL-työhaastatteluun valmistautuminen-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL-työhaastatteluun valmistautuminen-kurssilla on yhteensä 4 oppituntia.

Ehdollisen aggregoinnin lisäksi

Tunnette jo eri tietokannoissa toimivan CASE-pivotoinnin. Haastattelijat haluavat kuitenkin tietää myös, osaatteko käyttää tietokantatoimittajakohtaisia pivot-operaattoreita, kun niitä on saatavilla.

SQL Server sisältää erillisen PIVOT-operaattorin. PostgreSQL tarjoaa crosstab-funktion tablefunc-laajennuksessa. Molempien sekä niiden kompastuskivien tunteminen kertoo käytännön kokemuksesta.

SQL Serverin PIVOT-rakenne

SQL Serverin PIVOT tarvitsee kolme asiaa:

  • Arvosarakkeeseen kohdistuvan aggregaattifunktion.
  • FOR-lauseen, jossa nimetään sarake, jonka arvoista tulee uusia sarakkeita.
  • IN-luettelon literaaliarvoista, jotka muutetaan sarakkeiksi.

Sitä on käytettävä johdettuun tauluun, josta löytyvät täsmälleen avain, pivot-sarake ja arvo — ei mitään muuta.

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

Implisiittinen GROUP BY

Haastatteluissa testataan usein seuraavaa hienovaraista PIVOT-kompastuskiveä: ryhmittely on implisiittinen. SQL Server ryhmittelee jokaisen lähteen sarakkeen perusteella, joka EI ole aggregoinnin kohteena oleva sarake eikä FOR-sarake.

Jos johdettu taulunne sisältää vahingossa ylimääräisen sarakkeen, kuten order_id-sarakkeen, pivot ryhmittelee myös sen perusteella, jolloin saatte paljon odotettua enemmän rivejä. Rajatkaa sisempi kysely aina vain avaimeen, pivot-sarakkeeseen ja arvoon.

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

Hakasulkeissa olevat sarakenimet

SQL Serverissä pivotoinnin tuloksena syntyvät sarakenimet ovat datan literaaliarvoja hakasulkeisiin merkittyinä. Jos arvo alkaa numerolla tai sisältää välilyöntejä, hakasulkeet ovat pakolliset.

Valitsette sarakkeet ulommassa SELECT-lauseessa käyttäen samaa hakasulkeissa olevaa nimeä. Tästä syystä PIVOT ei myöskään voi käsitellä tuntemattomia arvoja ilman dynaamista SQL:ää: IN-luettelo on kovakoodattu.

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

PostgreSQL: crosstab

PostgreSQL:ssä ei ole PIVOT-avainsanaa. Sen sijaan tablefunc-laajennus tarjoaa crosstab-funktion, joka ottaa vastaan SQL-merkkijonon ja muotoilee sen tuloksen uudelleen.

Laajennus on otettava käyttöön ensin. crosstab odottaa lähdekyselyn palauttavan täsmälleen kolme saraketta tässä järjestyksessä: rivitunniste, luokka ja arvo.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

Sarakemäärittelyluettelo

crosstab-funktion virhealttein osa on perässä oleva AS ct(...)-sarakemäärittelyluettelo. Teidän on ilmoitettava tulossarakkeiden nimet ja tyypit itse, ja niiden on vastattava luokkien määrää ja järjestystä.

Jos jokin luokka puuttuu riviltä, crosstab täyttää arvot sijainnin perusteella. Tämä voi kohdistaa tiedot vääriin sarakkeisiin, ellette käytä alla kuvattua kahden argumentin muotoa.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

Kahden argumentin crosstab

Välttääksenne kohdistusvirheet silloin, kun joiltakin riveiltä puuttuu luokkia, käyttäkää kahden argumentin muotoa. Toinen kysely palauttaa luokka-arvojen täydellisen ja järjestetyn luettelon, joten crosstab tietää tarkalleen, mihin sarakkeeseen kukin arvo kuuluu.

Tämä on vankka muoto, jota haastattelijat odottavat luokkien ollessa harvalukuisia.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

MySQL: kumpaakaan ei ole

Jos haastattelija kysyy MySQL:stä, vastaus on suora: MySQL:ssä ei ole PIVOTia eikä crosstabia. Ainoa vaihtoehto on ehdollinen aggregointi käyttämällä CASE-rakennetta (tai lyhyempää rakennetta SUM(... ) + IF()).

Juuri siksi eri tietokannoissa toimivaa CASE-mallia arvostetaan: se on yhteinen nimittäjä, joka toimii kaikkialla.

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

Käytännön esimerkki: tilamäärät SQL Serverissä

Raportointitarve voi olla seuraava: "yksi rivi aluetta kohden ja sarake, joka laskee kussakin tilassa olevat tilaukset". SQL Serverissä syötätte rajatusta johdetusta taulusta tiedot PIVOT-operaattorille käyttämällä COUNT-funktiota.

Koska laskette itse status-sarakkeen arvoja, jokainen säilön ei-NULL-statusrivi tulee lasketuksi. Ulommassa SELECT-lauseessa kukin status luetellaan hakasulkeissa olevana sarakkeena. Tämä on tiiviimpi vaihtoehto kuin kolmen COUNT(CASE ...)-lausekkeen kirjoittaminen.

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

Yhteiset rajoitukset

Sekä PIVOT- että crosstab-rakenteella on sama keskeinen rajoitus kuin ehdollisella aggregoinnilla: tulossarakkeiden on oltava tiedossa kyselyä kirjoitettaessa.

  • SQL Server: IN-luettelo on literaali.
  • Postgres crosstab: sarakemäärittelyluettelo on literaali.

Kumpikaan ei voi löytää luokkia ajonaikaisesti. Se edellyttää SQL-merkkijonon muodostamista dynaamisesti.

Mitä niistä kannattaa käyttää?

Hyvä haastatteluvastaus vertailee vaihtoehtoja rehellisesti:

  • CASE-aggregointi: siirrettävä, selkeä ja toimii kaikissa tietokantamoottoreissa. Oletusvalinta.
  • SQL Serverin PIVOT: tiivis monille sarakkeille, mutta implisiittinen ryhmittely yllättää helposti.
  • Postgresin crosstab: tehokas mutta monisanainen, ja vaatii laajennuksen sekä sarakemäärittelyluettelon.

Jos olette epävarma, valitkaa ehdollinen aggregointi ja mainitkaa tietokantatoimittajien operaattorit vaihtoehtoina.

Pikatesti

Selvittäkää SQL Serverin PIVOT-toiminta, jota haastattelijat yleensä testaavat.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Tietokantatoimittajien pivot-syntaksi yhdellä ruudulla:

  • SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])), jossa implisiittinen GROUP BY tehdään jäljelle jäävien sarakkeiden perusteella.
  • Postgres: crosstab() lähteestä tablefunc; se tarvitsee sarakemäärittelyluettelon. Käyttäkää kahden argumentin muotoa harvalukuisille tiedoille.
  • MySQL: kumpaakaan ei ole, joten käyttäkää CASE-rakennetta.
  • Kaikissa kolmessa sarakkeiden on oltava tiedossa kyselyä kirjoitettaessa.
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 maksuttaNäytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi” ilmainen?

Kyllä – oppitunnin ”Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi” 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 ”Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi”?

SQL Serverin PIVOT ja Postgresin crosstab sekä niiden rajoitukset. 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 2/4.

Kuinka kauan ”Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi”-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

  1. Pivotointi ehdollisella aggregoinnilla
  2. Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi
  3. Sarakkeiden muuttaminen riveiksi
  4. Dynaamiset pivotit tuntemattomilla sarakkeilla
← Takaisin: SQL-työhaastatteluun valmistautuminen