KAL
Menu

Fungsi Window

Total berjalan, peringkat, dan perbandingan antar baris dengan OVER.

Daftar isi

Fungsi window menghitung atas sekumpulan baris yang berdekatan tanpa menyusutkan barisnya, sehingga setiap baris membawa angka agregat tetangganya sebagaimana groupby yang tidak memotong. Topik ini menerapkan tiga fungsi window yang paling sering dipakai analisis, yaitu RANK untuk peringkat, SUM berjalan untuk kumulatif, dan LAG untuk selisih terhadap baris sebelumnya, semuanya di atas database latihan analitik. Setelah membacanya, tiga pertanyaan analisis klasik itu dijawab dalam satu query tanpa subquery bersarang.

OVER: agregasi tanpa menyusutkan#

Kata OVER membedakan fungsi window dari agregat biasa. Agregat dengan GROUP BY menyusutkan setiap kelompok menjadi satu baris, sedangkan fungsi window menambahkan satu kolom pada setiap baris aslinya, dan jendelanya didefinisikan dengan PARTITION BY untuk membagi baris, ORDER BY untuk mengurutkan di dalam jendela, atau keduanya.

Perilaku ganda ini adalah sumber nilainya, yaitu baris yang menunjukkan pengukuran dan kolom yang menunjukkan posisinya di antara tetangganya dibaca dalam satu tabel. Pada pandas, padanan terdekatnya adalah transform dan rank, dan pola keduanya identik dengan hasil yang sama.

Latihan topik ini memakai tabel fakta_ukur enam barisnya dari database latihan yang sama dengan dua topik sebelumnya, dan skrip penyiapannya disertakan agar setiap query dapat dijalankan berdiri sendiri.

RANK untuk peringkat#

rank() memberi peringkat di dalam jendela, dan PARTITION BY menjadikan peringkat itu berulang per kelompok, sedangkan ORDER BY di dalam OVER menyatakan arah urutannya. Kombinasi keduanya menjawab pertanyaan “pengukuran mana yang tertinggi pada masing-masing stasiun” tanpa satu subquery pun.

bash 
# siapkan ulang database latihan agar topik dapat dijalankan sendiri
psql -h /home/kal/my-project/pgsock -U postgres -c "DROP DATABASE IF EXISTS analitik"
psql -h /home/kal/my-project/pgsock -U postgres -c "CREATE DATABASE analitik"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "CREATE TABLE dim_stasiun (id_stasiun serial PRIMARY KEY, nama text NOT NULL, wilayah text NOT NULL)"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "INSERT INTO dim_stasiun (nama, wilayah) VALUES ('Stasiun A','Kawasan Sekolah'), ('Stasiun B','Jalan Utama')"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "CREATE TABLE fakta_ukur (id serial PRIMARY KEY, waktu timestamp NOT NULL, id_stasiun integer REFERENCES dim_stasiun, pm25 numeric NOT NULL, suhu_c numeric, kelembapan numeric)"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "INSERT INTO fakta_ukur (waktu, id_stasiun, pm25, suhu_c, kelembapan) VALUES ('2026-03-01 07:00',1,38.2,25.6,71.0), ('2026-03-01 08:00',1,44.9,26.8,69.4), ('2026-03-01 09:00',1,41.3,27.9,68.7), ('2026-03-01 07:00',2,58.7,24.9,72.3), ('2026-03-01 08:00',2,66.1,25.8,70.9), ('2026-03-01 09:00',2,61.4,26.7,71.5)"
text Keluaran 
DROP DATABASE
CREATE DATABASE
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 6
bash 
# peringkat pengukuran di dalam tiap stasiun
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "SELECT s.nama, f.waktu, f.pm25, rank() OVER (PARTITION BY s.nama ORDER BY f.pm25 DESC) AS peringkat FROM fakta_ukur f JOIN dim_stasiun s ON f.id_stasiun = s.id_stasiun ORDER BY s.nama, peringkat"
text Keluaran 
   nama    |        waktu        | pm25 | peringkat 
-----------+---------------------+------+-----------
 Stasiun A | 2026-03-01 08:00:00 | 44.9 |         1
 Stasiun A | 2026-03-01 09:00:00 | 41.3 |         2
 Stasiun A | 2026-03-01 07:00:00 | 38.2 |         3
 Stasiun B | 2026-03-01 08:00:00 | 66.1 |         1
 Stasiun B | 2026-03-01 09:00:00 | 61.4 |         2
 Stasiun B | 2026-03-01 07:00:00 | 58.7 |         3
(6 rows)

Enam baris tetap enam, dan kolom peringkat berulang dari satu pada setiap stasiun karena PARTITION BY-nya. Perbedaan rank() dengan dense_rank() dan row_number() muncul saat ada nilai yang sama, yaitu rank melompati peringkat yang tersangkut, dense_rank tidak melompat, dan row_number memberi nomor unik tanpa peduli seri.

SUM berjalan untuk kumulatif#

sum() OVER (ORDER BY waktu) menghitung total kumulatif, yaitu jendela yang membentang dari awal sampai baris saat ini, dan inilah bentuk paling umum dari metrik berjalan seperti akumulasi emisi atau total transaksi.

bash 
# total berjalan PM2.5 Stasiun A
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "SELECT waktu, pm25, sum(pm25) OVER (ORDER BY waktu) AS kumulatif FROM fakta_ukur WHERE id_stasiun = 1 ORDER BY waktu"
text Keluaran 
        waktu        | pm25 | kumulatif 
---------------------+------+-----------
 2026-03-01 07:00:00 | 38.2 |      38.2
 2026-03-01 08:00:00 | 44.9 |      83.1
 2026-03-01 09:00:00 | 41.3 |     124.4
(3 rows)

Kolom kumulatif menambah pm25 baris saat ini ke total sebelumnya, dan jendelanya dapat diberi batas eksplisit dengan ROWS BETWEEN bila rata-rata bergulir yang dimaksud, misalnya tiga baris terakhir. Waktu yang sama pada beberapa baris menuntut kehati-hatian tambahan pada ORDER BY, karena urutan yang tidak pasti membuat kumulatifnya ikut tidak pasti.

LAG untuk selisih antar baris#

lag() membaca nilai dari baris sebelumnya di dalam jendela, sehingga selisih antar pengukuran menjadi satu ekspresi sederhana, dan baris pertama yang tidak punya tetangga berisi NULL secara jujur.

bash 
# perubahan PM2.5 terhadap jam sebelumnya
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "SELECT waktu, pm25, pm25 - lag(pm25) OVER (ORDER BY waktu) AS selisih FROM fakta_ukur WHERE id_stasiun = 1 ORDER BY waktu"
text Keluaran 
        waktu        | pm25 | selisih 
---------------------+------+---------
 2026-03-01 07:00:00 | 38.2 |        
 2026-03-01 08:00:00 | 44.9 |     6.7
 2026-03-01 09:00:00 | 41.3 |    -3.6
(3 rows)

lead() adalah kembarannya yang membaca baris berikutnya, dan NULL pada tepi jendela adalah perilaku bawaan yang jujur, dengan penggantinya dapat dinyatakan sebagai argumen ketiga bila diperlukan. Selisih antar baris inilah bahan dasar deteksi lonjakan, dan ambang pada selisihnya dipilih dari domain data sebagaimana pemeriksaan rentang pada modul pembersihan.

Kesalahan umum#

Kesalahan pertama adalah lupa ORDER BY pada jendela yang berurutan, seperti SUM berjalan dan LAG. Tanpa urutan, hasilnya tidak terdefinisi dan angkanya dapat berubah antar eksekusi, sehingga ORDER BY waktu selalu menyertainya.

Kesalahan kedua adalah menggabungkan fungsi window dengan GROUP BY tanpa memikirkan urutan evaluasi. Window dihitung setelah GROUP BY, sehingga jendelanya beroperasi atas hasil agregasi, dan kebingungan granularitas yang menyusulnya dibereskan dengan menulis tahapannya sebagai subquery atau CTE.

Kesalahan ketiga adalah menyaring hasil window pada WHERE. Saringan dijalankan sebelum window dihitung, sedangkan syarat atas hasilnya ditaruh pada subquery luar, dan pesan error kolom tidak ada menunjuk persis keadaan itu.

Ringkasan#

  • OVER menghitung atas jendela baris tanpa menyusutkannya, dengan PARTITION BY membagi dan ORDER BY mengurutkan di dalam jendela.
  • rank() dengan PARTITION BY memberi peringkat per kelompok, dan varian rank, dense_rank, serta row_number berbeda pada nilai yang seri.
  • sum() OVER (ORDER BY ...) menghasilkan total berjalan, dan ROWS BETWEEN membentuk rata-rata bergulir.
  • lag() dan lead() membaca baris tetangga untuk selisih antar pengukuran, dengan NULL yang jujur di tepi jendela.

Referensi#