KAL
Menu

Fungsi Window

OVER dan PARTITION BY dengan ROW_NUMBER, LAG, dan agregat berjendela.

Daftar isi

Fungsi window adalah fungsi yang menghitung nilai dari sekelompok baris yang berhubungan dengan baris saat ini, tanpa memadatkan baris-baris itu menjadi satu hasil. Topik ini membahas klausa OVER beserta PARTITION BY, fungsi pemeringkatan ROW_NUMBER, RANK, dan DENSE_RANK, pengambilan baris sebelumnya dengan LAG, serta agregat berjendela untuk total berjalan. Setelah mengikuti topik ini, peringkat dan perbandingan antarbaris dapat dihitung langsung di dalam SELECT tanpa subquery tambahan.

Perbedaan dengan GROUP BY#

GROUP BY meringkas banyak baris menjadi satu baris per kelompok, sehingga detail di bawah kelompok itu hilang dari hasil. Fungsi window menempuh arah yang sebaliknya: setiap baris tetap tampil, dan hasil perhitungan atas kelompoknya ditambahkan sebagai kolom baru. Kedua pendekatan dijalankan pada data yang sama agar perbedaannya terlihat.

Jalankan persiapan berikut, lalu bandingkan dua query berikutnya.

sql persiapan-fungsi-window.sql 
-- tidak-dijalankan
-- Skrip persiapan topik fungsi window: basis data perpustakaan

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE anggota (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL
);

CREATE TABLE buku (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  judul   VARCHAR(200) NOT NULL,
  penulis VARCHAR(100) NOT NULL,
  stok    INT NOT NULL DEFAULT 0
);

CREATE TABLE peminjaman (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id      INT NOT NULL,
  buku_id         INT NOT NULL,
  tanggal_pinjam  DATE NOT NULL,
  jatuh_tempo     DATE NOT NULL,
  tanggal_kembali DATE NULL,
  FOREIGN KEY (anggota_id) REFERENCES anggota(id),
  FOREIGN KEY (buku_id) REFERENCES buku(id)
);

INSERT INTO anggota (nama) VALUES
  ('Ali'), ('Budi'), ('Citra'), ('Dewi'), ('Eka');

INSERT INTO buku (judul, penulis, stok) VALUES
  ('Dasar Astronomi untuk Pemula', 'Hartono', 3),
  ('Sejarah Maritim Nusantara', 'Wijaya', 2),
  ('Panduan Menanam Sayuran', 'Santoso', 4),
  ('Pengantar Fisika Klasik', 'Kusuma', 1),
  ('Cerita Rakyat Nusantara', 'Lestari', 3),
  ('Ilmu Gizi Sehari-hari', 'Pratiwi', 2);

INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, jatuh_tempo, tanggal_kembali) VALUES
  (1, 1, '2026-05-02', '2026-05-16', '2026-05-15'),
  (1, 2, '2026-05-02', '2026-05-16', '2026-05-14'),
  (1, 3, '2026-06-01', '2026-06-15', '2026-06-20'),
  (2, 4, '2026-05-10', '2026-05-24', '2026-05-22'),
  (2, 5, '2026-06-03', '2026-06-17', NULL),
  (2, 6, '2026-06-03', '2026-06-17', '2026-06-10'),
  (3, 1, '2026-05-20', '2026-06-03', '2026-06-01'),
  (3, 4, '2026-06-05', '2026-06-19', NULL),
  (4, 2, '2026-05-25', '2026-06-08', '2026-06-07'),
  (4, 3, '2026-06-12', '2026-06-26', NULL),
  (5, 4, '2026-06-14', '2026-06-28', '2026-06-27'),
  (5, 5, '2026-06-18', '2026-07-02', NULL),
  (3, 6, '2026-07-01', '2026-07-15', NULL);
bash 
# membuat ulang basis data perpustakaan dari nol
mysql -u root < persiapan-fungsi-window.sql
bash 
# GROUP BY memadatkan baris menjadi satu per kelompok
mysql -t -u root perpustakaan <<'SQL'
SELECT anggota_id, COUNT(*) AS jumlah_pinjam
FROM peminjaman
GROUP BY anggota_id;
SQL
text Keluaran 
+------------+---------------+
| anggota_id | jumlah_pinjam |
+------------+---------------+
|          1 |             3 |
|          2 |             3 |
|          3 |             3 |
|          4 |             2 |
|          5 |             2 |
+------------+---------------+
bash 
# fungsi window menambahkan hitungan per anggota tanpa memadatkan baris
mysql -t -u root perpustakaan <<'SQL'
SELECT id, anggota_id, buku_id,
       COUNT(*) OVER (PARTITION BY anggota_id) AS pinjam_anggota
FROM peminjaman
ORDER BY id;
SQL
text Keluaran 
+----+------------+---------+----------------+
| id | anggota_id | buku_id | pinjam_anggota |
+----+------------+---------+----------------+
|  1 |          1 |       1 |              3 |
|  2 |          1 |       2 |              3 |
|  3 |          1 |       3 |              3 |
|  4 |          2 |       4 |              3 |
|  5 |          2 |       5 |              3 |
|  6 |          2 |       6 |              3 |
|  7 |          3 |       1 |              3 |
|  8 |          3 |       4 |              3 |
|  9 |          4 |       2 |              2 |
| 10 |          4 |       3 |              2 |
| 11 |          5 |       4 |              2 |
| 12 |          5 |       5 |              2 |
| 13 |          3 |       6 |              3 |
+----+------------+---------+----------------+

Keluaran pertama menyisakan lima baris, sedangkan keluaran kedua mempertahankan seluruh tiga belas baris peminjaman. Kolom pinjam_anggota mengulang angka yang sama untuk baris-baris satu anggota, sehingga detail peminjaman dan ringkasan anggota terbaca pada satu tempat.

PARTITION BY dan jendela data#

Klausa OVER mendefinisikan jendela (window), yaitu kumpulan baris yang dipakai fungsi untuk menghitung nilai satu baris. PARTITION BY membagi semua baris hasil ke dalam partisi, dan fungsi window dihitung terpisah untuk setiap partisi. Tanpa PARTITION BY, seluruh hasil menjadi satu partisi.

COUNT(*) OVER (PARTITION BY anggota_id)anggota_id = 1id 1id 2id 3COUNT(*) = 3anggota_id = 2id 4id 5id 6COUNT(*) = 3anggota_id = 3id 7id 8id 13COUNT(*) = 3Setiap partisi dihitung terpisah, sedangkan baris tetap utuh.
Pembagian partisi oleh anggota_id

Sintaks lengkapnya menyertakan urutan di dalam partisi, yaitu OVER (PARTITION BY kolom ORDER BY kolom). Bagian ORDER BY menentukan urutan baris di dalam jendela dan menjadi dasar fungsi yang peka urutan seperti LAG atau total berjalan. Apabila beberapa fungsi memakai definisi jendela yang sama, definisi itu dapat diberi nama dengan klausa WINDOW dan dirujuk berulang kali.

sql Contoh: jendela bernama dengan klausa WINDOW 
-- tidak-dijalankan
SELECT anggota_id,
       COUNT(*) OVER w AS jumlah_pinjam,
       SUM(buku_id) OVER w AS total_buku_id
FROM peminjaman
WINDOW w AS (PARTITION BY anggota_id);

Fungsi pemeringkatan#

ROW_NUMBER, RANK, dan DENSE_RANK menomori baris di dalam partisi, tetapi ketiganya menangani seri dengan cara berbeda. ROW_NUMBER memberi nomor unik berurutan tanpa memedulikan nilai yang sama. RANK memberi nomor yang sama untuk nilai seri, lalu melompati nomor berikutnya sebanyak jumlah baris seri. DENSE_RANK memberi nomor seri yang sama tanpa melompati nomor mana pun.

bash 
# tiga fungsi pemeringkatan atas total peminjaman per anggota
mysql -t -u root perpustakaan <<'SQL'
WITH jumlah_pinjam AS (
  SELECT anggota_id, COUNT(*) AS total_pinjam
  FROM peminjaman
  GROUP BY anggota_id
)
SELECT a.nama, j.total_pinjam,
       ROW_NUMBER() OVER (ORDER BY j.total_pinjam DESC, a.nama) AS no_urut,
       RANK()       OVER (ORDER BY j.total_pinjam DESC)          AS peringkat,
       DENSE_RANK() OVER (ORDER BY j.total_pinjam DESC)          AS peringkat_padat
FROM jumlah_pinjam AS j
JOIN anggota AS a ON a.id = j.anggota_id
ORDER BY no_urut;
SQL
text Keluaran 
+-------+--------------+---------+-----------+-----------------+
| nama  | total_pinjam | no_urut | peringkat | peringkat_padat |
+-------+--------------+---------+-----------+-----------------+
| Ali   |            3 |       1 |         1 |               1 |
| Budi  |            3 |       2 |         1 |               1 |
| Citra |            3 |       3 |         1 |               1 |
| Dewi  |            2 |       4 |         4 |               2 |
| Eka   |            2 |       5 |         4 |               2 |
+-------+--------------+---------+-----------+-----------------+

Tiga anggota menyamai total peminjaman, dan ketiga fungsi memperlakukan seri itu berbeda. ROW_NUMBER memerlukan pemecah seri, yang dalam contoh ini adalah a.nama, agar nomor urutnya tidak berubah-ubah antar eksekusi. Tanpa pemecah seri yang deterministik, urutan ROW_NUMBER di antara baris yang nilainya sama tidak dapat dijamin.

Mengakses baris sebelumnya dengan LAG#

LAG membaca nilai dari baris sebelumnya di dalam partisi yang sama, sehingga perbandingan bulan berjalan dengan bulan sebelumnya tidak butuh self join. Argumen pertamanya adalah kolom yang dibaca, sedangkan PARTITION BY dan ORDER BY di dalam OVER menentukan pembagian partisi serta urutan baris yang dipakai LAG.

bash 
# perbandingan jumlah peminjaman bulan ini dengan bulan sebelumnya
mysql -t -u root perpustakaan <<'SQL'
WITH per_bulan AS (
  SELECT anggota_id, DATE_FORMAT(tanggal_pinjam, '%Y-%m') AS bulan, COUNT(*) AS jumlah
  FROM peminjaman
  GROUP BY anggota_id, DATE_FORMAT(tanggal_pinjam, '%Y-%m')
)
SELECT anggota_id, bulan, jumlah,
       LAG(jumlah) OVER (PARTITION BY anggota_id ORDER BY bulan) AS bulan_sebelumnya,
       jumlah - LAG(jumlah) OVER (PARTITION BY anggota_id ORDER BY bulan) AS selisih
FROM per_bulan
ORDER BY anggota_id, bulan;
SQL
text Keluaran 
+------------+---------+--------+------------------+---------+
| anggota_id | bulan   | jumlah | bulan_sebelumnya | selisih |
+------------+---------+--------+------------------+---------+
|          1 | 2026-05 |      2 |             NULL |    NULL |
|          1 | 2026-06 |      1 |                2 |      -1 |
|          2 | 2026-05 |      1 |             NULL |    NULL |
|          2 | 2026-06 |      2 |                1 |       1 |
|          3 | 2026-05 |      1 |             NULL |    NULL |
|          3 | 2026-06 |      1 |                1 |       0 |
|          3 | 2026-07 |      1 |                1 |       0 |
|          4 | 2026-05 |      1 |             NULL |    NULL |
|          4 | 2026-06 |      1 |                1 |       0 |
|          5 | 2026-06 |      2 |             NULL |    NULL |
+------------+---------+--------+------------------+---------+

Baris pertama setiap partisi tidak memiliki bulan sebelumnya, sehingga LAG mengembalikan NULL dan selisihnya ikut NULL. Nilai NULL itu adalah data, bukan cacat, karena bulan pertama memang tidak memiliki pembanding. Apabila selisih harus berupa angka, bungkus LAG dengan COALESCE.

Agregat berjendela dan total berjalan#

Fungsi agregat seperti SUM dan COUNT dapat dipakai sebagai fungsi window. Dengan ORDER BY di dalam OVER, agregat berjalan dari awal partisi sampai baris saat ini, sehingga total kumulatif terbentuk tanpa subquery. Contoh berikut menumpuk jumlah peminjaman per bulan menjadi total berjalan.

bash 
# total berjalan peminjaman per bulan
mysql -t -u root perpustakaan <<'SQL'
WITH per_bulan AS (
  SELECT DATE_FORMAT(tanggal_pinjam, '%Y-%m') AS bulan, COUNT(*) AS jumlah
  FROM peminjaman
  GROUP BY DATE_FORMAT(tanggal_pinjam, '%Y-%m')
)
SELECT bulan, jumlah,
       SUM(jumlah) OVER (ORDER BY bulan) AS total_berjalan
FROM per_bulan
ORDER BY bulan;
SQL
text Keluaran 
+---------+--------+----------------+
| bulan   | jumlah | total_berjalan |
+---------+--------+----------------+
| 2026-05 |      5 |              5 |
| 2026-06 |      7 |             12 |
| 2026-07 |      1 |             13 |
+---------+--------+----------------+

Kerangka (frame) default agregat berjendela berurutan adalah dari baris awal partisi sampai baris saat ini, termasuk baris-baris sejajar yang nilai pengurutnya sama. Pada contoh ini nilai bulan unik per partisi, sehingga kerangka default bekerja seperti harapan. Bila kolom pengurut bisa seri dan baris harus dimasukkan satu per satu, nyatakan kerangkanya secara eksplisit dengan ROWS UNBOUNDED PRECEDING AND CURRENT ROW.

Kesalahan umum#

Fungsi window hanya boleh ditulis pada daftar SELECT dan pada ORDER BY pernyataan terluar. Pencobaan menyaring baris dengan fungsi window di dalam WHERE ditolak server.

bash 
# fungsi window tidak boleh dipakai di dalam WHERE
mysql -u root perpustakaan -e "SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS no_urut FROM peminjaman WHERE ROW_NUMBER() OVER (ORDER BY id) = 1;"
text Keluaran 
ERROR 3593 (HY000) at line 1: You cannot use the window function 'row_number' in this context.'

Pesan errornya menegaskan konteks yang tidak diizinkan. Jalur pembenahnya adalah membungkus fungsi window di dalam CTE atau subquery, kemudian menyaring hasilnya di pernyataan luar. Kesalahan lain yang lazim adalah ORDER BY tanpa pemecah seri pada fungsi pemeringkatan, yang membuat nomor ROW_NUMBER berpindah-pindah di antara baris seri. Kesalahan berikutnya adalah mengira fungsi window mengurangi jumlah baris hasil seperti GROUP BY.

Ringkasan#

  • Fungsi window menghitung nilai dari jendela baris yang didefinisikan OVER, dan setiap baris hasil tetap tampil tanpa dipadatkan.
  • PARTITION BY membagi hasil ke dalam partisi yang dihitung terpisah, dan ORDER BY di dalam jendela mengatur urutan internalnya.
  • ROW_NUMBER, RANK, dan DENSE_RANK berbeda pada perlakuan seri, sedangkan LAG membaca baris sebelumnya dan mengembalikan NULL di awal partisi.
  • Agregat berjendela dengan ORDER BY menghasilkan total berjalan, dan kerangkanya dapat dinyatakan eksplisit bila kolom pengurut bisa seri.

Referensi#