OLAP-Operationen: GROUPING SETS

Datengestützte Entscheidungsfindung in SQL

Irene Ortner

Data Scientist at Applied Statistics

Überblick über OLAP-Operatoren in SQL

Erweiterungen in SQL, um OLAP-Operationen einfacher zu machen

GROUP BY CUBE
GROUP BY ROLLUP
GROUP BY GROUPING SETS

Tischvermietung_erweitert

Die ersten paar Zeilen der Tabelle „ renting_extended “:

| renting_id | country  | genre  | rating |
|------------|----------|--------|--------|
| 2          | Belgium  | Drama  | 10     |
| 32         | Belgium  | Drama  | 10     |
| 203        | Austria  | Drama  | 6      |
| 292        | Austria  | Comedy | 8      |
| 363        | Belgium  | Drama  | 7      |
| .......... | ........ | ...... | ...... |

GROUP BY GROUPING SETS

Beispiel für eine Abfrage mit dem Operator „ GROUPING SETS “:

SELECT country, 
       genre, 
       COUNT(*)
FROM rentings_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

Die Namen der Spalten in Klammern zeigen eine Aggregationsebene an.
GROUP BY GROUPING SETS gibt ein „ UNION ” über mehrere „ GROUP BY ”-Abfragen zurück.

GROUPING SETS und „ GROUP BY “-Abfragen

SELECT country, 
       genre, 
       COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (country, genre);

Zähl die Filmverleihvorgänge für jede einzelne Kombination aus Land und Genre.
Ausdruck in „ GROUPING SETS “: (country, genre)

SELECT country, 
       genre, 
       COUNT(*)
FROM renting_extended
GROUP BY country, genre;


| country | genre  | count  |
|---------|--------|--------|
| Austria | Comedy | 2      |
| Belgium | Drama  | 15     |
| Austria | Drama  | 4      |
| Belgium | Comedy | 1      |

GROUPING SETS und „ GROUP BY “-Abfragen

SELECT country, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (country);

Zähl die Filmverleihe für jedes Land.
Ausdruck in „ GROUPING SETS “: (country)

SELECT country, COUNT(*)
FROM renting_extended
GROUP BY country;

| country | count |
|---------|-------|
| Austria | 16    |
| Belgium | 6     |

GROUPING SETS und „ GROUP BY “-Abfragen

SELECT genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (genre);

Zähl die Filmverleihzahlen für jedes Genre.
Ausdruck in „ GROUPING SETS “: (genre)

SELECT genre, COUNT(*)
FROM renting_extended
GROUP BY genre;

| country | count |
|---------|-------|
| Comedy  | 3     |
| Drama   | 19    |

GROUPING SETS und „ GROUP BY “-Abfragen

SELECT COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ();

Gesamtaggregation – zähl alle Filmverleihe.
Ausdruck in „ GROUPING SETS “: ()

SELECT COUNT(*)
FROM renting_extended;

| count |
|-------|
| 22    |

Notation für „ GROUP BY GROUPING “ SETS

GROUP BY GROUPING SETS (...)

SELECT country, genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

UNION über 4 vorherige Abfragen.
Alle Infos aus einer Pivot-Tabelle in einer Abfrage zusammenfassen.
Diese Abfrage ist dasselbe wie „ GROUP BY CUBE (country, genre) “.

Ergebnis mit dem SETS-Operator von GROUPING

SELECT country, genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

| country | genre  |count|
|---------|--------|-----|
| NULL    | NULL   | 22  |
| Austria | Comedy | 2   |
| Belgium | Drama  | 15  |
| Austria | Drama  | 4   |
| Belgium | Comedy | 1   |
| Belgium | NULL   | 16  |
| Austria | NULL   | 6   |
| NULL    | Comedy | 3   |
| NULL    | Drama  | 19  |

Berechne die Anzahl der Vermietungen und die durchschnittliche Bewertung.

Nur ausgewählte Aggregationen kombinieren:
- Land und Genre
- Genre

Nimm die Anzahl der ausgeliehenen Filme und die durchschnittlichen Bewertungen für die Zusammenfassung.

SELECT country, 
     genre, 
     COUNT(*), 
     AVG(rating) AS avg_rating
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (genre));

Berechne die Anzahl der Vermietungen und die durchschnittliche Bewertung.

SELECT country, genre, COUNT(*), AVG(rating) AS avg_rating
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (genre));

| country | genre  | count| avg_rating |
|---------|--------|------|------------|
| Austria | Comedy | 2    | 8.00       |
| Belgium | Drama  | 15   | 9.17       |
| Austria | Drama  | 4    | 6.00       |
| Belgium | Comedy | 1    | NULL       |
| NULL    | Comedy | 3    | 8.00       |
| NULL    | Drama  | 19   | 8.38       |

Lass uns üben!

Datengestützte Entscheidungsfindung in SQL