Menggunakan SingleRow
Function untuk Mengcustomize Output
Fungsi fungsi SQL
Fungsi fungsi sangat mendukung fitur pada SQL. Fungsi fungsi tersebut dapat digunakan untuk
melakukan hal berikut:
Melakukan perhitunganperhitungan·
pada data
Memodifikasi itemitem·
data individual
Memanipulasi keluaran dari kelompok barisbaris·
Format untuk tampilan tanggal dan angkaangka·
Konversi tipe data tipe data kolom·
Fungsi fungsi sangat mendukung fitur pada SQL. Fungsi fungsi tersebut dapat digunakan untuk
melakukan hal berikut:
Melakukan perhitunganperhitungan·
pada data
Memodifikasi itemitem·
data individual
Memanipulasi keluaran dari kelompok barisbaris·
Format untuk tampilan tanggal dan angkaangka·
Konversi tipe data tipe data kolom·
Fungsi Fungsi SQL (lanjutan)
Ada dua tipe dari fungsi fungsi
:
Singlerows·
functions (fungsi fungsi baris tunggal)
Multiple
row functions (fungsi fungsi banyak baris)·
Single Row Functions
Fungsi fungsi
ini hanya digunakan pada baris baris tunggal dan
mengembalikan satu hasil per baris.
Ada tipe tipe berbeda dari single row functions.
Pelajaran ini mencakup halhal
berikut :
Character
(karakter)·
Number
(angka)·
Date
(tanggal)·
Conversion
(konversi)·
General
(umum)·
Multiplerow Functions
Fungsi fungsi dapat memanipulasi kelompok dari
barisbaris untuk memberi suatu hasil baris baris
per kelompok. Fungsivfungsi ini dikenal juga
sebagai group functions
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 : ***** ConnectedMari 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 BYSQL> 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 CUBEROLLUP 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.sqlTabel 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 10Ternyata, 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. 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.
source: http://www.klik-oracle.com/?p=59
