Common Table Expressions Lanjutan

Kita sudah mengenal dasar Common Table Expression (CTE) pada section sebelumnya: klausa WITH memberi nama pada subquery, beberapa CTE bisa dirangkai beruntun, dan satu CTE bisa dipanggil berkali-kali dalam satu statement. Chapter ini menaikkan level pemakaian CTE pada tiga arah yang sering muncul di pekerjaan nyata. Pertama, teknik penulisan — kolom eksplisit dan pemakaian berulang lewat self-join — yang membuat pipeline transformasi data makin rapi. Kedua, kombinasi CTE dengan window function untuk query laporan bertingkat. Ketiga, posisi WITH pada statement pemodifikasi data: MySQL 8 memperbolehkan CTE mendahului UPDATE dan DELETE, dan kita akan uji sendiri bentuk mana yang diterima untuk INSERT. Semuanya kita bawa ke database latihan belajar_adv dengan tabel penjualan yang sederhana namun cukup untuk semua skenario.

Menyiapkan Tabel Latihan #

Kita pakai delapan transaksi dari lima pelanggan. Nilai kolom jumlah dipilih agar total unit tiap pelanggan mudah dihitung ulang secara manual — Budi 5, Siti 5, Andi 6, Rina 2, dan Dewi 8:

DROP DATABASE IF EXISTS belajar_adv;
CREATE DATABASE belajar_adv;
USE belajar_adv;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE penjualan (
  id INT PRIMARY KEY,
  pelanggan VARCHAR(30) NOT NULL,
  kategori VARCHAR(20) NOT NULL,
  produk VARCHAR(30) NOT NULL,
  jumlah INT NOT NULL,
  catatan VARCHAR(30) NULL
);

INSERT INTO penjualan (id, pelanggan, kategori, produk, jumlah) VALUES
  (1, 'Budi',  'Kopi', 'Kopi Gayo',   2),
  (2, 'Budi',  'Teh',  'Teh Melati',  3),
  (3, 'Siti',  'Kopi', 'Kopi Toraja', 4),
  (4, 'Siti',  'Teh',  'Teh Melati',  1),
  (5, 'Andi',  'Gula', 'Gula Aren',   6),
  (6, 'Rina',  'Teh',  'Teh Melati',  2),
  (7, 'Dewi',  'Kopi', 'Kopi Toraja', 5),
  (8, 'Dewi',  'Gula', 'Gula Aren',   3);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Rekap singkat pola dasar: WITH rekap AS (SELECT pelanggan, SUM(jumlah) AS total_unit FROM penjualan GROUP BY pelanggan) menghasilkan tabel sementara bernama rekap, lalu query utama tinggal memanggil rekap di klausa FROM. Total unit kelima pelanggan adalah bahan baku seluruh contoh pada chapter ini.

CTE Bersarang: CTE dari CTE #

CTE kedua boleh membaca CTE pertama, dan CTE ketiga boleh membaca hasil gabungan keduanya — pola bersarang ini memecah transformasi panjang menjadi tahapan yang terbaca dari atas ke bawah. Kita rangkai dua tahap: saring transaksi besar terlebih dahulu, baru rekapitulasi per pelanggan:

WITH besar AS (
  SELECT pelanggan, kategori, jumlah
  FROM penjualan
  WHERE jumlah >= 3
),
rekap AS (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM besar
  GROUP BY pelanggan
)
SELECT pelanggan, total_unit
FROM rekap
ORDER BY total_unit DESC, pelanggan;

Output yang Dihasilkan:

+-----------+------------+
| pelanggan | total_unit |
+-----------+------------+
| Dewi      |          8 |
| Andi      |          6 |
| Siti      |          4 |
| Budi      |          3 |
+-----------+------------+
4 rows in set (0.00 sec)

Mari kita urutkan alirannya tahap demi tahap. CTE besar menyaring penjualan dan hanya menyimpan transaksi berjumlah tiga unit ke atas, sehingga tersisa lima baris: Teh Melati Budi (3), Kopi Toraja Siti (4), Gula Aren Andi (6), Kopi Toraja Dewi (5), dan Gula Aren Dewi (3). CTE rekap membaca besar — bukan tabel asli — lalu menjumlahkan per pelanggan menjadi Dewi 8, Andi 6, Siti 4, dan Budi 3. Rina lenyap karena kedua transaksinya di bawah ambang tiga unit. Query utama tinggal mengurutkan hasil akhir; urutan definisi tetap penting karena rekap hanya boleh membaca CTE yang dideklarasikan di atasnya.

Kolom Eksplisit pada Definisi CTE #

Nama kolom sebuah CTE biasanya mengikuti alias di dalam definisinya. MySQL menyediakan bentuk kedua: daftar kolom eksplisit ditulis langsung setelah nama CTE, seperti WITH rekap (nama, unit) AS (...). Kedua bentuk menghasilkan hal yang sama, tetapi bentuk eksplisit menegaskan kontrak kolom di satu tempat:

WITH rekap (nama, unit) AS (
  SELECT pelanggan, SUM(jumlah)
  FROM penjualan
  GROUP BY pelanggan
)
SELECT nama, unit
FROM rekap
ORDER BY unit DESC, nama;

Output yang Dihasilkan:

+------+------+
| nama | unit |
+------+------+
| Dewi |    8 |
| Andi |    6 |
| Budi |    5 |
| Siti |    5 |
| Rina |    2 |
+------+------+
5 rows in set (0.00 sec)

Daftar (nama, unit) menimpa nama kolom hasil: kolom pertama subquery menjadi nama, kolom kedua menjadi unit — alias SUM(jumlah) di dalam definisi bahkan tidak perlu ditulis lagi. Jumlah kolom di daftar wajib sama dengan jumlah kolom hasil subquery; kelebihan atau kekurangan kolom langsung ditolak server. Bentuk ini paling berguna saat subquery berisi ekspresi kompleks yang kalau diberi alias akan menghasilkan nama panjang dan sulit dibaca, atau saat kita ingin nama kolom seragam pada beberapa CTE yang bentuknya berbeda.

Satu CTE Dipakai Dua Kali: Self-Join #

Pemakaian berulang yang paling menantang adalah join sebuah CTE dengan dirinya sendiri. Kita cari pasangan pelanggan yang totalnya persis sama — query yang tanpa CTE harus menyalin subquery rekap dua kali:

WITH rekap AS (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM penjualan
  GROUP BY pelanggan
)
SELECT a.pelanggan AS pelanggan_1,
       b.pelanggan AS pelanggan_2,
       a.total_unit
FROM rekap a
JOIN rekap b ON b.total_unit = a.total_unit AND a.pelanggan < b.pelanggan
ORDER BY a.pelanggan;

Output yang Dihasilkan:

+-------------+-------------+------------+
| pelanggan_1 | pelanggan_2 | total_unit |
+-------------+-------------+------------+
| Budi        | Siti        |          5 |
+-------------+-------------+------------+
1 row in set (0.00 sec)

Satu definisi rekap muncul dua kali di klausa FROM dengan alias berbeda: a dan b. Kondisi join b.total_unit = a.total_unit mencari pasangan bermuatan sama, sedangkan a.pelanggan < b.pelanggan memastikan tiap pasangan hanya muncul sekali dan tidak membandingkan seorang pelanggan dengan dirinya sendiri. Hasilnya satu baris: Budi dan Siti sama-sama bertotal 5 unit. Berbeda dengan derived table yang harus disalin utuh, satu sumber kebenaran rekap menjamin kedua sisi join memakai angka yang identik.

Kolom pada self-join seperti ini wajib memakai alias. Menyebut nama kolom tanpa awalan — pelanggan alih-alih a.pelanggan — membuat server kebingungan karena nama itu ada di kedua sisi. Kita buktikan sendiri perilakunya:

-- @error
WITH rekap AS (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM penjualan
  GROUP BY pelanggan
)
SELECT pelanggan FROM rekap JOIN rekap AS r2 ON rekap.pelanggan = r2.pelanggan;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1052 (23000): Column 'pelanggan' in field list is ambiguous
Bye

Server menolak dengan ERROR 1052 — kolom pelanggan ambigu karena tersedia di dua tabel sekaligus. Perbaikannya sederhana: beri alias pada kedua instance CTE (rekap AS a, rekap AS b) lalu selalu tulis kolomnya terkualifikasi.

CTE dan Window Function dalam Satu Query #

Kombinasi paling produktif di query laporan adalah CTE sebagai tahap agregasi, lalu window function sebagai tahap analisis di atasnya. Kita rangking pelanggan berdasarkan total unit, lalu saring hanya tiga teratas:

WITH rekap AS (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM penjualan
  GROUP BY pelanggan
)
SELECT pelanggan, total_unit,
       RANK() OVER (ORDER BY total_unit DESC) AS peringkat
FROM rekap
ORDER BY peringkat, pelanggan;

Output yang Dihasilkan:

+-----------+------------+-----------+
| pelanggan | total_unit | peringkat |
+-----------+------------+-----------+
| Dewi      |          8 |         1 |
| Andi      |          6 |         2 |
| Budi      |          5 |         3 |
| Siti      |          5 |         3 |
| Rina      |          2 |         5 |
+-----------+------------+-----------+
5 rows in set (0.00 sec)

Perhatikan pembagian tugasnya. CTE rekap meringkas delapan baris transaksi menjadi lima baris pelanggan; setelah ringkasan jadi, window function RANK() bekerja pada lima baris tersebut — bukan pada transaksi mentah. Kalau RANK() dipasang langsung pada penjualan, peringkat akan dihitung per transaksi dan hasilnya salah. Hasil akhir menempatkan Dewi di peringkat 1 dengan 8 unit, disusul Andi, lalu Budi dan Siti berseri di peringkat 3 dengan 5 unit — pola 1, 2, 3, 3 yang kita kenal dari chapter RANK dan DENSE_RANK. Pola bungkus-CTE inilah cara standar menyaring hasil window function, karena WHERE tidak bisa menyentuh hasil RANK() secara langsung.

WITH pada UPDATE dan DELETE #

Sejak MySQL 8, klausa WITH boleh mendahului statement UPDATE dan DELETE — CTE tidak lagi terbatas pada SELECT. Kita manfaatkan ini untuk menandai transaksi milik pelanggan besar: siapa pun yang totalnya lima unit ke atas mendapat catatan khusus:

WITH pembeli_besar AS (
  SELECT pelanggan
  FROM penjualan
  GROUP BY pelanggan
  HAVING SUM(jumlah) >= 5
)
UPDATE penjualan
SET catatan = 'pelanggan besar'
WHERE pelanggan IN (SELECT pelanggan FROM pembeli_besar);

SELECT id, pelanggan, produk, jumlah, catatan
FROM penjualan
ORDER BY id;

Output yang Dihasilkan:

Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

+----+-----------+-------------+--------+-----------------+
| id | pelanggan | produk      | jumlah | catatan         |
+----+-----------+-------------+--------+-----------------+
|  1 | Budi      | Kopi Gayo   |      2 | pelanggan besar |
|  2 | Budi      | Teh Melati  |      3 | pelanggan besar |
|  3 | Siti      | Kopi Toraja |      4 | pelanggan besar |
|  4 | Siti      | Teh Melati  |      1 | pelanggan besar |
|  5 | Andi      | Gula Aren   |      6 | pelanggan besar |
|  6 | Rina      | Teh Melati  |      2 | NULL            |
|  7 | Dewi      | Kopi Toraja |      5 | pelanggan besar |
|  8 | Dewi      | Gula Aren   |      3 | pelanggan besar |
+----+-----------+-------------+--------+-----------------+
8 rows in set (0.00 sec)

Klausa WITH pembeli_besar AS (...) berdiri di depan UPDATE dan menghitung daftar pelanggan dengan total lima ke atas: Budi, Siti, Andi, dan Dewi. Statement UPDATE kemudian menandai semua baris milik keempat nama itu — tujuh dari delapan baris — sedangkan transaksi Rina dibiarkan NULL karena totalnya hanya dua unit. Tanpa CTE, daftar itu harus ditulis sebagai subquery di dalam WHERE; versi WITH membaca sama seperti versi SELECT dan memindahkan logika ke depan.

Pola yang sama berlaku untuk DELETE. Kita hapus transaksi pelanggan yang total unitnya paling kecil — ambang dua unit ke bawah hanya menangkap Rina:

WITH pelanggan_kecil AS (
  SELECT pelanggan
  FROM penjualan
  GROUP BY pelanggan
  HAVING SUM(jumlah) <= 2
)
DELETE FROM penjualan
WHERE pelanggan IN (SELECT pelanggan FROM pelanggan_kecil);

SELECT id, pelanggan, produk, jumlah, catatan
FROM penjualan
ORDER BY id;

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

+----+-----------+-------------+--------+-----------------+
| id | pelanggan | produk      | jumlah | catatan         |
+----+-----------+-------------+--------+-----------------+
|  1 | Budi      | Kopi Gayo   |      2 | pelanggan besar |
|  2 | Budi      | Teh Melati  |      3 | pelanggan besar |
|  3 | Siti      | Kopi Toraja |      4 | pelanggan besar |
|  4 | Siti      | Teh Melati  |      1 | pelanggan besar |
|  5 | Andi      | Gula Aren   |      6 | pelanggan besar |
|  7 | Dewi      | Kopi Toraja |      5 | pelanggan besar |
|  8 | Dewi      | Gula Aren   |      3 | pelanggan besar |
+----+-----------+-------------+--------+-----------------+
7 rows in set (0.00 sec)

CTE pelanggan_kecil menghitung total per pelanggan lalu menyaring yang hasilnya dua unit ke bawah — hanya Rina (total 2) yang lolos. Statement DELETE kemudian menghapus satu-satunya baris milik Rina, baris dengan id 6, dan hasil SELECT penutup menunjukkan tujuh baris yang bertahan — semuanya masih membawa catatan 'pelanggan besar' dari langkah sebelumnya. Posisi WITH untuk DELETE sama seperti UPDATE: mendahului statement dan diikuti tabel target. Aturan penempatan ini tidak berlaku seragam untuk semua statement — INSERT menolak WITH di posisi depan, dan kita buktikan sendiri:

-- @error
WITH sumber AS (SELECT 'Kopi Robusta' AS produk, 3 AS jumlah)
INSERT INTO penjualan (id, pelanggan, kategori, produk, jumlah)
SELECT 9, 'Tono', 'Kopi', produk, jumlah FROM sumber;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO penjualan (id, pelanggan, kategori, produk, jumlah)
SELECT 9, 'Tono'' at line 2
Bye

Server menolak dengan ERROR 1064 — kesalahan sintaks tepat di kata INSERT. Bentuk yang benar untuk INSERT adalah kebalikannya: WITH diletakkan di sumber data setelah daftar kolom, seperti yang sudah kita pakai pada chapter RECURSIVE CTE. Contoh berikut memasukkan pelanggan besar ke tabel loyalitas dengan bentuk yang diterima:

CREATE TABLE loyal (pelanggan VARCHAR(30) PRIMARY KEY);

INSERT INTO loyal (pelanggan)
WITH besar AS (
  SELECT pelanggan FROM penjualan GROUP BY pelanggan HAVING SUM(jumlah) >= 5
)
SELECT pelanggan FROM besar
ORDER BY pelanggan;

SELECT * FROM loyal ORDER BY pelanggan;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

+-----------+
| pelanggan |
+-----------+
| Andi      |
| Budi      |
| Dewi      |
| Siti      |
+-----------+
4 rows in set (0.00 sec)

Klausa WITH besar AS (...) menggantikan posisi SELECT biasa di dalam INSERT ... SELECT, sehingga sumber data yang rumit tetap bisa dipecah menjadi tahapan bernama. Aturan praktisnya mudah diingat: WITH di depan untuk membaca data (SELECT) dan mengubah data (UPDATE, DELETE), WITH di dalam untuk mengisi data (INSERT).

Batasan yang Tetap Berlaku #

Semua kebebasan baru di atas tidak mengubah satu hukum dasar: CTE hanya hidup selama satu statement. Nama rekap atau pembeli_besar lenyap begitu statement selesai; statement berikutnya yang memanggil nama itu akan ditolak dengan error tabel tidak ada. Perulangan WITH ... UPDATE lalu SELECT FROM cte_lama dalam dua statement terpisah adalah kesalahan klasik yang tetap harus dihindari. Bila hasil antara perlu bertahan lintas statement, objek yang tepat adalah tabel sementara atau VIEW — keduanya sudah kita bahas di section masing-masing.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

DROP DATABASE belajar_adv;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)

Referensi #

• WITH (Common Table Expressions) — MySQL 8.4 Reference Manual
• UPDATE Statement — MySQL 8.4 Reference Manual
• DELETE Statement — MySQL 8.4 Reference Manual
• INSERT Statement — MySQL 8.4 Reference Manual

CTE kini kita kendalikan penuh: bersarang, berkolom eksplisit, dipakai ulang lewat self-join, dirangkai dengan window function, bahkan menemani UPDATE dan DELETE. Kemampuan berikutnya berpindah dari organisasi query ke isi query — chapter window-functions-lanjutan menghadirkan fungsi jendela yang belum kita sentuh: NTILE, FIRST_VALUE, LAST_VALUE, hingga perhitungan distribusi CUME_DIST dan PERCENT_RANK.