Fungsi Window ROW_NUMBER & RANK: PARTITION BY untuk Analisis SQL

Foto oleh Element5 Digital via Pexels

Fungsi Window ROW_NUMBER & RANK: PARTITION BY untuk Analisis Data SQL Server

Halo teman-teman Malam Ngoding!

Dalam perjalanan kita mendalami SQL Server, kita sudah banyak membahas bagaimana cara mengambil, memfilter, mengurutkan, hingga mengelompokkan data menggunakan berbagai klausa seperti GROUP BY dan fungsi agregat. Namun, pernahkah Anda dihadapkan pada situasi di mana Anda perlu melakukan perhitungan atau ranking di dalam setiap kelompok data tanpa harus mengagregasinya menjadi satu baris tunggal? Misalnya, ingin mencari 3 produk terlaris di setiap kategori, atau menemukan transaksi terbaru untuk setiap pelanggan. Di sinilah Window Function di SQL Server hadir sebagai solusi yang elegan dan powerful.

Apa itu Window Function dan Mengapa Kita Membutuhkannya?

Secara tradisional, ketika kita ingin melakukan perhitungan pada kelompok data, kita cenderung menggunakan GROUP BY. Kendala utama GROUP BY adalah ia akan mengkompres atau mengagregasi baris-baris tersebut menjadi satu baris per kelompok, sehingga kita kehilangan detail dari masing-masing baris individu. Contoh klasik: Anda ingin menghitung total penjualan per kategori, tetapi juga ingin melihat setiap produk individual dan berapa persentase penjualannya terhadap total kategori tersebut dalam satu hasil query. Dengan GROUP BY saja, ini mustahil dilakukan tanpa subquery atau join yang rumit.

Window function mengatasi batasan ini dengan memungkinkan Anda melakukan perhitungan (seperti ranking, agregasi, atau perhitungan lainnya) pada "jendela" atau "partisi" baris yang terkait dengan baris saat ini, tetapi tanpa mengurangi jumlah baris yang dikembalikan oleh query. Artinya, setiap baris asli tetap ada dalam hasil, namun kini dilengkapi dengan informasi kontekstual dari "jendelanya" masing-masing. Ini sangat berguna untuk skenario analisis data yang kompleks, di mana kita perlu melihat data individu bersamaan dengan konteks grupnya.

Memahami PARTITION BY: Jantung Window Function

Persiapan data sampel untuk demonstrasi Window Function.

CREATE TABLE PenjualanProduk (
    ID INT PRIMARY KEY IDENTITY(1,1),
    KategoriProduk VARCHAR(50),
    NamaProduk VARCHAR(100),
    Penjualan DECIMAL(10, 2),
    TanggalPenjualan DATE
);

INSERT INTO PenjualanProduk (KategoriProduk, NamaProduk, Penjualan, TanggalPenjualan) VALUES
('Elektronik', 'Laptop A', 1500.00, '2023-01-10'),
('Elektronik', 'Smartphone X', 800.00, '2023-01-15'),
('Elektronik', 'Laptop B', 1500.00, '2023-02-01'),
('Elektronik', 'Smart TV Y', 1200.00, '2023-02-05'),
('Pakaian', 'Kemeja Pria', 250.00, '2023-01-12'),
('Pakaian', 'Celana Jeans', 300.00, '2023-01-20'),
('Pakaian', 'T-Shirt Casual', 150.00, '2023-02-10'),
('Pakaian', 'Jaket Bomber', 500.00, '2023-02-15'),
('Pakaian', 'Kemeja Pria', 250.00, '2023-03-01'), -- Duplikat penjualan (karena penjualan dan nama produk sama)
('Buku', 'SQL Mastery', 100.00, '2023-01-05'),
('Buku', 'Python for Beg', 90.00, '2023-01-25'),
('Buku', 'Clean Code', 110.00, '2023-02-08');

Konsep kunci di balik window function adalah klausa OVER(), dan di dalamnya terdapat PARTITION BY dan ORDER BY. Jika Anda sudah familiar dengan GROUP BY, maka PARTITION BY dapat dianggap sebagai "pengelompokan" secara logis untuk keperluan perhitungan window function, bukan untuk agregasi baris. PARTITION BY membagi set hasil query menjadi beberapa kelompok (disebut partisi) di mana window function akan diterapkan secara independen pada setiap partisi.

Sintaks dasarnya adalah FUNGSI_WINDOW() OVER (PARTITION BY Kolom1, Kolom2 ... ORDER BY KolomA, KolomB ...). Bagian ORDER BY di dalam klausa OVER() menentukan urutan baris di dalam setiap partisi, yang sangat krusial untuk fungsi seperti ROW_NUMBER dan RANK karena hasil ranking sangat bergantung pada urutan. Tanpa ORDER BY, urutan (dan oleh karena itu hasil ranking) dapat menjadi non-deterministik.

Contoh Skenario: Bayangkan Anda memiliki data penjualan produk dari berbagai kota. Anda ingin memberi peringkat produk berdasarkan total penjualan di setiap kota secara terpisah. Di sinilah PARTITION BY Kota akan berperan, memastikan peringkat produk di Surabaya tidak bercampur dengan peringkat produk di Jakarta.

Window Function ROW_NUMBER(): Memberi Nomor Urut Unik

ROW_NUMBER() adalah window function yang paling dasar dan sering digunakan. Fungsi ini memberikan nomor urut yang unik untuk setiap baris di dalam setiap partisi, dimulai dari 1. Nomor urut ini akan direset untuk setiap partisi baru. Ini berarti jika ada dua baris yang identik dalam hal kriteria pengurutan, ROW_NUMBER() akan tetap memberikan nomor urut yang berbeda.

Kapan Menggunakan ROW_NUMBER()?

Deteksi dan Penanganan Duplikasi: Ini adalah salah satu kasus penggunaan paling populer di dunia kerja. Seringkali, data memiliki duplikat berdasarkan kriteria tertentu (misalnya, dua entri untuk ID transaksi yang sama tetapi dengan tanggal yang berbeda karena kesalahan input). ROW_NUMBER() dapat digunakan untuk mengidentifikasi duplikat dan memilih salah satu baris (misalnya, yang paling baru atau paling lama) untuk dipertahankan, sementara sisanya dihapus atau diarsipkan.
Paginasi Data per Kategori: Jika Anda perlu menampilkan data secara paginasi (halaman-halaman) di mana setiap kategori memiliki "halaman" sendiri, ROW_NUMBER() dapat membantu menentukan baris mana yang termasuk dalam halaman tertentu.
Memilih Catatan Terbaru/Terlama: Misalnya, mengambil entri transaksi terakhir untuk setiap pelanggan, riwayat perubahan status terbaru dari suatu entitas, atau alamat pengiriman utama untuk setiap pengguna.

Kesalahan Umum dengan ROW_NUMBER()

Kesalahan terbesar yang sering dilakukan developer pemula adalah melupakan atau salah menentukan klausa ORDER BY di dalam OVER(). Tanpa ORDER BY, atau jika ORDER BY tidak unik (misalnya hanya mengurutkan berdasarkan nama tanpa tanggal), nomor urut yang dihasilkan oleh ROW_NUMBER() bisa jadi nondeterministik (berbeda setiap kali dijalankan) atau tidak sesuai ekspektasi. Sebagai contoh, jika Anda ingin mendapatkan baris terbaru, Anda harus memastikan ORDER BY TanggalWaktu DESC. Jika ada beberapa baris dengan tanggal waktu yang sama, tanpa kriteria pengurutan tambahan, SQL Server akan memilih baris mana yang diberi nomor 1 secara arbitrer. Untuk hasil yang benar-benar deterministik, pastikan kombinasi kolom di ORDER BY adalah unik.

Window Function RANK() dan DENSE_RANK(): Untuk Peringkat dengan "Tie"

Menggunakan ROW_NUMBER() untuk menemukan catatan terbaru (misalnya, untuk eliminasi duplikasi) per kategori.

WITH RankedProducts AS (
    SELECT
        KategoriProduk,
        NamaProduk,
        Penjualan,
        TanggalPenjualan,
        ROW_NUMBER() OVER (PARTITION BY KategoriProduk, NamaProduk, Penjualan ORDER BY TanggalPenjualan DESC, ID DESC) AS rn
    FROM PenjualanProduk
)
SELECT
    KategoriProduk,
    NamaProduk,
    Penjualan,
    TanggalPenjualan
FROM RankedProducts
WHERE rn = 1
ORDER BY KategoriProduk, TanggalPenjualan DESC;

Berbeda dengan ROW_NUMBER() yang selalu memberikan nomor unik, RANK() dan DENSE_RANK() dirancang khusus untuk skenario peringkat di mana ada kemungkinan "tie" atau nilai yang sama antar baris. Mereka sangat relevan ketika Anda ingin menghargai semua entitas yang memiliki skor/nilai yang sama dengan peringkat yang sama.

RANK()

RANK() memberikan peringkat yang sama untuk baris-baris yang memiliki nilai yang sama (tie). Namun, ia akan melewatkan peringkat berikutnya setelah tie. Misalnya, jika dua baris berada di peringkat 1 (karena nilainya sama), maka peringkat berikutnya akan menjadi 3 (peringkat 2 dilewatkan).

DENSE_RANK()

DENSE_RANK() juga memberikan peringkat yang sama untuk baris-baris yang memiliki nilai yang sama (tie). Bedanya, ia tidak akan melewatkan peringkat berikutnya. Jadi, jika dua baris berada di peringkat 1 (karena nilainya sama), peringkat berikutnya akan menjadi 2.

Kapan Menggunakan RANK() dan DENSE_RANK()?

Leaderboards Kompetisi: Membuat daftar peringkat pemain dalam game, atlet, atau karyawan berdasarkan performa, di mana ada kemungkinan nilai yang sama. Penggunaan RANK() cocok jika Anda ingin jumlah "posisi" di leaderboard mencerminkan jumlah peringkat unik (misal: juara 1, 1, lalu 3).
Top N per Kategori (dengan Tie): Menemukan N produk terlaris di setiap kategori. Jika ada produk lain yang memiliki penjualan sama persis dengan produk terakhir di Top N, mereka juga ikut disertakan jika menggunakan RANK() atau DENSE_RANK().
Analisis Kinerja: Memberi peringkat entitas berdasarkan metrik tertentu (misalnya penjualan, laba, waktu respons) untuk identifikasi performa terbaik atau terburuk, dengan mempertimbangkan kesamaan nilai.

Perbedaan Kritis dan Contoh Nyata

Memahami perbedaan antara ROW_NUMBER(), RANK(), dan DENSE_RANK() sangat penting untuk memilih fungsi yang tepat. Mari kita ilustrasikan dengan data sederhana mengenai skor siswa:

Bayangkan Anda memiliki data skor siswa. Berikut adalah data mentahnya:

Siswa	Skor
Ani	90
Budi	90
Citra	85
Dedi	80
Eka	80
Fani	75

Jika kita menerapkan ketiga fungsi ini dengan ORDER BY Skor DESC, hasilnya akan seperti ini:

Siswa	Skor	ROW_NUMBER	RANK	DENSE_RANK
Ani	90	1	1	1
Budi	90	2	1	1
Citra	85	3	3	2
Dedi	80	4	4	3
Eka	80	5	4	3
Fani	75	6	6	4

Perhatikan bagaimana ROW_NUMBER selalu unik, RANK melewati angka 2 dan 5 (karena ada dua yang tie di posisi 1, dan dua yang tie di posisi 4), sedangkan DENSE_RANK tidak melewati angka dan memberikan peringkat berurutan.

Best Practices dan Pertimbangan Performa

Menggunakan RANK() dan DENSE_RANK() untuk memberi peringkat produk berdasarkan penjualan per kategori.

SELECT
    KategoriProduk,
    NamaProduk,
    Penjualan,
    RANK() OVER (PARTITION BY KategoriProduk ORDER BY Penjualan DESC) AS PeringkatRank,
    DENSE_RANK() OVER (PARTITION BY KategoriProduk ORDER BY Penjualan DESC) AS PeringkatDenseRank,
    ROW_NUMBER() OVER (PARTITION BY KategoriProduk ORDER BY Penjualan DESC, NamaProduk ASC) AS PeringkatRowNumber
FROM PenjualanProduk
ORDER BY KategoriProduk, Penjualan DESC;

Indeks yang Tepat: Pastikan kolom yang digunakan dalam klausa PARTITION BY dan ORDER BY (di dalam OVER()) memiliki indeks yang tepat. Operasi pengurutan (sorting) yang dibutuhkan oleh window function dapat menjadi sangat mahal (memakan banyak CPU dan I/O) pada dataset besar jika SQL Server tidak dapat menggunakan indeks untuk memenuhi urutan yang diminta. Tanpa indeks yang mendukung, SQL Server mungkin harus melakukan sort penuh di tempdb, yang bisa menyebabkan performa menurun drastis. Indeks komposit yang mencakup kolom PARTITION BY dan ORDER BY bisa sangat membantu.
Perhatikan Ukuran Partisi: Jika satu partisi berisi jutaan baris, operasi window function di partisi tersebut akan memakan banyak memori dan CPU. Dalam skenario enterprise, developer seringkali menemui masalah performa ketika partisi terlalu besar dan tidak dapat ditangani efisien dalam memori, menyebabkan "spill to disk" yang memperlambat query. Pertimbangkan untuk memecah masalah, memfilter data terlebih dahulu, atau mendesain ulang skema jika partisi secara inheren akan selalu sangat besar.
Gunakan Bersama CTE: Untuk query yang kompleks, menggunakan Common Table Expression (CTE) (seperti yang telah kita bahas di artikel Common Table Expression (CTE) di SQL Server) bersama window function dapat meningkatkan keterbacaan dan modularitas kode Anda secara signifikan. Anda bisa membuat satu CTE yang menerapkan window function, lalu melakukan filtering, join, atau agregasi di CTE berikutnya, membuat alur logika lebih mudah dipahami dan di-debug.
Pahami Kebutuhan Anda: Selalu tanyakan pada diri Anda: "Apakah saya perlu nomor urut yang benar-benar unik (ROW_NUMBER), peringkat dengan lompatan jika ada tie (RANK), atau peringkat berurutan tanpa lompatan (DENSE_RANK)?" Memilih fungsi yang tepat sejak awal adalah kunci untuk hasil yang akurat dan sesuai harapan.
Testing pada Data Realistis: Jangan hanya menguji pada data kecil di lingkungan pengembangan. Uji performa dan kebenaran logika window function Anda pada data yang mendekati ukuran produksi untuk mengidentifikasi potensi masalah performa atau bug logis sebelum deployment.

Kesimpulan

Window function, khususnya ROW_NUMBER(), RANK(), dan DENSE_RANK() bersama dengan klausa PARTITION BY, adalah alat yang sangat ampuh dalam kotak perkakas SQL Anda. Mereka memungkinkan kita untuk melakukan analisis data yang canggih seperti ranking, deteksi duplikasi, dan segmentasi data tanpa harus mengorbankan detail baris individu. Menguasai konsep ini akan membuka banyak kemungkinan baru dalam memecahkan masalah analitis yang kompleks dan menulis query yang lebih efisien serta mudah dipelihara. Ingat, kunci utamanya terletak pada pemahaman bagaimana PARTITION BY mendefinisikan "jendela" dan bagaimana ORDER BY mengatur urutan di dalamnya. Dengan praktik dan pemahaman yang tepat, Anda akan dapat memanfaatkan kekuatan penuh dari fitur SQL Server yang esensial ini.

FAQ Seputar Window Function

Melihat perbandingan hasil ROW_NUMBER, RANK, DENSE_RANK secara global (tanpa PARTITION BY).

SELECT
    NamaProduk,
    Penjualan,
    ROW_NUMBER() OVER (ORDER BY Penjualan DESC, NamaProduk ASC) AS GlobalRowNumber,
    RANK() OVER (ORDER BY Penjualan DESC) AS GlobalRank,
    DENSE_RANK() OVER (ORDER BY Penjualan DESC) AS GlobalDenseRank
FROM PenjualanProduk
ORDER BY Penjualan DESC, NamaProduk;

Apakah window function selalu membutuhkan PARTITION BY?: Tidak selalu. Jika Anda menghilangkan klausa PARTITION BY (misalnya, hanya OVER (ORDER BY Kolom)), maka window function akan memperlakukan seluruh hasil query sebagai satu partisi tunggal. Ini berarti fungsi tersebut akan diterapkan pada semua baris yang dikembalikan oleh query Anda, seolah-olah semua baris itu adalah satu grup besar. Contohnya, jika Anda ingin memberi peringkat semua karyawan di seluruh perusahaan tanpa membaginya per departemen, Anda tidak perlu PARTITION BY.
Kapan sebaiknya saya memilih ROW_NUMBER() daripada RANK() atau DENSE_RANK()?: Pilih ROW_NUMBER() ketika Anda membutuhkan nomor urut yang benar-benar unik untuk setiap baris di dalam partisi. Ini ideal untuk skenario seperti eliminasi duplikasi (mempertahankan hanya satu baris dari setiap set duplikat) atau paginasi di mana setiap baris harus memiliki ID unik dalam konteks grupnya. Pilih RANK() atau DENSE_RANK() ketika Anda ingin memberikan peringkat, dan ada kemungkinan beberapa baris memiliki nilai yang sama (tie), sehingga mereka harus berbagi peringkat yang sama. Perbedaan antara RANK() dan DENSE_RANK() terletak pada penanganan "lompatan" peringkat: RANK() akan melompat, sedangkan DENSE_RANK() tidak.
Apakah window function lebih cepat daripada subquery atau self-join?: Seringkali, ya. Untuk tugas-tugas seperti mencari "Top N per grup" atau "deteksi duplikasi" di mana Anda ingin mempertahankan detail baris, window function umumnya lebih efisien dan lebih mudah dibaca dibandingkan dengan solusi menggunakan subquery korelatif yang kompleks atau multiple self-join. Hal ini karena window function dirancang untuk melakukan tugas tersebut dalam satu pass data atau dengan overhead yang lebih rendah, sementara subquery atau join yang tidak dioptimalkan dapat memicu banyak pemindaian atau pencarian data, terutama pada volume data yang besar. Namun, seperti semua hal dalam optimasi database, selalu lakukan pengujian pada data realistis Anda.

Catatan dari Penulis

Sebagai seorang pengembang yang berhadapan dengan data setiap hari, saya ingat betul betapa frustrasinya mencoba mencapai hasil analitis kompleks menggunakan kombinasi subquery dan self-join yang berbelit-belit. Saat pertama kali mengenal window function, terutama ROW_NUMBER dan PARTITION BY ini, rasanya seperti menemukan alat ajaib yang menyederhanakan banyak masalah. Namun, saya juga sering menyaksikan junior developer keliru dalam menentukan ORDER BY di dalam OVER clause, yang berakibat pada hasil yang tidak konsisten dan non-deterministik. Memahami nuansa antara RANK dan DENSE_RANK juga butuh waktu, karena kesalahan kecil di sana bisa mengubah interpretasi data secara fundamental. Kunci suksesnya adalah memvisualisasikan 'jendela' tempat fungsi ini bekerja dan selalu memvalidasi logika pengurutan di dalamnya.

Seri Belajar SQL Server:

← Sebelumnya: Common Table Expression (CTE) di SQL Server

→ Selanjutnya: Membuat dan Menggunakan VIEW di SQL Server

Fungsi Window ROW_NUMBER & RANK: PARTITION BY untuk Analisis SQL