Day 32: SQL Untuk Analisa Data


Learning In HomeOriginal Date: 13 Februari 2009

Sebagai lanjutan dari pelajaran kemarin, hari ini aku akan mempelajari beberapa perluasan SQL milik Oracle Database yang berfungsi untuk analisa data. Aku akan mulai dengan RANK() dan DENSE_RANK(). Kedua fungsi tersebut akan mengembalikan nilai peringkat mulai dari 1, 2, dan seterusnya. Perbedaannya terletak pada penanganan dua atau lebih record yang memiliki peringkat yang sama. Misalnya, ada 3 record yang memiliki peringkat yang sama, yaitu peringkat 1. Jika menggunakan RANK(), record berikut-nya yang bernilai berbeda akan memiliki peringkat 4. Jika menggunakan DENSE_RANK(), record berikut-nya yang bernilai berbeda akan memiliki peringkat 2. Contoh penggunakan DENSE_RANK(), misalnya, aku ingin mengetahui produk apa yang nilai penjualannya terbesar dan memberikan peringkat terhadap masing-masing produk:


SELECT
s.prod_id, p.prod_name,
SUM(s.amount_sold),
DENSE_RANK() OVER (ORDER BY SUM(s.amount_sold) DESC)
FROM
sales s, products p
WHERE
s.prod_id = p.prod_id
GROUP BY
s.prod_id, p.prod_name;

Fungsi CUME_DIST() dapat dipakai untuk memberikan nilai yang relatif terhadap kumpulan nilai (bisa berupa seluruh kolom, atau berdasarkan partisi yang ditentukan). Nilai yang dikembalikan antara nilai lebih besar dari 0 (nol) hingga mencapai 1 (satu). Aku dapat menggunakan fungsi ini untuk mendapatkan nilai distribusi kumulatif, seperti pada contoh:


SELECT
c.cust_state_province,
SUM(s.quantity_sold),
CUME_DIST() OVER (ORDER BY SUM(s.quantity_sold)) as "Distribusi"
FROM
sales s, customers c
WHERE
s.cust_id = c.cust_id
GROUP BY
c.cust_state_province;

Fungsi NTILE() dapat dipakai untuk membagi tabel ke dalam sejumlah bagian yang ditentukan dan menghasilkan nilai mulai dari 1 hingga N (jumlah yang ditentukan) untuk masing-masing baris. Misalnya, aku ingin membagi tbl_pembelian ke dalam 5 kelompok, aku dapat menggunakan query berikut:


SELECT TANGGAL, JUMLAH, NTILE(5) OVER (ORDER BY TANGGAL ASC) FROM tbl_pembelian;

Dan database Oracle akan mengembalikan nilai seperti berikut untuk tabel dengan 15 baris:


---------------------------------
  TANGGAL  JUMLAH     NTILE(5)
---------------------------------
    x         x        1
    x         x        1
    ...       ...      1
                       2
                       2
                       2
                       3 
                       3
                       3
                       4
                       4
                       4
   ...        ...      5
   x          x        5
---------------------------------

Aku dapat menggunakan windowing function untuk menghitung nilai aggregate secara kumulatif. Misalnya, aku ingin menghitung nilai SUM untuk penjualan secara kumulatif mulai dari bulan Januari hingga bulan Desember dengan menggunakan SQL berikut:


SELECT
t.calendar_month_number,
SUM(s.amount_sold),
SUM(SUM(s.amount_sold)) OVER
(ORDER BY t.calendar_month_number ROWS UNBOUNDED PRECEDING) AS "Total Kumulatif"
FROM
sales s, times t
WHERE
s.time_id = t.time_id
GROUP BY
t.calendar_month_number;

Beberapa variasi untuk windowing function, misalnya moving berdasarkan selang 2 record sebelumnya:


AVG(x) OVER (ORDER BY x ROWS 2 PRECEDING)

atau centered pada nilai saat ini (row aktif), misalnya menghitung jumlah dari 2 baris sebelum hingga 2 baris sesudah untuk masing-masing baris:


SUM(x) OVER (ORDER BY x RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING)

Query SQL analisa lain yang cukup berguna adalah perluasan terhadap fungsi aggregate seperti SUM, AVG, COUNT(*), dsb sehingga mendukung OVER (PARTITION BY). Sebagai contoh, aku memiliki tabel TBL_PEMBELIAN, dan TBL_BARANG. Aku ingin mendapatkan laporan total pembelian per tanggal, dimana total tersebut didapatkan dari SUM field jumlah yang ada di TBL_PEMBELIAN dikali dengan field harga yang ada di TBL_BARANG. Perluasan OVER (PARTITION BY) untuk fungsi agregate SUM() mempermudah aku dalam membuat query-nya:


SELECT DISTINCT "Tanggal", "Total Pembelian" FROM
(SELECT
pb.tanggal AS "Tanggal",
brg.nama AS "Nama Barang",
brg.harga,
SUM(pb.jumlah),
SUM(SUM(pb.jumlah) * brg.harga) OVER (PARTITION BY pb.tanggal) AS "Total Pembelian"
FROM
tbl_pembelian pb, tbl_barang brg
WHERE
pb.barang_id = brg.id
GROUP BY
tanggal, brg.nama, brg.harga);

Untuk mendapatkan nilai ratio untuk suatu ekspresi, aku dapat menggunakan fungsi RATIO_TO_REPORT. Sebagai contoh, aku ingin mendapatkan nilai persentase jumlah penjualan berdasarkan bulan:


SELECT
t.calendar_month_number,
sum(s.quantity_sold),
TO_CHAR(RATIO_TO_REPORT(SUM(s.quantity_sold)) OVER() * 100, '99.99') || ' %' AS "Persentase"
FROM
sales s, times t
WHERE
s.time_id = t.time_id
GROUP BY
t.calendar_month_number;

Fungsi LAG dan LEAD dapat dipakai untuk menggeser baris dari sebuah kolom berdasarkan nilai offset yang ditentukan. LAG akan menggeser sejumlah N offset ke bawah, sementara LEAD akan menggser sejumlah N offset ke atas.

Perihal Solid Snake
I'm nothing...

Apa komentar Anda?

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: