Pernyataan CASE dalam SQL Dijelaskan

Jika Anda pernah bekerja dengan basis data, Anda mungkin menyadari bahwa data jarang sempurna dan sering kali perlu dimanipulasi untuk mengekstrak wawasan yang bermakna.

Untuk memenuhi kebutuhan ini, SQL menawarkan sebuah konstruksi yang kuat bernama pernyataan CASE, yang memungkinkan Anda membuat kolom baru atau mentransformasi data berdasarkan kondisi yang diterapkan pada kolom yang ada.

Dalam artikel ini, kami akan menjelaskan apa itu pernyataan CASE, mengapa itu penting, dan mengeksplorasi berbagai kasus penggunaan untuk membantu Anda memaksimalkan potensinya. Mari mulai!

Apa itu Pernyataan CASE?

Pernyataan CASE dalam SQL adalah ekspresi kondisional yang memperkenalkan logika pengambilan keputusan ke dalam kueri Anda. Ini bekerja mirip dengan pernyataan if-elif-else di Python, memungkinkan Anda mengevaluasi beberapa kondisi dan mengembalikan hasil tertentu berdasarkan kondisi tersebut. 

Sintaks umum adalah sebagai berikut:

CASE
	WHEN boolean_condition1 THEN result1
	WHEN boolean_condition2 THEN result2
	ELSE result3
END

Begini cara kerjanya:

• Kata kunci CASE menandai awal ekspresi, sementara END menandai akhirnya, menutup pernyataan kondisional seperti bingkai.
• Setiap klausa WHEN mengevaluasi suatu kondisi. Jika kondisi benar, klausa THEN yang sesuai mengembalikan hasil yang ditentukan.
• Jika tidak ada kondisi dalam klausa WHEN yang terpenuhi, klausa ELSE memberikan hasil default.

Mengapa Pernyataan CASE Penting?

Pernyataan CASE WHEN adalah alat yang berharga dalam kueri SQL, menawarkan beberapa manfaat utama:

• Transformasi data: memungkinkan Anda membuat kolom baru berdasarkan nilai dari kolom yang sudah ada.
• Agregasi kondisional: memungkinkan Anda membuat nilai agregat (misalnya, jumlah, hitung, maksimum) untuk subset data tertentu berdasarkan kondisi.
• Penyaringan data: Anda dapat menyertakan atau mengecualikan baris berdasarkan kriteria tertentu.
• Penyederhanaan kueri dan keterbacaan: Membantu mengonsolidasikan logika ke dalam satu kueri, mengurangi kebutuhan akan banyak kueri, tabel sementara, atau join yang rumit.
• Optimasi kinerja: dengan mengurangi kompleksitas kueri SQL, pernyataan CASE juga dapat berkontribusi pada pengoptimalan kinerja dan meminimalkan waktu eksekusi, terutama saat menghindari kebutuhan perhitungan dan transformasi tambahan.

Pernyataan CASE Sederhana

Dalam artikel ini, kita akan menggunakan tabel film dari basis data cinema. Misalkan kita ingin membuat kolom kategorikal baru berdasarkan nilai ROI (rasio pendapatan kotor terhadap anggaran):

SELECT 
	title,
	gross,
	budget,
	gross / budget AS ROI,
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END as ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;

Berdasarkan pendapatan kotor dan anggaran, kita menghitung ROI, yang merupakan metrik profitabilitas populer yang mengukur pendapatan yang dihasilkan oleh sebuah investasi.

Selain bidang ROI, kita juga mendapatkan kategorisasinya agar lebih mudah dikomunikasikan:

• Jika film memiliki ROI kurang dari 1, nilainya adalah “low ROI”
• Film dengan ROI antara 1 dan 2 termasuk kategori “medium ROI”
• “High ROI” adalah label yang diberikan untuk film dengan ROI lebih besar atau sama dengan 2.

Kami juga memfilter film yang memiliki anggaran atau pendapatan kotor nol atau negatif. Selain itu, kami membatasi hasil pada film yang dirilis pada tahun 2015.

Menggunakan CASE dalam Klausa Group By

Pada contoh pertama, kita membuat bidang baru yang dapat diterapkan untuk mengagregasi data dan memperoleh wawasan tambahan.

Sebagai contoh, kita dapat menghitung rata-rata pendapatan kotor dan rata-rata anggaran untuk setiap grup ROI:

SELECT 
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END AS ROI_group,
avg(gross) as avg_gross,
avg(budget) as avg_budget
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015
GROUP BY
CASE
	 WHEN gross / budget < 1 THEN 'low ROI'
	 WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
	 ELSE 'high ROI'
END
ORDER BY avg(gross) DESC
;

Untuk mengelompokkan data, kita cukup menempatkan pernyataan CASE di klausa GROUP BY. Untuk memperoleh wawasan tambahan, kita mengurutkan baris dalam urutan menurun berdasarkan rata-rata pendapatan kotor. Dengan cara ini, kita dapat menangkap bahwa rata-rata pendapatan kotor tertinggi diberikan pada high ROI dan sebaliknya untuk low ROI.

Menggunakan CASE dalam Klausa WHERE

Manfaat lain dari pernyataan CASE adalah memungkinkan penyaringan baris berdasarkan kondisi tertentu. Misalnya, kita dapat menerapkan ambang ROI yang berbeda berdasarkan bahasa film. Diketahui bahwa film berbahasa Inggris secara konsisten memiliki ROI tinggi. Jadi, ambang batasnya harus lebih tinggi, sementara kita berasumsi bahwa bahasa lainnya memiliki ROI yang lebih rendah.

SELECT 
    title,
    gross,
    budget,
    language,
    gross / budget AS ROI
FROM cinema.films
WHERE 
    CASE 
        WHEN language = 'English' THEN gross / budget > 2 -- English films need ROI > 2
        WHEN language = 'French' THEN gross / budget > 1.5 -- French films need ROI > 1.5
        ELSE gross / budget > 1.3 -- Default for other languages
    END;

Menghitung Agregasi

Kita juga mungkin tertarik menghitung jumlah film dengan ROI tinggi versus ROI rendah yang dirilis pada 2015:

SELECT 
    SUM(CASE WHEN gross / budget > 2 THEN 1 ELSE 0 END) AS high_roi_films,
    SUM(CASE WHEN gross / budget BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS medium_roi_films,
    SUM(CASE WHEN gross / budget < 1 THEN 1 ELSE 0 END) AS low_roi_films
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;

Di dalam fungsi SUM, seolah-olah kita membuat bidang baru bernilai 1 ketika ROI mencapai ambang itu, 0 jika tidak. Jika kita menjumlahkan variabel biner baru ini, kita memperoleh jumlah film yang memenuhi kriteria tersebut.

Dari keluaran, kita dapat melihat bahwa sebagian besar film memiliki ROI kurang dari 1, sementara sedikit film yang sangat menguntungkan.

Pernyataan Case Bersarang

Salah satu kasus penggunaan yang lebih lanjut adalah pernyataan CASE bersarang, yaitu CASE di dalam CASE. Ini sangat berguna ketika Anda perlu membagi data menjadi subkelompok dan menerapkan logika yang berbeda untuk setiap subkelompok. 

Serupa dengan contoh pertama, kita ingin membuat kolom baru yang dipengaruhi oleh nilai ROI. Seperti yang telah kita sebutkan sebelumnya, ROI untuk film berbahasa Inggris secara global lebih tinggi dibandingkan film dalam bahasa lain. 

Untuk alasan ini, akan lebih baik jika mengategorikan film ke dalam grup ROI dengan mempertimbangkan baik ROI maupun bahasa

SELECT 
    title,
    language,
    gross / budget AS ROI,
    CASE
        WHEN language = 'English' THEN 
            CASE 
                WHEN gross / budget < 2 THEN 'low ROI'
                WHEN gross / budget BETWEEN 2 AND 3 THEN 'medium ROI'
                ELSE 'high ROI'
            END
        ELSE 
            CASE
                WHEN gross / budget < 1 THEN 'low ROI'
                WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
                ELSE 'high ROI'
            END
    END AS ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 and release_year = 2006;

Dalam kueri, pernyataan CASE luar digunakan untuk mengevaluasi apakah bahasanya Inggris atau bukan. Bergantung pada bahasa, CASE dalam mengevaluasi ROI: ambang batas lebih tinggi untuk film berbahasa Inggris dan lebih rendah untuk film non-Inggris. Kondisi multi-level ini memungkinkan peningkatan pemisahan antara film dengan ROI rendah, sedang, dan tinggi.

Kesimpulan

Pernyataan CASE adalah konstruksi SQL yang tak tergantikan untuk mengekstrak wawasan bermakna dari data Anda. Teori diselingi dengan contoh praktis untuk membantu Anda memahami secara mendalam ekspresi kondisional yang kuat ini. Jika Anda ingin mendalami, lihat kursus data manipulation in SQL. Kursus ini membahas pernyataan CASE serta aspek kunci SQL lainnya, seperti subquery dan window function. Terakhir, Anda sebaiknya memeriksa SQL Fundamentals untuk mendapatkan gambaran umum kursus yang membantu menguasai SQL.