Operasi OLAP: GROUPING SETS

Pengambilan Keputusan Berbasis Data dengan SQL

Irene Ortner

Data Scientist at Applied Statistics

Gambaran operator OLAP di SQL

Ekstensi SQL untuk memudahkan operasi OLAP

GROUP BY CUBE
GROUP BY ROLLUP
GROUP BY GROUPING SETS

Tabel renting_extended

Beberapa baris awal tabel 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

Contoh kueri dengan operator GROUPING SETS:

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

Nama kolom dalam tanda kurung mewakili satu level agregasi.
GROUP BY GROUPING SETS mengembalikan UNION atas beberapa kueri GROUP BY.

GROUPING SETS dan kueri GROUP BY

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

Hitung penyewaan film untuk setiap kombinasi unik country dan genre.
Ekspresi di 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 dan kueri GROUP BY

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

Hitung penyewaan film per negara.
Ekspresi di GROUPING SETS: (country)

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

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

GROUPING SETS dan kueri GROUP BY

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

Hitung penyewaan film per genre.
Ekspresi di GROUPING SETS: (genre)

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

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

GROUPING SETS dan kueri GROUP BY

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

Agregasi total: hitung semua penyewaan film.
Ekspresi di GROUPING SETS: ()

SELECT COUNT(*)
FROM renting_extended;

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

Notasi untuk GROUP BY GROUPING SETS

GROUP BY GROUPING SETS (...)

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

UNION atas 4 kueri sebelumnya.
Gabungkan semua info tabel pivot dalam satu kueri.
Setara dengan GROUP BY CUBE (country, genre).

Hasil dengan operator 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  |

Hitung jumlah sewa dan rating rata-rata

Gabungkan hanya agregasi terpilih:
- country dan genre
- genre

Gunakan jumlah penyewaan dan rata-rata rating untuk agregasi.

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

Hitung jumlah sewa dan rating rata-rata

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       |

Ayo berlatih!

Pengambilan Keputusan Berbasis Data dengan SQL