Common Table Expressions

Query analitik jarang selesai dalam satu langkah. Data engineer harus menggabungkan empat tabel, memfilter pesanan yang valid, lalu membandingkan agregasi dengan rata-rata — dan memaksakan semuanya ke dalam satu SELECT menghasilkan query yang sulit diperiksa ulang. Common Table Expression (CTE) menjawabnya: klausa WITH memberi nama pada hasil query antara sehingga query besar tersusun dari bagian kecil yang mudah dibaca. Kita berlatih pada database toko online: CTE dasar, CTE berantai, perbandingan dengan subquery, dan recursive CTE.

Database Contoh: Toko Online #

Sisa section ini memakai satu dataset dari skrip setup_toko.py — seluruh pelanggan, produk, pesanan, dan detail pesanan dari Chapter Pengenalan SQL tetap dipakai apa adanya. Toko digambarkan terus berjalan: katalog bertambah empat produk baru (id 7-10) dan dua pelanggan baru mendaftar (id 7-8), sedangkan seluruh pesanan beserta detailnya tidak berubah. Tabel kategori ditambahkan untuk contoh recursive CTE agar chapter 8 sampai 13 berbagi angka yang sama. Lima tabelnya:

Cuplikan isi keempat tabel inti:

from setup_toko import buat_database, tampilkan

koneksi = buat_database()
for tabel in ("pelanggan", "produk", "pesanan", "detail_pesanan"):
    print(f"=== {tabel} ===")
    tampilkan(koneksi, f"SELECT * FROM {tabel}")
    print()

Output yang Dihasilkan:

=== pelanggan ===
id | nama | kota | tanggal_daftar
1 | Budi Santoso | Jakarta | 2024-01-15
2 | Siti Rahayu | Bandung | 2024-02-03
3 | Dewi Lestari | Surabaya | 2024-03-21
4 | Rina Kartika | Jakarta | 2024-04-18
5 | Andi Wijaya | NULL | 2024-05-02
6 | Farhan Pratama | Bandung | 2024-06-25
7 | Gita Nuraini | Yogyakarta | 2024-08-14
8 | Hendra Gunawan | Medan | 2024-09-02

=== produk ===
id | nama | kategori | harga
1 | Smartphone X1 | elektronik | 3500000
2 | Laptop Pro 14 | elektronik | 12500000
3 | TWS Earbuds | elektronik | 750000
4 | Kemeja Batik | fashion | 250000
5 | Dress Summer | fashion | 380000
6 | Sneakers Klasik | fashion | 900000
7 | Kaos Polos Katun | pakaian | 95000
8 | Kemeja Flanel | pakaian | 180000
9 | Novel Laut Bercerita | buku | 110000
10 | Buku Sains Data | buku | 145000

=== pesanan ===
id | pelanggan_id | tanggal | status
1 | 1 | 2024-06-01 | selesai
2 | 2 | 2024-06-03 | selesai
3 | 1 | 2024-06-10 | dikirim
4 | 3 | 2024-06-15 | selesai
5 | 4 | 2024-06-20 | dibatalkan
6 | 2 | 2024-07-01 | diproses
7 | 5 | 2024-07-05 | selesai
8 | 3 | 2024-07-12 | diproses
9 | 1 | 2024-07-20 | selesai

=== detail_pesanan ===
pesanan_id | produk_id | jumlah | subtotal
1 | 1 | 1 | 3500000
1 | 4 | 2 | 500000
2 | 5 | 1 | 380000
2 | 3 | 1 | 750000
3 | 2 | 1 | 12500000
4 | 6 | 1 | 900000
4 | 4 | 1 | 250000
5 | 5 | 1 | NULL
6 | 3 | 2 | 1500000
7 | 6 | 1 | 900000
9 | 4 | 1 | 250000

Dua kejadian warisan chapter pertama tetap ada — kota Andi NULL dan subtotal NULL pada detail pesanan 5 yang dibatalkan — keduanya ditangani pada Chapter Conditional Expressions.

WITH Dasar: Memberi Nama pada Query #

Pertanyaan pertama: berapa pendapatan tiap kategori dari pesanan selesai? Bahan jawabannya tersebar di tiga tabel, jadi langkah agregasinya kita beri nama sebagai CTE:

WITH pendapatan_per_kategori AS (
    SELECT pr.kategori, SUM(d.subtotal) AS total
    FROM detail_pesanan d
    JOIN pesanan ps ON ps.id = d.pesanan_id
    JOIN produk pr ON pr.id = d.produk_id
    WHERE ps.status = 'selesai'
    GROUP BY pr.kategori
)
SELECT kategori, total
FROM pendapatan_per_kategori
ORDER BY total DESC;

Output yang Dihasilkan:

kategori | total
elektronik | 4250000
fashion | 3180000

Mari kita telaah klausa-klausa query di atas:

1. WITH pendapatan_per_kategori AS (...): mendefinisikan CTE — hasil query dalam kurung tersedia bagi query utama dengan nama tersebut.
2. JOIN tiga tabel: subtotal, status, dan kategori tersimpan di tabel berbeda sehingga harus digabung.
3. WHERE ps.status = 'selesai': hanya pesanan selesai yang dihitung sebagai pendapatan.
4. SELECT ... FROM pendapatan_per_kategori: query utama memakai CTE seperti tabel biasa.

Kategori pakaian dan buku tidak muncul karena JOIN tipe dalam hanya mempertahankan baris yang cocok — produk baru pada katalog memang belum memiliki penjualan, dan detail pesanan 5 yang dibatalkan tersaring oleh filter status. Klausa WITH didukung semua database utama; rujukan resminya di dokumentasi SQLite dan PostgreSQL .

CTE Berantai: Membangun Query Bertahap #

Kekuatan CTE baru terasa saat langkah dirangkai: CTE kedua boleh membaca CTE pertama. Kita lanjutkan pertanyaannya — dari kedua kategori tersebut, mana yang pendapatannya di atas rata-rata?

WITH pendapatan_per_kategori AS (
    SELECT pr.kategori, SUM(d.subtotal) AS total
    FROM detail_pesanan d
    JOIN pesanan ps ON ps.id = d.pesanan_id
    JOIN produk pr ON pr.id = d.produk_id
    WHERE ps.status = 'selesai'
    GROUP BY pr.kategori
),
nilai_rata_rata AS (
    SELECT AVG(total) AS rata_rata
    FROM pendapatan_per_kategori
)
SELECT p.kategori, p.total, r.rata_rata
FROM pendapatan_per_kategori p
CROSS JOIN nilai_rata_rata r
WHERE p.total > r.rata_rata
ORDER BY p.total DESC;

Output yang Dihasilkan:

kategori | total | rata_rata
elektronik | 4250000 | 3715000.0

Query ini membaca seperti daftar langkah: hitung pendapatan per kategori, hitung rata-ratanya, lalu bandingkan. Nilai 3715000.0 adalah 7430000 dibagi dua kategori — AVG() selalu mengembalikan pecahan; CROSS JOIN aman karena CTE kedua berisi satu baris.

CTE vs Subquery: Keterbacaan #

Hasil yang sama bisa dicapai tanpa CTE, tetapi subquery tidak bernama — setiap pemakaian ulang menuntut penulisan ulang penuh:

SELECT kategori, total
FROM (
    SELECT pr.kategori, SUM(d.subtotal) AS total
    FROM detail_pesanan d
    JOIN pesanan ps ON ps.id = d.pesanan_id
    JOIN produk pr ON pr.id = d.produk_id
    WHERE ps.status = 'selesai'
    GROUP BY pr.kategori
) pendapatan
WHERE total > (
    SELECT AVG(total)
    FROM (
        SELECT pr.kategori, SUM(d.subtotal) AS total
        FROM detail_pesanan d
        JOIN pesanan ps ON ps.id = d.pesanan_id
        JOIN produk pr ON pr.id = d.produk_id
        WHERE ps.status = 'selesai'
        GROUP BY pr.kategori
    ) salinan
)
ORDER BY total DESC;

Output yang Dihasilkan:

kategori | total
elektronik | 4250000

Hasilnya identik dengan versi CTE, tetapi query agregasi yang sama tertulis dua kali. Bila aturan bisnis berubah, dua tempat harus diperbaiki serempak — dan selisih kecil di antara keduanya menjadi galat yang sulit dilacak.

Recursive CTE untuk Hierarki Kategori #

Tabel kategori menyimpan hierarki lewat parent_id: gadget dan audio di bawah elektronik, pakaian dan alas kaki di bawah fashion. Query biasa tidak bisa menampilkan seluruh tingkatan sekaligus karena jumlahnya tidak tetap. Recursive CTE memecahkannya dengan dua bagian: baris jangkar, dan bagian rekursif yang membaca hasil CTE itu sendiri.

WITH RECURSIVE pohon_kategori AS (
    SELECT id, nama, parent_id, 1 AS kedalaman
    FROM kategori
    WHERE parent_id IS NULL
    UNION ALL
    SELECT k.id, k.nama, k.parent_id, p.kedalaman + 1
    FROM kategori k
    JOIN pohon_kategori p ON k.parent_id = p.id
)
SELECT kedalaman, nama
FROM pohon_kategori
ORDER BY kedalaman, nama;

Output yang Dihasilkan:

kedalaman | nama
1 | buku
1 | elektronik
1 | fashion
2 | alas kaki
2 | audio
2 | gadget
2 | pakaian

Mari kita telaah bagian-bagian recursive CTE di atas:

1. Bagian jangkar (WHERE parent_id IS NULL): mengambil kategori akar — buku, elektronik, fashion — dengan kedalaman 1 sebagai titik awal.
2. UNION ALL: menggabungkan hasil jangkar dengan setiap gelombang hasil berikutnya.
3. Bagian rekursif (JOIN pohon_kategori p): mencari anak dari baris gelombang sebelumnya; p.kedalaman + 1 menandai turun satu tingkat.
4. Perulangan berhenti ketika sebuah gelombang tidak menghasilkan baris baru — di sini, setelah kategori tingkat dua ditemukan.

CTE menjadikan query bertahap terbaca sebagai rangkaian langkah bernama, dan recursive CTE menjangkau data berjenjang yang tidak terjangkau subquery. Namun agregasi di atas tetap meruntuhkan baris: dua kategori berarti dua baris hasil. Chapter berikutnya, Window Functions, menghitung agregasi tanpa meruntuhkan baris — rata-rata berdampingan dengan tiap baris, total berjalan terakumulasi sepanjang waktu.