Bab 13: Subquery, CTE, dan Window Function

Apa Itu Subquery

Subquery adalah sebuah query SQL yang ditempatkan di dalam query SQL lainnya, biasanya untuk menghasilkan nilai atau kumpulan data perantara yang kemudian dipakai oleh query utama. Kamu bisa membayangkan subquery sebagai "pertanyaan di dalam pertanyaan", di mana kamu perlu menjawab satu pertanyaan kecil terlebih dahulu, sebelum bisa menjawab pertanyaan besar yang sebenarnya ingin kamu ketahui.

Sebagai ilustrasi paling sederhana dalam konteks Belanjain, bayangkan kamu ingin mengetahui pelanggan mana saja yang nilai transaksinya berada di atas rata-rata nilai transaksi seluruh pelanggan. Untuk menjawab pertanyaan ini, kamu sebenarnya perlu menjawab dua pertanyaan secara berurutan: pertama, berapa rata-rata nilai transaksi seluruh pelanggan, dan kedua, pelanggan mana saja yang nilai transaksinya melebihi angka rata-rata tersebut. Subquery memungkinkan kamu menjawab pertanyaan pertama sebagai bagian dari query yang sama dengan pertanyaan kedua, tanpa perlu menjalankan dua query terpisah secara manual dan mencatat hasilnya satu per satu.

Penulisan subquery ini akan terlihat sebagai berikut:

SELECT nama, nilai_transaksi
FROM pesanan
WHERE nilai_transaksi > (SELECT AVG(nilai_transaksi) FROM pesanan);

Perhatikan bagian yang ditulis dalam tanda kurung, (SELECT AVG(nilai_transaksi) FROM pesanan), ini adalah subquery yang akan dijalankan terlebih dahulu oleh database, menghasilkan satu angka tunggal berupa rata-rata nilai transaksi. Angka hasil subquery ini kemudian dipakai oleh query utama di bagian WHERE, untuk menyaring baris-baris yang nilai transaksinya lebih besar dari angka tersebut. Cara membaca query ini adalah dari dalam ke luar, subquery diproses lebih dulu, hasilnya baru dipakai oleh bagian query yang mengelilinginya.

Subquery Non-Correlated vs Correlated

Subquery bisa dibedakan menjadi dua jenis berdasarkan hubungannya dengan query utama, yaitu non-correlated dan correlated, dan memahami perbedaan ini penting karena keduanya bekerja dengan cara yang cukup berbeda.

Subquery non-correlated adalah subquery yang bisa dijalankan secara mandiri, tanpa bergantung sama sekali pada baris yang sedang diproses oleh query utama. Contoh subquery pada bagian sebelumnya, (SELECT AVG(nilai_transaksi) FROM pesanan), adalah contoh subquery non-correlated, karena subquery ini menghasilkan satu angka tunggal yang sama, tidak peduli baris mana pun yang sedang diperiksa oleh query utama. Subquery jenis ini hanya dijalankan satu kali oleh database, hasilnya kemudian dipakai berulang kali oleh query utama.

Subquery correlated adalah subquery yang bergantung pada nilai dari baris yang sedang diproses oleh query utama, sehingga subquery ini sebenarnya dijalankan berulang kali, satu kali untuk setiap baris yang diperiksa oleh query utama. Sebagai ilustrasi, bayangkan kamu ingin mencari pelanggan yang nilai transaksinya berada di atas rata-rata nilai transaksi khusus di kota mereka masing-masing, bukan rata-rata nasional. Querynya akan menjadi:

SELECT nama, kota, nilai_transaksi
FROM pesanan p1
WHERE nilai_transaksi > (
  SELECT AVG(nilai_transaksi) 
  FROM pesanan p2 
  WHERE p2.kota = p1.kota
);

Perhatikan bahwa subquery pada contoh ini merujuk pada p1.kota, yaitu kolom dari tabel di query utama (yang diberi alias p1). Ini berarti subquery tersebut perlu dijalankan ulang untuk setiap baris yang diperiksa oleh query utama, karena rata-rata yang dihitung akan berbeda tergantung kota dari baris yang sedang diperiksa. Subquery correlated seperti ini secara umum memproses data lebih lambat dibanding non-correlated, karena harus dijalankan berulang kali, sehingga penggunaannya perlu dipertimbangkan dengan cermat terutama pada dataset yang sangat besar.

CTE: Cara Menulis Query Kompleks yang Lebih Rapi

CTE, singkatan dari Common Table Expression, adalah cara menulis subquery dengan struktur yang lebih rapi dan mudah dibaca, dengan memberikan nama sementara pada hasil sebuah query, sebelum dipakai oleh query utama. CTE ditulis menggunakan klausa WITH di bagian paling awal sebuah query, dengan struktur dasar:

WITH nama_cte AS (
  SELECT ...
)
SELECT ...
FROM nama_cte;

Sebagai ilustrasi, mari kembali ke contoh mencari pelanggan dengan nilai transaksi di atas rata-rata nasional, namun kali ini ditulis menggunakan CTE:

WITH rata_rata_nasional AS (
  SELECT AVG(nilai_transaksi) AS rata_rata
  FROM pesanan
)
SELECT nama, nilai_transaksi
FROM pesanan, rata_rata_nasional
WHERE nilai_transaksi > rata_rata_nasional.rata_rata;

Pada contoh sederhana ini, perbedaan antara subquery biasa dan CTE mungkin belum terlihat terlalu signifikan. Namun keunggulan CTE menjadi sangat terasa ketika query yang dibutuhkan semakin kompleks, misalnya melibatkan beberapa tahap perhitungan perantara sekaligus. Sebagai ilustrasi, bayangkan kamu perlu menghitung total penjualan per kategori produk, kemudian dari hasil tersebut mencari kategori dengan penjualan di atas rata-rata seluruh kategori, sebuah kebutuhan yang melibatkan dua tahap perhitungan berbeda. Dengan CTE, kamu bisa memecah kebutuhan ini menjadi beberapa CTE yang saling berurutan, masing-masing diberi nama yang jelas sesuai fungsinya:

WITH penjualan_per_kategori AS (
  SELECT kategori, SUM(nilai_transaksi) AS total_penjualan
  FROM pesanan
  GROUP BY kategori
),
rata_rata_kategori AS (
  SELECT AVG(total_penjualan) AS rata_rata
  FROM penjualan_per_kategori
)
SELECT penjualan_per_kategori.kategori, penjualan_per_kategori.total_penjualan
FROM penjualan_per_kategori, rata_rata_kategori
WHERE penjualan_per_kategori.total_penjualan > rata_rata_kategori.rata_rata;

Perhatikan bagaimana query ini terbagi menjadi tahapan yang jelas dan diberi nama masing-masing, penjualan_per_kategori dan rata_rata_kategori, sehingga siapa pun yang membaca query ini, termasuk kamu sendiri di kemudian hari, bisa memahami alur logikanya tanpa perlu membongkar subquery bertingkat yang membingungkan. Inilah keunggulan utama CTE dibanding subquery biasa yang ditulis bersarang (nested), yaitu keterbacaan yang jauh lebih baik, terutama ketika logika yang dibutuhkan melibatkan banyak tahapan.

Refactor dari Subquery Bersarang Menjadi CTE

Untuk melihat lebih jelas kenapa CTE lebih disukai dibanding subquery bersarang yang kompleks, mari bandingkan dua versi query yang menghasilkan output yang sama persis, namun ditulis dengan pendekatan berbeda. Bayangkan kamu ingin mencari kategori produk dengan penjualan tertinggi di setiap kota, sebuah kebutuhan yang cukup kompleks karena melibatkan beberapa tahap logika.

Versi sebelum refactor, ditulis sebagai subquery bersarang di dalam FROM:

SELECT kota, kategori, total_penjualan
FROM (
  SELECT kota, kategori, SUM(nilai_transaksi) AS total_penjualan,
    RANK() OVER (PARTITION BY kota ORDER BY SUM(nilai_transaksi) DESC) AS peringkat
  FROM pesanan
  GROUP BY kota, kategori
) AS hasil_ranking
WHERE peringkat = 1;

Versi setelah refactor menggunakan CTE, menghasilkan output yang identik:

WITH penjualan_kota_kategori AS (
  SELECT kota, kategori, SUM(nilai_transaksi) AS total_penjualan
  FROM pesanan
  GROUP BY kota, kategori
),
hasil_ranking AS (
  SELECT kota, kategori, total_penjualan,
    RANK() OVER (PARTITION BY kota ORDER BY total_penjualan DESC) AS peringkat
  FROM penjualan_kota_kategori
)
SELECT kota, kategori, total_penjualan
FROM hasil_ranking
WHERE peringkat = 1;

Meskipun versi CTE terlihat lebih panjang dari segi jumlah baris, strukturnya jauh lebih mudah ditelusuri tahap demi tahap, dibanding versi subquery bersarang yang mengharuskan kamu membaca dari bagian paling dalam terlebih dahulu untuk memahami alurnya. Sebagai kebiasaan yang baik, semakin kompleks logika yang dibutuhkan sebuah query, semakin disarankan untuk memecahnya menjadi beberapa CTE bertahap, dibanding memaksakan seluruh logika dalam satu subquery bersarang yang sulit dibaca ulang.

Window Function: ROW_NUMBER, RANK, dan PARTITION BY

Window function adalah kelompok fungsi SQL yang memungkinkan perhitungan dilakukan di seluruh "jendela" (window) baris data tertentu, tanpa menggabungkan baris-baris tersebut menjadi satu baris ringkasan seperti yang dilakukan GROUP BY. Perbedaan mendasar inilah yang membuat window function sangat berguna, karena kamu tetap bisa melihat detail setiap baris data, sambil menambahkan informasi perhitungan yang memperhitungkan konteks baris-baris lain di sekitarnya.

ROW_NUMBER memberikan nomor urut unik untuk setiap baris, berdasarkan urutan yang ditentukan. Sebagai ilustrasi, jika kamu ingin memberikan nomor urut pada setiap pesanan Belanjain berdasarkan nilai transaksi dari yang tertinggi, querynya ditulis sebagai:

SELECT nama, nilai_transaksi,
  ROW_NUMBER() OVER (ORDER BY nilai_transaksi DESC) AS nomor_urut
FROM pesanan;

Klausa OVER menandakan bahwa fungsi ini adalah window function, dan di dalamnya kamu menentukan urutan yang dipakai untuk menghitung nomor urut tersebut, dalam hal ini ORDER BY nilai_transaksi DESC. Hasilnya, setiap baris akan mendapatkan nomor urut unik, dimulai dari 1 untuk nilai transaksi tertinggi, dan terus bertambah untuk baris-baris berikutnya, tanpa ada nomor yang sama meskipun ada dua baris dengan nilai transaksi yang persis sama.

RANK bekerja mirip dengan ROW_NUMBER, namun dengan satu perbedaan penting, yaitu RANK memberikan peringkat yang sama untuk baris-baris dengan nilai yang sama persis, dan akan melompati angka peringkat berikutnya sesuai jumlah baris yang memiliki peringkat sama tersebut. Sebagai ilustrasi, jika ada dua pesanan dengan nilai transaksi yang sama persis dan sama-sama menempati peringkat pertama, RANK akan memberikan angka 1 untuk keduanya, namun baris berikutnya akan langsung mendapat peringkat 3, bukan peringkat 2, karena dua baris sudah menempati peringkat 1. Perilaku ini berbeda dengan ROW_NUMBER yang akan tetap memberikan nomor urut berbeda (misalnya 1 dan 2) meskipun nilai transaksinya sama persis, karena ROW_NUMBER memang dirancang untuk selalu menghasilkan nomor yang unik untuk setiap baris.

PARTITION BY adalah klausa tambahan yang memungkinkan window function dihitung secara terpisah untuk setiap kelompok data, mirip dengan konsep GROUP BY namun tanpa meringkas baris-barisnya menjadi satu. Sebagai ilustrasi, jika kamu ingin memberi peringkat pada setiap produk berdasarkan jumlah terjual, namun peringkat ini dihitung terpisah untuk setiap kategori (bukan peringkat gabungan seluruh produk), querynya menjadi:

SELECT nama_produk, kategori, jumlah_terjual,
  RANK() OVER (PARTITION BY kategori ORDER BY jumlah_terjual DESC) AS peringkat_kategori
FROM produk;

Query ini akan menghitung peringkat jumlah terjual secara terpisah untuk setiap kategori, sehingga setiap kategori akan memiliki produk-produk dengan peringkat 1, 2, 3, dan seterusnya masing-masing, bukan satu peringkat gabungan dari seluruh produk lintas kategori. Kombinasi PARTITION BY dengan RANK atau ROW_NUMBER seperti ini sangat sering dipakai dalam analisis bisnis nyata, misalnya untuk menemukan produk terlaris di setiap kategori, atau pelanggan dengan transaksi terbesar di setiap kota, sesuatu yang akan sulit dicapai hanya dengan GROUP BY biasa karena kamu tetap membutuhkan detail setiap baris data, bukan hanya angka ringkasannya saja.

Ranking Produk Terlaris per Kategori

Untuk mengaplikasikan window function secara menyeluruh, mari bayangkan skenario tim manajemen Belanjain ingin mengetahui tiga produk terlaris (berdasarkan total nilai transaksi) di setiap kategori produk, sebuah kebutuhan analisis yang cukup umum dalam praktik nyata. Query lengkapnya, menggabungkan CTE dan window function, akan terlihat sebagai berikut:

WITH penjualan_produk AS (
  SELECT 
    produk.nama_produk,
    produk.kategori,
    SUM(pesanan.jumlah * produk.harga) AS total_penjualan
  FROM pesanan
  JOIN produk ON pesanan.id_produk = produk.id_produk
  GROUP BY produk.nama_produk, produk.kategori
),
ranking_produk AS (
  SELECT *,
    RANK() OVER (PARTITION BY kategori ORDER BY total_penjualan DESC) AS peringkat
  FROM penjualan_produk
)
SELECT nama_produk, kategori, total_penjualan, peringkat
FROM ranking_produk
WHERE peringkat <= 3
ORDER BY kategori, peringkat;

Perhatikan bagaimana query ini menggabungkan beberapa konsep yang sudah dipelajari sepanjang Bagian III Handbook ini: JOIN untuk menggabungkan tabel pesanan dan produk (Bab 12), CTE untuk memecah logika menjadi tahapan yang jelas, dan window function dengan PARTITION BY untuk menghitung peringkat secara terpisah per kategori. Hasil akhirnya adalah daftar tiga produk terlaris di setiap kategori, sebuah insight yang langsung actionable bagi tim manajemen, misalnya untuk menentukan produk mana yang perlu diprioritaskan stoknya, atau produk mana yang layak dipromosikan lebih gencar di kategori yang penjualannya masih tertinggal.

Kesadaran Dasar Mengenai Query Optimization: EXPLAIN

Seiring query yang kamu tulis semakin kompleks, melibatkan banyak JOIN, subquery, maupun window function sekaligus, penting untuk mulai memiliki kesadaran bahwa tidak semua query diproses dengan kecepatan yang sama oleh database, terutama pada tabel dengan jumlah baris yang sangat besar. EXPLAIN adalah perintah yang bisa ditambahkan di depan sebuah query, yang alih-alih menjalankan query tersebut dan menampilkan hasilnya, justru menampilkan rencana eksekusi yang akan dipakai database untuk memproses query tersebut.

Sebagai ilustrasi, jika kamu menuliskan EXPLAIN SELECT * FROM pesanan WHERE nilai_transaksi > 500000;, database tidak akan menampilkan data pesanan yang sebenarnya, melainkan menampilkan penjelasan teknis bagaimana database berencana mencari data tersebut, misalnya apakah database akan memeriksa seluruh baris tabel satu per satu, atau memanfaatkan struktur khusus (yang disebut index) untuk mempercepat pencarian tanpa perlu memeriksa seluruh baris.

Pada tahap awal belajar SQL, kamu belum perlu memahami secara mendalam cara membaca output EXPLAIN secara detail, karena topik ini termasuk area yang lebih teknis dan biasanya menjadi perhatian lebih besar bagi Data Engineer atau administrator database yang bertanggung jawab menjaga performa sistem secara keseluruhan. Namun penting bagi kamu sebagai Data Analyst untuk setidaknya menyadari keberadaan EXPLAIN ini, terutama ketika suatu saat kamu menulis query yang ternyata berjalan sangat lambat pada dataset besar, sehingga kamu tahu ada cara untuk menelusuri kenapa query tersebut lambat, alih-alih hanya menduga-duga tanpa dasar yang jelas.