KATEGORI: DATABASE
Operasi SELECT MySQL Penting Untuk Analisa Data
Membahas penggunaan query SELECT yang sering digunakan untuk analisa data dalam MySQL.
Pada tahun 2021 ini, saya memiliki tujuan untuk mendalami ilmu Data Science. Mulai dari matematika, statistika lalu telah sampai pada penggunaan query SELECT Advanced.
Sepengetahuan saya, sebagai seorang Data Analyst tak jarang kita harus mengolah data langsung didalam aplikasi Database MySQL. Pengolahan data ini sangat penting, karena dapat menghasilkan insight yang sangat berguna untuk perusahaan, sehingga pengambilan keputusan perusahaan selalu didasari oleh data.
Lalu kenapa belajarnya cuman query SELECT? Kan ada INSERT, CREATE, ALTER dan banyak lagi.
Pengelolaan data oleh seorang Data Analyst tidak sampai pada mengubah, membuat atau menghapus data di tabel secara permanen. Itu merupakan pekerjaan seorang Data Engineer atau orang yang memiliki tanggung jawab terhadap server.
⚠️Diharapkan kalian sudah membaca dan mempraktikan tutorial sebelumnya!
Sebelum kita banyak bermain dengan perintah SELECT, kita perlu memiliki data yang akan diolah. Tetapi, karena pada tutorial sebelumnya kita telah menyiapkan database juga, mari tetap menggunakan database tersebut.
Pengetahuan Data
Ada 3 tabel yang akan kita gunakan, diantaranya:
- dependents, tabel ini mengandung karyawan yang memiliki tanggungan. Seluruh karyawan disini memiliki tanggungan yaitu anak.
- employees, tabel ini mengandung detail atribut tentang karyawan. Data pribadi karyawan yang berkaitan dengan perusahaan di simpan disini.
- departments, tabel ini berisikan daftar-daftar nama departemen.
Kolom yang penting harus kalian perhatikan disini adalah kolom yang terhubung dengan kolom lainnya. Seperti kolom employee_id di tabel dependents yang terhubung dengan kolom employee_id pada tabel employees juga.
Setelah kita mengenal atribut-atribut data yang akan diolah, sekarang kita perlu masuk ke dalam databasenya.
USE employees;
GROUP BY
Klausa ini penting untuk mengelompokkan data yang sama di dalam sebuah kolom atau beberapa kolom. Kita gunakan tabel employees untuk latihan. Simak cerita saya.
Jika kalian perhatikan kolom pada tabel employees mengandung department_id. Data yang ada didalamnya terhubung pada kolom department_id di tabel departments. Lalu data pada setiap barisnya ada yang sama, tentu saja hal ini terjadi, karena beberapa orang bisa tergabung dalam 1 departemen.
Tugas kita kali ini adalah menghitung banyaknya orang yang tergabung pada setiap departemen.
mysql> SELECT department_id, COUNT(employees_id) AS banyak_orang FROM employees GROUP BY department_id;
+---------------+--------------+
| department_id | banyak_orang |
+---------------+--------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 6 |
| 4 | 1 |
| 5 | 7 |
| 6 | 5 |
| 7 | 1 |
| 8 | 6 |
| 9 | 3 |
| 10 | 6 |
| 11 | 2 |
+---------------+--------------+Bandingkan dengan:
mysql> SELECT COUNT(employees) AS banyak_orang FROM employees;
+--------------+
| banyak_orang |
+--------------+
| 40 |
+--------------+Keterangan:
* Fungsi COUNT menghitung banyaknya baris yang terkandung dalam kolom.
Apa yang terjadi sebenarnya, kenapa hasilnya bisa berbeda?
Pada contoh pertama, fungsi COUNT() tidak menghitung langsung banyaknya baris yang ada di dalam kolom department_id. Karena klausa GROUP BY meng-intruksikan bahwa, fungsi tersebut harus menghitung banyaknya baris dalam kolom employee_id berdasarkan department_id.
Tetapi pada contoh kedua, fungsi COUNT() melakukan hal berlawanan dari contoh pertama.
HAVING
Selain klausa WHERE, GROUP BY mempunyai keunikan sendiri. Perbedaan klausa HAVING dan WHERE adalah seperti perbedaan fungsi aggregate dan fungsi skalar.
- HAVING digunakan untuk membuat kondisi yang membutuhkan fungsi aggregate.
- WHERE merupakan klausa berlawanan dari fungsi klausa HAVING.
Si bos: “Waduhh, pemasukkan perusahaan bulan ini mengalami penurunan, apa saranmu ojan?”
Ojan: “Sebaiknya kita kurangi 25% gaji karyawan dari biasanya berdasarkan departemennya.”
Si bos: “Jangan kurangi untuk gaji karyawan pada departemen yang dibawah sudah dibawah rata-rata ya jan. Cukup kurangi gaji karyawan pada departemen yang diatas rata-rata saja.”
Tampaknya sudah tercapai kesepakatan antara HR dan bos. HR tersebut harus mencari tahu departemen mana saja yang diatas rata-rata. Rata-rata yang dimaksud dalam konteks ini adalah rata-rata gaji tiap departemen yang dicari rata-ratanya lagi.
mysql> SELECT department_id, AVG(salary) AS min_department FROM employees GROUP BY department_id HAVING min_department > AVG(min_department);
+---------------+----------------+
| department_id | min_department |
+---------------+----------------+
| 2 | 9500.000000 |
| 4 | 6500.000000 |
| 7 | 10000.000000 |
| 9 | 19333.333333 |
+---------------+----------------+Keterangan:
* Penggunaan AS dalam GROUP BY dan HAVING sangat penting. Catat ini!
Bandingkan dengan
mysql> SELECT department_id, AVG(salary) AS min_department FROM employees WHERE min_department > AVG(min_department) GROUP BY department_id;
ERROR 1054 (42S22): Unknown column 'min_department' in 'where clause'Apa lagi yang terjadi diantara kedua query tersebut?
Pada contoh query pertama, rata-rata gaji dihitung berdasarkan department_id, tentu kalian sudah tahu hal tersebut. Lalu klausa HAVING menambahkan kondisi, dimana yang ditampilkan hanya departemen yang memiliki gaji diatas rata-rata gaji keseluruhan departemen. Ditunjukkan pada kondisi min_department > AVG(min_department).
Lalu contoh query kedua, yang menggunakan klausa WHERE kenapa bisa salah? Kondisi min_department > AVG(min_department) di letakkan sebelum GROUP BY, tentu saja hal itu tidak bisa dilakukan karena urutannya seperti gambar dibawah ini.
Artinya kolom min_department hanya dapat digunakan setelah klausa GROUP BY dan juga harus dengan klausa HAVING.
ORDER BY
Klausa ini penting untuk menyortir data dalam sebuah kolom atau beberapa kolom. Kita coba gunakan klausa ini dengan tabel employees.
Satuan
Sebelumnya kita tau tabel tersebut mengandung first_name dan last_name, yang kita ingin lakukan sekarang adalah menyortir kolom first_name tersebut, agar menampilkan nama dari huruf paling awal terlebih dahulu.
mysql> SELECT first_name, last_name FROM employees ORDER BY first_name;
+-------------+-------------+
| first_name | last_name |
+-------------+-------------+
| Adam | Fripp |
| Alexander | Hunold |
| Alexander | Khoo |
| ... | ... |
| Masih | banyak lagi |Lalu bagaimana jika kita ingin menyortir kolom first_name dengan urutan sebaliknya, artinya huruf paling terakhir terlebih dahulu kemudian menuju ke huruf ter-awal.
mysql> SELECT first_name, last_name FROM employees ORDER BY first_name DESC;
+-------------+-------------+
| first_name | last_name |
+-------------+-------------+
| William | Gietz |
| Valli | Pataballa |
| Susan | Mavris |
| ... | ... |
| Masih | banyak lagi |Keterangan:
* Cukup menambahkan parameter DESC setelah nama kolomnya.
Bercabang
Artinya penyortiran tidak terbatas hanya berdasarkan satu kolom saja, bisa beberapa kolom juga.
Coba lagi contoh, kita pertama ingin menyortir urutan id departemen agar bisa menampilkan angka ter-awal dulu. Kemudian setelah kolom department_id disortir, kolom first_name disortir lagi.
mysql> SELECT first_name, last_name, department_id FROM employees ORDER BY department_id, first_name;
+-------------+-------------+---------------+
| first_name | last_name | department_id |
+-------------+-------------+---------------+
| Jennifer | Whalen | 1 |
| Michael | Hartstein | 2 |
| Pat | Fay | 2 |
| Alexander | Khoo | 3 |
| Den | Raphaely | 3 |
| Guy | Himuro | 3 |
| ... | ... | ... |
| Masih | Banyak | lagi |Keterangan:
* Seperti klausa SELECT, pada klausa ORDER BY juga sama. Jika ingin menyortir berdasarkan 2 atau lebih kolom cukup menambahkan koma (,) dan nama kolomnya.
Sedikit penjelasan, pasti kalian bingung kenapa namanya tidak tersortir dengan konsisten. Sebenarnya itu konsisten, hanya saja penyortirannya berdasarkan setiap angka di department_id. Cobalah bandingkan dengan:
mysql> SELECT first_name, last_name, department_id FROM employees ORDER BY department_id;
+-------------+-------------+---------------+
| first_name | last_name | department_id |
+-------------+-------------+---------------+
| Jennifer | Whalen | 1 |
| Pat | Fay | 2 |
| Michael | Hartstein | 2 |
| Karen | Colmenares | 3 |
| Guy | Himuro | 3 |
| ... | ... | ... |
| Masih | Banyak | Lagi |Bersamaan dengan klausa GROUP BY, kita coba lanjutkan penggunaan tabel sebelumnya. Tetapi data yang ingin ditampilkan sedikit di ubah:
Kita ingin menyortir berdasarkan banyaknya gaji setiap departemen, sehingga bisa diketahui departemen mana yang paling banyak menerima gaji.
mysql> SELECT department_id, SUM(salary) AS jumlah_gaji FROM employees GROUP BY department_id ORDER BY jumlah_gaji DESC;
+---------------+-------------+
| department_id | jumlah_gaji |
+---------------+-------------+
| 9 | 58000.00 |
| 8 | 57700.00 |
| 10 | 51600.00 |
| ... | ... |
| Masih banyak | lagi |Kayaknya kalau kita cuman tau department_id nya aja gak cukup deh, harus sama nama departemennya juga. * Mungkin kalian berpikir seperti itu, oke deh saya kasih spoiler poin materi selanjutnya sekalian disini berarti.
mysql> SELECT departments.department_name, employees.department_id, SUM(salary) AS jumlah_gaji FROM employees INNER JOIN departments ON employees.department_id = departments.department_id GROUP BY employees.department_id ORDER BY jumlah_gaji DESC;
+------------------+---------------+-------------+
| department_name | department_id | jumlah_gaji |
+------------------+---------------+-------------+
| Executive | 9 | 58000.00 |
| Sales | 8 | 57700.00 |
| Finance | 10 | 51600.00 |
| ... | ... | ... |
| Masih | banyak | lagi |Secara keseluruhan, querynya tetap sama. Hanya saja sedikit ditambahkan klausa INNER JOIN, kalian akan pelajari itu di poin materi selanjutnya.
JOIN
Kita sebelumnya tahu bahwa ketiga tabel sebelumnya memiliki kolom yang saling terhubung satu sama dengan yang lain. Pada tutorial SQL pertama kali sudah pernah dijelaskan, inilah keuntungan menggunakan RDBMS.
Sebelum lebih jauh mengenal query-query join, kalian perlu mengenal juga tentang Diagram Venn. Penting banget buat menguasai konsep dasar dari JOIN dalam MySQL. 👇
Syarat dasar dari menggunakan JOIN ini adalah setiap tabel harus memiliki kecocokkan kolom.
INNER JOIN
Artinya penggabungan tabel hanya menampilkan baris kolom yang sama. Jika kita lihat tabel employees hanya menyimpan department_id dari tabel departments.
Dari situ, kita sudah melihat kedua tabel tersebut memiliki kolom yang terhubung. Sekarang yang ingin kita lakukan adalah menampilkan nama departemen dari setiap karyawan juga.
mysql> SELECT departments.department_name, employees.first_name, employees.last_name AS jumlah_gaji FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
+------------------+-------------+-------------+
| department_name | first_name | jumlah_gaji |
+------------------+-------------+-------------+
| Administration | Jennifer | Whalen |
| Marketing | Michael | Hartstein |
| Marketing | Pat | Fay |
| Purchasing | Den | Raphaely |
| Purchasing | Alexander | Khoo |Keterangan:
* Klausa setelah ON sama seperti penggunaan kondisi WHERE, tetapi ini mengintruksikan nama kolom yang terhubung diantara dua tabel.
* Jika kalian menggunakan klausa JOIN, pastikan untuk selalu menulis nama kolom disertai dengan nama tabelnya juga. Syntaxnya seperti ini: nama_tabel.nama_kolom.
Ingin lebih lanjut menggunakan query INNER JOIN? Mari kita coba 1 kasus lagi sebelum lanjut materi, yaitu:
Kita ingin menampilkan daftar karyawan yang memiliki tanggungan dan menyertakan nama departemennya juga.
Artinya ini akan menjadi query INNER JOIN bercabang 2.
mysql> SELECT employees.first_name, employees.last_name, departments.department_name FROM dependents INNER JOIN employees ON dependents.employee_id = employees.employee_id INNER JOIN departments ON employees.department_id = departments.department_id;
+-------------+------------+------------------+
| first_name | last_name | department_name |
+-------------+------------+------------------+
| Steven | King | Executive |
| Neena | Kochhar | Executive |
| Lex | De Haan | Executive |
| ... | ... | ... |
| Masih | banyak | lagi |Inilah diagram venn yang tergambar jika terjadi penggabungan 3 tabel.
LEFT JOIN
Artinya penggabungan tabel hanya untuk kolom yang sama dan kolom yang ada di kiri walaupun tidak sama.
RIGHT JOIN
Artinya penggabungan tabel hanya untuk kolom yang sama dan kolom yang ada di kanan walaupun tidak sama.
Lumayan sulit mengenalkan kepada kalian contoh penggunaan kedua query JOIN ini, karena gak ada kolom yang cocok di jadikan contoh. Jadi maaf banget 🙇♂️ saya gak bisa ajarin langsung. Saya punya salah satu tantangan yang bisa membantu kalian memahami materi LEFT JOIN & RIGHT JOIN ini. Cek situs dibawah. 👇
Penutup
Banyak fungsi yang dapat di jelajahi lagi sekaligus dengan penggunaan klausa-klausanya. Namun untuk mempersingkat banyaknya kata dalam artikel ini, kita gunakan satu fungsi saja tapi sudah cukup menjelaskan. OKEE 🙆♂️.
Berikut sumber daya melanjutkan latihan MySQL dengan BERPIKIR.
