Window Function: Peringkat, Total Berjalan, dan Perbandingan Antarbaris

Masalah GROUP BY: detail baris hilang

GROUP BY melipat banyak baris menjadi satu. Padahal kadang kita ingin tetap melihat setiap baris sambil menampilkan angka perbandingan: peringkat harga dalam kategorinya, atau selisih dengan rata-rata. Itulah tugas window function.

Ciri khasnya: kata OVER (...) setelah fungsi.

Rata-rata kategori di samping setiap buku

SELECT judul, kategori_id, harga,
       ROUND(AVG(harga) OVER (PARTITION BY kategori_id), 0) AS rata_kategori
FROM buku;

• PARTITION BY kategori_id: bagi baris menjadi "jendela" per kategori (seperti GROUP BY, tetapi baris tidak dilipat).
• Setiap buku tetap muncul, ditambah kolom rata-rata kategorinya.

Fungsi peringkat

SELECT judul, kategori_id, harga,
       RANK() OVER (PARTITION BY kategori_id ORDER BY harga DESC) AS peringkat
FROM buku
WHERE kategori_id IS NOT NULL
ORDER BY kategori_id, peringkat;

Hasilnya: di setiap kategori, buku termahal berperingkat 1. Pola "top N per kelompok" ini sangat sering diminta.

Total berjalan (running total)

SELECT tanggal, jumlah,
       SUM(jumlah) OVER (ORDER BY tanggal) AS total_berjalan
FROM penjualan_harian;

Dengan ORDER BY di dalam OVER, jumlah dihitung kumulatif dari baris pertama sampai baris saat ini.

LAG dan LEAD: melihat baris sebelum/sesudah

SELECT id, tanggal,
       LAG(tanggal)  OVER (ORDER BY tanggal) AS tanggal_sebelumnya,
       LEAD(tanggal) OVER (ORDER BY tanggal) AS tanggal_berikutnya
FROM pesanan;

Cocok untuk menghitung selisih dengan periode sebelumnya, misalnya pertumbuhan penjualan bulan ini dibanding bulan lalu.

WITH r AS (
  SELECT judul, kategori_id, harga,
         ROW_NUMBER() OVER (PARTITION BY kategori_id ORDER BY harga DESC) AS rn
  FROM buku WHERE kategori_id IS NOT NULL
)
SELECT judul, kategori_id, harga FROM r WHERE rn = 1;   -- buku termahal di tiap kategori

Kasus nyata: peringkat buku termahal di tiap kategori dan total penjualan berjalan

SELECT judul, kategori_id, harga,
       RANK() OVER (PARTITION BY kategori_id ORDER BY harga DESC) AS peringkat
FROM buku;

-- total berjalan (running total) penjualan harian
SELECT tanggal, COUNT(*) AS pesanan,
       SUM(COUNT(*)) OVER (ORDER BY tanggal) AS kumulatif
FROM pesanan GROUP BY tanggal;

Kesalahan yang sering terjadi

• Membingungkan PARTITION BY (membagi kelompok) dengan GROUP BY (meringkas baris).
• Memakai window function di WHERE. Bungkus dulu dalam subquery atau CTE, lalu saring hasilnya.
• Lupa bahwa RANK() melompati nomor saat ada seri, sedangkan DENSE_RANK() tidak.