SQL Academy · Oppitunti

CUBE kaikille yhdistelmille

Kaikki ryhmittely-yhdistelmät kerralla.

Oppitunti 3/413 vaihetta

CUBE kaikille yhdistelmille on ilmainen SQL Academy-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 SQL Academy-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL Academy-kurssilla on yhteensä 4 oppituntia.

Mikä CUBE on

SQL:n CUBE-laajennus muodostaa kaikki mahdolliset ryhmittely-yhdistelmät annetusta sarakeluettelosta. Siinä missä ROLLUP luo hierarkian, CUBE muodostaa välisummat kaikkien mahdollisten yhdistelmien mukaan — myös kokonaissumman.

Ajatelkaa sen vastaavan pyyntöä: "Antakaa minulle kaikki näistä sarakkeista laskettavat välisummaa."

Taulun määrittäminen

Käytämme sales-taulua, joka seuraa liikevaihtoa vuoden, alueen ja tuoteluokan mukaan. Tällainen moniulotteinen data on juuri sitä, missä CUBE on parhaimmillaan.

CREATE TABLE sales (
  year     INT,
  region   VARCHAR(20),
  category VARCHAR(20),
  revenue  NUMERIC(10,2)
);

INSERT INTO sales VALUES
  (2023, 'North', 'Electronics', 12000),
  (2023, 'North', 'Clothing',     8000),
  (2023, 'South', 'Electronics',  9500),
  (2023, 'South', 'Clothing',     6000),
  (2024, 'North', 'Electronics', 14000),
  (2024, 'North', 'Clothing',     9500),
  (2024, 'South', 'Electronics', 11000),
  (2024, 'South', 'Clothing',     7200);

Ensimmäinen CUBE-kysely

Syntaksi on suoraviivainen: korvatkaa GROUP BY muodolla GROUP BY CUBE(...) ja luetelkaa sarakkeet sulkeiden sisällä. SQL muodostaa kaikista näiden sarakkeiden yhdistelmistä ryhmittelyjoukot.

SELECT
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

Kuinka monta ryhmittelyä CUBE tuottaa

Kun sarakkeita on n, CUBE tuottaa 2ⁿ ryhmittelyjoukkoa — yhden luettelon jokaista osajoukkoa kohti, mukaan lukien tyhjän joukon eli kokonaissumman.

Kolmella sarakkeella (year, region, category) tuloksena on 2³ = 8 ryhmittelyjoukkoa: kukin sarake erikseen, kukin sarakepari, kaikki kolme yhdessä ja ei yhtäkään saraketta.

-- 3 columns => 8 grouping sets:
-- (year, region, category)
-- (year, region)
-- (year, category)
-- (region, category)
-- (year)
-- (region)
-- (category)
-- () <- grand total
SELECT COUNT(*) AS row_count
FROM (
  SELECT year, region, category, SUM(revenue)
  FROM sales
  GROUP BY CUBE(year, region, category)
) sub;

NULL-arvot osoittavat koostetun ulottuvuuden

Kuten ROLLUP, myös CUBE käyttää arvoa NULL osoittamaan, että sarakkeen yli on muodostettu yhteenveto. Jos region on tulosrivillä NULL, rivi sisältää kaikkien alueiden yhteenvedon jäljellä olevien sarakkeiden kyseiselle yhdistelmälle.

Käyttäkää GROUPING(col)-funktiota aidon NULL-arvon erottamiseen koostamisen paikkamerkistä.

SELECT
  GROUPING(year)     AS g_year,
  GROUPING(region)   AS g_region,
  GROUPING(category) AS g_category,
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY g_year, g_region, g_category;

NULL-arvojen selkeyttäminen COALESCE-funktion avulla

Jotta tulos olisi raporteissa helpommin luettava, kääritkää kukin ryhmitelty sarake COALESCE-funktion sisään ja korvatkaa koostamisen NULL kuvaavalla nimellä, kuten 'ALL'.

SELECT
  COALESCE(CAST(year AS VARCHAR), 'ALL YEARS')     AS year,
  COALESCE(region,   'ALL REGIONS')                AS region,
  COALESCE(category, 'ALL CATEGORIES')             AS category,
  SUM(revenue)                                     AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

CUBE vai ROLLUP – keskeinen ero

ROLLUP(a, b, c) muodostaa välisummaa vain yhtä hierarkiaa pitkin: (a,b,c), (a,b), (a), (). Se noudattaa järjestystä vasemmalta oikealle.

CUBE(a, b, c) muodostaa jokaisen osajoukon, myös poikkileikkaukset kuten (a,c) tai pelkän (b):n, jotka ROLLUP jättää kokonaan väliin. Käyttäkää CUBE-toimintoa, kun tarvitsette täyden moniulotteisen analyysin ilman ennalta määrättyä hierarkiaa.

-- ROLLUP: 4 grouping sets
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, region);

-- CUBE: 4 grouping sets for 2 columns (same count here)
-- but adds the (region) subtotal that ROLLUP omits
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY CUBE(year, region);

Osittainen CUBE

Voitte yhdistää tavalliset GROUP BY -sarakkeet ja CUBE-aliluettelon. CUBE(...)-rakenteen ulkopuolella luetellut sarakkeet ovat aina mukana jokaisessa ryhmittelyjoukossa, kun taas sen sisällä oleville sarakkeille muodostetaan kaikki mahdolliset yhdistelmät.

-- year is fixed; CUBE only over region and category
SELECT
  year,
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY year, CUBE(region, category)
ORDER BY year, region, category;

CUBE-tulosten suodattaminen HAVING-lauseella

HAVING toimii CUBE-tulosten kanssa täsmälleen samoin kuin tavallisen GROUP BY-lauseen kanssa. Voitte suodattaa pois ryhmittelyrivit, joiden aggregaattiarvo ei täytä kynnysarvoa – esimerkiksi säilyttää vain rivit, joilla kokonaistulot ylittävät vähimmäisarvon.

SELECT
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(region, category)
HAVING SUM(revenue) > 15000
ORDER BY total_revenue DESC;

CUBEn yhdistäminen ikkunafunktioihin

Voitte sijoittaa CUBE-kyselyn CTE-rakenteeseen ja käyttää sen jälkeen ikkunafunktioita tuloksen rivien järjestämiseen tai vertailuun. Tämä on tehokas tapa rakentaa johtotason koontinäyttöjä.

WITH cube_result AS (
  SELECT
    COALESCE(region,   'ALL') AS region,
    COALESCE(category, 'ALL') AS category,
    SUM(revenue) AS total_revenue
  FROM sales
  GROUP BY CUBE(region, category)
)
SELECT
  region,
  category,
  total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM cube_result
ORDER BY revenue_rank;

Milloin CUBE kannattaa valita

CUBE on oikea työkalu, kun:

Tarvitsette kaikki osasummayhdistelmät ad hoc- tai OLAP-tyyliseen raporttiin.
Ryhmittelysarakkeiden välillä ei ole luonnollista hierarkiaa.
Haluatte antaa analyytikoille mahdollisuuden tarkastella tietoja haluamallaan tavalla.

Välttäkää CUBE-rakennetta, kun sarakkeita on paljon: neljä saraketta tuottaa jo 16 ryhmittelyjoukkoa ja viisi saraketta 32. Suosikaa ROLLUP-rakennetta tai eksplisiittisiä GROUPING SETS -määrittelyjä, jotta tulosjoukot pysyvät hallittavina.

Pikatarkistus

Kuinka monta toisistaan poikkeavaa ryhmittelyjoukkoa GROUP BY CUBE(a, b, c, d) tuottaa?

Vastaa interaktiivisessa oppitunnissa

Oppitunnin kertaus

Tässä oppitunnissa opitte, miten CUBE muodostaa kaikki mahdolliset ryhmittelyjoukkojen yhdistelmät sarakeluettelosta, joten se sopii erinomaisesti moniulotteiseen raportointiin.

Keskeiset opit:

GROUP BY CUBE(a, b, c) tuottaa 2ⁿ ryhmittelyjoukkoa.
Tulosarakkeen NULL tarkoittaa, että kyseinen ulottuvuus on yhdistetty; tunnistakaa tämä GROUPING()-funktion avulla.
COALESCE muuttaa aggregoinnin tuottamat NULL-arvot merkityksellisiksi nimikkeiksi.
Osittainen CUBE (esimerkiksi GROUP BY year, CUBE(region, category)) lukitsee jotkin sarakkeet ja muodostaa kuution vain jäljelle jäävistä sarakkeista.
Käyttäkää CUBE-rakennetta täydelliseen ad hoc -analyysiin. Suosikaa ROLLUP-rakennetta tai eksplisiittisiä GROUPING SETS -määrittelyjä, kun hierarkia tunnetaan tai sarakkeita on paljon.

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: 46
Oppitunnit: 183

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”CUBE kaikille yhdistelmille” ilmainen?

Kyllä – oppitunnin ”CUBE kaikille yhdistelmille” 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 Academy-kurssin, päivitä CoddyKit PROhon. SQL Academy-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”CUBE kaikille yhdistelmille”?

Kaikki ryhmittely-yhdistelmät kerralla. Harjoittelet SQL Academy-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni SQL Academy-opiskelun?

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

Kuinka kauan ”CUBE kaikille yhdistelmille”-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 Academy-oppitunnilla?

Kyllä. Jokainen SQL Academy-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

← Takaisin: SQL Academy