Naik Level: CTE (WITH) & Window Function (RANK, ROW_NUMBER)

Dua fitur yang mengubah cara menulis SQL

MySQL 8.0 (2018) membawa dua senjata yang sebelumnya hanya ada di database "besar": CTE dan window function. Keduanya membuat query rumit jadi rapi dan mudah dibaca.

CTE: tabel sementara bernama

WITH memberi nama pada sebuah query agar bisa dipakai seperti tabel di query utama. Bandingkan dengan subquery di FROM: CTE ditulis di atas, dibaca dari atas ke bawah.

WITH belanja AS (
  SELECT pelanggan_id, COUNT(*) AS jumlah_pesanan
  FROM pesanan
  GROUP BY pelanggan_id
)
SELECT pl.nama, b.jumlah_pesanan
FROM belanja b
JOIN pelanggan pl ON pl.id = b.pelanggan_id
WHERE b.jumlah_pesanan >= 2;

Boleh membuat beberapa CTE berurutan (WITH a AS (...), b AS (...)) yang saling memakai. Ada juga WITH RECURSIVE untuk data berjenjang seperti struktur organisasi.

Window function: hitung tanpa menggabungkan baris

Agregat + GROUP BY meringkas baris menjadi satu per kelompok. Window function menghitung hal serupa tetapi setiap baris tetap tampil. Ia ditandai kata OVER (...).

SELECT nama, departemen_id, gaji,
       RANK() OVER (PARTITION BY departemen_id ORDER BY gaji DESC) AS peringkat
FROM karyawan;

• PARTITION BY departemen_id — bagi baris menjadi "jendela" per departemen (seperti GROUP BY, tapi tanpa menghilangkan baris).
• ORDER BY gaji DESC — urutan di dalam tiap jendela.
• RANK() — nomor peringkat di dalam jendela itu.

+----------------+---------------+----------+------------+
| nama           | departemen_id |     gaji | peringkat  |
+----------------+---------------+----------+------------+
| Rina Wijaya    |             1 | 30000000 |          1 |
| Bagas Prakoso  |             1 | 15000000 |          2 |
| Dimas Anggara  |             1 |  5500000 |          3 |
| Elsa Maharani  |             1 |  5500000 |          3 |   ← seri
| Hendra Saputra |             1 |  5000000 |          5 |   ← lompat ke 5
...

Fungsi peringkat yang perlu dibedakan

Fungsi lain: LAG()/LEAD() (nilai baris sebelumnya/berikutnya), SUM() OVER (...) untuk total berjalan, AVG() OVER (PARTITION BY ...) untuk membandingkan tiap baris dengan rata-rata kelompoknya.

Kombinasi paling berguna: CTE + window = "top N per kelompok"

Window function tidak boleh dipakai langsung di WHERE. Solusinya: hitung di CTE, saring di query luar.

WITH ranked AS (
  SELECT nama, departemen_id, gaji,
         RANK() OVER (PARTITION BY departemen_id ORDER BY gaji DESC) AS peringkat
  FROM karyawan
)
SELECT nama, departemen_id, gaji, peringkat
FROM ranked
WHERE peringkat <= 2
ORDER BY departemen_id, peringkat, nama;

Itu menjawab pertanyaan klasik "2 gaji tertinggi di setiap departemen", sesuatu yang sangat merepotkan di MySQL 5.7 ke bawah.