KAL
Menu

CTE dan keterbacaan

Menyusun query kompleks dengan CTE agar terbaca seperti pipeline, lengkap dengan pola dan batasannya.

Daftar isi

Topik ini membahas cara menyusun query kompleks agar tetap terbaca, dengan Common Table Expression (CTE), yakni tabel sementara yang didefinisikan di awal query melalui klausa WITH. Pembahasan membandingkan subquery bertumpuk dengan CTE berantai, menunjukkan CTE rekursif untuk data hierarkis, lalu menutup dengan batasan penggunaannya. Semua contoh dijalankan dengan DuckDB 2.0 melalui Python, dan setiap keluaran dapat direproduksi ulang dari skripnya. Setelah membaca, query bertingkat dapat dipecah menjadi langkah-langkah bernama yang mudah diaudit.

Dataset contoh#

Contoh memakai katalog kecil kedai kopi dengan tiga tabel yang saling terhubung. Tabel kategori membentuk hierarki dua tingkat melalui kolom parent_id, tabel produk merujuk kategori melalui kategori_id, dan tabel detail_pesanan mencatat qty serta merujuk produk melalui produk_id. Pertanyaan yang dijawab sepanjang topik ini selalu sama, yaitu pendapatan per kategori dengan ambang nilai tertentu. Skrip berikut membangun ulang basis data katalog.duckdb setiap kali dijalankan.

python siapkan_basisdata.py 
import os
import duckdb

# basis data kecil dibangun ulang setiap kali skrip dijalankan
if os.path.exists("katalog.duckdb"):
    os.remove("katalog.duckdb")

con = duckdb.connect("katalog.duckdb")
con.execute("""
CREATE TABLE kategori (
    id        INTEGER PRIMARY KEY,
    nama      VARCHAR NOT NULL,
    parent_id INTEGER
)
""")
con.execute("""
INSERT INTO kategori VALUES
    (1, 'minuman', NULL),
    (2, 'kopi',    1),
    (3, 'kopi susu', 2),
    (4, 'teh',     1),
    (5, 'makanan', NULL),
    (6, 'roti',    5)
""")
con.execute("""
CREATE TABLE produk (
    id          INTEGER PRIMARY KEY,
    nama        VARCHAR NOT NULL,
    kategori_id INTEGER REFERENCES kategori (id),
    harga       INTEGER NOT NULL
)
""")
con.execute("""
INSERT INTO produk VALUES
    (10, 'kopi susu gula aren', 3, 22000),
    (11, 'kopi susu cokelat',   3, 24000),
    (12, 'americano',           2, 18000),
    (13, 'teh melati',          4, 12000),
    (14, 'roti bakar',          6, 15000)
""")
con.execute("""
CREATE TABLE detail_pesanan (
    pesanan_id INTEGER,
    produk_id  INTEGER REFERENCES produk (id),
    qty        INTEGER NOT NULL
)
""")
con.execute("""
INSERT INTO detail_pesanan VALUES
    (501, 10, 2),
    (501, 14, 1),
    (502, 11, 1),
    (502, 13, 2),
    (503, 12, 3),
    (503, 10, 1),
    (504, 14, 2),
    (504, 13, 1)
""")

for tabel in ("kategori", "produk", "detail_pesanan"):
    jumlah = con.execute(f"SELECT COUNT(*) FROM {tabel}").fetchone()[0]
    print(f"{tabel}: {jumlah} baris")
con.close()
text Keluaran 
kategori: 6 baris
produk: 5 baris
detail_pesanan: 8 baris

Subquery bertumpuk dan biaya keterbacanya#

Subquery bertumpuk menaruh satu query di dalam query lain, dan setiap tingkat tambahan menambah satu lapisan indentasi yang harus diurai pembaca. Query di bawah ini menghitung pendapatan per kategori dengan tiga tingkat, yaitu perhitungan nilai item di paling dalam, agregasi per kategori di tengah, dan penyaringan di luar.

python subquery_bertumpuk.py 
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("katalog.duckdb")

tampilkan(con, """
SELECT k.nama, rk.total
FROM kategori k
JOIN (
    SELECT kategori_id, SUM(nilai) AS total
    FROM (
        SELECT p.kategori_id, d.qty * p.harga AS nilai
        FROM detail_pesanan d
        JOIN produk p ON p.id = d.produk_id
    ) item_terjual
    GROUP BY kategori_id
) rk ON rk.kategori_id = k.id
WHERE rk.total >= 50000
ORDER BY rk.total DESC
""")
con.close()
text Keluaran 
nama       total
kopi susu  90000
kopi       54000

Hasilnya benar, yaitu kategori kopi susu dengan 90000 dan kopi dengan 54000. Masalahnya bukan pada hasil, melainkan pada urutan membacanya. Pembaca harus mencari subquery paling dalam terlebih dahulu, mencatat alias item_terjual, naik ke agregasi rk, lalu kembali ke daftar kolom. Alias satu atau dua huruf seperti rk memaksa pembaca mengingat pemetaan yang tidak tertulis. Ketika kebutuhan bertambah, misalnya memecah per bulan, tingkat tumpukan bertambah dan versi ini cepat sulit dirawat.

CTE berantai sebagai pipeline#

CTE memecah query yang sama menjadi langkah-langkah bernama yang dibaca dari atas ke bawah, mirip urutan kerja pipeline data. Setiap CTE didefinisikan sekali dengan nama yang bermakna, boleh merujuk CTE sebelumnya, dan hanya bagian terakhir yang menghasilkan keluaran. Query berikut menghitung hasil yang identik dengan versi subquery, tetapi strukturnya berupa tiga langkah berurutan.

python cte_berantai.py 
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("katalog.duckdb")

tampilkan(con, """
WITH item_terjual AS (
    SELECT p.kategori_id, d.qty * p.harga AS nilai
    FROM detail_pesanan d
    JOIN produk p ON p.id = d.produk_id
),
per_kategori AS (
    SELECT kategori_id, SUM(nilai) AS total
    FROM item_terjual
    GROUP BY kategori_id
)
SELECT k.nama, pk.total
FROM per_kategori pk
JOIN kategori k ON k.id = pk.kategori_id
WHERE pk.total >= 50000
ORDER BY pk.total DESC
""")
con.close()
text Keluaran 
nama       total
kopi susu  90000
kopi       54000

Keluarannya sama persis dengan versi subquery, dan kesamaan itu sendiri menjadi bukti bahwa CTE adalah perubahan bentuk, bukan perubahan semantik. Langkah item_terjual menjelaskan dari mana nilai lahir, langkah per_kategori menjelaskan agregasinya, sedangkan query utama hanya mengurus penyaringan dan pelabelan. Setiap nama juga menjadi tempat pemeriksaan, misalnya melihat isi item_terjual dengan SELECT * FROM item_terjual tanpa menyentuh sisa query. Kebiasaan ini sekaligus mengurangi duplikasi, karena satu CTE dapat dirujuk lebih dari sekali dalam query yang sama.

CTE rekursif untuk hierarki#

CTE rekursif melangkah berulang pada data yang saling merujuk antarbarisnya, seperti hierarki kategori pada tabel contoh. Bentuknya terdiri atas dua bagian yang dihubungkan UNION ALL, yaitu anggota jangkar (anchor) yang memilih titik awal dan anggota rekursif yang memperluas hasil putaran sebelumnya. Query berikut menelusuri pohon kategori dari akar ke daun sambil mencatat tingkat dan jalur lengkapnya.

python cte_rekursif.py 
import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("katalog.duckdb")

tampilkan(con, """
WITH RECURSIVE pohon AS (
    SELECT id, nama, 1 AS tingkat, nama AS jalur
    FROM kategori
    WHERE parent_id IS NULL
    UNION ALL
    SELECT k.id, k.nama, p.tingkat + 1, p.jalur || ' > ' || k.nama
    FROM kategori k
    JOIN pohon p ON k.parent_id = p.id
)
SELECT tingkat, jalur
FROM pohon
ORDER BY jalur
""")
con.close()
text Keluaran 
tingkat  jalur
1        makanan
2        makanan > roti
1        minuman
2        minuman > kopi
3        minuman > kopi > kopi susu
2        minuman > teh

Anggota jangkar memilih dua kategori tanpa induk, yaitu minuman dan makanan, dengan tingkat 1. Setiap putaran anggota rekursif mengambil kategori yang induknya sudah ada di dalam pohon, menaikkan tingkatnya, dan menggabungkan jalur induk dengan nama kategori baru. Hasil akhirnya memuat enam kategori dengan kedalaman hingga tiga tingkat, dan ORDER BY jalur membuat induk selalu tampil sebelum anaknya. Pola yang sama dipakai untuk struktur organisasi, rute pengiriman, dan graf dependensi antartabel.

Batasan dan praktik penggunaan CTE#

CTE adalah alat keterbacaan, bukan penjamin performa, dan dua batasannya perlu diketahui sejak awal. Banyak mesin, termasuk DuckDB, melipat CTE ke dalam query utama saat menyusun rencana eksekusi, sehingga CTE yang dirujuk berkali-kali dapat dihitung lebih dari sekali. Untuk kasus itu, DuckDB menyediakan kata kunci MATERIALIZED pada definisi CTE agar hasilnya dihitung sekali dan disimpan sementara.

Jumlah langkah juga perlu dibatasi, karena rantai sepuluh CTE untuk logika yang sebenarnya ditulis dengan dua JOIN justru menyembunyikan bentuk sederhananya. Aturan praktisnya, satu CTE mewakili satu transformasi yang dapat dijelaskan dalam satu kalimat, dan rantainya tidak lebih panjang dari yang masih nyaman ditelusuri. Keputusan performa akhirnya tetap dinilai dari rencana eksekusi, bukan dari bentuk tulisannya.

Kesalahan umum#

Tiga kekeliruan berikut paling sering muncul saat CTE mulai dipakai, beserta penyebab dan perbaikannya.

  • Merujuk CTE sebelum didefinisikan. Query diuraikan dari atas ke bawah, sehingga sebuah CTE hanya boleh memanggil CTE yang tercantum lebih dulu. Susun urutan definisi mengikuti aliran datanya.
  • Menganggap CTE otomatis dihitung sekali. Tanpa MATERIALIZED, CTE yang dirujuk berulang dapat dievaluasi ulang pada setiap rujukannya. Tandai dengan MATERIALIZED bila perhitungannya mahal.
  • Menganggap CTE mengubah hasil query. CTE mengubah struktur tulisan, bukan semantiknya, sehingga hasilnya harus identik dengan versi bertumpuk. Bandingkan keluaran versi lama dan baru bila ragu.

Ringkasan#

  • Subquery bertumpuk dibaca dari dalam ke luar, sedangkan CTE berantai dibaca dari atas ke bawah dengan langkah bernama.
  • Keluaran kedua bentuk itu identik, sehingga pemilihannya adalah keputusan keterbacaan dan pemeliharaan.
  • CTE rekursif terdiri atas jangkar dan anggota rekursif yang dihubungkan UNION ALL, dan wajib memiliki kondisi berhenti.
  • CTE tidak menjamin performa, dan MATERIALIZED tersedia ketika perhitungan sekali jalan lebih diinginkan.

Referensi#