Window Function

OVER, PARTITION BY, dan fungsi ranking untuk perbandingan antarbaris.

Daftar isi

Window function adalah fungsi yang menghitung nilai dari sekumpulan baris terkait yang disebut jendela, tetapi tetap menampilkan setiap baris pada hasil akhir, sehingga perbandingan antarbaris dapat ditulis dalam satu query. Topik ini membahas kerangka OVER tanpa dan dengan PARTITION BY, penomoran ROW_NUMBER, perbedaan RANK dengan DENSE_RANK pada nilai yang kembar, total berjalan dengan SUM dan ORDER BY dalam jendela, serta posisi window function dalam urutan evaluasi query. Seluruh contoh memakai tabel buku perpustakaan yang kecil sehingga setiap angka pada keluaran dapat dihitung ulang dengan mata.

Kerangka OVER#

Tanda OVER menyisipkan hasil agregat ke samping baris tanpa menciutkan baris menjadi satu, dan inilah pembeda window function dari GROUP BY yang menggabungkan grup menjadi satu baris per grup. OVER yang kosong menjadikan seluruh baris hasil sebagai satu jendela, sehingga nilai agregatnya sama untuk setiap baris. Fungsi yang dapat diberi kerangka ini mencakup agregat biasa seperti SUM dan COUNT, keluarga peringkat seperti ROW_NUMBER dan RANK, serta fungsi nilai seperti LAG dan LEAD. Query berikut menampilkan jumlah pinjam setiap buku berdampingan dengan total seluruh koleksi.

sql SUM dengan OVER kosong pada seluruh baris

CREATE TABLE buku (
  id INTEGER PRIMARY KEY,
  judul TEXT,
  kategori TEXT,
  tahun INTEGER,
  pinjam INTEGER
);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Teknologi', 2019, 7),
  (2, 'Peta Langit', 'Sains', 2021, 5),
  (3, 'Kode dan Sandi', 'Teknologi', 2019, 6),
  (4, 'Sejarah Kertas', 'Sejarah', 2018, 3),
  (5, 'Jejak Angin', 'Sains', 2021, 5);

SELECT judul, pinjam, SUM(pinjam) OVER () AS total_semua FROM buku ORDER BY id;

text Keluaran

Dasar Algoritma|7|26
Peta Langit|5|26
Kode dan Sandi|6|26
Sejarah Kertas|3|26
Jejak Angin|5|26

Kelima baris tetap tampil dengan identitasnya masing-masing, sedangkan kolom tambahan memuat angka 26 yang merupakan jumlah seluruh pinjam. Persentase kontribusi tiap buku dapat dihitung langsung dengan membagi pinjam terhadap kolom baru itu tanpa query kedua. Kalau GROUP BY yang dipakai, keluarannya hanya tiga baris grup dan informasi per buku hilang, sehingga pemilihan antara keduanya ditentukan oleh bentuk laporan yang diinginkan. Bentuk SUM(pinjam) OVER () sendiri setara dengan subquery skalar yang menghitung total, tetapi lebih pendek dan tidak mengulang tabelnya.

PARTITION BY membagi jendela#

PARTITION BY membagi baris menjadi beberapa jendela yang dihitung terpisah, dan setiap baris hanya melihat anggota jendelanya sendiri. Pembagian ini menggantikan pola join terhadap subquery yang menghitung total per grup, sehingga query menjadi lebih pendek dan niatnya lebih jelas. Kombinasi PARTITION BY dengan ORDER BY di dalam OVER juga menjadi dasar seluruh pola peringkat pada bagian berikutnya. Blok berikut menghitung total pinjam per kategori, lalu memberi nomor urut di dalam setiap kategori dengan ROW_NUMBER.

sql PARTITION BY untuk total kategori dan nomor urut

CREATE TABLE buku (
  id INTEGER PRIMARY KEY,
  judul TEXT,
  kategori TEXT,
  tahun INTEGER,
  pinjam INTEGER
);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Teknologi', 2019, 7),
  (2, 'Peta Langit', 'Sains', 2021, 5),
  (3, 'Kode dan Sandi', 'Teknologi', 2019, 6),
  (4, 'Sejarah Kertas', 'Sejarah', 2018, 3),
  (5, 'Jejak Angin', 'Sains', 2021, 5);

SELECT kategori, judul, pinjam,
       SUM(pinjam) OVER (PARTITION BY kategori) AS total_kategori
FROM buku ORDER BY kategori, id;
SELECT kategori, judul,
       ROW_NUMBER() OVER (PARTITION BY kategori ORDER BY pinjam DESC, id) AS urut
FROM buku ORDER BY kategori, urut;

text Keluaran

Sains|Peta Langit|5|10
Sains|Jejak Angin|5|10
Sejarah|Sejarah Kertas|3|3
Teknologi|Dasar Algoritma|7|13
Teknologi|Kode dan Sandi|6|13
Sains|Peta Langit|1
Sains|Jejak Angin|2
Sejarah|Sejarah Kertas|1
Teknologi|Dasar Algoritma|1
Teknologi|Kode dan Sandi|2

Query pertama menempelkan total kategori pada setiap barisnya, sehingga kategori Sains bernilai 10 yang merupakan gabungan dua bukunya, sedangkan Sejarah bernilai 3 karena anggotanya satu. Query kedua menomori baris di dalam jendela dengan urutan pinjam yang menurun, dan klausul ORDER BY di dalam OVER menentukan urutan penomoran itu. Banyaknya jendela mengikuti banyaknya nilai kategori yang muncul, dan baris yang berseorangan tetap membentuk jendela tersendiri dengan nomor urut 1. Tambahan , id pada urutannya memastikan baris kembar diberi nomor secara tetap, sehingga keluaran tidak berubah antar eksekusi.

RANK dan DENSE_RANK pada nilai kembar#

RANK dan DENSE_RANK memberi peringkat berdasarkan urutan yang dinyatakan, dan perbedaannya hanya terlihat pada nilai yang kembar. RANK memberi nomor yang sama untuk nilai kembar lalu melompati nomor berikutnya sebanyak anggota kembar tersebut, sedangkan DENSE_RANK memberi nomor yang sama tanpa melompat. Data contoh sengaja memuat dua buku dengan jumlah pinjam sama, sehingga kedua fungsi dapat dibandingkan pada keluaran yang sama. Dua fungsi lain dalam keluarga ini, yaitu PERCENT_RANK dan CUME_DIST, mengembalikan proporsi alih-alih nomor, tetapi kelompok kembar yang menentukan hasilnya tetap sama.

sql RANK berdampingan dengan DENSE_RANK

CREATE TABLE buku (
  id INTEGER PRIMARY KEY,
  judul TEXT,
  kategori TEXT,
  tahun INTEGER,
  pinjam INTEGER
);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Teknologi', 2019, 7),
  (2, 'Peta Langit', 'Sains', 2021, 5),
  (3, 'Kode dan Sandi', 'Teknologi', 2019, 6),
  (4, 'Sejarah Kertas', 'Sejarah', 2018, 3),
  (5, 'Jejak Angin', 'Sains', 2021, 5);

SELECT judul, kategori, pinjam,
       RANK() OVER (ORDER BY pinjam DESC) AS r,
       DENSE_RANK() OVER (ORDER BY pinjam DESC) AS dr
FROM buku ORDER BY r, id;

text Keluaran

Dasar Algoritma|Teknologi|7|1|1
Kode dan Sandi|Teknologi|6|2|2
Peta Langit|Sains|5|3|3
Jejak Angin|Sains|5|3|3
Sejarah Kertas|Sejarah|3|5|4

Dua buku dengan nilai 5 sama-sama mendapat peringkat 3 pada kedua fungsi, kemudian perbedaannya muncul pada Sejarah Kertas yang bernilai 3. RANK melewati angka 4 dan langsung memberi 5 karena dua tempat telah dipakai oleh pasangan kembar, sedangkan DENSE_RANK meneruskan ke 4 tanpa celah. Pilihan fungsinya mengikuti makna laporan, yaitu peringkat gaya lomba memakai RANK, sedangkan posisi pada daftar berjenjang memakai DENSE_RANK. Pada data tanpa nilai kembar, keduanya mengembalikan angka yang identik sehingga pilihannya terasa tak berpengaruh.

Total berjalan dengan ORDER BY dalam jendela#

ORDER BY di dalam OVER mengubah jendela berjalan, yaitu jendela yang berisi baris-baris dari awal hingga baris sekarang menurut urutan tersebut. Bentuk inilah yang menghasilkan total berjalan, atau running total, yang sering diminta pada laporan berkala. Akumulasi pada data contoh mencapai 18 pada baris ketiga, dan nilai semacam itu berguna sebagai penanda tengah koleksi. Query berikut menjumlahkan pinjam secara kumulatif mengikuti urutan id, dan karena id bersifat unik, setiap baris menambah satu nilai baru.

sql Total berjalan mengikuti urutan id

CREATE TABLE buku (
  id INTEGER PRIMARY KEY,
  judul TEXT,
  kategori TEXT,
  tahun INTEGER,
  pinjam INTEGER
);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Teknologi', 2019, 7),
  (2, 'Peta Langit', 'Sains', 2021, 5),
  (3, 'Kode dan Sandi', 'Teknologi', 2019, 6),
  (4, 'Sejarah Kertas', 'Sejarah', 2018, 3),
  (5, 'Jejak Angin', 'Sains', 2021, 5);

SELECT judul, pinjam, SUM(pinjam) OVER (ORDER BY id) AS total_berjalan
FROM buku ORDER BY id;

text Keluaran

Dasar Algoritma|7|7
Peta Langit|5|12
Kode dan Sandi|6|18
Sejarah Kertas|3|21
Jejak Angin|5|26

Kolom total berjalan dimulai dari 7, lalu bertambah dengan nilai setiap baris hingga mencapai 26 pada baris terakhir. Pola ini lazim pada laporan arus kas dan rekonsiliasi stok, yaitu laporan yang menuntut posisi akumulatif di samping perubahan per baris. Apabila kolom pengurutan memuat nilai kembar, kerangka bawaan memasukkan baris kembar itu sebagai satu kelompok sehingga totalnya sama, dan perilaku ini berasal dari kerangka RANGE yang menjadi bawaan SQLite. Penomoran dan total berjalan semacam ini menempatkan urutan eksekusi sebagai hal yang wajib dipahami, karena jendela tidak bekerja pada data mentah.

Posisi window function dalam evaluasi query#

SQLite menghitung window function setelah WHERE dan GROUP BY selesai, sehingga jendela hanya melihat baris yang lolos penyaringan dan bukan seluruh tabel. Urutan itu berdampak dua arah, yaitu total jendela berubah ketika WHERE membuang baris, sekaligus window function tidak boleh dipakai di dalam WHERE sendiri karena penyaringan berlangsung lebih dahulu. Blok berikut memperlihatkan kedua sisi urutan itu pada dataset yang sama.

sql Jendela setelah penyaringan dan penolakan di WHERE

CREATE TABLE buku (
  id INTEGER PRIMARY KEY,
  judul TEXT,
  kategori TEXT,
  tahun INTEGER,
  pinjam INTEGER
);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Teknologi', 2019, 7),
  (2, 'Peta Langit', 'Sains', 2021, 5),
  (3, 'Kode dan Sandi', 'Teknologi', 2019, 6),
  (4, 'Sejarah Kertas', 'Sejarah', 2018, 3),
  (5, 'Jejak Angin', 'Sains', 2021, 5);

SELECT judul, pinjam, SUM(pinjam) OVER () AS total_setelah_saring
FROM buku WHERE kategori = 'Sains';

SELECT judul FROM buku WHERE ROW_NUMBER() OVER (ORDER BY id) = 1;

text Keluaran

Peta Langit|5|10
Jejak Angin|5|10
Parse error near line 18: misuse of window function ROW_NUMBER()
  SELECT judul FROM buku WHERE ROW_NUMBER() OVER (ORDER BY id) = 1;
                 error here ---^

Query pertama menghasilkan total 10 alih-alih 26 karena WHERE telah membuang tiga baris sebelum jendela dihitung, dan angka ini menegaskan urutan evaluasinya. Query kedua ditolak dengan pesan misuse of window function ROW_NUMBER() karena penyaringan WHERE tidak boleh menunggu fungsi yang baru dihitung setelahnya. Solusi yang lazim adalah membungkus window function dalam subquery atau CTE, kemudian menyaring hasilnya pada query luar, misalnya mengambil baris dengan urut = 1 dari query nomor urut pada bagian PARTITION BY. Aturan yang sama berlaku pada HAVING, sehingga penyaringan pascaagregasi juga tidak dapat menyentuh nilai jendela.

Kesalahan umum#

Mengira SUM(pinjam) OVER () menggantikan GROUP BY. Keduanya menjawab pertanyaan yang berbeda, karena GROUP BY menciutkan baris menjadi satu baris per grup sedangkan OVER menempelkan agregat pada setiap baris. Periksa bentuk laporan yang diminta sebelum memilih, lalu ingat bahwa jendela membutuhkan hasil GROUP BY sebagai bahan masukannya apabila keduanya dipakai bersama.

Memakai window function di dalam WHERE lalu terkejut oleh pesan misuse of window function. Penyebabnya adalah urutan evaluasi, karena WHERE selesai lebih dahulu sebelum jendela dihitung. Pindahkan penyaringan ke query luar melalui subquery atau CTE yang membungkus nomor urut atau peringkatnya.

Melupakan ORDER BY di dalam OVER untuk fungsi ranking. Tanpa urutan yang dinyatakan, penomoran tidak memiliki makna yang tetap, sehingga hasilnya sulit dijelaskan. Deklarasikan ORDER BY beserta kolom pengikat seperti id agar baris kembar selalu menerima nomor yang sama pada setiap eksekusi. Ujilah pula dengan data yang memuat nilai kembar agar perbedaan RANK dan DENSE_RANK terlihat sejak awal.

Ringkasan#

OVER menempelkan hasil agregat pada setiap baris tanpa menciutkannya, sedangkan PARTITION BY membagi baris menjadi jendela-jendela yang dihitung terpisah.
ROW_NUMBER menomori baris dengan ORDER BY di dalam jendela, sedangkan RANK melompati nomor setelah nilai kembar dan DENSE_RANK meneruskannya tanpa celah.
SUM dengan ORDER BY di dalam OVER menghasilkan total berjalan, dan kerangka bawaannya memperlakukan nilai kembar sebagai satu kelompok.
Window function dihitung setelah WHERE dan GROUP BY, sehingga jendela hanya melihat baris yang lolos dan tidak boleh dirujuk dari WHERE sendiri.

Referensi#

Window Functions — SQLite — Dokumentasi resmi OVER, PARTITION BY, fungsi ranking, dan kerangka jendela SQLite.
SQLite Query Language — SQLite — Indeks pernyataan SQL SQLite yang memuat aturan pemakaian window function pada SELECT.