Sebagai data scientist atau analis data, data tentu menjadi inti dari pekerjaan Anda. Ada banyak sumber tempat Anda dapat mengumpulkan data. Sering kali data tersimpan dalam basis data SQL, dan dalam kasus tersebut pemahaman tentang perintah kueri SQL bisa menjadi hal yang penting agar peran ini dapat dijalankan dengan sukses. Artikel ini akan memperkenalkan Anda pada beberapa perintah dasar hingga operasi yang lebih lanjut yang akan sangat berguna saat bekerja sebagai analis data atau data scientist.
Untuk memahami perintah-perintah di atas, kita akan menggunakan basis data open source DVD rental untuk menunjukkan cara penggunaannya. Anda dapat menemukan entity relationship diagram (ERD) di sini.
Untuk keperluan artikel ini, kami menyiapkan basis data secara lokal dan menjalankan semua kueri menggunakan pgAdmin4, sehingga kami menggunakan postgreSQL. Banyak, jika tidak semua, perintah seharusnya berfungsi di server SQL lain. Instalasi postgreSQL dan penyiapan basis data itu sendiri berada di luar cakupan artikel ini; kami mendorong Anda untuk melihat sumber lain seperti Panduan Pemula PostgreSQL dari DataCamp untuk mendapatkan saran tentang proses tersebut.
Pengambilan Data Sederhana
SELECT FROM
Contoh pengambilan data paling sederhana adalah mengambil seluruh isi dari tabel tertentu dalam basis data. Jika kita ingin mengetahui semua kategori film yang ada di tabel 'Category', Anda dapat mencoba memasukkan perintah ini:
SELECT *
FROM category
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Di sini kita menentukan nama tabel setelah FROM. Karena kita menginginkan seluruh isi tabel, kita menggunakan * setelah SELECT untuk menyatakan bahwa kita ingin semuanya.
DISTINCT
Ada kasus ketika kita hanya tertarik pada nilai unik. Misalnya, bagaimana jika kita ingin mengetahui semua rating MPAA dari tabel film? Dengan menggunakan DISTINCT kita dapat mengambil nilai unik saja:
SELECT DISTINCT(rating)
FROM film
Yang mengembalikan:
Pengambilan Data dengan Kondisi Sederhana
WHERE
Ada juga situasi ketika kita hanya ingin mengambil entri tertentu yang memenuhi kondisi tertentu. Kita dapat memberikan kondisi yang harus dipenuhi sebelum data dikembalikan menggunakan klausa WHERE. Intinya, kita menyaring entri berdasarkan kondisi. Misalnya, bagaimana jika kita hanya ingin mengetahui film yang berating PG-13?
SELECT title
FROM film
WHERE rating = 'PG-13'
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Selain itu, kita dapat mencari daftar film yang memiliki rental rating 4,5 atau lebih tinggi:
SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
ORDER BY
Mengurutkan hasil yang dikembalikan sering kali membantu kita memahami data dengan lebih baik. Bagaimana jika kita menginginkan daftar yang sama seperti di atas untuk film PG-13, tetapi diurutkan dari durasi film terpanjang ke terpendek, beserta durasinya? Dengan menggunakan ORDER BY, hal ini mudah dilakukan:
SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
LIMIT
Selain itu, terkadang kita hanya tertarik pada sejumlah entri terbatas. Di sini, kita sebenarnya hanya tertarik pada 10 film terpanjang yang berating PG-13, jadi kita dapat menggunakan klausa LIMIT.
SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10
Yang mengembalikan:
Agregasi
GROUP BY & COUNT( )
Agregasi sering digunakan untuk mendapatkan ringkasan dataset guna memperoleh wawasan. Agregasi sering dipakai bersama klausa GROUP BY. Misalnya, jika kita perlu mengetahui berapa banyak sewa yang telah dilakukan setiap pelanggan sejauh ini, kita dapat menghitung jumlah sewa dan mengurutkan hasil dengan jumlah sewa terbanyak di bagian atas.
SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
SUM( )
Bagaimana dengan berapa banyak uang yang dihabiskan setiap pelanggan? Kita dapat melakukannya menggunakan fungsi agregat SUM. Di sini, kita ingin pembelanja tertinggi di bagian atas.
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
AVG( ) & HAVING
Kita dapat menambahkan kondisi tambahan menggunakan HAVING setelah Anda menghitung agregasi terkelompok. Misalnya, kita ingin mengetahui kategori rating MPAA yang memiliki film dengan durasi rata-rata lebih dari 115 menit?
SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115
Yang mengembalikan:
MIN( ) & Alias
Pengelompokan tidak selalu diperlukan saat menggunakan agregasi. Jika Anda ingin mengetahui durasi film terpendek, kita cukup menggunakan fungsi MIN. Kita juga ingin nama kolomnya menjadi 'shortest_movie_length', yang dapat dilakukan dengan menetapkan alias menggunakan 'AS'.
SELECT MIN(length) AS shortest_movie_length
FROM film
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Anda juga dapat menggunakan alias untuk nama tabel. Ini berguna saat Anda bekerja dengan nama tabel yang panjang, terutama saat melakukan join tabel (lihat di bawah). Kita juga perlu memperhatikan jenis alias yang kita gunakan karena dapat sangat memengaruhi keterbacaan kode.
Ada fungsi agregasi lain yang juga berguna, dan Anda dapat mempelajarinya lebih lanjut di kursus Introduction to SQL.
Join
INNER JOIN (JOIN)
Anda mungkin telah memperhatikan bahwa ada keterbatasan jika hanya melihat satu tabel. Kita sering ingin melihat data dari banyak tabel. Di sinilah kita menggunakan JOIN. Kita dapat menggabungkan tabel berdasarkan kunci yang sama. Misalnya, jika kita mencari film yang tidak berbahasa Inggris:
SELECT film.title, language.name
FROM film
JOIN language
ON film.language_id = language.language_id
WHERE language.name != 'English'
(Harap diperhatikan bahwa tidak ada film selain berbahasa Inggris di basis data ini. Anda akan mendapatkan tabel kosong dengan kueri di atas.)
Anda juga dapat melakukan join pada beberapa tabel. Misalnya, di bawah ini kita ingin mengetahui kategori dan rata-rata ratingnya masing-masing. Kita juga mengurutkan nilai yang dikembalikan berdasarkan rata-rata rating, dari yang tertinggi.
SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
ON film.film_id = film_category.film_id
JOIN category
ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Ada banyak jenis join: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. Di sini, kita menggunakan JOIN, yang secara bawaan adalah INNER JOIN, yaitu Anda hanya mendapatkan entri jika ada di kedua tabel. Jika Anda ingin mempelajari lebih lanjut perbedaan antar jenis join ini, Anda dapat melihat kursus Joining Data in SQL.
Mengubah Tipe Data
CAST( )
Pada contoh di atas saat kita menghitung jumlah dolar, Anda mungkin memperhatikan bahwa SQL hanya melihat nilai sebagai angka. Kita dapat mengubahnya menjadi jumlah dolar menggunakan fungsi CAST:
SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Uang bukan satu-satunya hal yang dapat kita ubah. Kita dapat mengubah angka menjadi float, teks, bahkan menjadi tanggal dan waktu.
ROUND( )
Demikian pula, kita juga dapat membulatkan angka. Pada contoh di atas saat kita menghitung rata-rata rental rating, terdapat banyak angka desimal pada nilai yang dihitung. Kita dapat menggunakan fungsi ROUND untuk membulatkan ke dua desimal:
SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
ON film.film_id = film_category.film_id
JOIN category
ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Kondisi Kompleks
Pernyataan CASE
Dapat dibayangkan bahwa dengan menggabungkan banyak fungsi dan klausa di atas, Anda dapat menjalankan kueri yang cukup kompleks. Namun, ada situasi ketika hal-hal dasar tidak cukup. Misalnya, jika kita ingin menambahkan kolom dengan kategori durasi film. Kita dapat memberi label film sebagai 'Short Film' jika kurang dari 50 menit, 'Long Film' jika lebih dari 150 menit, dan 'Medium Film' untuk sisanya. Kita dapat melakukannya dengan menggunakan pernyataan CASE:
SELECT title, length,
CASE
WHEN length < 50 THEN 'Short Film'
WHEN length > 150 THEN 'Long Film'
ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Seperti yang Anda lihat, hasil dikembalikan saat kondisi yang ditentukan terpenuhi. Ini mirip dengan pernyataan 'if-else' dalam bahasa pemrograman.
Subquery
Sekarang kita mungkin ingin mengetahui film mana yang paling panjang durasinya, beserta judulnya. Untuk ini, kita dapat menggunakan klausa WHERE yang telah diperkenalkan di atas, hanya saja dengan kondisi yang lebih kompleks:
SELECT title, length
FROM film
WHERE length = (SELECT max(length)
FROM film)
Yang mengembalikan:
Kueri kompleks dapat dibuat menggunakan subquery. Pada contoh ini, kita ingin mengetahui daftar judul film yang memiliki rental rating lebih tinggi daripada rata-rata rental rating semua film komedi:
SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
ON film.film_id=cat_id.film_id
JOIN category AS cat
ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Pada contoh ini, pertama-tama kita mencari rata-rata rental rating untuk film komedi. Nilai yang ditemukan dalam kueri ini (terlihat di dalam tanda kurung di atas) kemudian digunakan sebagai kondisi pada klausa WHERE untuk menemukan judul film.
Common Table Expressions (CTE)
Pada contoh yang lebih kompleks, kita dapat menggunakan common table expression (CTE) untuk menyimpan tabel sementara yang darinya kita mengekstrak informasi yang diperlukan. Untuk contoh ini, kita ingin mengetahui rata-rata jumlah sewa yang diproses setiap staf per minggu.
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id
Yang mengembalikan:
Pada kasus di atas, kita menyimpan jumlah sewa per minggu dalam tabel sementara bernama 'weekly_rentals' yang kemudian dapat kita gunakan untuk menjalankan kueri lain guna memperoleh hasil akhir.
Fungsi Window
Fungsi window adalah serangkaian keterampilan SQL lain yang penting dan dapat membawa Anda ke level berikutnya. Meskipun fungsi window dianggap sebagai keterampilan tingkat menengah hingga lanjutan, banyak analis dan scientist data menganggapnya esensial untuk pekerjaan mereka. Oleh karena itu, sebaiknya Anda meluangkan waktu untuk membiasakan diri dengannya.
OVER( ) & PARTITION BY Di sini, misalkan kita ingin mengetahui jumlah sewa mingguan masing-masing staf dan total keseluruhan jumlah sewa, bersama dengan ID staf dan nomor minggu. Kita dapat menggunakan kembali CTE yang dibuat pada contoh di atas, seperti ditunjukkan di bawah ini:
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Adanya OVER dalam kode menandakan bahwa ini adalah fungsi window. Di sini kita menyatakan fungsi window (SUM di sini) diterapkan pada setiap baris. Kita juga dapat menggunakan fungsi window bersama PARTITION BY untuk menerapkan fungsi window pada 'partisi' tertentu. Misalkan sekarang kita ingin mengetahui total jumlah sewa per minggu alih-alih total keseluruhan (selain kolom-kolom lainnya). Sekali lagi, pada contoh di bawah ini kita menggunakan CTE yang dibuat sebelumnya.
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals
Yang mengembalikan (hanya 10 baris pertama yang ditampilkan):
Kolom tambahan berisi total jumlah sewa mingguan, bukan total kumulatif.
Kami hanya memperkenalkan beberapa contoh sederhana fungsi window yang penting bagi analis atau data scientist di banyak perusahaan. Kami mendorong Anda untuk mempelajari lebih lanjut perintah-perintah ini melalui tautan yang disediakan. Anda juga dapat melihat kursus Intermediate SQL untuk mempelajari atau menyegarkan kembali keterampilan SQL Anda. Selain itu, Anda dapat melatih keterampilan Anda dengan menggunakan basis data open source dan mencoba berbagai perintah ini sambil memikirkan pertanyaan relevan untuk dijawab.
