ROLLUP und CUBE

PostgreSQL: Zusammenfassende Statistiken und Fensterfunktionen

Michel Semaan

Data Scientist

Summen auf Gruppenebene

Chinesische und russische Medaillen bei den Olympischen Sommerspielen 2008 nach Medaillengattung

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | Total  | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | Total  | 143    |

Der alte Lösungsweg

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, Medal
ORDER BY Country ASC, Medal ASC

UNION ALL

SELECT
  Country, 'Total', COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, 2
ORDER BY Country ASC;

Verwende ROLLUP

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, ROLLUP(Medal)
ORDER BY Country ASC, Medal ASC;

ROLLUP ist eine Unterklausel von GROUP BY mit zusätzlichen Zeilen für Aggregationen auf Gruppenebene
GROUP BY Country, ROLLUP(Medal) zählt alle Summen von Country und Medal, zählt dann nur die Summen auf der Ebene Country und füllt Medal mit null für diese Zeilen

ROLLUP – Abfrage

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY ROLLUP(Country, Medal)
ORDER BY Country ASC, Medal ASC;

ROLLUP ist hierarchisch aufgebaut und wird von der linken Spalte nach rechts aufgelöst
- ROLLUP(Country, Medal) umfasst Summen nach Country
- ROLLUP(Medal, Country) umfasst Summen nach Medal
- Beide umfassen die übergeordneten Gesamtsummen

ROLLUP – Ergebnis

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | null   | 327    |

Summen auf Gruppenebene enthalten null; Zeile mit allen null-Werten ist Gesamtsumme
Hinweis: Summen nach Medal sind nicht enthalten, da ROLLUP(Country, Medal) und nicht ROLLUP(Medal, Country)

Verwende CUBE

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY CUBE(Country, Medal)
ORDER BY Country ASC, Medal ASC;

CUBE ist wie ROLLUP nur nicht hierarchisch
Erzeugt alle möglichen Aggregationen auf Gruppenebene
- CUBE(Country, Medal) zählt Werte auf den Ebenen Country, Medal und der Gesamtsummen

CUBE – Ergebnis

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | Bronze | 113    |
| null    | Gold   | 117    |
| null    | Silver | 97     |
| null    | null   | 327    |

Achte auf die hier auch aufgeführten Gesamtwerte der Ebene Medal

ROLLUP und CUBE im Vergleich

Quelle

| Year | Quarter | Sales |
|------|---------|-------|
| 2008 | Q1      | 12    |
| 2008 | Q2      | 15    |
| 2009 | Q1      | 21    |
| 2009 | Q2      | 27    |

Verwende ROLLUP für hierarchische Daten (z. B. Datumsangaben) und um nicht alle möglichen Aggregationen auf Gruppenebene zu erhalten
Verwende CUBE für die Berechnung aller möglichen Aggregationen auf Gruppenebene

ROLLUP(Year, Quarter)

| Year | Quarter | Sales |
|------|---------|-------|
| 2008 | null    | 27    |
| 2009 | null    | 48    |
| null | null    | 75    |

CUBE(Year, Quarter)

Die obigen und die folgenden Zeilen

| Year | Quarter | Sales |
|------|---------|-------|
| null | Q1      | 33    |
| null | Q2      | 42    |

Lass uns üben!

PostgreSQL: Zusammenfassende Statistiken und Fensterfunktionen