SQL Server: Bedanya ROW_NUMBER, RANK, dan DENSE_RANK

Foto oleh panumas nikhomkhai via Pexels

Memahami Window Function ROW_NUMBER, RANK, dan DENSE_RANK di SQL Server

Ketika Anda perlu menganalisis performa karyawan berdasarkan penjualan bulanan, menentukan posisi juara dalam kompetisi, atau mencari data unik seperti transaksi terbaru dari setiap pelanggan, tantangan utamanya adalah bagaimana memberikan peringkat yang akurat pada setiap baris data. Melakukan ini dengan subquery atau GROUP BY bisa jadi rumit dan tidak efisien, terutama saat Anda perlu memperhitungkan skenario "seri" atau *ties*.

Di artikel sebelumnya, kita sudah membahas konsep dasar Window Functions, termasuk ROW_NUMBER dan RANK beserta pentingnya klausa PARTITION BY. Kali ini, kita akan mendalami lebih lanjut ketiga fungsi ranking utama ini—ROW_NUMBER, RANK, dan DENSE_RANK—beserta perbedaan fundamental dan kapan sebaiknya menggunakannya untuk skenario data yang berbeda. Pemahaman ini krusial untuk menghasilkan laporan yang presisi dan analisis yang mendalam.

Mari kita siapkan data penjualan sederhana untuk demonstrasi:

Menyiapkan Data Demonstrasi

Kita akan menggunakan tabel Penjualan yang berisi data transaksi karyawan setiap bulannya. Data ini akan membantu kita memahami bagaimana setiap fungsi ranking bekerja dalam konteks yang realistis.

Mendefinisikan skema tabel Penjualan

CREATE TABLE Penjualan ( IDTransaksi INT PRIMARY KEY IDENTITY(1,1), IDKaryawan INT NOT NULL, NamaKaryawan VARCHAR(100) NOT NULL, BulanTransaksi DATE NOT NULL, TotalPenjualan DECIMAL(18, 2) NOT NULL );

Dan kita isi dengan beberapa data sampel, termasuk skenario penjualan yang sama antar karyawan untuk menyoroti perbedaan antar fungsi ranking:

Menyisipkan data contoh ke tabel Penjualan

INSERT INTO Penjualan (IDKaryawan, NamaKaryawan, BulanTransaksi, TotalPenjualan) VALUES (101, 'Budi Santoso', '2023-01-01', 5000000.00), (102, 'Citra Dewi', '2023-01-01', 7500000.00), (103, 'Andi Wijaya', '2023-01-01', 5000000.00), -- Tie with Budi (104, 'Dewi Lestari', '2023-01-01', 9000000.00), (105, 'Eko Prasetyo', '2023-01-01', 7500000.00), -- Tie with Citra (101, 'Budi Santoso', '2023-02-01', 6000000.00), (102, 'Citra Dewi', '2023-02-01', 8000000.00), (103, 'Andi Wijaya', '2023-02-01', 6000000.00), -- Tie with Budi (104, 'Dewi Lestari', '2023-02-01', 8000000.00), -- Tie with Citra (105, 'Eko Prasetyo', '2023-02-01', 9500000.00);

ROW_NUMBER(): Penomoran Baris Unik

Fungsi ROW_NUMBER() memberikan nomor urut yang unik dan berurutan untuk setiap baris di dalam sebuah "window" atau partisi. Nomor urut ini dimulai dari 1. Poin pentingnya adalah, ROW_NUMBER() tidak memperdulikan nilai data yang sama; setiap baris akan mendapatkan nomor yang berbeda, bahkan jika semua kolom lain identik dalam partisi tersebut. Ini sangat berguna ketika Anda ingin mengambil satu baris spesifik (misalnya, yang terbaru, terlama, atau tertinggi) dari setiap grup.

Kapan Menggunakan ROW_NUMBER?

  • Mengambil N baris pertama/terakhir dari setiap kategori (misal: 3 produk terlaris di setiap kategori).
  • Mendapatkan record terbaru atau terlama dari suatu entitas (misal: transaksi terakhir setiap pelanggan).
  • Menghilangkan duplikasi jika semua kolom relevan sama, dengan mempertahankan satu record berdasarkan kriteria tertentu (misal: ID terkecil/terbesar).

Contoh ROW_NUMBER

Untuk melihat karyawan dengan penjualan tertinggi pertama setiap bulannya:

Contoh penggunaan ROW_NUMBER() untuk peringkat penjualan bulanan

SELECT IDKaryawan, NamaKaryawan, BulanTransaksi, TotalPenjualan, ROW_NUMBER() OVER (PARTITION BY BulanTransaksi ORDER BY TotalPenjualan DESC) AS RowNumPenjualan FROM Penjualan ORDER BY BulanTransaksi, RowNumPenjualan;

Perhatikan hasilnya. Meskipun Budi Santoso dan Andi Wijaya memiliki penjualan yang sama di Januari, ROW_NUMBER() tetap memberikan nomor urut yang berbeda (1 dan 2) karena ia hanya menghitung posisi baris secara sequential setelah diurutkan.

RANK(): Peringkat dengan Lompatan

Fungsi RANK() memberikan peringkat untuk setiap baris dalam partisi. Jika ada dua atau lebih baris yang memiliki nilai yang sama (berdasarkan klausa ORDER BY), mereka akan mendapatkan peringkat yang sama. Namun, RANK() akan "melompati" peringkat berikutnya. Misalnya, jika ada dua baris di peringkat 1, maka peringkat berikutnya akan menjadi 3 (melewati peringkat 2).

Kapan Menggunakan RANK?

  • Memberikan peringkat dalam kompetisi di mana ada kemungkinan hasil seri, dan Anda ingin ada lompatan peringkat.
  • Menentukan top N item di mana posisi "ke N" bisa diisi oleh beberapa item yang seri.

Contoh RANK

Mari kita lihat peringkat karyawan bulanan menggunakan RANK():

Contoh penggunaan RANK() untuk peringkat penjualan bulanan

SELECT IDKaryawan, NamaKaryawan, BulanTransaksi, TotalPenjualan, RANK() OVER (PARTITION BY BulanTransaksi ORDER BY TotalPenjualan DESC) AS RankPenjualan FROM Penjualan ORDER BY BulanTransaksi, RankPenjualan;

Di bulan Januari, Budi Santoso dan Andi Wijaya sama-sama mendapat RankPenjualan 2. Karena ada dua karyawan di peringkat 2, peringkat berikutnya yang tersedia adalah 4 (peringkat 3 dilewati). Ini sesuai dengan perilaku RANK().

DENSE_RANK(): Peringkat Tanpa Lompatan

Mirip dengan RANK(), fungsi DENSE_RANK() juga memberikan peringkat yang sama untuk baris dengan nilai yang identik. Perbedaannya terletak pada penomoran peringkat selanjutnya. DENSE_RANK() tidak akan melompati peringkat. Jika ada dua baris di peringkat 1, maka peringkat berikutnya akan menjadi 2, bukan 3.

Kapan Menggunakan DENSE_RANK?

  • Membuat peringkat di mana Anda ingin urutan peringkat tetap berurutan tanpa "gap", meskipun ada seri.
  • Menentukan level atau tingkatan performa di mana setiap level harus memiliki nomor yang berurutan.
  • Leaderboard atau daftar penghargaan di mana jumlah posisi yang berbeda (distinct ranks) adalah yang terpenting.

Contoh DENSE_RANK

Sekarang, mari kita terapkan DENSE_RANK() pada data penjualan yang sama:

Contoh penggunaan DENSE_RANK() untuk peringkat penjualan bulanan

SELECT IDKaryawan, NamaKaryawan, BulanTransaksi, TotalPenjualan, DENSE_RANK() OVER (PARTITION BY BulanTransaksi ORDER BY TotalPenjualan DESC) AS DenseRankPenjualan FROM Penjualan ORDER BY BulanTransaksi, DenseRankPenjualan;

Kali ini, Budi Santoso dan Andi Wijaya sama-sama mendapat DenseRankPenjualan 2. Namun, peringkat berikutnya adalah 3, bukan 4. Ini menunjukkan bahwa DENSE_RANK() mempertahankan urutan peringkat yang padat tanpa ada celah.

Perbandingan Ketiga Fungsi Ranking

Untuk memahami perbedaan ketiganya secara lebih jelas, mari kita jalankan ketiga fungsi tersebut dalam satu query yang sama:

Perbandingan ROW_NUMBER, RANK, dan DENSE_RANK dalam satu query

SELECT IDKaryawan, NamaKaryawan, BulanTransaksi, TotalPenjualan, ROW_NUMBER() OVER (PARTITION BY BulanTransaksi ORDER BY TotalPenjualan DESC) AS RowNum, RANK() OVER (PARTITION BY BulanTransaksi ORDER BY TotalPenjualan DESC) AS RankVal, DENSE_RANK() OVER (PARTITION BY BulanTransaksi ORDER BY TotalPenjualan DESC) AS DenseRankVal FROM Penjualan ORDER BY BulanTransaksi, TotalPenjualan DESC, NamaKaryawan;

Dari hasil di atas, Anda bisa melihat bagaimana ketiga fungsi ini menangani baris dengan nilai TotalPenjualan yang sama (misalnya, Budi dan Andi, atau Citra dan Eko di bulan Januari) secara berbeda:

  • ROW_NUMBER: Setiap baris memiliki nomor unik. Tidak ada seri, tidak ada lompatan.
  • RANK: Baris yang seri mendapatkan peringkat yang sama, dan peringkat berikutnya dilompati.
  • DENSE_RANK: Baris yang seri mendapatkan peringkat yang sama, dan peringkat berikutnya tidak dilompati (berurutan padat).

Klausa OVER (PARTITION BY dan ORDER BY)

Penting untuk diingat bahwa ketiga fungsi ini selalu memerlukan klausa OVER() yang di dalamnya harus ada ORDER BY. Klausa PARTITION BY bersifat opsional, tetapi sangat umum digunakan.

  • PARTITION BY: Mendefinisikan "jendela" atau grup di mana fungsi ranking akan diterapkan. Misalnya, PARTITION BY BulanTransaksi berarti ranking akan dihitung ulang untuk setiap bulan. Jika tidak ada PARTITION BY, seluruh dataset akan dianggap sebagai satu partisi.
  • ORDER BY: Menentukan kriteria pengurutan di dalam setiap partisi. Ini krusial karena ranking didasarkan pada urutan data. Tanpa ORDER BY, fungsi ranking tidak tahu bagaimana mengurutkan data untuk memberikan peringkat.

Kesalahan Umum dan Pertimbangan

Meskipun Window Function sangat powerful, ada beberapa kesalahan umum dan pertimbangan yang perlu diperhatikan:

  • Tidak Menggunakan ORDER BY

    Ini adalah kesalahan fatal. Klausa OVER() *wajib* memiliki ORDER BY untuk fungsi ranking. Tanpa ORDER BY, SQL Server tidak bisa menentukan urutan data untuk memberikan peringkat. Hasilnya bisa error atau tidak konsisten jika diizinkan oleh versi SQL Server tertentu.

    Contoh kesalahan: Lupa ORDER BY dalam klausa OVER()

    SELECT IDKaryawan, NamaKaryawan, BulanTransaksi, TotalPenjualan, -- Ini akan menghasilkan error atau hasil yang tidak terprediksi di beberapa versi SQL Server -- ROW_NUMBER() OVER (PARTITION BY BulanTransaksi) AS RankPenjualan FROM Penjualan;

  • Kesalahan dalam PARTITION BY

    Jika Anda lupa menambahkan PARTITION BY atau menentukannya dengan kolom yang salah, ranking akan dihitung untuk seluruh dataset alih-alih per grup yang Anda inginkan. Misalnya, jika ingin ranking per bulan tapi lupa PARTITION BY BulanTransaksi, maka ranking akan global untuk semua transaksi.

    Contoh kesalahan: Ranking global bukan per bulan karena salah PARTITION BY

    SELECT IDKaryawan, NamaKaryawan, BulanTransaksi, TotalPenjualan, -- Ranking ini akan dihitung untuk seluruh dataset, bukan per bulan ROW_NUMBER() OVER (ORDER BY TotalPenjualan DESC) AS GlobalRowNum FROM Penjualan ORDER BY BulanTransaksi, GlobalRowNum;

  • Memilih Fungsi yang Salah untuk Skenario Tie

    Pastikan Anda memilih RANK() atau DENSE_RANK() sesuai dengan bagaimana Anda ingin menangani seri dan apakah Anda ingin adanya lompatan peringkat atau tidak. Memahami perbedaan mendasar ini akan mencegah hasil laporan yang salah interpretasi.

  • Dampak Performa pada Data Besar

    Window functions, terutama pada tabel dengan jumlah data sangat besar dan banyak partisi, bisa memakan sumber daya CPU dan memori. Pastikan Anda memiliki indeks yang tepat pada kolom yang digunakan di PARTITION BY dan ORDER BY untuk optimasi. Dalam skenario kompleks, gabungan dengan Common Table Expression (CTE) (seperti yang pernah dibahas di artikel CTE vs Temp Table: Kapan Sebaiknya Digunakan) dapat membantu memecah query menjadi lebih mudah dibaca dan terkadang lebih efisien dengan materialisasi sebagian hasil.

Kesimpulan

Fungsi ROW_NUMBER(), RANK(), dan DENSE_RANK() adalah alat yang tak ternilai dalam toolkit SQL Anda untuk melakukan analisis data yang kompleks dan memberikan peringkat yang presisi. Setiap fungsi memiliki karakteristik unik dalam menangani urutan dan seri:

  • ROW_NUMBER(): Memberikan nomor urut unik, tidak ada seri, tidak ada lompatan. Ideal untuk mengambil "satu terbaik" per grup.
  • RANK(): Memberikan peringkat yang sama untuk baris yang seri, dengan lompatan pada peringkat berikutnya. Cocok untuk skenario di mana lompatan peringkat diperlukan.
  • DENSE_RANK(): Memberikan peringkat yang sama untuk baris yang seri, tanpa melompati peringkat berikutnya. Ideal untuk peringkat yang padat dan berurutan.

Memilih fungsi yang tepat bergantung pada kebutuhan bisnis dan bagaimana Anda ingin hasil seri diperlakukan. Dengan pemahaman yang solid mengenai perbedaan ketiganya, Anda bisa menulis query SQL yang lebih akurat, efisien, dan powerful untuk berbagai kebutuhan pelaporan dan analisis data.