0Pricing
SQL Academy · Lekcja

CUBE dla wszystkich kombinacji

Obliczaj jednocześnie każdą kombinację grupowania

CUBE dla wszystkich kombinacji to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Czym jest CUBE

Rozszerzenie CUBE w SQL generuje każdą możliwą kombinację grupowania z listy kolumn. Podczas gdy ROLLUP tworzy hierarchię, CUBE generuje pełny przekrój sum częściowych — w tym sumę całkowitą.

Można myśleć o nim jak o poleceniu: „Podaj mi każdą sumę częściową, którą można obliczyć na podstawie tych kolumn”.

Przygotowanie tabeli

Będziemy używać tabeli sales, która śledzi przychody według roku, regionu i kategorii produktu. Ten typ danych wielowymiarowych doskonale pokazuje możliwości CUBE.

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);

Pierwsze zapytanie CUBE

Składnia jest prosta: zastąp GROUP BY wyrażeniem GROUP BY CUBE(...) i wymień w nim kolumny. SQL wygeneruje wszystkie kombinacje tych kolumn jako zestawy grupowania.

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

Ile zestawów grupowania tworzy CUBE

Dla n kolumn CUBE tworzy 2n zestawów grupowania — po jednym dla każdego podzbioru listy kolumn, w tym dla zbioru pustego (sumy całkowitej).

W przypadku 3 kolumn (year, region, category) daje to 2³ = 8 zestawów grupowania: każda kolumna osobno, każda para, wszystkie trzy razem oraz żadna z nich.

-- 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 oznacza agregowany wymiar

Podobnie jak ROLLUP, CUBE używa wartości NULL, aby wskazać, że kolumna została zagregowana. Jeśli wiersz wynikowy ma wartość NULL w kolumnie region, oznacza to, że podsumowuje wszystkie regiony dla danej kombinacji pozostałych kolumn.

Użyj GROUPING(col), aby odróżnić rzeczywistą wartość NULL od znacznika agregacji.

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;

Uczytelnianie wartości NULL za pomocą COALESCE

Aby zwiększyć czytelność wyników w raportach, obejmij każdą zgrupowaną kolumnę funkcją COALESCE, zastępując agregacyjną wartość NULL opisową etykietą, taką jak '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 a ROLLUP — najważniejsza różnica

ROLLUP(a, b, c) tworzy sumy częściowe tylko wzdłuż jednej hierarchii: (a,b,c), (a,b), (a), (). Zachowuje kolejność od lewej do prawej.

CUBE(a, b, c) tworzy każdy podzbiór — w tym przekroje takie jak (a,c) lub sam (b) — które ROLLUP całkowicie pomija. Użyj CUBE, gdy potrzebujesz pełnej analizy wielowymiarowej bez z góry ustalonej hierarchii.

-- 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);

Częściowy CUBE

Można połączyć zwykłe kolumny GROUP BY z podlistą CUBE. Kolumny wymienione poza CUBE(...) są zawsze obecne w każdym zestawie grupowania, a pełne kombinacje są tworzone tylko dla kolumn znajdujących się wewnątrz.

-- 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;

Filtrowanie wyników CUBE za pomocą HAVING

HAVING działa z wynikami CUBE dokładnie tak samo jak ze zwykłym GROUP BY. Można odfiltrować wiersze grupowania, których wartość agregatu nie spełnia określonego progu — na przykład pozostawić tylko te wiersze, w których łączny przychód przekracza wartość minimalną.

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;

Łączenie CUBE z funkcjami okna

Zapytanie z CUBE można umieścić w CTE, a następnie zastosować funkcje okna do rangowania wierszy lub ich porównywania w obrębie wyniku. Jest to zaawansowany wzorzec przydatny podczas tworzenia pulpitów nawigacyjnych dla kadry zarządzającej.

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;

Kiedy wybrać CUBE

CUBE jest właściwym narzędziem, gdy:

  • Potrzebują Państwo wszystkich kombinacji sum częściowych w raporcie ad hoc lub raporcie w stylu OLAP.
  • Między kolumnami grupowania nie istnieje naturalna hierarchia.
  • Chcą Państwo umożliwić analitykom analizowanie danych w dowolny wybrany sposób.

Należy unikać CUBE, gdy liczba kolumn jest duża — już 4 kolumny generują 16 zestawów grupowania, a 5 kolumn generuje ich 32. Aby zachować kontrolę nad rozmiarem wyników, należy preferować ROLLUP lub jawnie określone GROUPING SETS.

Szybki test

Ile różnych zestawów grupowania generuje GROUP BY CUBE(a, b, c, d)?

Podsumowanie lekcji

W tej lekcji poznali Państwo sposób, w jaki CUBE generuje wszystkie możliwe kombinacje zestawów grupowania z listy kolumn, dzięki czemu doskonale nadaje się do raportowania wielowymiarowego.

Najważniejsze informacje:

  • GROUP BY CUBE(a, b, c) generuje 2n zestawów grupowania.
  • NULL w kolumnie wyniku oznacza, że dany wymiar został zagregowany; do wykrywania tego stanu służy GROUPING().
  • COALESCE zamienia wartości NULL wynikające z agregacji na znaczące etykiety.
  • Częściowy CUBE (np. GROUP BY year, CUBE(region, category)) ustala niektóre kolumny i tworzy CUBE tylko dla pozostałych.
  • Należy używać CUBE do pełnej analizy ad hoc, a ROLLUP lub jawnie określonych GROUPING SETS, gdy hierarchia jest znana lub liczba kolumn jest duża.

Często zadawane pytania

Czy lekcja „CUBE dla wszystkich kombinacji” jest bezpłatna?

Tak — pełny tekst „CUBE dla wszystkich kombinacji” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „CUBE dla wszystkich kombinacji”?

Obliczaj jednocześnie każdą kombinację grupowania Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.

Ile czasu zajmuje lekcja „CUBE dla wszystkich kombinacji”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?

Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Co dalej po pojedynczym GROUP BY
  2. ROLLUP dla sum częściowych
  3. CUBE dla wszystkich kombinacji
  4. GROUPING SETS — wyjaśnienie
← Powrót do SQL Academy