Fungsi window

Menggunakan fungsi window untuk peringkat, total berjalan, dan perbandingan baris dengan lag serta lead.

Daftar isi

Topik ini membahas fungsi window (window function), yaitu fungsi yang menghitung nilai dari sekumpulan baris yang saling terkait tanpa memadatkannya menjadi satu baris. Pembahasan mencakup peringkat dengan ROW_NUMBER, RANK, dan DENSE_RANK, total berjalan dengan SUM() OVER, serta perbandingan antarbaris dengan LAG dan LEAD. Semua contoh dijalankan dengan DuckDB 2.0 melalui Python, dan setiap keluaran yang ditampilkan adalah keluaran asli dari skripnya. Setelah membaca, pola peringkat, akumulasi, dan perbandingan berdampingan dapat ditulis tanpa mengompres tabelnya.

Dataset contoh#

Contoh memakai catatan peminjaman harian dua cabang perpustakaan selama satu pekan yang tersimpan dalam tabel peminjaman. Kolomnya terdiri atas id, cabang, tanggal, dan jumlah peminjaman. Data sengaja memuat dua hari dengan jumlah peminjaman yang sama persis pada cabang Pusat agar perilaku peringkat saat nilai kembar terlihat jelas. Skrip berikut membangun ulang basis data perpustakaan.duckdb setiap kali dijalankan.

python siapkan_basisdata.py

import os
import duckdb

# basis data kecil dibangun ulang setiap kali skrip dijalankan
if os.path.exists("perpustakaan.duckdb"):
    os.remove("perpustakaan.duckdb")

con = duckdb.connect("perpustakaan.duckdb")
con.execute("""
CREATE TABLE peminjaman (
    id      INTEGER PRIMARY KEY,
    cabang  VARCHAR NOT NULL,
    tanggal DATE,
    jumlah  INTEGER NOT NULL
)
""")
con.execute("""
INSERT INTO peminjaman VALUES
    (1,  'Pusat', DATE '2024-06-03', 120),
    (2,  'Pusat', DATE '2024-06-04', 135),
    (3,  'Pusat', DATE '2024-06-05', 120),
    (4,  'Pusat', DATE '2024-06-06', 150),
    (5,  'Pusat', DATE '2024-06-07', 110),
    (6,  'Pusat', DATE '2024-06-08', 160),
    (7,  'Pusat', DATE '2024-06-09', 175),
    (8,  'Timur', DATE '2024-06-03', 90),
    (9,  'Timur', DATE '2024-06-04', 95),
    (10, 'Timur', DATE '2024-06-05', 90),
    (11, 'Timur', DATE '2024-06-06', 100),
    (12, 'Timur', DATE '2024-06-07', 85),
    (13, 'Timur', DATE '2024-06-08', 105),
    (14, 'Timur', DATE '2024-06-09', 110)
""")

jumlah = con.execute("SELECT COUNT(*) FROM peminjaman").fetchone()[0]
total = con.execute("SELECT SUM(jumlah) FROM peminjaman").fetchone()[0]
print(f"peminjaman: {jumlah} baris, total {total} transaksi")
con.close()

text Keluaran

peminjaman: 14 baris, total 1645 transaksi

Cabang Pusat mencatat 120 peminjaman pada 3 Juni dan lagi pada 5 Juni, dua nilai kembar yang akan menguji setiap fungsi peringkat. Seluruh 14 baris tersebar merata pada dua cabang, sehingga efek PARTITION BY mudah diamati. Angka pada contoh sengaja kecil agar setiap baris keluaran dapat dihitung ulang dengan cepat.

Peringkat dengan ROW_NUMBER, RANK, dan DENSE_RANK#

ROW_NUMBER, RANK, dan DENSE_RANK memberi nomor urut pada setiap baris di dalam partisi, tetapi ketiganya berbeda cara menomori nilai yang kembar. ROW_NUMBER selalu unik, RANK memberi nomor yang sama untuk nilai kembar lalu melompat, dan DENSE_RANK melompati nilai tanpa melompati nomor. Ketiga fungsi dinotasikan bersama OVER, klausa yang mendefinisikan jendela atau himpunan baris yang menjadi bahan hitungan. DuckDB juga menerima klausa WINDOW bernama agar definisi jendela cukup ditulis sekali.

python peringkat.py

import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("perpustakaan.duckdb")

tampilkan(con, """
SELECT tanggal,
       jumlah,
       ROW_NUMBER() OVER w AS nomor_baris,
       RANK()       OVER w AS peringkat,
       DENSE_RANK() OVER w AS peringkat_padat
FROM peminjaman
WHERE cabang = 'Pusat'
WINDOW w AS (ORDER BY jumlah DESC)
ORDER BY nomor_baris
""")
con.close()

text Keluaran

tanggal     jumlah  nomor_baris  peringkat  peringkat_padat
2024-06-09  175     1            1          1
2024-06-08  160     2            2          2
2024-06-06  150     3            3          3
2024-06-04  135     4            4          4
2024-06-03  120     5            5          5
2024-06-05  120     6            5          5
2024-06-07  110     7            7          6

Hari dengan 175 peminjaman menempati posisi pertama pada ketiga kolom sekaligus. Dua hari bernilai 120 memperlihatkan perbedaannya, yaitu ROW_NUMBER menomori 5 dan 6, RANK memberi 5 dan 5 lalu melompat ke 7, sedangkan DENSE_RANK memberi 5 dan 5 lalu berlanjut ke 6. Pemilihan fungsinya tergantung kebutuhan, yaitu ROW_NUMBER untuk memilih satu baris per kelompok, RANK untuk klasemen, dan DENSE_RANK untuk tingkatan tanpa celah nomor. Perhatikan juga bahwa ORDER BY jumlah DESC di dalam OVER terpisah dari ORDER BY nomor_baris di akhir query, yang hanya mengatur urutan tampil.

Total berjalan dengan SUM() OVER#

Total berjalan (running total) dihitung dengan menempelkan SUM pada jendela yang diurutkan, sehingga setiap baris memuat akumulasi sampai posisinya. Bentuk umumnya adalah SUM(jumlah) OVER (PARTITION BY cabang ORDER BY tanggal), dan dua komponen di dalam kurung itulah yang menentukan hasilnya. PARTITION BY memulai ulang perhitungan untuk setiap cabang, sedangkan ORDER BY menetapkan urutan akumulasinya.

python total_berjalan.py

import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("perpustakaan.duckdb")

tampilkan(con, """
SELECT cabang,
       tanggal,
       jumlah,
       SUM(jumlah) OVER (PARTITION BY cabang ORDER BY tanggal) AS total_berjalan
FROM peminjaman
ORDER BY cabang, tanggal
""")
con.close()

text Keluaran

cabang  tanggal     jumlah  total_berjalan
Pusat   2024-06-03  120     120
Pusat   2024-06-04  135     255
Pusat   2024-06-05  120     375
Pusat   2024-06-06  150     525
Pusat   2024-06-07  110     635
Pusat   2024-06-08  160     795
Pusat   2024-06-09  175     970
Timur   2024-06-03  90      90
Timur   2024-06-04  95      185
Timur   2024-06-05  90      275
Timur   2024-06-06  100     375
Timur   2024-06-07  85      460
Timur   2024-06-08  105     565
Timur   2024-06-09  110     675

Kolom total_berjalan pada cabang Pusat bergerak 120, 255, 375, dan seterusnya, lalu kembali ke 90 pada baris pertama cabang Timur karena partisi baru dimulai. Tanpa PARTITION BY, akumulasi akan menyeberang antarcabang dan angkanya menjadi tidak bermakna. Tanpa ORDER BY, SUM menghitung seluruh partisi sekaligus, sehingga setiap baris memuat total yang sama. Secara default, bingkai jendela (frame) untuk agregasi berjalan dari awal partisi sampai baris saat ini, dan batas itu dapat diubah bila perlu, misalnya untuk rata-rata beberapa hari terakhir.

Membandingkan baris dengan LAG dan LEAD#

LAG menunjuk nilai dari baris sebelumnya, sedangkan LEAD menunjuk nilai dari baris sesudahnya, sehingga perbandingan antarbaris tidak memerlukan join tabel dengan dirinya sendiri. Keduanya menerima offset opsional dan nilai pengganti bila posisinya berada di luar partisi. Skrip berikut menghitung perubahan harian peminjaman cabang Pusat dengan jendela bernama yang sama untuk dua fungsi.

python lag_lead.py

import duckdb


def tampilkan(con, sql):
    """Cetak hasil query sebagai tabel rapi berkolom sejajar."""
    hasil = con.execute(sql)
    kolom = [d[0] for d in hasil.description]
    baris = hasil.fetchall()
    lebar = []
    for i in range(len(kolom)):
        anggota = [len(kolom[i])] + [len(str(b[i])) for b in baris]
        lebar.append(max(anggota))
    fmt = "  ".join(f"{{:<{w}}}" for w in lebar)
    print(fmt.format(*kolom).rstrip())
    for b in baris:
        print(fmt.format(*["NULL" if v is None else str(v) for v in b]).rstrip())


con = duckdb.connect("perpustakaan.duckdb")

tampilkan(con, """
SELECT tanggal,
       jumlah,
       LAG(jumlah)  OVER w AS kemarin,
       jumlah - LAG(jumlah) OVER w AS selisih,
       LEAD(jumlah) OVER w AS besok
FROM peminjaman
WHERE cabang = 'Pusat'
WINDOW w AS (ORDER BY tanggal)
""")
con.close()

text Keluaran

tanggal     jumlah  kemarin  selisih  besok
2024-06-03  120     NULL     NULL     135
2024-06-04  135     120      15       120
2024-06-05  120     135      -15      150
2024-06-06  150     120      30       110
2024-06-07  110     150      -40      160
2024-06-08  160     110      50       175
2024-06-09  175     160      15       NULL

Baris pertama memuat NULL pada kolom kemarin dan selisih karena tidak ada baris di depannya, dan baris terakhir memuat NULL pada kolom besok. Kolom selisih lahir dari ekspresi jumlah - LAG(jumlah) OVER w, sehingga arah perubahan harian langsung terbaca tanpa perhitungan manual. Klausa WHERE cabang = 'Pusat' menyederhanakan contoh, sedangkan PARTITION BY tetap wajib apabila kedua cabang dihitung sekaligus agar baris terakhir Pusat tidak meminjam data pertama Timur.

Kesalahan umum#

Tiga kekeliruan berikut paling sering muncul ketika fungsi window mulai dipakai, beserta penyebab dan perbaikannya.

Lupa PARTITION BY pada data yang memiliki beberapa kelompok. Perhitungan menyeberang antarkelompok, misalnya total berjalan mencampur dua cabang. Tambahkan PARTITION BY sesuai kunci kelompoknya.
Membuang ORDER BY di dalam OVER untuk agregasi berjalan. Tanpa urutan, SUM menghitung seluruh partisi, sehingga setiap baris memuat angka yang sama. Tetapkan ORDER BY sesuai urutan akumulasi yang dimaksud.
Menyaring hasil fungsi window di dalam WHERE. WHERE dievaluasi sebelum jendela dihitung, sehingga kolom peringkatnya belum ada. Gunakan klausa QUALIFY yang disediakan DuckDB, atau bungkus query dalam subquery atau CTE.
Menganggap fungsi window mengurangi jumlah baris. Fungsi window menganotasi baris yang ada, sedangkan GROUP BY yang memadatkannya, sehingga keduanya tidak saling menggantikan. Pilih bentuknya sesuai bentuk keluaran yang diinginkan.

Ringkasan#

Fungsi window menganotasi setiap baris dengan hasil hitungan atas jendelanya, tanpa memadatkan baris seperti GROUP BY.
ROW_NUMBER, RANK, dan DENSE_RANK berbeda hanya pada penomoran nilai kembar, yaitu unik, melompat, dan padat.
SUM() OVER dengan PARTITION BY dan ORDER BY menghasilkan total berjalan yang dimulai ulang per kelompok.
LAG dan LEAD membandingkan baris secara berdampingan, dan batas partisinya menghasilkan NULL yang perlu diantisipasi.

Referensi#

Window Functions — DuckDB — Daftar fungsi window bawaan DuckDB beserta contoh pemakaiannya.
WINDOW — DuckDB — Sintaks OVER dan klausa WINDOW bernama pada DuckDB.
QUALIFY — DuckDB — Klausa untuk menyaring hasil fungsi window tanpa subquery tambahan.
Window Functions — PostgreSQL — Penjelasan konsep window function pada dokumentasi PostgreSQL.