Window Function: Position Awareness Query di PostgreSQL

posted Jul 6, 2014, 6:19 AM by Feris Thia [ updated Jul 7, 2014, 7:39 AM ]

Contents

1 Pendahuluan
2 Window Functions dan Contoh Penggunaannya
3 Penjelasan Query
4 Penutup
5 Sumber Referensi

Pendahuluan

Analisa data selalu dilakukan dengan membandingkan dua atau lebih data. Contohnya perbandingan data penjualan dua tahun terakhir untuk melihat pertumbuhan, membandingkan data penjualan bulan terkini dengan kontribusi penjualan secara keseluruhan, dan sebagainya.

Pada berbagai kesempatan, telah saya jelaskan kalau kemampuan ini sangat kurang di SQL yang tidak sensitif terhadap posisi data. Analisa data menuntut suatu syarat agar dapat melakukan navigasi data dengan mudah. Ini berujung pada terciptanya konsep cube dan bahasa query MDX (Multi Dimensional Expression).

Namun ternyata dari informasi yang saya dapatkan di milis id-python, di PostgreSQL ada fungsi SQL khusus yang dapat membantu kita untuk melakukan navigasi data, yang dalam beberapa hal mirip dengan MDX.

Window Functions dan Contoh Penggunaannya

Position awareness function ini disebut dengan Window Functions pada PostgreSQL, dan rincian penjelasannya dapat Anda baca pada halaman situs http://www.postgresql.org/docs/8.4/static/functions-window.html.

Function-function ini disebut windows atau jendela karena berfokus pada suatu partisi data, misalkan partisi data terhadap kelompok data berdasarkan kolom "bulan" dan "tahun".

Dan setiap window didefinisikan dengan klausa over. Ada beberapa syntax, tapi salah satu yang akan digunakan adalah dengan konstruksi berikut:

NAMA_FUNGSI(ekspresi) OVER ( ORDER BY ekspresi [ASC | DESC| USING operator] )

Artikel ini akan fokus ke dua contoh fungsi ini, yaitu lag dan penggunaan sum dari partisi / window. Cobalah perhatikan contoh data sederhana yang akan kita gunakan berikut ini. Script untuk table ini dapat Anda download di bagian akhir artikel ini.

Kita ingin mendapatkan hasil akhir sebagai berikut:

Fungsi lag yang akan kita gunakan akan menggunakan syntax berikut:

LAG(field, [, jarak / offsset [, default any ]]) OVER ( ORDER BY ekspresi [ASC | DESC| USING operator] )

Dan query lengkapnya untuk menghasilkan output di atas adalah sebagai berikut:

SELECT
tahun,
bulan,
nilai_penjualan,
LAG(nilai_penjualan, 1) OVER (ORDER BY tahun, bulan) AS "penjualan_sebelumnya",
nilai_penjualan - (COALESCE(LAG(nilai_penjualan, 1) OVER (ORDER BY tahun, bulan), 0)) AS "pertumbuhan"
FROM summary_penjualan;

Penjelasan Query

Function LAG lengkap yang kita gunakan adalah sebagai berikut:

LAG(nilai_penjualan, 1) OVER (ORDER BY tahun, bulan) AS "penjualan_sebelumnya"
Function akan mengambil data nilai_penjualan dari 1 window dari konstruksi LAG(nilai_penjualan,1).
Kemudian window yang akan digunakan adalah bulan sebelumnya, ditunjukkan dengan konstruksi OVER (ORDER BY tahun, bulan).
Selesai

Penutup

Demikian artikel singkat ini saya buat, semoga bermanfaat. Pada kesempatan lain, akan penulis coba buat lagi berbagai case analytic lain seperti running total atau moving average. Stay tuned!