KAL
Menu

Window function II

Membaca baris tetangga dan total berjalan dengan LAG, LEAD, dan NTILE.

Daftar isi

Topik ini menutup modul subquery dan CTE dengan window function yang membaca posisi baris di dalam jendelanya, yaitu LAG dan LEAD untuk baris tetangga, total berjalan yang mengakumulasi, dan NTILE yang membagi baris menjadi kelompok sama besar. Ketiganya menghidupkan laporan deret waktu dan pembagian tingkat.

Blok penyiapan dataset#

bash 
# rekap bulanan peminjaman untuk latihan deret waktu
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori, bulanan 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);
CREATE TABLE bulanan (bulan DATE PRIMARY KEY, pinjaman INTEGER NOT NULL);
INSERT INTO bulanan VALUES ('2025-01-01', 12), ('2025-02-01', 18), ('2025-03-01', 15), ('2025-04-01', 21), ('2025-05-01', 26);
SQL

Tabel bulanan berisi lima bulan kegiatan, kecil dan mudah dihitung ulang dengan mata.

LAG dan LEAD membaca tetangga#

LAG membaca nilai baris sebelumnya dan LEAD membaca baris berikutnya, dengan argumen kedua sebagai jarak dan argumen ketiga sebagai nilai pengganti ketika tetangganya tidak ada. Selisih antar periode menjadi pola paling umumnya.

bash 
# selisih pinjaman antar bulan
psql -U postgres -d perpustakaan -c "SELECT bulan, pinjaman, LAG(pinjaman) OVER (ORDER BY bulan) AS bulan_lalu, pinjaman - LAG(pinjaman) OVER (ORDER BY bulan) AS perubahan FROM bulanan ORDER BY bulan;"
text Keluaran 
   bulan    | pinjaman | bulan_lalu | perubahan
------------+----------+------------+-----------
 2025-01-01 |       12 |            |
 2025-02-01 |       18 |         12 |         6
 2025-03-01 |       15 |         18 |        -3
 2025-04-01 |       21 |         15 |         6
 2025-05-01 |       26 |         21 |         5
(5 rows)

Baris pertama membawa NULL pada kolom pembandingnya karena tidak ada bulan sebelumnya, dan nilai penggantinya dapat dinyatakan pada argumen ketiga bila NULL itu mengganggu laporan. Perbandingan antar baris yang menumpang query terpisah kini menjadi satu kolom hitungan.

Total berjalan dan bingkai jendela#

Agregat dengan OVER (ORDER BY …) mengalir menumpuk nilai dari awal bingkai hingga baris saat ini, sehingga total berjalan menjadi satu ekspresi. Bingkai (frame) bawaannya untuk mode ini adalah dari awal jendela hingga baris saat ini beserta rekan nilainya.

bash 
# total berjalan pinjaman per bulan
psql -U postgres -d perpustakaan -c "SELECT bulan, pinjaman, SUM(pinjaman) OVER (ORDER BY bulan) AS total_berjalan FROM bulanan ORDER BY bulan;"
text Keluaran 
   bulan    | pinjaman | total_berjalan
------------+----------+----------------
 2025-01-01 |       12 |             12
 2025-02-01 |       18 |             30
 2025-03-01 |       15 |             45
 2025-04-01 |       21 |             66
 2025-05-01 |       26 |             92
(5 rows)

Setiap baris memuat akumulasi sampai bulannya, dan partisi membatasi akumulasi per kelompok bila data memuat beberapa deret sekaligus. Apabila rata-rata bergerak tiga bulan yang diinginkan, bingkainya dinyatakan eksplisit, misalnya ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, dan deklarasi bingkai yang eksplisit menjadi kebiasaan pada laporan yang serius.

NTILE membagi tingkat#

NTILE (n) membagi baris jendela menjadi n kelompok dengan ukuran sama besar menurut urutan yang dinyatakan, sehingga tingkatan seperti teratas dan terbawah menjadi satu kolom. Dataset anggota yang meminjam paling banyak dipilah menjadi dua tingkat.

bash 
# pembagian anggota menjadi dua tingkat keaktifan
psql -U postgres -d perpustakaan -c "SELECT nama, pinjaman, NTILE(2) OVER (ORDER BY pinjaman DESC, nama) AS tingkat FROM (SELECT a.nama, COUNT(p.id) AS pinjaman FROM anggota a LEFT JOIN peminjaman p ON p.anggota_id = a.id GROUP BY a.nama) t ORDER BY tingkat, pinjaman DESC, nama;"
text Keluaran 
     nama     | pinjaman | tingkat
--------------+----------+---------
 Ali Rahman   |        2 |       1
 Budi Santoso |        2 |       1
 Dewi Lestari |        2 |       1
 Citra Kirana |        1 |       2
 Eka Pratama  |        1 |       2
(5 rows)

Lima anggota terbagi menjadi tiga baris tingkat pertama dan dua baris tingkat kedua, karena sisa pembagian diberikan pada kelompok terdepan. Nilai kembalian selalu bilangan bulat antara satu dan n, dan pembagian yang sering dipakai lainnya adalah empat untuk kuartil.

Nilai ujung jendela#

FIRST_VALUE dan LAST_VALUE membaca nilai baris ujung bingkai, dan LAST_VALUE dengan bingkai bawaannya membaca baris saat ini alih-alih baris terakhir jendela, kejutan klasik yang penangkalnya mendeklarasikan bingkai penuh secara eksplisit. Pola yang aman menuliskan ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ketika ujung jendela penuh dimaksud.

bash 
# pembanding terbaik dan terendah per baris
psql -U postgres -d perpustakaan -c "SELECT bulan, pinjaman, FIRST_VALUE(pinjaman) OVER (ORDER BY bulan ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS terendah, LAST_VALUE(pinjaman) OVER (ORDER BY bulan ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS tertinggi FROM bulanan ORDER BY bulan;"
text Keluaran 
   bulan    | pinjaman | terendah | tertinggi
------------+----------+----------+-----------
 2025-01-01 |       12 |       12 |        26
 2025-02-01 |       18 |       12 |        26
 2025-03-01 |       15 |       12 |        26
 2025-04-01 |       21 |       12 |        26
 2025-05-01 |       26 |       12 |        26
(5 rows)

Dengan bingkai yang dinyatakan penuh, kedua kolom membaca batas sungguhannya dan perilaku query tetap sama pada setiap eksekusi. Kebiasaan ini menyelesaikan satu-satunya jebakan yang tersisa dari keluarga fungsi jendela.

Kesalahan umum#

Kesalahan pertama adalah LAG tanpa ORDER BY di dalam OVER, yang urutan tetangganya tidak terdefinisi, sehingga nilai pembandingnya berganti-ganti. Kesalahan kedua adalah LAST_VALUE dengan bingkai bawaan yang menyertakan hanya sampai baris saat ini, dan perbaikannya bingkai penuh sebagaimana di atas.

Kesalahan ketiga adalah total berjalan tanpa ORDER BY pada OVER, yang menjumlahkan seluruh jendela di setiap baris alih-alih mengalirkan. Kesalahan keempat adalah NTILE pada baris yang jumlahnya lebih kecil dari n, yang menghasilkan kelompok kosong dalam makna, dan pilihan n disesuaikan dengan banyaknya baris yang nyata.

Ringkasan#

  • LAG dan LEAD membaca baris tetangga dengan jarak dan pengganti yang dinyatakan, dan selisih antar periode menjadi pola utamanya.
  • Agregat dengan OVER (ORDER BY …) mengalirkan total berjalan, dan bingkai yang eksplisit menjaga makna perhitungannya.
  • NTILE membagi baris menjadi kelompok sama besar menurut urutan, dengan sisa pembagian pada kelompok terdepan.
  • FIRST_VALUE dan LAST_VALUE menuntut deklarasi bingkai penuh agar ujung jendelanya dibaca dengan benar.

Referensi#