Window functions adalah fitur SQL yang sering diabaikan, padahal bisa menggantikan puluhan baris Python. Ranking penjualan per region, running total, selisih antar bulan — semuanya selesai dalam satu query tanpa self-join dan tanpa loop. Saya pakai SQLite untuk semua contoh di sini karena sudah mendukung window functions sejak versi 3.25. Query yang sama juga jalan di PostgreSQL atau DuckDB tanpa banyak ubahan.
Masalah yang Dipecahkan Window Functions
Ambil contoh nyata: kamu punya tabel penjualan per salesperson per bulan, lalu ingin tahu siapa peringkat satu di tiap region. Dengan SQL biasa, kamu bakal menulis subquery atau self-join yang panjang dan lambat. Dengan window function, cukup tambahkan OVER (PARTITION BY region ORDER BY jumlah DESC). Satu baris, beres.
Siapkan Data
Saya buat tabel penjualan dengan empat kolom: salesperson, region, bulan, dan jumlah (dalam juta rupiah). Jalankan skrip ini untuk membuat datanya. Kalau kamu belum pernah menyentuh modul sqlite3, saya sudah membahas dasarnya di artikel SQLite.
import sqlite3
con = sqlite3.connect("penjualan.db")
cur = con.cursor()
cur.execute("""
CREATE TABLE penjualan (
id INTEGER PRIMARY KEY,
salesperson TEXT,
region TEXT,
bulan TEXT,
jumlah INTEGER
)
""")
data = [
("Andi", "Jakarta", "Jan", 120),
("Budi", "Jakarta", "Jan", 95),
("Citra", "Jakarta", "Jan", 95),
("Dewi", "Jakarta", "Jan", 140),
("Andi", "Jakarta", "Feb", 130),
("Budi", "Jakarta", "Feb", 110),
("Citra", "Jakarta", "Feb", 150),
("Eko", "Surabaya", "Jan", 80),
("Fajar", "Surabaya", "Jan", 105),
("Eko", "Surabaya", "Feb", 90),
("Fajar", "Surabaya", "Feb", 120),
("Gita", "Bandung", "Jan", 160),
]
cur.executemany(
"INSERT INTO penjualan (salesperson, region, bulan, jumlah) VALUES (?, ?, ?, ?)",
data,
)
con.commit()
print("Data siap:", cur.execute("SELECT COUNT(*) FROM penjualan").fetchone()[0], "baris")Mulai dari sini saya asumsikan kamu menjalankan query lewat cur.execute(...) lalu fetchall(), atau lewat CLI sqlite3. Outputnya identik.
ROW_NUMBER: Nomor Urut Unik per Grup
ROW_NUMBER() memberi nomor urut mulai dari 1 untuk tiap baris di dalam partisinya. PARTITION BY membagi data jadi grup-grup; di dalam tiap grup, ORDER BY menentukan urutannya.
SELECT
salesperson,
region,
jumlah,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY jumlah DESC) AS peringkat
FROM penjualan;Untuk region Jakarta, hasilnya: Citra (150) peringkat 1, Dewi (140) peringkat 2, Andi (130) peringkat 3, dan seterusnya. Penomoran reset ke 1 setiap ganti region, jadi Surabaya dan Bandung punya peringkat masing-masing.
RANK vs DENSE_RANK: Nasib Nilai yang Seri
Perbedaan keduanya cuma satu: cara menangani nilai yang sama. Budi dan Citra sama-sama menjual 95 di bulan Jan. RANK() memberi keduanya peringkat yang sama lalu melompati angka berikutnya. DENSE_RANK() tidak melompat.
SELECT
salesperson,
jumlah,
RANK() OVER (PARTITION BY region ORDER BY jumlah DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY region ORDER BY jumlah DESC) AS dense_rank
FROM penjualan
WHERE region = 'Jakarta';Lihat baris Budi dan Citra yang nilainya 95. Keduanya dapat rank 6, lalu RANK() melompati angka 7 — nilai berikutnya, kalau ada, langsung dapat 8. DENSE_RANK() memberi 6 dan 6 tanpa lompatan. Kalau kamu butuh "top 3" yang benar-benar berisi tiga orang meski ada seri, pakai DENSE_RANK().
LAG dan LEAD: Membandingkan dengan Baris Sebelum atau Sesudahnya
LAG() mengembalikan nilai dari baris sebelumnya di dalam partisi, LEAD() dari baris sesudahnya. Ini cara paling cepat menghitung selisih bulan-ke-bulan tanpa self-join.
SELECT
salesperson,
bulan,
jumlah,
LAG(jumlah) OVER (PARTITION BY salesperson ORDER BY bulan) AS bulan_lalu,
jumlah - LAG(jumlah) OVER (PARTITION BY salesperson ORDER BY bulan) AS selisih
FROM penjualan
WHERE salesperson = 'Andi'
ORDER BY bulan;Untuk Andi, baris Januari punya bulan_lalu NULL karena memang tidak ada bulan sebelumnya, dan selisih-nya NULL juga. Baris Februari menampilkan 120 sebagai bulan_lalu dengan selisih +10. NULL itu normal — ganti dengan COALESCE(bulan_lalu, 0) kalau kamu ingin nilainya nol.
Running Total dengan SUM() OVER
Window function juga bisa menjalankan agregat biasa seperti SUM, AVG, atau MAX tanpa GROUP BY. Menambahkan ORDER BY di dalam OVER mengubahnya jadi running total.
SELECT
salesperson,
bulan,
jumlah,
SUM(jumlah) OVER (PARTITION BY salesperson ORDER BY bulan) AS total_berjalan
FROM penjualan
ORDER BY salesperson, bulan;Baris demi baris, total_berjalan menumpuk nilai jumlah untuk salesperson yang sama secara berurutan. Ini berguna untuk melihat kapan seorang salesperson menembus target kumulatif tertentu.
Frame: Mengendalikan Jendela Perhitungan
SELECT
salesperson,
bulan,
jumlah,
AVG(jumlah) OVER (
PARTITION BY salesperson
ORDER BY bulan
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rata_rata_2_bulan
FROM penjualan
ORDER BY salesperson, bulan;ROWS BETWEEN 1 PRECEDING AND CURRENT ROW membatasi jendela ke baris sekarang plus satu baris sebelumnya, menghasilkan rata-rata bergerak dua bulan. Ganti ke ROWS BETWEEN 2 PRECEDING AND CURRENT ROW untuk rata-rata tiga bulan, dan seterusnya.
SQLite baru mendukung window functions sejak versi 3.25 (September 2018). Kalau query-mu error di sistem lama, cek dulu dengan SELECT sqlite_version();.
Menarik Hasil ke Python
rows = cur.execute("""
SELECT salesperson, bulan, jumlah,
RANK() OVER (PARTITION BY salesperson ORDER BY jumlah DESC) AS rank
FROM penjualan
""").fetchall()
for r in rows:
print(r)Hasilnya berupa list of tuple yang siap dimasukkan ke DataFrame. Kalau biasanya kamu mengerjakan analisis semacam ini dengan Pandas lewat groupby().transform(), anggap window functions sebagai versi SQL-nya — sering kali lebih cepat karena dihitung langsung di mesin database, bukan di RAM.
Window functions dievaluasi setelah WHERE, GROUP BY, dan HAVING, tapi sebelum ORDER BY akhir. Artinya kamu bisa memfilter baris dulu, lalu ranking baru dihitung dari baris yang tersisa.
Kalau konsepnya sudah nempel, ambil satu tabel asli di proyekmu — log aplikasi, transaksi, atau nilai mahasiswa — lalu hitung ranking per kategori pakai ROW_NUMBER(). Bandingkan kecepatannya dengan versi Python yang biasa kamu tulis.
