Hari 27: Window Functions — ROW_NUMBER & RANK

Window Functions — Agregasi Tanpa Mengurangi Baris

Window functions (fungsi jendela) melakukan kalkulasi di atas set baris yang berkaitan, tanpa mengecilkan hasil seperti GROUP BY.

-- Syntax: FUNGSI() OVER (PARTITION BY ... ORDER BY ...)
ROW_NUMBER() OVER (ORDER BY harga DESC)

-- ROW_NUMBER — nomor urut unik
SELECT
  nama_produk, harga,
  ROW_NUMBER() OVER (ORDER BY harga DESC) AS urutan
FROM produk;

-- RANK — boleh ada nilai yang sama (ada "lompatan")
-- DENSE_RANK — boleh ada nilai sama (tidak ada lompatan)
SELECT
  nama_produk, harga,
  RANK() OVER (ORDER BY harga DESC) AS rank_harga,
  DENSE_RANK() OVER (ORDER BY harga DESC) AS dense_rank_harga
FROM produk;

PARTITION BY — Ranking Per Kelompok

-- Ranking produk dalam setiap kategori
SELECT
  nama_produk, kategori_id, harga,
  RANK() OVER (PARTITION BY kategori_id ORDER BY harga DESC) AS rank_dalam_kategori
FROM produk
ORDER BY kategori_id, rank_dalam_kategori;