Window Functions

GROUP BY punya satu efek samping yang kadang tidak kita inginkan: baris-baris lenyap diringkas menjadi satu baris per kelompok. Padahal banyak analisis justru butuh keduanya — angka ringkas dan baris asli tetap utuh, misalnya “setiap produk beserta peringkat harganya” atau “setiap transaksi beserta total berjalan”. Window functions (hadir sejak SQLite 3.25) menjawab kebutuhan itu: agregat dihitung di atas “jendela” baris, tetapi hasilnya menjadi kolom tambahan tanpa menutup satu baris pun.

Data Contoh #

Chapter ini memakai tabel produk, pelanggan, dan detail_pesanan dari domain toko (tabel pesanan ikut disertakan karena dirujuk oleh foreign key detail_pesanan):

CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  kota TEXT
);
CREATE TABLE produk (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  harga INTEGER NOT NULL,
  stok INTEGER
);
CREATE TABLE pesanan (
  id INTEGER PRIMARY KEY,
  pelanggan_id INTEGER REFERENCES pelanggan(id),
  tanggal TEXT NOT NULL
);
CREATE TABLE detail_pesanan (
  pesanan_id INTEGER REFERENCES pesanan(id),
  produk_id INTEGER REFERENCES produk(id),
  jumlah INTEGER NOT NULL,
  PRIMARY KEY (pesanan_id, produk_id)
);

INSERT INTO pelanggan (id, nama, kota) VALUES
  (1, 'Budi', 'Jakarta'), (2, 'Siti', 'Bandung'), (3, 'Ani', NULL),
  (4, 'Rina', 'Surabaya'), (5, 'Dewi', 'Jakarta');
INSERT INTO produk (id, nama, harga, stok) VALUES
  (1, 'Kopi Gayo', 45000, 20), (2, 'Teh Melati', 18000, 35),
  (3, 'Gula Aren', 25000, 15), (4, 'Sirup Vanila', 25000, NULL),
  (5, 'Kopi Toraja', 52000, 10), (6, 'Teh Hijau', 22000, 0),
  (7, 'Cangkir Keramik', 35000, 8);
INSERT INTO pesanan (id, pelanggan_id, tanggal) VALUES
  (1, 1, '2025-06-01'), (2, 2, '2025-06-03'), (3, 1, '2025-06-07'),
  (4, 3, '2025-06-10'), (5, 2, '2025-06-15'), (6, 1, '2025-07-02');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah) VALUES
  (1, 1, 2), (1, 3, 1), (2, 2, 3), (3, 1, 1),
  (3, 4, 2), (4, 5, 1), (5, 6, 2), (5, 3, 2), (6, 1, 1);

Penomoran dan Peringkat #

Tiga fungsi penomoran paling sering dipakai, dan ketiganya berbeda justru saat ada nilai yang sama:

• ROW_NUMBER() — nomor urut 1, 2, 3, … tanpa pertimbangan hasil seri.
• RANK() — peringkat dengan lompatan setelah seri: 1, 2, 2, 4.
• DENSE_RANK() — peringkat rapat tanpa lompatan: 1, 2, 2, 3.

Ketiganya ditulis dengan klausa OVER (ORDER BY ...) yang menentukan urutan penghitungan:

SELECT nama, harga,
       ROW_NUMBER() OVER (ORDER BY harga DESC, nama) AS nomor_urut,
       RANK() OVER (ORDER BY harga DESC) AS peringkat,
       DENSE_RANK() OVER (ORDER BY harga DESC) AS peringkat_padat
FROM produk
ORDER BY harga DESC, nama;

Output yang Dihasilkan:

Kopi Toraja|52000|1|1|1
Kopi Gayo|45000|2|2|2
Cangkir Keramik|35000|3|3|3
Gula Aren|25000|4|4|4
Sirup Vanila|25000|5|4|4
Teh Hijau|22000|6|6|5
Teh Melati|18000|7|7|6

Gula Aren dan Sirup Vanila sama-sama berharga 25000. RANK() memberi keduanya peringkat 4 lalu melompat ke 6, sedangkan DENSE_RANK() melanjutkan ke 5; ROW_NUMBER() memaksa keduanya bernomor berbeda sesuai urutan nama. Perhatikan juga ORDER BY harga DESC, nama di dalam OVER — kolom kedua menjadikan seri deterministik sehingga hasilnya selalu sama.

PARTITION BY: Jendela per Kelompok #

Klausa PARTITION BY membelah baris menjadi jendela-jendela terpisah sebelum diurutkan — mirip GROUP BY, tetapi baris tetap utuh. Di bawah ini, penomoran dimulai ulang dari 1 untuk setiap pesanan:

SELECT pesanan_id, produk_id, jumlah,
       ROW_NUMBER() OVER (PARTITION BY pesanan_id ORDER BY jumlah DESC, produk_id) AS urutan
FROM detail_pesanan
ORDER BY pesanan_id, urutan;

Output yang Dihasilkan:

1|1|2|1
1|3|1|2
2|2|3|1
3|4|2|1
3|1|1|2
4|5|1|1
5|3|2|1
5|6|2|2
6|1|1|1

Pola ini adalah resep klasik “ambil N teratas per kelompok”: kombinasikan dengan CTE dari Chapter Common Table Expressions, lalu saring urutan = 1 pada query luar.

Agregat di Jendela: Total Berjalan #

Fungsi agregat biasa seperti SUM berubah sifat begitu diberi OVER — penjumlahan dilakukan baris demi baris sesuai urutan jendela, menghasilkan total berjalan (running total):

SELECT id, nama, harga,
       SUM(harga) OVER (ORDER BY id) AS total_berjalan
FROM produk
ORDER BY id;

Output yang Dihasilkan:

1|Kopi Gayo|45000|45000
2|Teh Melati|18000|63000
3|Gula Aren|25000|88000
4|Sirup Vanila|25000|113000
5|Kopi Toraja|52000|165000
6|Teh Hijau|22000|187000
7|Cangkir Keramik|35000|222000

Kolom total_berjalan kini memuat “total harga produk sampai baris ini” — sesuatu yang mustahil ditulis dengan GROUP BY tanpa kehilangan barisnya.

Window vs GROUP BY #

Perbedaan mendasar keduanya: GROUP BY meringkas baris, OVER menambah kolom. Bandingkan dua query menghitung pelanggan per kota:

SELECT nama, kota,
       COUNT(*) OVER (PARTITION BY kota) AS pelanggan_satu_kota
FROM pelanggan
ORDER BY kota, nama;

Output yang Dihasilkan:

Ani||1
Siti|Bandung|1
Budi|Jakarta|2
Dewi|Jakarta|2
Rina|Surabaya|1

Versi GROUP BY kota akan mengembalikan empat baris (termasuk kelompok NULL yang sudah kita bahas); versi window mengembalikan lima baris — Ani tetap tampil dengan hitungan 1, dan NULL tidak perlu dibahas ulang karena PARTITION BY memperlakukannya sebagai kelompok tersendiri juga.

LAG dan LEAD: Membandingkan dengan Tetangga #

LAG() dan LEAD() mengambil nilai dari baris sebelum dan sesudah baris saat ini — tepat untuk menghitung selisih berurutan:

SELECT nama, harga,
       LAG(harga) OVER (ORDER BY harga) AS sebelumnya,
       LEAD(harga) OVER (ORDER BY harga) AS berikutnya
FROM produk
ORDER BY harga;

Output yang Dihasilkan:

Teh Melati|18000||22000
Teh Hijau|22000|18000|25000
Gula Aren|25000|22000|25000
Sirup Vanila|25000|25000|35000
Cangkir Keramik|35000|25000|45000
Kopi Gayo|45000|35000|52000
Kopi Toraja|52000|45000|

Baris pertama tidak punya tetangga sebelumnya, sehingga LAG() menghasilkan NULL — demikian pula LEAD() pada baris terakhir. Ekspresi harga - LAG(harga) OVER (ORDER BY harga) akan menghitung selisih ke produk sebelumnya.

Frame: Membatasi Jendela #

Secara bawaan, jendela ber-ORDER BY membentang dari baris pertama sampai baris saat ini. Klausa ROWS BETWEEN membatasinya — misalnya jendela tiga baris (satu sebelum, sekarang, satu sesudah) untuk total bergulir:

SELECT id, nama, harga,
       SUM(harga) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS jendela_3baris
FROM produk
ORDER BY id;

Output yang Dihasilkan:

1|Kopi Gayo|45000|63000
2|Teh Melati|18000|88000
3|Gula Aren|25000|68000
4|Sirup Vanila|25000|102000
5|Kopi Toraja|52000|99000
6|Teh Hijau|22000|109000
7|Cangkir Keramik|35000|57000

Baris 3 memuat 18000 + 25000 + 25000 — tepat tiga baris di sekitarnya. Bawaan frame untuk query ber-ORDER BY adalah RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; perbedaan ROWS dan RANGE hanya terasa saat ada nilai seri pada kolom ORDER BY — pada contoh kita yang diurutkan id unik, keduanya memberi hasil sama.

Window functions melengkapi kotak perkakas query kita: agregasi tanpa kehilangan baris. Bab berikutnya kembali ke penyusunan hasil: menggabungkan keluaran beberapa query dengan operasi set seperti UNION.