KAL
Menu

CTE

Memecah query rumit menjadi langkah bernama dengan klausa WITH.

Daftar isi

Topik ini membahas CTE, kependekan common table expression, yaitu hasil query bernama yang dideklarasikan pada klausa WITH dan dipakai seperti tabel sementara dalam satu pernyataan. Kontribusinya yang utama adalah keterbacaan, karena query panjang terbaca sebagai urutan langkah yang dinamai, bukan sebagai satu ekspresi bersarang yang dalam.

Blok penyiapan dataset#

bash 
# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

Satu langkah yang dinamai#

CTE dideklarasikan sebagai WITH nama AS (query), dan nama itu dipakai pada FROM sebagaimana tabel. Query berikut memisahkan perhitungan peminjaman per anggota dari laporan yang menyaringnya.

bash 
# langkah peminjaman per anggota, lalu disaring
psql -U postgres -d perpustakaan -c "WITH pinjaman_per_anggota AS (SELECT anggota_id, COUNT(*) AS jumlah FROM peminjaman GROUP BY anggota_id) SELECT a.nama, coalesce(p.jumlah, 0) AS pinjaman FROM anggota a LEFT JOIN pinjaman_per_anggota p ON p.anggota_id = a.id ORDER BY pinjaman DESC, a.nama;"
text Keluaran 
     nama     | pinjaman
--------------+----------
 Ali Rahman   |        2
 Budi Santoso |        2
 Dewi Lestari |        2
 Citra Kirana |        1
 Eka Pratama  |        1
(5 rows)

Bagian yang menghitung dan bagian yang melaporkan terpisah jelas, dan setiap bagian dapat diuji sendiri-sendiri dengan menjalankan isi CTE-nya. Bentuk ini menggantikan subquery bersarang yang sama, dan pembandingannya jelas ketika sarangnya lebih dari satu tingkat.

CTE yang dirujuk berkali-kali#

Satu CTE boleh dirujuk lebih dari sekali dalam satu pernyataan, dan rujukan kedua tidak mengulang teks perhitungannya. Query berikut membandingkan setiap buku dengan rata-rata tahun terbit kategorinya, yang ringkasannya dirujuk dua kali.

bash 
# buku yang terbit lebih baru dari rata-rata kategorinya
psql -U postgres -d perpustakaan -c "WITH rata_kategori AS (SELECT kategori_id, ROUND(AVG(tahun), 1) AS rata FROM buku GROUP BY kategori_id) SELECT b.judul, b.tahun, r.rata FROM buku b JOIN rata_kategori r ON r.kategori_id = b.kategori_id WHERE b.tahun > r.rata ORDER BY b.judul;"
text Keluaran 
            judul            | tahun |  rata
-----------------------------+-------+--------
 Algoritma dan Struktur Data |  2021 | 2020.3
 Dasar Basis Data            |  2021 | 2020.3
 Kimia Organik               |  2022 | 2021.0
(3 rows)

Subquery biasa untuk kebutuhan ini dirujuk sekali, sedangkan CTE menyimpan ringkasan itu sebagai langkah yang dipakai utuh. Saat rujukan bertambah, misalnya laporan yang membandingkan dua sisi terhadap ringkasan yang sama, CTE menjaga perhitungan tetap satu tempat.

Rantai CTE yang berurutan#

Beberapa CTE dipisahkan koma pada satu klausa WITH, dan yang berikutnya boleh merujuk yang sebelumnya. Rantai ini menjadi tulang punggung query analitik, karena setiap transformasi berdiri sebagai satu langkah yang diberi nama pekerjaannya.

bash 
# dua langkah: rekap bulanan, lalu bulan teratas
psql -U postgres -d perpustakaan <<'SQL'
WITH rekap_bulan AS (
    SELECT date_trunc('month', tanggal_pinjam)::date AS bulan, COUNT(*) AS pinjaman
    FROM peminjaman GROUP BY 1
),
teratas AS (
    SELECT * FROM rekap_bulan ORDER BY pinjaman DESC, bulan LIMIT 2
)
SELECT * FROM teratas ORDER BY bulan;
SQL
text Keluaran 
   bulan    | pinjaman
------------+----------
 2025-03-01 |        3
 2025-04-01 |        3
(2 rows)

CTE teratas merujuk rekap_bulan, dan query utamanya tinggal membaca hasil akhir. Struktur ini juga menjadi tempat data diuji per langkah, karena setiap CTE dijalankan sendiri dengan menyalin isi pernyataannya, kebiasaan yang mempercepat penelusuran hasil yang aneh.

Perilaku materialisasi#

Sejak PostgreSQL 12, CTE yang dirujuk sekali dan bebas efek samping diselipkan (inline) ke query induknya, sehingga tidak ada pagar pengoptimal antara keduanya. Kata MATERIALIZED memaksakan hasilnya dihitung sekali sebagai pemisah, berguna ketika perhitungannya mahal dan dirujuk berulang, atau ketika pemisahan rencana memang diinginkan.

Sebelum versi itu, seluruh CTE berperilaku pagar, sehingga sumber lama kadang memperlakukan CTE sebagai penghalang performa. Mengetahui kapan CTE diselipkan menghapus kebingungan saat membaca rencana eksekusi, dan pembahasannya menyusul pada modul index dan performa.

Kesalahan umum#

Kesalahan pertama adalah memecah query yang sudah jelas menjadi CTE berlapis-lapis, karena keterbacaan menurun ketika langkahnya memecah satu gagasan kecil. Kesalahan kedua adalah mengira CTE selalu dihitung sekali, padahal bentuk yang tidak dimaterialisasi diselipkan dan perhitungannya dapat berulang menurut rencana, dan MATERIALIZED dipakai saat penghitungan tunggal diinginkan.

Kesalahan ketiga adalah menamai CTE sama dengan tabel sungguhan, yang membuat rujukan selanjutnya di dalam pernyataan itu menunjuk CTE-nya, kebiasaan yang mengubah makna diam-diam. Kesalahan keempat adalah menaruh efek samping seperti INSERT di dalam CTE tanpa menyadari urutan eksekusinya tidak dijanjikan, pola yang dilarang pada data-modifying CTE tanpa kejelasan urutan.

Kebiasaan pengerjaan yang mempercepat query bertingkat adalah menguji setiap CTE secara terpisah dengan menyalin isinya ke jendela psql, lalu menyusunnya kembali setiap langkah terbukti, karena struktur WITH memang memisahkan unit pengujian secara alami. Ketika satu langkah menghasilkan jumlah baris yang janggal, kesalahannya terisolasi di CTE itu, bukan terselubung di dalam satu ekspresi bersarang. Praktik kecil ini menjadikan query panjang dapat dikerjakan oleh tim, karena tiap langkahnya dapat diperiksa orang lain.

Ringkasan#

  • WITH memberi nama pada hasil satu query, dan nama itu dipakai seperti tabel sepanjang pernyataan yang sama.
  • CTE dirujuk berkali-kali tanpa mengulang teks perhitungan, dan rantainya menyusun transformasi berurutan.
  • CTE yang dirujuk sekali diselipkan sejak PostgreSQL 12, dan MATERIALIZED memaksa penghitungan tunggal.
  • Keterbacaan menjadi ukuran utamanya, sehingga langkah yang dinamai mencerminkan satu gagasan utuh per langkah.

Referensi#