Foto oleh panumas nikhomkhai via Pexels

Window function adalah kelompok fungsi di SQL Server yang memungkinkan perhitungan dilakukan terhadap sekumpulan baris terkait (disebut 'window') tanpa mengurangi jumlah baris pada hasil akhir, berbeda dengan GROUP BY yang meringkas banyak baris menjadi satu baris per kelompok.

Tiga window function yang paling sering dipakai adalah ROW_NUMBER, RANK, dan DENSE_RANK, yang semuanya memberi nomor urut pada baris berdasarkan urutan tertentu, namun berbeda cara menangani nilai yang sama persis (seri/tie). Ditambah dengan PARTITION BY, perhitungan ini bisa dipecah ulang untuk setiap kelompok data tanpa kehilangan baris detail aslinya.

Memahami perbedaan ketiga fungsi ini serta cara kerja PARTITION BY sangat penting karena window function sering dipakai untuk kebutuhan seperti mengambil transaksi terbaru per pelanggan, memberi peringkat produk terlaris per kategori, atau menghitung running total.

ROW_NUMBER untuk Memberi Nomor Urut Unik

ROW_NUMBER dengan PARTITION BY per pelanggan

SELECT OrderID, CustomerID, OrderDate, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS Urutan FROM Orders; -- Expected: setiap CustomerID mendapat nomor urut 1, 2, 3... -- dimulai dari pesanan terbarunya masing-masing
ROW_NUMBER selalu menghasilkan angka unik berurutan tanpa pernah mengulang angka yang sama, bahkan jika dua baris memiliki nilai ORDER BY yang persis sama. Ini membuatnya sangat cocok dipakai untuk mengambil 'baris pertama per kelompok', misalnya transaksi terbaru setiap pelanggan, dengan membungkusnya dalam subquery lalu memfilter Urutan = 1.

RANK dan DENSE_RANK Menangani Nilai Seri

Perbandingan RANK dan DENSE_RANK pada nilai seri

SELECT ProductName, Category, TotalSold, RANK() OVER (PARTITION BY Category ORDER BY TotalSold DESC) AS Peringkat, DENSE_RANK() OVER (PARTITION BY Category ORDER BY TotalSold DESC) AS PeringkatRapat FROM ProductSales; -- Expected: jika dua produk terjual sama banyak, -- RANK melompati angka berikutnya, DENSE_RANK tidak
Perbedaan utama RANK dan DENSE_RANK muncul saat ada nilai yang sama: RANK akan memberi peringkat sama untuk baris yang seri lalu melompati angka berikutnya sejumlah baris yang seri tersebut, sementara DENSE_RANK memberi peringkat sama namun angka berikutnya tetap berurutan tanpa lompatan. Pemilihan salah satu tergantung apakah lompatan angka tersebut diinginkan secara bisnis atau tidak.

Menghitung Running Total dengan SUM OVER

SUM OVER menghasilkan running total tanpa GROUP BY

SELECT OrderDate, TotalAmount, SUM(TotalAmount) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS TotalBerjalan FROM Orders; -- Expected: kolom TotalBerjalan terus bertambah -- mengikuti akumulasi nilai dari baris-baris sebelumnya
Window function tidak terbatas pada pemberian peringkat; fungsi agregat seperti SUM, AVG, COUNT juga bisa dipakai dengan OVER untuk menghasilkan running total atau rata-rata bergerak, tanpa perlu menggabungkan tabel dengan dirinya sendiri seperti pada pendekatan self-join yang lebih rumit dan lebih lambat.

Mengambil Baris Pertama per Kelompok dengan Subquery

Membungkus ROW_NUMBER dalam subquery untuk difilter

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn FROM Orders ) AS Hasil WHERE rn = 1; -- Expected: hanya satu baris per CustomerID, -- yaitu pesanan dengan OrderDate paling baru
Karena window function tidak bisa langsung dipakai di klausa WHERE pada level yang sama, pola umum yang dipakai adalah membungkus query berisi ROW_NUMBER ke dalam subquery atau CTE, lalu memfilter hasilnya di query luar. Pola ini jauh lebih bersih dibanding pendekatan lama memakai subquery MAX(OrderDate) berkorelasi.

Window Function LAG LEAD dan Running Total dengan OVER

LAG/LEAD dan running total menggunakan frame ROWS BETWEEN

-- LAG/LEAD untuk membandingkan nilai dengan baris sebelumnya/berikutnya SELECT OrderDate, TotalAmount, LAG(TotalAmount, 1) OVER (ORDER BY OrderDate) AS PrevDayAmount, TotalAmount - LAG(TotalAmount, 1) OVER (ORDER BY OrderDate) AS Selisih FROM DailySales; -- Hasil: kolom Selisih menunjukkan kenaikan/penurunan penjualan harian -- Running total per customer memakai SUM() OVER dengan frame ROWS SELECT CustomerID, OrderDate, TotalAmount, SUM(TotalAmount) OVER ( PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningTotal FROM Orders; -- Hasil: akumulasi total belanja customer bertambah di setiap baris

LAG() dan LEAD() memungkinkan satu baris mengakses nilai kolom dari baris sebelumnya atau berikutnya dalam urutan yang ditentukan ORDER BY, tanpa perlu melakukan self-join yang biasanya diperlukan pada pendekatan SQL konvensional. Fungsi ini sangat berguna untuk menghitung selisih periode ke periode, mendeteksi perubahan status berurutan, atau membandingkan transaksi terakhir pelanggan dengan transaksi sebelumnya.

Klausa frame seperti ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW menentukan batas baris mana saja yang ikut dihitung dalam agregasi window function untuk setiap baris; tanpa frame eksplisit, SQL Server memakai default ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ketika ada ORDER BY, yang menghasilkan running total kumulatif seperti pada contoh, bukan total keseluruhan partisi.

Perbedaan mendasar antara window function dan agregasi GROUP BY biasa adalah window function tidak meringkas baris menjadi satu baris per grup, melainkan tetap menampilkan setiap baris detail sambil menyertakan nilai agregat yang relevan di sampingnya, sehingga baris mentah dan hasil perhitungan analitis bisa dilihat bersamaan dalam satu hasil query.

RANK DENSE_RANK dan ROW_NUMBER Saat Menangani Data Seri (Ties)

Tiga fungsi ranking menangani baris dengan nilai sama secara berbeda

SELECT ProductName, CategoryID, SalesQty, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY SalesQty DESC) AS RowNum, RANK() OVER (PARTITION BY CategoryID ORDER BY SalesQty DESC) AS Rnk, DENSE_RANK() OVER (PARTITION BY CategoryID ORDER BY SalesQty DESC) AS DenseRnk FROM ProductSales; -- Contoh data dengan dua produk yang SalesQty-nya sama (seri/tie): -- ProductName SalesQty RowNum Rnk DenseRnk -- Produk A 500 1 1 1 -- Produk B 500 2 1 1 -- Produk C 480 3 3 2 -- RowNum selalu unik, Rnk melompati nomor, DenseRnk tidak melompat

ROW_NUMBER() selalu memberikan nomor urut yang unik untuk setiap baris dalam partisi, bahkan ketika dua baris memiliki nilai ORDER BY yang persis sama; urutan di antara baris yang nilainya sama ini sebenarnya tidak deterministik kecuali ORDER BY diperjelas dengan kolom tambahan sebagai tie-breaker, sehingga menjalankan query yang sama dua kali berpotensi menghasilkan urutan berbeda untuk baris-baris yang nilainya identik.

RANK() memberikan nomor yang sama untuk baris-baris dengan nilai identik, namun nomor berikutnya akan melompati sejumlah baris yang memiliki rank sama tersebut, seperti terlihat pada contoh di mana Produk C mendapat Rnk 3 bukan 2 karena ada dua baris di atasnya yang berbagi Rnk 1. DENSE_RANK() menyelesaikan masalah lompatan ini dengan memberikan nomor berikutnya secara berurutan tanpa celah, sehingga Produk C mendapat DenseRnk 2.

Pemilihan fungsi yang tepat bergantung pada kebutuhan bisnis: ROW_NUMBER cocok ketika setiap baris memang harus diberi identitas urutan unik, misalnya untuk keperluan paging data; RANK cocok ketika posisi harus mencerminkan jumlah pesaing di atasnya seperti peringkat kompetisi; dan DENSE_RANK cocok ketika yang dibutuhkan adalah jumlah tingkatan nilai yang berbeda, misalnya mengelompokkan produk ke dalam tier penjualan tanpa peduli berapa banyak produk di tiap tier. Memakai ROW_NUMBER untuk kebutuhan yang sebenarnya menuntut penanganan seri yang adil, seperti menentukan top-N per kategori tanpa menghilangkan produk yang nilainya sama, justru dapat menghasilkan laporan yang keliru.

NTILE dan PERCENT_RANK untuk Membagi Data ke dalam Kelompok Persentil

Membagi customer ke dalam kuartil dengan NTILE dan posisi relatif dengan PERCENT_RANK

SELECT CustomerID, TotalBelanja, NTILE(4) OVER (ORDER BY TotalBelanja DESC) AS Kuartil, PERCENT_RANK() OVER (ORDER BY TotalBelanja DESC) AS PersentilRank FROM RingkasanBelanjaCustomer; -- Kuartil: 1 = top 25% customer berdasarkan TotalBelanja, 4 = 25% terbawah -- PersentilRank: 0.0 untuk baris teratas, mendekati 1.0 untuk baris terbawah

NTILE(n) membagi baris dalam partisi menjadi n kelompok dengan jumlah baris sedekat mungkin sama rata, sehingga cocok dipakai untuk segmentasi seperti membagi customer ke kuartil berdasarkan total belanja; jika jumlah baris tidak habis dibagi n, kelompok-kelompok awal akan mendapat satu baris ekstra dibanding kelompok berikutnya.

PERCENT_RANK() menghasilkan posisi relatif setiap baris dalam rentang 0 sampai 1 berdasarkan rumus (rank-1) dibagi (jumlah baris-1), sehingga nilainya menunjukkan persentase baris lain yang berada di bawah baris tersebut. Berbeda dari NTILE yang membagi ke kelompok diskret, PERCENT_RANK memberi ukuran posisi yang kontinu dan berguna untuk analisis seperti menentukan customer berada di persentil ke berapa. Kedua fungsi ini sering dipakai bersamaan pada laporan segmentasi, di mana NTILE menentukan label kelompok dan PERCENT_RANK memberi angka presisi untuk pengurutan lanjutan di dalam kelompok yang sama.

Kesalahan Umum yang Sering Terjadi

Memakai Window Function Langsung di WHERE

SQL Server tidak mengizinkan window function dipakai langsung pada klausa WHERE di level query yang sama karena urutan evaluasi klausa; solusinya harus dibungkus subquery atau CTE terlebih dahulu.

Lupa Menyertakan PARTITION BY Saat Butuh Per Kelompok

Tanpa PARTITION BY, window function akan menghitung terhadap seluruh hasil query sebagai satu kelompok besar, bukan per kelompok seperti per pelanggan atau per kategori yang sebenarnya diinginkan.

Menggunakan RANK Padahal Butuh Nomor Urut Unik

Jika tujuannya memastikan setiap baris punya nomor berbeda, RANK bisa menghasilkan nomor yang sama pada baris seri sehingga filter 'ambil baris nomor 1' bisa mengembalikan lebih dari satu baris tanpa disadari.

Kapan Pendekatan Ini Tidak Cocok Dipakai

Window function sangat efisien untuk kebutuhan analitis seperti peringkat dan running total karena dihitung dalam satu pass data tanpa perlu self-join, namun ia membutuhkan versi SQL Server yang mendukungnya (2012 ke atas untuk sebagian besar fungsi) dan pemahaman yang lebih mendalam dibanding GROUP BY biasa.

Untuk kebutuhan yang memang hanya butuh satu baris ringkasan per kelompok, GROUP BY tetap lebih sederhana dan lebih mudah dibaca; window function unggul justru ketika baris detail aslinya tetap harus dipertahankan berdampingan dengan hasil perhitungannya.

Ringkasan

ROW_NUMBER, RANK, dan DENSE_RANK memberi cara yang bersih untuk memberi nomor urut atau peringkat per kelompok tanpa kehilangan baris detail, sementara PARTITION BY menentukan batas setiap kelompok perhitungan tersebut. Pahami perbedaan penanganan nilai seri sebelum memilih fungsi mana yang dipakai, karena pilihan yang salah bisa menghasilkan data yang terlihat benar namun sebenarnya keliru.

Seri Belajar SQL Server:

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

→ Selanjutnya: Membuat dan Menggunakan VIEW di SQL Server