Belajar SQL PostgreSQL - Common Table Expressions (CTE) & Recursive Queries

#Pendahuluan

Selamat datang di episode 10 series Belajar SQL PostgreSQL! Pada episode 7, kita mengenal subquery yang bersarang di dalam query. Tapi ada satu masalah: subquery yang dalam dan berlapis sangat cepat menjadi tidak terbaca. Solusi elegannya adalah Common Table Expressions (CTE).

Di episode ini, kita akan membahas non-recursive CTE dan cara menyusun multiple CTEs dalam satu query, lalu masuk ke recursive CTE dengan sintaks anchor member + UNION ALL + recursive member, dan mengaplikasikannya pada data hierarki seperti struktur organisasi dan taksonomi kategori.

#Non-Recursive CTE: Clause WITH

CTE adalah tabel hasil sementara yang diberi nama dan hanya hidup dalam satu query. Sintaksnya sederhana:

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

#Contoh: Membuat Query Modular

Ambil contoh kasus dari episode 7: mencari user dengan total belanja di atas rata-rata. Tanpa CTE, query-nya berlapis dan sulit dibaca. Dengan CTE, kita pecah menjadi langkah-langkah bernama:

WITH ringkasan_order AS (
    SELECT customer_id, SUM(total) AS total_belanja
    FROM orders
    GROUP BY customer_id
),
rata_rata AS (
    SELECT AVG(total_belanja) AS nilai_rata FROM ringkasan_order
)
SELECT u.email, r.total_belanja
FROM ringkasan_order r
JOIN users u ON u.id = r.customer_id
CROSS JOIN rata_rata ra
WHERE r.total_belanja > ra.nilai_rata
ORDER BY r.total_belanja DESC;

#Multiple CTEs: Memecah Query Raksasa

Salah satu kekuatan CTE adalah kemampuan memecah query raksasa menjadi potongan-potongan yang bisa diuji satu per satu:

WITH
jumlah_pelanggan AS (
    SELECT country, COUNT(*) AS total FROM users GROUP BY country
),
revenue AS (
    SELECT c.country, SUM(o.total) AS total_revenue
    FROM orders o
    JOIN users u ON u.id = o.customer_id
    JOIN customers c ON c.id = u.id
    GROUP BY c.country
)
SELECT j.country, j.total AS pelanggan, r.total_revenue
FROM jumlah_pelanggan j
JOIN revenue r USING (country)
ORDER BY r.total_revenue DESC;

#CTE vs Subquery: Kapan Pakai Apa?

#Recursive CTE: WITH RECURSIVE

Recursive CTE memungkinkan query memanggil dirinya sendiri, sehingga bisa mengolah data hierarkis: struktur organisasi, taksonomi kategori, pohon komentar, bill of materials, dan jaringan.

#Sintaks Recursive CTE

Recursive CTE terdiri atas dua bagian yang digabung UNION ALL:

WITH RECURSIVE nama AS (
    query_anchor      -- titik awal (seed)
    UNION ALL
    query_recursive   -- memanggil nama sendiri
)
SELECT ... FROM nama;

• Anchor member: query awal yang menghasilkan baris pertama (biasanya node akar/level 0).
• Recursive member: query yang mereferensikan CTE itu sendiri, menghasilkan baris level berikutnya.
• Proses berulang sampai recursive member tidak menghasilkan baris lagi.

#Studi Kasus: Struktur Organisasi

Misal kita punya tabel employees dari episode 6 (dengan manager_id). Untuk menampilkan seluruh hierarki dengan level kedalamannya:

WITH RECURSIVE hierarki AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarki h ON e.manager_id = h.id
)
SELECT id, name, level
FROM hierarki
ORDER BY level, name;

Anchor member memilih CEO (karyawan tanpa manager). Recursive member lalu mengambil karyawan yang managernya adalah baris hasil sebelumnya, menaikkan level. Hasilnya: setiap karyawan dengan level kedalamannya di organisasi.

#Studi Kasus: Taksonomi Kategori

Pola yang sama berlaku untuk kategori produk yang bertingkat:

WITH RECURSIVE kategori_tree AS (
    SELECT id, name, parent_id, name AS path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id,
           kt.path || ' > ' || c.name
    FROM categories c
    JOIN kategori_tree kt ON c.parent_id = kt.id
)
SELECT id, name, path
FROM kategori_tree
ORDER BY path;

Selain kedalaman, kita juga membangun path yang menunjukkan lintasan penuh: Elektronik > Laptop > Gaming. Ini contoh kekuatan recursive CTE yang tidak bisa ditiru oleh join biasa.

#Batasi Kedalaman: Menambahkan Guard

Untuk data yang berpotensi bermasalah, tambahkan batas kedalaman di recursive member:

WITH RECURSIVE hierarki AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarki h ON e.manager_id = h.id
    WHERE h.level < 10
)
SELECT id, name, level FROM hierarki;

#Penutup

Inti yang harus dibawa pulang:

• CTE membuat query kompleks terbaca seperti cerita berurutan — nama yang baik adalah dokumentasi.
• Multiple CTE bisa saling mereferensikan dalam satu query.
• WITH RECURSIVE memungkinkan query memanggil dirinya sendiri untuk data pohon.
• Sintaks recursive: anchor member + UNION ALL + recursive member.
• Selalu pasang guard kedalaman untuk mencegah loop tak berujung.

Di episode 11 selanjutnya, kita masuk ke fondasi keandalan database: Transaction Management, ACID & Concurrency Control — mulai dari prinsip Atomicity, Consistency, Isolation, Durability, perintah BEGIN, COMMIT, ROLLBACK, dan SAVEPOINT, tingkat isolation level, hingga locking eksplisit dengan SELECT ... FOR UPDATE untuk mencegah race condition.