AGREGASI DATA di mysql

Group function merupakan fungsi yang akan akan memproses banyak data dan menghasilkan satu atau beberapa output sesuai dengan pengelompokan yang dilakukan. Untuk pengelompokan digunakan klausa GROUP BY. Berbeda dengan Single-row function yang akan memproses satu data dan menghasilkan satu output. Untuk melihat gambar ilustrasi perbedaan antara single-row function dan group function lagi Materi 7 tentang Single-row function.
Jenis group function :
- AVG untuk mencari nilai rata-rata
- COUNT untuk menghitung jumlah data/baris
- MAX untuk mencari nilai paling besar
- MIN untuk mencari nilai paling kecil
- STDDEV untuk menghitung nilai standard deviasi (statistik)
- SUM untuk menghitung nilai total
- VARIANCE untuk menghitung nilai variance (statistik)

Fungsi AVG, SUM, STDDEV dan VARIANCE hanya berlaku untuk data bertipe numerik, tidak bisa untuk karakter atau date.

Syntax lengkap untuk perintah SQL dengan GROUP BY :

SELECT group_function(kolom),...
FROM tabel
[WHERE kondisi]
[GROUP BY group_function(kolom)|kolom_alias]
[HAVING kondisi]
[ORDER BY kolom|kolom_alias]

Untuk mempraktekkan group function, gunakan tabel EMP yang ada di schema SCOTT, untuk itu tentunya kita harus login dulu sebagai user SCOTT sbb:

SQL> connect scott
Password : *****
Connected

Mari kita periksa data di tabel EMP

SQL> set pagesize 50
SQL> SELECT deptno, ename, sal
  2  FROM emp
  3  ORDER BY deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING             5000
        10 MILLER           1300
        20 JONES            2975
        20 FORD             3000
        20 ADAMS            1100
        20 SMITH             800
        20 SCOTT            3000
        30 WARD             1250
        30 TURNER           1500
        30 ALLEN            1600
        30 JAMES             950
        30 BLAKE            2850
        30 MARTIN           1250

14 rows selected.

Kita diminta untuk menampilkan nilai salary yang paling tinggi, untuk itu gunakan function MAX sperti ini:

SQL> SELECT max(sal)
  2  FROM emp;

  MAX(SAL)
----------
      5000

Function MAX akan memperoses semua baris data yang ada (14 row) untuk menghasilkan satu nilai maksimum (5000). Nilai 5000 berarti salary tertinggi dalam perusahaan tersebut. Jika Anda menginginkan salary tertinggi di setiap department (DEPTNO) tambahkan klausa GROUP BY deptno, perintah diatas menjadi :

SQL> SELECT deptno, max(sal)
  2  FROM emp
  3  GROUP BY deptno;

    DEPTNO   MAX(SAL)
---------- ----------
        30       2850
        20       3000
        10       5000

Silahkan dicoba dengan group funtion yang lain.

HAVING

Jika dalam single-row function untuk menyeleksi data kita menggunakan klausa WHERE. Sedangkan untuk menyeleksi data dimana data yang akan kita seleksi merupakan hasil dari group function digunakan HAVING. Sebagai contoh, tampilkan Department yang rata-rata salary-nya diatas 2500.

Mari kita lihat dulu rata-rata gaji per-Department:

SQL> SELECT deptno, avg(sal)
  2  FROM emp
  3  GROUP BY deptno;

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667
        20       2175
        10 2916.66667

Jika yang ingin ditampilkan adalah department yang rata-rata salarynya diatas 2500 berarti yang dijadikan pembanding adalah AVG(SAL) karena ini merupakan group function maka harus digunakan HAVING untuk menyeleksinya. HAVING boleh diletakkan sebelum atau sesudah GROUP BY

SQL> SELECT deptno, avg(sal)
  2  FROM emp
  3  GROUP BY deptno
  4  HAVING avg(sal) > 2500;

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667

GROUP BY dengan ROLLUP dan CUBE

ROLLUP dan CUBE adalah group function yang sering digunakan dalam desain query untuk data warehouse. ROOLUP digunakan untuk menghitung nilai sub total dan total dari suatu group data sedangkan CUBE digunakan untuk menghitung sub total dari group data dalam beberapa dimensi.

Ok, untuk lebih memahami pemakaian ROLLUP dan CUBE buatlah tabel penduduk beserta isinya dengan menjalankan script create_penduduk.sql, ambil script disini. Setelah didownload dan letakkan dic:\create_penduduk.sql

Jalankan script dengan cara berikut:

SQL> @c:\create_penduduk.sql

Tabel penduduk berisi data sebanyak 40 penduduk yang dibagi dalam 2 kecamatan dan 4 kelurahan, jenis kelamin laki-laki ‘L’ dan perempuan ‘P’. Datanya seperti ini :

SQL> select * from penduduk;

ID NAMA            J KECAMATAN       KELURAHAN
---------- --------------- - --------------- ---------------
1 JOHAN           L Kecamatan 1     Kelurahan 1
2 AMIR            L Kecamatan 1     Kelurahan 1
3 KUSNANTO        L Kecamatan 1     Kelurahan 1
4 MELISA          P Kecamatan 1     Kelurahan 1
5 KUSNO           L Kecamatan 1     Kelurahan 1
6 ANDRIANI        P Kecamatan 1     Kelurahan 1
7 AHMAD           L Kecamatan 1     Kelurahan 1

--> dan seterusnya...

Kita diminta untuk menghitung jumlah penduduk per-kelurahan dan per-kecamatan. Apakah hal ini bisa diselesaikan dengan GROUP BY ?, mari kita coba.

SQL> SELECT kecamatan, kelurahan, COUNT(id) as "JlhPenduduk"
  2  FROM penduduk
  3  GROUP BY kecamatan, kelurahan
  4  ORDER BY kecamatan, kelurahan;

KECAMATAN       KELURAHAN       JlhPenduduk
--------------- --------------- -----------
Kecamatan 1     Kelurahan 1              19
Kecamatan 1     Kelurahan 2               6
Kecamatan 2     Kelurahan 3               5
Kecamatan 2     Kelurahan 4              10

Ternyata, dengan GROUP BY kita hanya bisa menampilkan sub total per kelurahan sedangkan total total per-kecamatan total keseluruhan penduduk tidak bisa dimunculkan. Untuk itu kita gunakan GROUP BY dengan ROLLUP, sbb:

SQL> SELECT kecamatan, kelurahan, COUNT(id) as "JlhPenduduk"
  2  FROM penduduk
  3  GROUP BY ROLLUP(kecamatan, kelurahan)
  4  ORDER BY kecamatan, kelurahan;

KECAMATAN       KELURAHAN       JlhPenduduk
--------------- --------------- -----------
Kecamatan 1     Kelurahan 1              19
Kecamatan 1     Kelurahan 2               6
Kecamatan 1                              25
Kecamatan 2     Kelurahan 3               5
Kecamatan 2     Kelurahan 4              10
Kecamatan 2                              15
                                         40

7 rows selected.

.
Kita bisa mempermanis tampilan dengan sedikit trik seperti ini :

SQL> BREAK ON kecamatan SKIP 1
SQL> SELECT kecamatan,
  2         NVL(kelurahan,'Total --->') As "Kelurahan",
  3         COUNT(id) AS "JlhPenduduk"
  4  FROM penduduk
  5  GROUP BY ROLLUP(kecamatan, kelurahan)
  6  ORDER BY kecamatan, kelurahan;

KECAMATAN       Kelurahan       JlhPenduduk
--------------- --------------- -----------
Kecamatan 1     Kelurahan 1              19
                Kelurahan 2               6
                Total --->               25

Kecamatan 2     Kelurahan 3               5
                Kelurahan 4              10
                Total --->               15

                Total --->               40

7 rows selected.

Dengan ROLLUP dan sedikit modifikasi tampilan masalah menghitung subtotal terselesaikan.
Catatan : untuk fungsi NVL silahkan lihat di single-row function.

Bagaimana dengan CUBE?, seperti sudah disebutkan diatas, CUBE digunakan untuk membuat cross-tabulasi(sub-total lebih dari satu dimensi). Misal kita diminta untuk menghitung jumlah penduduk per-kecamatan berdasarkan jenis kelaminnya, kemudian setelah itu juga dihitung berapa jumlah penduduk berdasarkan jenis kelamin tanpa memperhatikan kecamatannya. Mungkin pembaca masih sedikit bingung mencerna kalimat diatas  . Sebaiknya kita coba saja langsung, caranya adalah sbb:

SQL> SELECT kecamatan,
  2         jk as "JenKelamin",
  3         COUNT(id) as "JlhPenduduk"
  4  FROM penduduk
  5  GROUP BY CUBE(kecamatan, jk)
  6  ORDER BY kecamatan, jk;

KECAMATAN       J JlhPenduduk
--------------- - -----------
Kecamatan 1     L           7
                P          18
                           25

Kecamatan 2     L           5
                P          10
                           15

                L          12
                P          28
                           40
9 rows selected.

Apakah Anda masih bingung membaca hasil query diatas?, sebenarnya secara simple, hasil query diatas bisa ditampilkan dengan bentuk tabel seperti ini :

Dengan ilustrasi gambar diatas mudah-mudahan Anda bisa lebih memahami tentang CUBE.

Demikian ulasan tentang Aggregasi, GROUP BY, ROLLUP dan CUBE. Semoga bermanfaat.