TUTORIAL Mencari nilai Max dan Min
Aggregasi data dengan Group Function (GROUP BY, ROLLUP dan CUBE) 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. 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 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 di c:\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 2 AMIR
L Kecamatan 1 L Kecamatan 1
3 KUSNANTO
Kelurahan 1 Kelurahan 1
L Kecamatan 1
Kelurahan 1
4 MELISA
P Kecamatan 1
Kelurahan 1
5 KUSNO
L Kecamatan 1
Kelurahan 1
6 ANDRIANI 7 AHMAD
P Kecamatan 1 L Kecamatan 1
Kelurahan 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
Kecamatan 2
Kelurahan 1
19
Kelurahan 2
6
Total --->
25
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 P
L
7
18 25
Kecamatan 2 P
L 10 15
L
12
P
28
5
.
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.
