Day 33: SQL Untuk Modeling

Learning In Home-Original Date: 14 Februari 2009

Hari Valentine akhirnya tiba juga. Nuansa cinta terlihat dimana-mana, gadis manis tetangga yang sering menatapku (?) juga terlihat tambah menarik. Komunikasi tanpa kata-kata (dan tanpa protocol tentunya) diantara kami memang sulit dipahami, tapi itu bukanlah topik tulisan hari ini. Pagi ini, aku akan kembali mempelajari menggunakan SQL. Kali ini dalam kaitannya dengan MODEL yang memungkinkan manipulasi isi tabel sampai ke tahap cell. Sebagai contoh, misalnya, aku membuat sebuah tabel sederhana yang mirip lembaran kerja di Excel:




CREATE TABLE tbl_latihan_model (
  A VARCHAR2(10),
  B VARCHAR2(10),
  C VARCHAR2(10),
  D NUMBER(10)
);

INSERT INTO tbl_latihan_model 
  VALUES ('A1', 'B1', 'C1', 10);
INSERT INTO tbl_latihan_model 
  VALUES ('A2', 'B2', 'C2', 20);
INSERT INTO tbl_latihan_model 
  VALUES ('A3', 'B3', 'C3', 30);
INSERT INTO tbl_latihan_model 
  VALUES ('A4', 'B4', 'C4', 40);
INSERT INTO tbl_latihan_model 
  VALUES ('A5', 'B5', 'C5', 50);
INSERT INTO tbl_latihan_model 
  VALUES ('A6', 'B6', 'C6', 60);

Lalu, aku ingin mengubah nilai kolom D sesuai keinginan dengan MODEL:


SELECT
*
FROM
tbl_latihan_model
MODEL
DIMENSION BY (A, B, C)
MEASURES (D)
RULES UPDATE (
D[A='A1', B='B1', C='C1'] = 999,
D[A='A4', B='B4', C='C4'] = D[A='A1', B='B1', C='C1'] + 1
)
ORDER BY A
;


Hasil akhirnya akan mirip seperti ini:




-------------------
 A   B   C   D
-------------------
 A1  B1  C1   999
 A2  B2  C2    20
 A3  B3  C3    30
 A4  B4  C4  1000
 A5  B5  C5    50
 A6  B6  C6    60
-------------------

Aku telah mengubah nilai kolom D di baris 1 menjadi 999. Bukan hanya itu, aku juga mengubah nilai kolom D di baris 4 menjadi nilai kolom D di baris pertama ditambah dengan 1. Semuanya itu dalam sekali query. Dan bahkan bisa lebih lagi, dengan menambah statement baru di bagian klausa RULES.

Contoh yang lebih berguna, misalnya, aku akan menghitung dan menampilkan diskon untuk produk berdasarkan rule yang telah aku tentukan:




SELECT 
  tanggal,
  kategori_id,
  sum_jumlah,
  diskon
FROM 
  (SELECT
      tanggal, 
      brg.id AS brg_id, 
      SUM(pb.jumlah) AS sum_jumlah
   FROM
      tbl_pembelian pb, tbl_barang brg
   WHERE
      pb.barang_id = brg.id
   GROUP BY
      tanggal, brg.id
  ) pb,
  tbl_kategori kat, 
  tbl_barang brg
WHERE
  pb.brg_id = brg.id AND
  brg.kategori = kat.id 
MODEL  
  PARTITION BY (tanggal)
  DIMENSION BY (
    brg_id, 
    kat.id as kategori_id, 
    sum_jumlah)
  MEASURES (0 as diskon)
  RULES UPDATE (
     diskon[any, 
            any, 
            sum_jumlah > 10] = 0.01,
     diskon[any, 
            kategori_id = 3, 
            any] = 
      diskon[cv(brg_id), 
             cv(kategori_id), 
             cv(sum_jumlah)] +
        cv(sum_jumlah) * 0.1
  )
ORDER BY
  tanggal
;  

Pada contoh tersebut, aku memberikan dua rule, yaitu diskon sebesar 0.01 untuk jika atribut sum_jumlah lebih dari 10. Selain itu, jika kategori_id adalah 3, maka aku akan menambahkan diskon sebesar 0.01 dari sum_jumlah saat ini. Aku menggunakan fungsi cv() untuk mendapatkan nilai saat cell saat ini (cell yang sedang diproses). Contoh hasil query-nya, misalnya:




---------------------------------
 TANGGAL KATEGORI SUM     DISKON
         ID       JUMLAH 
---------------------------------
 28       5         2        0
 28       3        13     1,31
 30      10        15     0,01
 30       4         4        0
 31       3         2      0,2
---------------------------------

Sekarang sudah saatnya untuk beristirahat sejenak, sebab tadi aku melihat the girl next door sudah pulang. Rambutnya yang panjang dibiarkan terurai rapi.. Ini pasti pertanda yang baik di hari Valentine. Aku jadi semakin betah disini.

Day 32: SQL Untuk Analisa Data

Learning In Home-Original 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.