PostgreSQL - Kebenaran Kunci: Panduan Ramah untuk Pemula

Hai teman-teman yang sedang belajar basis data! Hari ini, kita akan mengemban sebuah perjalanan menarik ke dunia PostgreSQL, khususnya untuk mengexplore kunci yang kuat yaitu klausa WITH. Jangan khawatir jika Anda baru saja memulai programming; saya akan menjadi panduan ramah Anda, menjelaskan segala sesuatu secara langkah demi langkah. Jadi, ambilkan secangkir kopi Anda, dan mari kita masuk ke dalamnya!

Apa Itu Klausa WITH?

Klausa WITH, juga dikenal sebagai Common Table Expressions (CTEs), adalah seperti sebuah kotak pernak-pernik ajaib di PostgreSQL yang memungkinkan kita menulis pernyataan auxiliar dalam query yang lebih besar. Pahamiannya sebagai menciptakan hasil temporer yang dinamai yang dapat Anda referensikan dalam SELECT, INSERT, UPDATE, DELETE, atau MERGE statement.

Mengapa Menggunakan Klausa WITH?

1. Memperbaiki keterbacaan
2. Menyederhanakan query yang kompleks
3. Memungkinkan query rekursif

mari kita mulai dengan contoh sederhana untuk merasakan air:

WITH gaji_karyawan AS (
SELECT departemen, AVG(gaji) as rata_gaji
FROM karyawan
GROUP BY departemen
)
SELECT departemen, rata_gaji
FROM gaji_karyawan
WHERE rata_gaji > 50000;

Dalam contoh ini, kita menciptakan hasil temporer yang dinamai gaji_karyawan yang menghitung rata-rata gaji untuk setiap departemen. Kemudian, kita menggunakan hasil ini untuk menemukan departemen dengan rata-rata gaji di atas $50.000.

Sintaks Dasar dan Penggunaan

Sintaks dasar klausa WITH tampak seperti ini:

WITH nama_cte AS (
definisi_query_cte
)
SELECT * FROM nama_cte;

Di sini, nama_cte adalah nama yang Anda berikan ke Common Table Expression Anda, dan definisi_query_cte adalah query yang menentukan hasil.

mari kita lihat contoh lain:

WITH pesanan_berharga AS (
SELECT customer_id, SUM(total_order) as total_nilai
FROM orders
GROUP BY customer_id
HAVING SUM(total_order) > 1000
)
SELECT c.customer_name, h.total_nilai
FROM customers c
JOIN pesanan_berharga h ON c.customer_id = h.customer_id;

Dalam query ini, kita pertama-tama mengidentifikasi pelanggan dengan pesanan berharga tinggi (total di atas $1000) dan kemudian menggabungkan informasi ini dengan tabel pelanggan untuk mendapatkan namanya.

Beberapa CTE

Salah satu hal menarik tentang klausa WITH adalah Anda dapat mendefinisikan beberapa CTE dalam satu query. Itu seperti memiliki beberapa asisten untuk query utama Anda!

WITH
produk_terlaris AS (
SELECT product_id, SUM(kuantitas) as total_terjual
FROM order_items
GROUP BY product_id
ORDER BY total_terjual DESC
LIMIT 5
),
pendapatan_produk AS (
SELECT product_id, SUM(kuantitas * harga) as pendapatan
FROM order_items
GROUP BY product_id
)
SELECT p.product_name, t.total_terjual, r.pendapatan
FROM products p
JOIN produk_terlaris t ON p.product_id = t.product_id
JOIN pendapatan_produk r ON p.product_id = r.product_id;

Query ini pertama-tama mengidentifikasi 5 produk terlaris, menghitung pendapatan untuk semua produk, dan kemudian menggabungkan informasi ini dengan nama produk.

CTE Rekursif

Sekarang, mari kita masuk ke wilayah yang lebih maju: CTE rekursif. Ini sangat berguna untuk bekerja dengan data hierarki atau struktur pohon.

Sintaks untuk CTE rekursif tampak seperti ini:

WITH RECURSIVE nama_cte AS (
pernyataan_non_rekursif
UNION [ALL]
pernyataan_rekursif
)
SELECT * FROM nama_cte;

mari kita lihat contoh kelasik: menghasilkan deret angka.

WITH RECURSIVE deret_angka AS (
SELECT 1 as n
UNION ALL
SELECT n + 1
FROM deret_angka
WHERE n < 10
)
SELECT * FROM deret_angka;

Query ini menghasilkan deret angka dari 1 hingga 10. Pernyataan non-rekursif dimulai dengan 1, dan pernyataan rekursif menambah 1 ke angka sebelumnya sampai kita mencapai 10.

Contoh yang Lebih Praktis: Hierarki Organisasi

Imaginasi kita ada tabel karyawan dengan kolom employee_id, name, dan manager_id. Kita dapat menggunakan CTE rekursif untuk menampilkan seluruh hierarki organisasi:

WITH RECURSIVE org_hierarchy AS (
SELECT employee_id, name, manager_id, 1 as level
FROM karyawan
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, oh.level + 1
FROM karyawan e
JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT employee_id, name, level
FROM org_hierarchy
ORDER BY level, employee_id;

Query ini dimulai dengan manajer tingkat atas (di mana manager_id adalah NULL) dan secara rekursif menemukan semua karyawan dibawah setiap manajer, memberikan level seperti yang dimunculkan.

Praktik dan Tips Terbaik

1. Penamaan: Pilih nama yang jelas dan deskriptif untuk CTE Anda.
2. Kompleksitas: Pisahkan query yang kompleks menjadi beberapa CTE yang kecil dan mudah diatur.
3. Kinerja: Meskipun CTE dapat meningkatkan keterbacaan, perhatikan bahwa penggunaan berlebihan dapat mempengaruhi kinerja.
4. Rekursion: Hati-hati dengan CTE rekursif untuk menghindari loop tak terbatas.

Metode Umum yang Digunakan dengan Klausa WITH

Berikut adalah tabel yang menggabungkan beberapa metode umum yang digunakan dengan klausa WITH:

Metode	Deskripsi	Contoh
SELECT	Mengambil data dari CTE	SELECT * FROM nama_cte
JOIN	Menggabungkan CTE dengan tabel lain	SELECT * FROM tabel JOIN nama_cte ON ...
UNION	Menggabungkan hasil beberapa CTE	WITH cte1 AS (...), cte2 AS (...) SELECT * FROM cte1 UNION SELECT * FROM cte2
INSERT	Menyisipkan data menggunakan CTE	WITH cte AS (...) INSERT INTO tabel SELECT * FROM cte
UPDATE	Memperbarui data menggunakan CTE	WITH cte AS (...) UPDATE tabel SET ... FROM cte WHERE ...
DELETE	Menghapus data menggunakan CTE	WITH cte AS (...) DELETE FROM tabel USING cte WHERE ...

Ingat, latihan membuat sempurna! Jangan khawatir untuk mencoba konsep ini dalam lingkungan PostgreSQL Anda sendiri. Sebelum Anda mengetahui, Anda akan menulis query yang kompleks dengan keindahan seorang ahli basis data!

Selamat menanyakan, dan semoga data Anda selalu terstruktur dan query Anda optimal!

Credits: Image by storyset