Window Functions — SQL

Window Functions adalah fungsi yang menghitung nilai berdasarkan "jendela" baris — beda dari aggregate function yang me-collapse banyak baris jadi satu…

Window function menghitung sesuatu dari sekelompok baris, tapi tiap baris tetap muncul, dengan hasil hitungannya di kolom sebelahnya. Sekelompok baris itu disebut window. Bayangin rapor kelas: tiap siswa tetap punya barisnya sendiri, tapi di sebelah nilainya ada kolom "peringkat di kelas". Untuk mengisi kolom itu, guru melihat semua siswa di kelas yang sama, tanpa menggabungkan mereka jadi satu baris. Kelas itu window-nya.

🧪 Tebakan cepat: SUM(salary) ... GROUP BY department menghasilkan 2 baris. Kalau diganti SUM(salary) OVER (PARTITION BY department), hasilnya berapa baris? Tebak dulu, jawabannya ada di bagian "GROUP BY melipat, window enggak".

GROUP BY melipat, window enggak

Datanya tabel employees dari latihan, 5 karyawan (id, name, department, salary): Budi (IT, 5.000.000), Siti (IT, 8.000.000), Andi (IT, 6.500.000), Dewi (HR, 7.000.000), dan Rizky (HR, 4.500.000).

Di pelajaran GROUP BY, kamu menjumlah per kelompok. SELECT department, SUM(salary) AS total FROM employees GROUP BY department; menghasilkan 2 baris: HR 11.500.000 dan IT 19.500.000. Lima baris dilipat jadi dua, dan nama karyawannya hilang. Sekarang versi window:

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS total
FROM employees;

name	department	salary	total
Dewi	HR	7.000.000	11.500.000
Rizky	HR	4.500.000	11.500.000
Budi	IT	5.000.000	19.500.000
Siti	IT	8.000.000	19.500.000
Andi	IT	6.500.000	19.500.000

Lima baris tetap lima. Itu jawaban tebakannya: tiap karyawan tetap ada, dengan total departemennya di kolom sebelah.

OVER (...) yang bikin SUM jadi window function. Tanpa OVER, SUM itu fungsi agregat biasa yang melipat baris.
PARTITION BY department membagi tabel jadi beberapa window, mirip kelompok di GROUP BY, tapi barisnya gak dilipat.
OVER () yang kosong artinya satu window untuk seluruh tabel: SUM(salary) OVER () memberi 31.000.000 di tiap baris.

Isi kurung OVER punya tiga bagian, semuanya boleh gak ada: PARTITION BY (bagi jadi beberapa window), ORDER BY (urutan baris di dalam tiap window), dan frame seperti ROWS BETWEEN ... (baris mana yang ikut dihitung).

Ranking: ROW_NUMBER, RANK, DENSE_RANK

Ini query latihannya: nomor urut gaji di tiap departemen.

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
ORDER BY department ASC, rank ASC;

name	department	salary	rank
Dewi	HR	7.000.000	1
Rizky	HR	4.500.000	2
Siti	IT	8.000.000	1
Andi	IT	6.500.000	2
Budi	IT	5.000.000	3

ROW_NUMBER() memberi nomor 1, 2, 3 di tiap window. Kurungnya kosong, tapi wajib ditulis.
PARTITION BY department bikin nomornya mulai lagi dari 1 di HR dan di IT.
ORDER BY salary DESC di dalam OVER menentukan siapa nomor 1: gaji terbesar.
AS rank cuma nama kolom hasilnya.
ORDER BY department ASC, rank ASC di akhir query mengurutkan hasil akhirnya: HR sebelum IT (urutan abjad), lalu nomor 1 dulu. Makanya Dewi ada di baris pertama.

Beda tiga fungsi ranking baru kelihatan kalau ada yang seri. Misalnya Joko masuk IT dengan gaji 6.500.000, sama dengan Andi. Hasil SQLite di window IT:

name	salary	ROW_NUMBER	RANK	DENSE_RANK
Siti	8.000.000	1	1	1
Andi	6.500.000	2	2	2
Joko	6.500.000	3	2	2
Budi	5.000.000	4	4	3

ROW_NUMBER selalu memberi nomor beda, walau seri. Siapa dapat 2 dan siapa dapat 3 gak dijamin; tambah kolom kedua di ORDER BY, misalnya name, kalau harus pasti.
RANK memberi nomor sama untuk yang seri, lalu melompat: 2, 2, langsung 4. RANK = 1 + jumlah orang yang gajinya lebih besar.
DENSE_RANK juga memberi nomor sama, tapi gak melompat: 2, 2, lalu 3.
Butuh tepat 3 teratas per kategori walau ada yang seri? Pakai ROW_NUMBER. RANK dan DENSE_RANK bisa memberi lebih dari 3 baris. (NTILE(3) lain lagi: dia membagi baris ke 3 kelompok sama besar, bukan mengambil 3 teratas.)

Running total dan LAG

Saldo di mutasi rekening itu running total: jumlah semua transaksi dari awal sampai baris itu. LAG mengambil nilai dari baris sebelumnya, seperti membandingkan omzet hari ini dengan kemarin.

SELECT name, department, salary,
       SUM(salary) OVER (
         PARTITION BY department
         ORDER BY salary DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees
ORDER BY department, salary DESC;

name	department	salary	running_total	prev_salary
Dewi	HR	7.000.000	7.000.000	NULL
Rizky	HR	4.500.000	11.500.000	7.000.000
Siti	IT	8.000.000	8.000.000	NULL
Andi	IT	6.500.000	14.500.000	8.000.000
Budi	IT	5.000.000	19.500.000	6.500.000

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW adalah frame: baris yang dijumlah, dari baris pertama window sampai baris ini. Baris terakhir tiap window (11.500.000 dan 19.500.000) sama dengan total GROUP BY-nya.
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW cuma menjumlah baris sebelumnya dan baris ini. Dipakai untuk rata-rata bergerak (moving average).
LAG(salary) mengambil salary dari baris sebelumnya di window yang sama. Baris pertama tiap window gak punya baris sebelumnya, jadi hasilnya NULL. Kebalikannya, LEAD mengambil baris sesudahnya.
LAG(salary) OVER (...) - salary memberi selisih dengan orang di atasnya: Andi 1.500.000 di bawah Siti.

Top-N per kelompok: window + CTE

Mau 2 gaji tertinggi di tiap departemen? WHERE rn <= 2 langsung di query yang sama gak bisa, karena database mengerjakan WHERE sebelum menghitung kolom di SELECT. Hitung nomornya dulu di CTE (pelajaran sebelumnya), lalu saring di query utama:

WITH ranked AS (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 2;

Hasilnya 4 baris: Dewi dan Rizky dari HR, Siti dan Andi dari IT. Ganti PARTITION BY department dengan PARTITION BY email dan ORDER BY created_at DESC, dan pola yang sama membuang data dobel: simpan rn = 1, yaitu baris terbaru tiap email.

📌 Beda SQLite, PostgreSQL dan MySQL

Latihan dan Lab di sini memakai SQLite. Window function ada di SQLite 3.25 ke atas, PostgreSQL, dan MySQL 8.0 ke atas; MySQL 5.7 belum punya.

Di MySQL 8, rank, dense_rank dan row_number adalah reserved word, jadi AS rank ditolak. Tulis AS `rank` atau pakai nama lain. SQLite dan PostgreSQL menerima AS rank.
Pesan error WHERE rn <= 2 beda-beda: SQLite misuse of aliased window function rn, PostgreSQL column "rn" does not exist, MySQL Unknown column 'rn' in 'where clause'.
ORDER BY di dalam OVER tanpa frame berarti RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, sama di ketiganya. RANGE menghitung baris yang nilainya sama bareng-bareng: dengan Joko, SUM(salary) OVER (ORDER BY salary DESC) di IT memberi Andi dan Joko sama-sama 21.000.000. Pakai ROWS kalau mau baris demi baris.

⚠️ Jebakan yang sering ketemu

Menyaring hasil window function di WHERE. WHERE rn <= 2 di query yang sama ditolak. Bungkus dulu di CTE, lalu saring di query utama.
Lupa ORDER BY di dalam OVER. RANK() OVER (PARTITION BY department) memberi semua orang nomor 1, karena tanpa urutan semuanya seri. ROW_NUMBER() tanpa ORDER BY tetap memberi nomor, tapi urutannya asal.
Mengira ORDER BY di dalam OVER mengurutkan hasil akhir. Dia cuma mengurutkan baris di dalam window. Latihan butuh ORDER BY department ASC, rank ASC di akhir query supaya Dewi ada di baris pertama.
Mengetik nomor ranking sendiri. Latihan mengecek ada ROW_NUMBER(), RANK() atau DENSE_RANK() yang diikuti OVER (.

TL;DR: Window function menghitung dari sekelompok baris (window) tanpa melipat baris: fungsi() OVER (PARTITION BY ... ORDER BY ... frame). GROUP BY memberi 1 baris per kelompok, SUM(...) OVER (PARTITION BY ...) memberi semua baris dengan total kelompoknya. ROW_NUMBER selalu memberi nomor beda (pas untuk top-N), RANK memberi nomor sama untuk yang seri lalu melompat, DENSE_RANK gak melompat. SUM(...) OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) adalah running total, LAG mengambil baris sebelumnya. Hasil window function gak bisa disaring di WHERE query yang sama: hitung dulu di CTE.