Operații OLAP: GROUPING SETS

Luarea Deciziilor Bazate pe Date în SQL

Irene Ortner

Data Scientist at Applied Statistics

Prezentare generală a operatorilor OLAP în SQL

Extensii SQL pentru operații OLAP

GROUP BY CUBE
GROUP BY ROLLUP
GROUP BY GROUPING SETS

Tabelul renting_extended

Primele rânduri din tabelul 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

Exemplu de interogare cu operatorul GROUPING SETS:

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

Numele coloanelor între paranteze reprezintă un nivel de agregare.
GROUP BY GROUPING SETS returnează un UNION peste mai multe interogări GROUP BY.

GROUPING SETS și interogări GROUP BY

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

Numărul închirierilor pentru fiecare combinație unică de țară și gen.
Expresie în 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 și interogări GROUP BY

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

Numărul închirierilor de filme pentru fiecare țară.
Expresie în GROUPING SETS: (country)

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

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

GROUPING SETS și interogări GROUP BY

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

Numărul închirierilor de filme pentru fiecare gen.
Expresie în GROUPING SETS: (genre)

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

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

GROUPING SETS și interogări GROUP BY

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

Agregare totală - numărul tuturor închirierilor.
Expresie în GROUPING SETS: ()

SELECT COUNT(*)
FROM renting_extended;

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

Notație pentru GROUP BY GROUPING SETS

GROUP BY GROUPING SETS (...)

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

UNION peste cele 4 interogări anterioare.
Combină toate informațiile unui tabel pivot într-o singură interogare.
Echivalent cu GROUP BY CUBE (country, genre).

Rezultat cu operatorul GROUPING SETS

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  |

Număr de închirieri și rating mediu

Combinarea doar a anumitor agregări:
- țară și gen
- gen

Utilizarea numărului de închirieri și a ratingului mediu pentru agregare.

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

Număr de închirieri și rating mediu

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       |

Să exersăm!

Luarea Deciziilor Bazate pe Date în SQL