Sarakkeiden muuttaminen riveiksi
Leveiden taulukoiden kääntäminen UNPIVOT- tai UNION ALL -rakenteella.
Sarakkeiden muuttaminen riveiksi on ilmainen Valmistautuminen ohjelmointihaastatteluihin-oppitunti CoddyKitissä. Tämä on oppitunti 3/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.
Käänteinen ongelma
Unpivotointi on pivotoinnin peilikuva: otatte leveän taulun ja muutatte sen sarakkeet takaisin riveiksi. Haastattelijat kysyvät tästä, kun data saapuu taulukkolaskentataulukon muodossa mutta se on normalisoitava analyysia varten.
Esimerkiksi taulu, jossa on kutakin aluetta kohden sarakkeet q1, q2, q3, q4, on muutettava riveiksi muotoon (region, quarter, amount). Tätä pitkää muotoa aggregointi, liitokset ja kaavioiden luominen kaikki suosivat.
-- Wide input we want to unpivot
region | q1 | q2 | q3 | q4
-------+-----+-----+-----+----
East | 100 | 150 | 120 | 180
West | 200 | 250 | 210 | 260Eri SQL-murteissa toimiva UNION ALL -malli
SQL-murteesta riippumaton ratkaisu on UNION ALL: kirjoittakaa yksi SELECT kutakin lähdesaraketta kohden niin, että kukin palauttaa kiinteän tunnisteen ja kyseisen sarakkeen arvon.
Käyttäkää UNION ALL-rakennetta, älkää UNION-rakennetta, jotta ette maksa duplikaattien poistamisesta ja säilytätte jokaisen rivin, vaikka kahdella solulla olisi sama arvo.
SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL
SELECT region, 'Q2', q2 FROM wide_sales
UNION ALL
SELECT region, 'Q3', q3 FROM wide_sales
UNION ALL
SELECT region, 'Q4', q4 FROM wide_sales;Miksi UNION ALL eikä UNION
Tämä on klassinen haastattelukompastuskivi. UNION poistaa koko tuloksen päällekkäiset rivit. Jos sekä East- että West-alueella olisi Q1:n arvona 100, tavallinen UNION yhdistäisi samanlaiset rivit ja menettäisitte dataa.
UNION ALL yhdistää tulokset poistamatta duplikaatteja, mikä on juuri se, mitä unpivotointi tarvitsee. Se on myös nopeampi, koska duplikaattien poistamiseen ei tarvita lajittelua tai hajautusta.
-- UNION would wrongly merge identical (region, quarter, amount) rows
-- UNION ALL keeps every row, always the correct choice hereSaraketyyppien yhteensopivuus
Jokaisen UNION ALL-haaran on tuotettava sama määrä sarakkeita, joiden tyypit ovat yhteensopivat ja järjestys sama. Sarakenimet määräytyvät ensimmäisestä SELECT-lauseesta.
Jos leveän taulun sarakkeiden tyypit eroavat toisistaan, esimerkiksi toinen on int ja toinen decimal, moottori valitsee yhteisen tyypin. Jos tyypit ovat todella yhteensopimattomat, tehkää muunnos eksplisiittisesti, jotta union ei epäonnistu.
SELECT region, 'revenue' AS metric, CAST(revenue AS decimal(12,2)) AS val FROM t
UNION ALL
SELECT region, 'units', CAST(units AS decimal(12,2)) FROM t;SQL Serverin UNPIVOT
SQL Serverissä on erillinen UNPIVOT-operaattori, joka on tiiviimpi kuin UNION ALL. Nimeätte uuden arvosarakkeen ja uuden tunnistesarakkeen sekä luettelette yhteen koottavat lähdesarakkeet.
Yksi tärkeä toimintaperiaate on, että UNPIVOT poistaa rivit, joiden arvo on NULL. Haastatteluissa testataan, tiedättekö tämän sivuvaikutuksen.
SELECT region, quarter, amount
FROM wide_sales
UNPIVOT (
amount FOR quarter IN (q1, q2, q3, q4)
) AS u;UNPIVOT poistaa NULL-arvot
Jos alueella on q3-sarakkeessa arvo NULL, SQL Serverin UNPIVOT jättää kyseisen rivin yksinkertaisesti pois tuloksesta. Jos tarvitsette rivin jokaisesta sarakkeesta NULL-arvosta riippumatta, käyttäkää sen sijaan UNION ALL-rakennetta, joka säilyttää myös ne.
Tuokaa tämä kompromissi esiin haastattelussa: natiivi UNPIVOT on tiivis mutta hävittää NULL-arvoja, kun taas UNION ALL on monisanaisempi mutta täydellinen.
-- UNPIVOT: q3 NULL for East -> no (East, Q3) row produced
-- UNION ALL: (East, 'Q3', NULL) row IS producedPostgreSQL: LATERAL VALUES
PostgreSQL:ssä ei ole UNPIVOT-operaattoria, mutta siisti vakiintunut tapa on käyttää CROSS JOIN LATERAL-liitosta VALUES-luettelon kanssa. Jokainen leveän taulun rivi laajennetaan pienen upotetun (tunniste, arvo) -parien taulun avulla.
Tämä on selkeämpi kuin pitkä UNION ALL ja lukee lähdetaulun vain kerran.
SELECT w.region, v.quarter, v.amount
FROM wide_sales w
CROSS JOIN LATERAL (VALUES
('Q1', w.q1),
('Q2', w.q2),
('Q3', w.q3),
('Q4', w.q4)
) AS v(quarter, amount);Taulun lukeminen kerran
Mainitsemisen arvoinen suorituskykynäkökohta on, että naiivi UNION ALL lukee leveän taulun kerran haaraa kohden, eli neljänneksille tehdään neljä lukua. LATERAL VALUES-muoto ja SQL Serverin UNPIVOT lukevat lähteen kerran.
Suurissa tauluissa tällä on merkitystä. Jos teidän on käytettävä UNION ALL-rakennetta, optimoija voi silti lukea taulun toistuvasti, joten mainitkaa LATERAL tai UNPIVOT tehokkaampana vaihtoehtona.
Tyhjien solujen suodattaminen
UNION ALL- tai LATERAL-rakenteella säilytätte NULL-arvoiset rivit. Jos kysymyksessä halutaan vain täytetyt solut, lisätkää suodatin. Tämä jäljittelee sitä, mitä SQL Serverin UNPIVOT tekee automaattisesti.
NULL-arvojen säilyttäminen tai poistaminen on harkinnanvarainen päätös, joten varmistakaa vaatimus haastattelijalta ennen koodaamista.
SELECT region, quarter, amount
FROM (
SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
) t
WHERE amount IS NOT NULL;Esimerkki: aggregointi unpivotoinnin jälkeen
Yleinen jatkokysymys kuuluu: ”anna leveästä neljännesvuositaulukosta aluekohtainen kokonaisliikevaihto kaikilta neljänneksiltä”. Kun taulukko on muunnettu pitkään muotoon unpivotoinnilla, aggregointi on helppo: yksi SUM, ryhmiteltynä alueen mukaan.
Tämä osoittaa, miksi unpivotointi kannattaa tehdä ensin. Neljän erillisen sarakkeen summaaminen on haura ratkaisu, mutta pitkän muodon SUM(amount) GROUP BY region toimii neljännesvuosien määrästä riippumatta.
WITH long_sales AS (
SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
UNION ALL SELECT region, 'Q3', q3 FROM wide_sales
UNION ALL SELECT region, 'Q4', q4 FROM wide_sales
)
SELECT region, SUM(amount) AS total
FROM long_sales
GROUP BY region;Milloin unpivotointia käytetään
Tunnistakaa unpivotoinnin tarve sanallisesta tehtävästä:
- Syötteessä on toistuvia sarakkeita, jotka ovat todellisuudessa arvoja, kuten kuukausia, vuosia tai mittareita.
- Arvojen välillä on tehtävä aggregointi, liitos tai kaavio.
- Haluatte normalisoida denormalisoidun taulukkolaskentadatan tuonnin yhteydessä.
Pitkä muoto on lähes aina oikea rakenne myöhempää SQL-työskentelyä varten, joten unpivotointi on usein ensimmäinen vaihe.
Pikatarkistus
Varmistakaa, että tunnistatte yleisimmän unpivotoinnin ongelmakohdan.
Kertaus
Unpivotointi muuntaa sarakkeet riveiksi:
- Siirrettävyys: yksi
SELECTjokaista saraketta kohti, yhdistettynäUNION ALL-operaattorilla (ei koskaan tavallisella UNION-operaattorilla). - SQL Server: natiivi
UNPIVOT, joka on ytimekäs mutta jättää NULL-arvot pois. - Postgres:
CROSS JOIN LATERAL (VALUES ...), yksi taulun läpikäynti. - Varmistakaa haarojen sarakemäärien ja tietotyyppien vastaavuus; suodattakaa NULL-arvot, jos tehtävä sitä edellyttää.
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
Usein kysytyt kysymykset
Onko oppitunti ”Sarakkeiden muuttaminen riveiksi” ilmainen?
Kyllä – oppitunnin ”Sarakkeiden muuttaminen riveiksi” 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 ”Sarakkeiden muuttaminen riveiksi”?
Leveiden taulukoiden kääntäminen UNPIVOT- tai UNION ALL -rakenteella. 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 3/4.
Kuinka kauan ”Sarakkeiden muuttaminen riveiksi”-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
- Pivotointi ehdollisella aggregoinnilla
- Toimittajakohtainen PIVOT- ja ristiintaulukointisyntaksi
- Sarakkeiden muuttaminen riveiksi
- Dynaamiset pivotit tuntemattomilla sarakkeilla