Melakukan Grouping pada oracle

Seringkali kita ingin menampilkan data dari suatu kelompok, misalnya menampilkan data rata-rata gaji karyawan per departemen. Untuk melakukan itu kita memerlukan perintah yang dapat melakukan grouping data, Oracle menyediakan dua, yaitu GROUP BY dan HAVING. GROUP BY berfungsi untuk mengelompokan data per kolom, sedangkan HAVING berguna untuk melakukan filtering terhadap grup yang sudah terbentuk itu. Sehingga untuk dapat menampilkan data rata-rata gaji karyawan per departemen perintahnya adalah:

1	SELECT department_id, AVG(salary) FROM employees GROUP BY department_id

Saya sengaja tidak menampilkan nama departemen untuk menghilangkan kompleksitas query sehingga lebih mudah dibaca. Perhatikan bagaimana di dalam clausa SELECT saya menyebutkan nama kolom, ada dua kolom yang dipakai, yaitu department_id dan salary. Untuk department_id kolom tersebut muncul di dalam clausa GROUP BY, sedangkan kolom salary dijadikan parameter fungsi AVG. Apa kesimpulan yang bisa kita tarik?

Untuk penggunaan clausa GROUP BY, SEMUA kolom yang tampil di dalam clausa SELECT HARUS juga tampil di clausa GROUP BY ATAU menjadi parameter dalam fungsi aggreegate. Jika tidak ada GROUP BY maka semua kolom yang ada di clausa SELECT harus berada dalam fungsi aggreegate atau TIDAK sama sekali. Kenapa hal ini terjadi? Misalnya saja clausa GROUP BY kita hilangkan sehingga query menjadi seperti:

1	SELECT department_id, AVG(salary) FROM employees

Muncul error bukan, di sini Oracle mengalami konflik dalam menangani query. Pertama dia melihat kita meminta untuk menampilkan data dalam kolom department_id, data ini tentunya berasal lebih dari satu baris bukan. Sedangkan Oracle melihat kita juga ingin menampilkan rata-rata dari kolom salary, datanya tentu hanya satu. Di sinilah terjadi konflik dimana pertama kita ingin menampilkan data dari baris per baris (kolom department_id) lalu kita ingin menampilkan data tentang rata-rata dari kolom salary.

Setelah kita berhasil menampilkan data rata-rata gaji karyawan per departemen, sekarang kita ingin menampilkan data rata-rata gaji karyawan per departemen dimana rata-rata gajinya di atas 4000

1	SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 4000

Urutan eksekusi perintah SELECT

Sebenarnya bagaimana sih alur eksekusi perintah jika kita ingin mengambil data dari database. Kita sudah mengenal bahwa untuk mengambil/menampilkan data kita gunakan perintah SELECT yang memiliki bentuk umum seperti berikut:

SELECT [DISTINCT | ALL] {* | nama_kolom [AS nama_kolom_alias][,...]}
FROM nama_tabel
[WHERE kondisi]
[GROUP BY nama kolom HAVING kondisi]
[ORDER BY]

*[] menyatakan optional, boleh ada, boleh juga tidak

Urutan eksekusinya adalah sebagai berikut:

1. FROM Menyatakan tabel mana yang datanya mau kita ambil.
2. WHERE Melakukan filtering terhadap data dari tabel tersebut.
3. GROUP BY Melakukan gruping berdasarkan kolom.
4. HAVING Melakukan filtering grup yang terbentuk.
5. SELECT Menyatakan data dari kolom mana saja yang akan ditampilkan.
6. ORDER BY Melakukan pengurutan data berdasarkan kolom tertentu.

Sehingga perintah

1	SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 4000

Dapat kita baca urutan eksekusinya seperti ini

1. Ambil data dari tabel employees.
2. Buat grup data berdasarkan kolom department_id.
3. Filter grup yang terbentuk tersebut dengan kondisi > 4000.
4. Tampilkan data dari department_id, lalu tampilkan juga data rata-rata kolom gaji yang dihitung per grup.

Sekarang untuk lebih memahami proses gruping yang terjadi kita perhatikan dua gambar di bawah ini:

Gambar yang disebelah kiri adalah hasil dari perintah SELECT tanpa melakukan gruping, hanya diurut berdasarkan department_id saja, gambar di sebelah kanan adalah hasil perintah SELECT dengan melakukan gruping untuk kolom department_id. Kita lihat di gambar sebelah kanan, tabel akan terbagi menjadi tiga grup (lihat warnanya). Eksekusi perintah AVG(salary) akan menghitung nilai rata-rata untuk masing-masing grup.

Lalu mengapa kita harus memahami urutan eksekusi perintah SELECT ini? Jawabannya adalah untuk optimasi query. Secara umum, lebih sedikit record yang dikerjakan maka semakin cepat query dieksekusi. Ini semakin terasa jika kita juga menggunakan GROUP BY, semakin sedikit record yang ingin digrup maka semakin cepat query dieksekusi. Misal kita ingin melihat data rata-rata gaji karyawan per departemen dimana hanya department_id lebih besar dari 30 saja yang ditampilkan dan rata-rata gajinya lebih dari 4000, kita dapat menuliskan query seperti berikut:

1	SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVINGdepartment_id > 30 AND AVG(salary) > 4000

Tidak ada masalah dengan hasilnya bukan. Tetapi query ini jelas tidak efektif. Untuk melihatnya kita perhatikan alur eksekusi query ini:

1. Ambil data dari tabel employees.
2. Grup data tersebut berdasarkan department_id.
3. Filter grup yang sudah terbentuk tadi sehingga hanya menghasilkan data yang nilai depratment_id lebih dari 30 serta rata-rata gajinya lebih dari 4000.
4. Tampilkan data dari department_id dan rata-rata kolom AVG.

Masalah disebabkan pada eksekusi no 3. Di sini grup yang sudah terbentuk ingin difilter berdasarkan department_id. department_id yang lebih kecil atau sama dengan 30 akan dihilangkan. Proses gruping pada no 2 dilakukan untuk seluruh record pada tabel employees, jika nantinya kita tidak ingin kehadiran data dengan department_id lebih kecil atau sama dengan 30 mengapa kita harus melakukan gruping seluruh tabel? Akan lebih baik sebelum proses gruping datanya sudah difilter terlebih dahulu sehingga record yang dikerjakan pada proses gruping menjadi lebih sedikit. Bentuk perintahnya akan menjadi

1	SELECT department_id, AVG(salary) FROM employees WHERE department_id > 30 GROUP BYdepartment_id HAVING AVG(salary) > 4000

View yang mengandung grup

View merupakan tabel semu, biasanya digunakan untuk menyembunyikan data yang sebenarnya. Jika view ingin dibuat dari query yang mengandung fungsi aggreegate, maka kolom tersebut harus mempunyai nama alias. Alias sendiri merupakan nama lain dari kolom yang kita berikan, umumnya pemberian nama alias ini untuk mempermudah membaca hasil query.

1	CREATE OR REPLACE view salary_avg AS SELECT department_id, AVG(salary) AS Gaji_rata FROMemployees GROUP BY department_id HAVING department_id > 30 AND AVG(salary) > 4000

2	DESC salary_avg

3

4	SELECT * FROM salary_avg

Perhatikan query di bawah ini

1	SELECT department_id, gaji_rata FROM salary_avg

Hasilnya sama saja dengan yang di atas. Di sini kita menyebutkan nama kolom dari view, nama kolom ini harus sesuai dengan nama kolom dari perintah SELECT yang membangun view ini. Perhatikan untuk nama kolom ‘gaji_rata’, bandingkan dengan nama kolomnya saat kita membuat view ini ‘Gaji_rata’, sedikit berbeda bukan. Tapi mengapa query ini dapat dijalankan tanpa error?

Ternyata Oracle akan memaksa nama kolom ke dalam huruf besar, pada saat view dibuat nama kolomnya kita tulis ‘Gaji_rata’, Oracle menyimpannya dengan nama ‘GAJI_RATA’. Begitu juga pada saat kita mengeksekusi query dengan menggunakan nama kolom ‘gaji_rata’, Oracle juga akan menterjemahkannya menjadi ‘GAJI_RATA’. Pengecualian dilakukan jika pada saat kita mendefinisikan view kita menggunakan tanda petik dua pada nama kolom. Jika kita lakukan maka Oracle akan menyimpan nama kolomnya sesuai dengan yang berada di dalam petik dua tersebut, misal “GaJi_rAta” akan disimpan menjadi GaJi_rAta juga. Kesulitan akan dialami pada saat melakukan query SELECT, Oracle tidak akan dapat menemukan kolom bernama ‘gaji_rata’ karena yang tersimpan adalah GaJi_rAta bukan GAJI_RATA. Keciali nama kolom yang disebutkan dalam SELECT menggunakan tanda petik dua juga dan bentuknya sama.