KAL
Menu

Window function I

Menghitung nilai per baris di atas jendela baris dengan OVER dan PARTITION BY.

Daftar isi

Topik ini membuka pembahasan window function, keluarga fungsi yang menghitung nilai per baris di atas jendela baris yang dirujuknya, tanpa menciutkan baris sebagaimana GROUP BY. Perbedaan ini adalah intinya, karena laporan yang butuh angka kelompok berdampingan dengan baris detailnya menjadi satu query tunggal.

Blok penyiapan dataset#

bash 
# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

OVER tanpa partisi#

Bentuk paling sederhana adalah agregat dengan OVER (), yang menghitung agregat atas seluruh baris lalu menuliskannya di setiap baris, tanpa menghilangkan satu baris pun. Query berikut menyandingkan setiap buku dengan rata-rata tahun terbit seluruh koleksi.

bash 
# rata-rata koleksi berdampingan dengan tiap buku
psql -U postgres -d perpustakaan -c "SELECT judul, tahun, ROUND(AVG(tahun) OVER (), 1) AS rata_semua, tahun - ROUND(AVG(tahun) OVER (), 1) AS selisih FROM buku ORDER BY judul LIMIT 4;"
text Keluaran 
            judul            | tahun | rata_semua | selisih
-----------------------------+-------+------------+---------
 Algoritma dan Struktur Data |  2021 |     2020.8 |     0.2
 Cerita Kota Lama            |  2023 |     2020.8 |     2.2
 Dasar Basis Data            |  2021 |     2020.8 |     0.2
 Fisika untuk Pemula         |  2020 |     2020.8 |    -0.8
(4 rows)

Delapan baris tetap delapan, dan kolom selisih menghitung jarak setiap buku dari rata-rata secara langsung. Membuat angka yang sama dengan GROUP BY menuntut query terpisah yang disandingkan ulang, sedangkan window function menyelesaikannya dalam satu pembacaan.

PARTITION BY membatasi jendela#

PARTITION BY membagi baris menjadi jendela per nilai, sehingga agregatnya dihitung per kelompok tetapi barisnya tetap utuh. Query berikut melaporkan buku beserta banyaknya karya penulisnya masing-masing.

bash 
# banyaknya karya penulis pada tiap baris buku
psql -U postgres -d perpustakaan -c "SELECT penulis, judul, COUNT(*) OVER (PARTITION BY penulis) AS karya_penulis FROM buku ORDER BY penulis, judul LIMIT 5;"
text Keluaran 
    penulis     |            judul            | karya_penulis
----------------+-----------------------------+---------------
 Ahmad Fauzi    | Algoritma dan Struktur Data |             2
 Ahmad Fauzi    | Jaringan Komputer           |             2
 Bambang Wijaya | Sejarah Nusantara           |             1
 Nur Aisyah     | Cerita Kota Lama            |             1
 Rina Marlina   | Fisika untuk Pemula         |             2
(5 rows)

Dua baris milik penulis yang sama membawa angka karya yang sama, sedangkan detail judulnya tetap terbaca per baris. Partisinya boleh bertingkat dengan beberapa kolom, dan window function dapat dirujuk lebih pendek melalui WINDOW bernama ketika definisinya berulang.

ROW_NUMBER dan keluarga ranking#

Fungsi ranking memberi nomor per jendela sesuai ORDER BY di dalam OVER, dengan ROW_NUMBER yang berurutan tanpa seri, RANK yang membagi nomor pada nilai seri, dan DENSE_RANK yang menomori tingkat tanpa celah. Ketiganya menjadi fondasi pertanyaan peringkat.

bash 
# peringkat buku terbaru di penulisnya masing-masing
psql -U postgres -d perpustakaan -c "SELECT penulis, judul, tahun, ROW_NUMBER() OVER (PARTITION BY penulis ORDER BY tahun DESC, judul) AS no_urut FROM buku ORDER BY penulis, no_urut;"
text Keluaran 
    penulis     |            judul            | tahun | no_urut
----------------+-----------------------------+-------+---------
 Ahmad Fauzi    | Algoritma dan Struktur Data |  2021 |       1
 Ahmad Fauzi    | Jaringan Komputer           |  2019 |       2
 Bambang Wijaya | Sejarah Nusantara           |  2018 |       1
 Nur Aisyah     | Cerita Kota Lama            |  2023 |       1
 Rina Marlina   | Kimia Organik               |  2022 |       1
 Rina Marlina   | Fisika untuk Pemula         |  2020 |       2
 Sari Wulandari | Statistika Terapan          |  2022 |       1
 Sari Wulandari | Dasar Basis Data            |  2021 |       2
(8 rows)

Kunci pemutus judul menjaga nomor yang berseri tetap deterministik, kebiasaan yang dibahas sejak modul ORDER BY. Baris pertama setiap partisi adalah buku terbaru penulisnya, dan penyaringan no_urut = 1 dilakukan pada query pembungkus karena window function tidak boleh dirujuk WHERE.

Menyaring hasil window#

Karena window function dinilai setelah WHERE, penyaringan atas hasilnya dilakukan melalui subquery atau CTE yang membungkusnya, lalu WHERE luar menyaring kolom nomor itu. Pola ini muncul di hampir setiap laporan peringkat.

bash 
# buku terbaru setiap penulis lewat nomor urut
psql -U postgres -d perpustakaan -c "SELECT penulis, judul, tahun FROM (SELECT penulis, judul, tahun, ROW_NUMBER() OVER (PARTITION BY penulis ORDER BY tahun DESC, judul) AS no_urut FROM buku) r WHERE no_urut = 1 ORDER BY penulis;"
text Keluaran 
    penulis     |            judul            | tahun
----------------+-----------------------------+-------
 Ahmad Fauzi    | Algoritma dan Struktur Data |  2021
 Bambang Wijaya | Sejarah Nusantara           |  2018
 Nur Aisyah     | Cerita Kota Lama            |  2023
 Rina Marlina   | Kimia Organik               |  2022
 Sari Wulandari | Statistika Terapan          |  2022
(5 rows)

Lima baris terpilih, satu per penulis, dan CTE menjadikan pola ini lebih terbaca pada query yang lebih panjang sebagaimana dibahas pada topik CTE. DISTINCT ON yang diperkenalkan pada modul psql menyelesaikan kasus satu-per-kelompok yang sederhana, sedangkan window function menang ketika laporan butuh lebih dari satu tingkat sekaligus.

Kesalahan umum#

Kesalahan pertama adalah menaruh window function di WHERE, yang ditolak server karena penilaiannya berada di tahap SELECT, dan perbaikannya membungkus query. Kesalahan kedua adalah ORDER BY di dalam OVER yang tidak memutus seri, sehingga nomor baris yang berseri berubah-ubah antar eksekusi, dan kunci pemutus unik menjadi kewajibannya.

Kesalahan ketiga adalah mengira PARTITION BY menyaring baris, padahal ia hanya membatasi jendela, dan penyaringan tetap tugas WHERE pada lapisan yang tepat. Kesalahan keempat adalah menghitung COUNT(*) OVER () pada query dengan LIMIT dan mengira angkanya terbatas, padahal window dinilai atas seluruh baris sebelum LIMIT bekerja, sifat yang justru dipakai untuk total di samping halaman.

Ringkasan#

  • Window function menghitung nilai per baris atas jendela barisnya, tanpa menciutkan baris sebagaimana GROUP BY.
  • OVER () menghitung atas seluruh baris, dan PARTITION BY membagi jendela per kelompok nilai.
  • ROW_NUMBER, RANK, dan DENSE_RANK memberi peringkat per jendela, dengan kunci pemutus yang menjamin determinisme.
  • Penyaringan hasil window dilakukan pada query pembungkus, karena window function dinilai setelah WHERE.

Referensi#