Window Functions Dasar
Tujuan Pembelajaran
- Membedakan window function dari agregasi GROUP BY.
- Menggunakan ROW_NUMBER dan RANK dengan urutan deterministik.
- Menghitung agregat per kelompok sambil mempertahankan detail baris.
Menghitung dalam jendela tanpa menggabungkan baris
Window function menambahkan hasil hitung pada setiap baris yang ada. Dalam contoh, PARTITION BY membagi pesanan menurut pelanggan. Di dalam setiap kelompok, ORDER BY menentukan urutan tanggal dan ID, lalu ROW_NUMBER memberi nomor 1, 2, dan seterusnya. Jumlah baris tetap sama karena query tidak merangkum detail menjadi satu baris per pelanggan.
Window function menghitung nilai atas sekumpulan baris terkait, tetapi tetap mengembalikan baris detail satu per satu. Berbeda dengan GROUP BY, ia tidak mereduksi semua anggota kelompok menjadi satu baris. Klausa OVER mendefinisikan jendela; PARTITION BY membaginya menjadi kelompok dan ORDER BY menentukan urutan di dalam kelompok.
SELECT id_pesanan, id_pelanggan, tanggal_pesanan,
ROW_NUMBER() OVER (
PARTITION BY id_pelanggan
ORDER BY tanggal_pesanan, id_pesanan
) AS urutan_pesanan
FROM pesanan
ORDER BY id_pelanggan, urutan_pesanan;
Setiap pelanggan mendapat nomor mulai dari satu. ID pesanan menjadi tie-breaker saat tanggal sama agar urutan stabil.
Peringkat dan agregat window
PARTITION BY dapat dibayangkan sebagai memulai kembali hitungan untuk setiap kelompok. ORDER BY di dalam OVER menentukan baris mana yang mendapat nomor lebih dulu. ORDER BY di bagian akhir query mengatur tampilan seluruh hasil. Keduanya punya tugas berbeda, jadi menulis urutan luar tetap penting meskipun window sudah memiliki urutan.
RANK() memberi peringkat yang sama pada nilai seri dan melompati angka peringkat sesudahnya; DENSE_RANK() tidak melompati. Keduanya tersedia pada banyak DBMS modern. Contoh: RANK() OVER (ORDER BY omzet DESC) merangking omzet. Agregat juga bisa menjadi window function, misalnya SUM(omzet) OVER (PARTITION BY id_pelanggan) menambahkan total pelanggan pada setiap baris pesanan.
Untuk running total, tambahkan urutan dan frame eksplisit, misalnya ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Ketersediaan dan detail window frame dapat bergantung versi DBMS. Hasil tetap perlu urutan akhir melalui ORDER BY luar; urutan dalam OVER tidak menjamin urutan tampilan.
Latihan Interaktif
Latihan 01
Buat CTE dengan nomor urut pesanan per pelanggan menggunakan ROW_NUMBER yang diurutkan menurut tanggal dan ID. Tampilkan riwayat pelanggan ID 1.
WITH riwayat AS (
SELECT id_pelanggan, id_pesanan, tanggal_pesanan,
ROW_NUMBER() OVER (
PARTITION BY id_pelanggan
ORDER BY tanggal_pesanan, id_pesanan
) AS nomor_pesanan
FROM pesanan
)
SELECT id_pelanggan, id_pesanan, nomor_pesanan
FROM riwayat
WHERE id_pelanggan = 1
ORDER BY nomor_pesanan;
Hasil query
Lingkungan latihan belum disiapkan. Jalankan Query untuk memulai.
Lihat Petunjuk
Nomor ulang per pelanggan memerlukan PARTITION BY. Tambahkan ID sebagai tie-breaker bila tanggal sama.
Lihat Pembahasan
Window function menambahkan nomor tanpa menggabungkan pesanan. RANK dan SUM juga dapat dipakai dengan OVER, misalnya untuk peringkat dan total berjalan.
Ringkasan
- Window function mempertahankan detail baris,
GROUP BYmereduksi per kelompok. PARTITION BYmendefinisikan kelompok danORDER BYmenentukan urutan window.ROW_NUMBER,RANK, dan agregat window tersedia pada banyak DBMS modern; cek versi yang digunakan.- Tambahkan tie-breaker untuk nomor yang deterministik dan
ORDER BYluar untuk tampilan.
Bacaan lebih lanjut
Langkah berikutnya