Bermain Angka dengan Oracle bagian II (tamat)

Setelah di bagian satu kita melihat fungsi yang bekerja untuk nilai tunggal, sekarang saatnya kita lihat penggunaaan fungsi untuk suatu grup angka. Fungsi yang bekerja dalam suatu grup nilai biasanya disebut dengan fungsi aggregate, biasanya hanya menampilkan informasi mengenai grup tersebut. Beberapa fungsi berguna untuk urusan statistik. Fungsi aggregate tidak akan mengikutsertakan NULL dalam perhitungan. Untuk lebih jelasnya kita langsung saja praktekan, sebelum itu kita perlu menyiapkan tabel sebagai alat bantu. Setelah pada bagian pertama kita membuat tabel mat dan dan diisi data, selanjutnya kita buat tabel temp:

CREATE TABLE temp(
kota VARCHAR2(13) NOT NULL,
tanggal DATE NOT NULL,
siang NUMBER(3,1),
malam NUMBER(3,1)
);

Lalu isikan juga datanya

INSERT INTO temp VALUES(‘Jakarta’, TO_DATE(’21-Mar-03′), 62.5, 42.3);
INSERT INTO temp VALUES(‘Jakarta’, TO_DATE(’22-Jun-03′), 51.1, 71.9);
INSERT INTO temp VALUES(‘Jakarta’, TO_DATE(’23-Sep-03′), NULL, 42.3);
INSERT INTO temp VALUES(‘Jakarta’, TO_DATE(’22-Dec-03′), 52.6, 39.8);
INSERT INTO temp VALUES(‘Bandung’, TO_DATE(’21-Mar-03′), 39.9, -1.2);
INSERT INTO temp VALUES(‘Bandung’, TO_DATE(’22-Jun-03′), 85.1, 66.7);
INSERT INTO temp VALUES(‘Bandung’, TO_DATE(’23-Sep-03′), 99.8, 82.6);
INSERT INTO temp VALUES(‘Bandung’, TO_DATE(’22-Dec-03′), -7.2, -1.2);

AVG, COUNT, MAX, MIN, dan SUM

SELECT AVG(siang), COUNT(siang), MAX(siang), MIN(siang), SUM(siang) FROM temp WHERE kota = ‘Jakarta’

Perhatikan gambar 4, untuk baris ke tiga nilai siang adalah NULL. Ini akan mempunyai pengaruh kepada fungsi AVG, karena fungsi ini tidak kebal terhadap ketidakhadiran data. Untuk kota Jakarta ada 3 data yang terisi dalam kolom siang, sementara dalam perhitungan AVG (rata-rata) pembagi yang digunakan adalah total kesuluruhan baris yang dihasilkan dari clasusa WHERE kota = ‘Jakarta’ yaitu 4. Perhatikan dalam kolom COUNT hasil perhitungan, di sana tampil angka 3, bukan 4, sebab NULL di baris ketiga tidak dihitung. MAX akan mencari nilai maksimum dan MIN akan mencari nilai minimum. SUM akan menjumlahkan semua nilai dari kolom siang tanpa mengikutsertakan NULL.

Kita juga dapat mengkombinasikan fungsi nilai tunggal dengan fungsi aggregate. Di sini kita ingin melihat rata-rata perbedaan suhu siang dan malam untuk kota Bandung

SELECT AVG(ABS(siang – malam)) FROM temp WHERE kota = ‘Bandung’

Kenapa kita gunakan fungsi absolut? Sebab hasil dari fungsi pengurangan dapat saja bernilai negatif, sedangkan d sini kita hanya membutuhkan perbedaan nilai antara siang dan malam, tanda negatif tidak kita perhitungkan. Lalu bagaimana jika kita ingin menggabungkan fungsi aggregate dengan fungsi aggregate yang lain, misal

SELECT SUM(AVG(siang)) FROM temp

Kenapa hasilnya error? Ini disebabkan hasil dari AVG adalah nilai tunggal sedangkan SUM adalah fungsi yang bekerja untuk grup.

Sekarang kita lihat contoh berikutnya. Di sini kita ingin menampilkan kota yang memiliki suhu siang tertinggi beserta tanggalnya. Kebanyakan dari kita pasti akan menuliskan query seperti ini

SELECT kota, tanggal, MAX(siang) FROM temp

Dan hasilnya error  . Kenapa bisa error? Dalam query ini pertama kita ingin menampilkan baris dalam kolom kota dan tanggal, sedangkan fungsi MAX(siang) hanya mengembalikan satu nilai. Bertentangan bukan?. Untuk memecahkan permasalahan kita dapat menuliskan subquery seperti ini

SELECT kota, tanggal, siang FROM temp WHERE siang = (SELECT MAX(siang) FROM temp)

STDDEV dan VARIANCE

Bagi yang sudah belajar statistik tentulah familiar dengan istilah Standard Deviasi dan Variance. Berikut ini contoh penggunaannya

SELECT MAX(siang), AVG(siang), MIN(siang), STDDEV(siang), VARIANCE(siang) FROM temp WHERE kota = ‘Jakarta’

Terakhir kita akan melihat penggunaan fungsi yang digunakan dalam deretan angka. Fungsi-fungsi ini akan bekerja pada sekelompok kolom dalam satu baris (beda dengan fungsi aggregate yang bekerja pada satu kolom saja). Dengan kata lain, fungsi-fungsi ini akan membandingkan nilai dari masing-masing kolom dari satu baris lalu mencari nilai tertinggi atau terendah. Sebagai contoh perhatikan hasil perintah berikut

SELECT kota, tanggal, GREATEST(malam, siang) AS tertinggi, LEAST(malam, siang) AS terendah FROM temp

Perhatikan pada baris ketiga, nilainya NULL buka. Ini disebabkan fungsi GREATES dan LEAST tidak kebal terhadap NULL (sama seperti fungsi nilai tunggal). Jika kita lihat hasilnya, fungsi GREATES dan LEAST sama seperti fungsi MAX dan MIN. Hanya saja fungsi MAX dan MIN bekerja dalam 1 kolom.