Mengenal Window Function di SQL

Ini adalah sebuah catatan sederhana mengenai konsep Window Function di relational database menggunakan bahasa SQL (tentu saja). Window function sebenernya mirip dengan konsep GROUP BY di SQL, yaitu mengelompokan data sesuai kolom yang ditentukan, dengan sedikit perbedaan yaitu, tanpa melakukan penggabungan baris.

Misal seorang guru punya sebuah table ujian yang berisi data nilai tiap siswa dan jenis pelajaran yang diikutinya.

Kita menggunakan GROUP BY untuk melakukan agregasi data (sum, max, avg, dll). Jika sang guru ingin menghitung nilai rata-rata ujian yang diambil siswa tiap mata pelajaran. Maka kita akan menggunakan fungsi AVG() yang dikelompokan oleh (GROUP BY) pelajaran.

SELECT pelajaran, AVG(nilai) avg_nilai
FROM ujian 
GROUP BY pelajaran
;

Kemudian sang guru ingin mengetahui siapa saja siswa yang tidak lulus pada tiap pelajarannya, yaitu siswa yang nilainya kurang dari 20 dari nilai rata-rata kelas. Jadi kita harus menghitung selisih nilainya dengan nilai rata-rata kelas (nilai – avg_nilai). Kita tidak bisa hanya menggunakan GROUP BY, karena akan gabung menjadi satu baris seperti contoh di atas.

SELECT a.pelajaran, a.siswa, a.nilai
    , b.avg_nilai, (a.nilai - b.avg_nilai) selisih
FROM ujian a
INNER JOIN (
	SELECT pelajaran, AVG(nilai) avg_nilai
	FROM ujian 
	GROUP BY pelajaran
) b
ON a.pelajaran = b.pelajaran
;

Dengan menggunakan query di atas diketahui bahwa Jack tidak lulus pelajaran Aljabar dan Bryan tidak lulus Kalkulus karena selisih masing-masing kurang dari -20. Hasil di atas bisa kita sederhanakan (tanpa self join) dengan menggunakan Window Function, seperti berikut:

SELECT pelajaran, siswa, nilai, avg_nilai
    , (nilai - avg_nilai) selisih
FROM (
	SELECT pelajaran, siswa, nilai
            , AVG(nilai) OVER (PARTITION BY pelajaran) avg_nilai
	FROM ujian
)
;

Hasil dari query di atas sama persis seperti pada saat menggunakan INNER JOIN dan GROUP BY pada query sebelumnya. Dari situ dapat diketahui struktur yang membentuk Window Function, sebagai berikut:

• Fungsi SQL (sum, max, avg, dll)

• OVER, klausa ini memberitau SQL bahwa ini adalah sebuah Window Function.
• PARTITION BY, mengelompokan sesuai kolom yang ditentukan, tapi tidak menggabungkan barisnya.
• ORDER BY, untuk mengurutkan sesuai kolom yang ditentukan. Klausa ini tidak dibutuhkan pada kasus di atas, akan kita lihat pada contoh berikutnya.

Terakhir, sang guru ingin memberikan peringkat siswa berdasarkan nilai yang diperoleh pada setiap mata pelajaran yang diambil.

SELECT pelajaran, siswa, nilai
	, rank() OVER (PARTITION BY pelajaran ORDER BY nilai DESC) peringkat
	FROM ujian
;

Jendela.