Daftar Materi

SQL untuk Analisis Data Materi 03

Common Table Expressions (CTE)

Tujuan Pembelajaran

  • Menamai hasil antara menggunakan klausa WITH.
  • Menyusun query bertahap agar analisis kompleks lebih mudah diperiksa.
  • Menggunakan CTE untuk meringkas penjualan dengan query bertahap.

Hasil antara yang bernama

CTE dapat dibaca seperti langkah bernama dalam sebuah perhitungan. Bagian WITH menyiapkan hasil sementara bernama omzet_per_pesanan. SELECT utama kemudian membaca hasil itu dan menerapkan batas omzet. Nama tersebut menjelaskan isi setiap tahap, sehingga query panjang lebih mudah ditelusuri daripada satu ekspresi bersarang.

Common Table Expression (CTE) diperkenalkan dengan WITH dan berlaku untuk satu pernyataan berikutnya. CTE memberi nama pada query antara sehingga bagian analisis dapat dibaca secara berurutan, tanpa menyalin subquery bertingkat berkali-kali.

WITH omzet_per_pesanan AS (
    SELECT p.id_pesanan,
           SUM(dp.jumlah * dp.harga_saat_pesan) AS omzet
    FROM pesanan AS p
    JOIN detail_pesanan AS dp ON dp.id_pesanan = p.id_pesanan
    WHERE p.status = 'selesai'
    GROUP BY p.id_pesanan
)
SELECT id_pesanan, omzet
FROM omzet_per_pesanan
WHERE omzet >= 80000
ORDER BY omzet DESC;

CTE pertama menghitung nilai pada grain satu baris per pesanan; query utama kemudian memilih pesanan dengan omzet setidaknya 80.000. Pemisahan ini membuat batas agregasi mudah diverifikasi.

Lebih dari satu tahap

Pada contoh, tahap pertama menggabungkan pesanan dengan rinciannya dan menghitung total per ID pesanan. Tahap berikutnya memilih pesanan yang melewati batas. Periksa hasil antara sebelum melanjutkan: jika jumlah per pesanan sudah keliru, filter akhir hanya akan menyembunyikan masalah, bukan memperbaikinya.

Satu WITH dapat mendefinisikan beberapa CTE yang dipisahkan koma. CTE berikutnya dapat memakai CTE sebelumnya. Beri nama yang menjelaskan isi dan grain, seperti omzet_per_pesanan atau penjualan_per_kategori, bukan nama samar data1.

Banyak DBMS mendukung WITH RECURSIVE untuk pola berulang seperti hierarki, tetapi sintaks detail dan batas rekursinya dapat berbeda. CTE biasa bukan tabel permanen dan tidak mengubah database. Optimizer dapat meng-inline atau mematerialisasi CTE sesuai kebutuhan. Pilih struktur yang jelas, lalu ukur kinerjanya pada data yang mewakili penggunaan sebenarnya.

Latihan Interaktif

Latihan 01

Dengan CTE, hitung jumlah pesanan selesai yang memiliki detail, total omzetnya, dan nilai pesanan tertinggi.

WITH omzet_per_pesanan AS (
    SELECT p.id_pesanan,
           SUM(d.jumlah * d.harga_saat_pesan) AS omzet
    FROM pesanan AS p
    JOIN detail_pesanan AS d ON d.id_pesanan = p.id_pesanan
    WHERE p.status = 'selesai'
    GROUP BY p.id_pesanan
)
SELECT COUNT(*) AS jumlah_pesanan,
       SUM(omzet) AS total_omzet,
       MAX(omzet) AS omzet_tertinggi
FROM omzet_per_pesanan;

Lihat Petunjuk

Pisahkan omzet per pesanan di dalam CTE, kemudian gunakan COUNT, SUM, dan MAX di query utama.

Lihat Pembahasan

Tiga belas pesanan selesai memiliki detail. Total omzet itemnya Rp978.000; pesanan tertinggi Rp142.000.

Ringkasan

  • WITH nama AS (SELECT ...) memberi nama pada hasil antara.
  • CTE berlaku pada pernyataan yang mengikutinya.
  • Pisahkan query menurut langkah analisis dan tulis grain setiap langkah.
  • CTE membantu keterbacaan; ia bukan jaminan peningkatan kinerja.

Bacaan lebih lanjut