Oracle SQL: Memilih Record Secara Acak Dari Tabel

Ada saatnya kita diharuskan untuk memilih satu baris dari sekian banyak baris yang ada di tabel. Untuk itu, kita dapat menggunakan SQL seperti berikut di database Oracle:

SELECT nama_kolom FROM
( SELECT nama_kolom FROM nama_tabel
ORDER BY DBMS_RANDOM.VALUE()) WHERE ROWNUM = 1;

Jika menginginkan lebih dari 1 baris yang acak, kita dapat mengganti ROWNUM = 1, menjadi ROWNUM <= n, dimana n adalah jumlah baris record yang kita inginkan.

Bagaimana Cara Kerja Query Di Atas?

Pada umumnya kita menggunakan nama field pada bagian ORDER BY untuk menentukan pengurutan berdasarkan field apa. Selain itu, kita juga bisa menggunakan angka (bilangan bulat) untuk menunjukkan posisi kolom yang akan dijadikan basis pengurutan.

Sebagai contoh, SQL ini akan mengurutkan berdasarkan kolom kedua:

SELECT kolom1, kolom2 FROM nama_tabel ORDER BY 2;

Lalu, bagaimana dengan ORDER BY DBMS_RANDOM.VALUE()? Fungsi tersebut akan mengembalikan angka pecahan di antara angka 0 dan 1. Tentu saja ini tidak berarti akan mengurutkan di-“antara” kolom pertama?

Selain mengharapkan nama kolom dan angka bilangan bulat, klausa ORDER BY juga menerima ekspresi, seperti hasil kembalian sebuah function. Pengurutan akan dilakukan berdasarkan nilai ekspresi ini untuk setiap baris yang ada. Oleh sebab itu, query pengurutan yang kita berikan pada dasarnya adalah penyederhanaan dari query berikut ini:

SELECT nama_kolom FROM
( SELECT DBMS_RANDOM.VALUE() AS RANDOM, nama_kolom FROM nama_tabel
ORDER BY RANDOM) WHERE ROWNUM = 1;
Iklan

Sun Oracle Exadata Storage Server: Butuh Storage Kencang Untuk Oracle?

Belakangan ini aku kebetulan sering berhubungan dengan database Oracle di live system.  Aku beberapa kali  mengalami query yang prosesnya sangat lama sekali… sampai-sampai aku khawatir akan menganggu proses transaksi dari user dan segera aku batalkan.  Aku menginginkan database yang di-tuning dengan baik, database yang responsif dan dapat menangani banyak permintaan.

Jika budget bukan masalah, salah satu solusi yang dapat dipakai adalah Sun Oracle Exadata Storage.  Tidak seperti storage server biasa, Sun Oracle Exadata Storage menyediakan fitur khusus untuk meningkatkan kinerja database Oracle.  O ya, storage ini juga dipakai oleh Sun Oracle Database Machine, sebuah server khusus yang dilengkapi dengan database Oracle yang telah ter-install dan ter-konfigurasi.  Ini adalah salah satu ‘hasil’ merge Oracle dan Sun: Software From Oracle, Hardware From Sun.

Salah satu fitur Sun Oracle Exadata Storage adalah ia dapat melakukan SQL processing dan hanya mengembalikan data yang dibutuhkan saja.  Ini berarti akan ada penghematan bandwidth dibanding storage biasa.  Dan juga penghematan CPU, karena CPU dari server database tidak perlu bekerja melakukan SQL processing lagi.  Fitur ini dapat terwujud berkat CELLSRV (Cell Services), software multi-thread yang berjalan di storage server untuk berkomunikasi dengan server database.

Sun Oracle Exadata Storage juga dilengkapi dengan 384 GB Exadata Smart Flash Cache.  Ini adalah solid state storage yang berfungsi sebagai cache untuk transaksi yang sering dilakukan.  Kelebihannya adalah Exadata Smart Flash Cache ini akan bekerja sama dengan database Oracle secara otomatis.  Jika ada tabel atau index tertentu yang dianggap sangat mempengaruhi kinerja, ia akan ditaruh ke dalam cache secara otomatis.

Oracle Stored Procedure: Membaca Dan Menulis Ke File Excel

Seseorang pernah bertanya bagimana membaca dan menulis file Excel melalui API PL/SQL (tanpa memakai Oracle Form, dsb). Tentu saja yang pertama kali terbayang dalam pikiranku adalah googling dulu. Aku menemukan beberapa alternatif solusi, seperti menulis dan membaca dalam format CSV untuk di-export dan di-import ke file XLS. Tapi tidak ada cara gampang untuk membaca format native Excel. Bagaimana jika si-penanya bersikeras untuk tetap ingin database-nya bisa membaca file Excel native melalui stored procedure? Stored procedure dalam Oracle tidak harus selalu berupa PL/SQL, kenapa tidak mencoba Java? Apache POI adalah salah satu API open source berbasis Java untuk membaca dan menulis file native Excel & Word. Aku akan mencoba mensimulasikan apa yang ingin dicapai oleh si-penanya melalui langkah-langkah sederhana berikut.

Pertama-tama, aku men-download JAR Apache POI dan memasukkannya ke dalam database Oracle agar dapat di-referensi oleh stored procedure Java nantinya. Perintah yang aku berikan kira-kira seperti berikut:

loadjava -u snake/password poi-3.2-FINAL-20081019.jar

Lalu, aku membuat program Java yang nantinya akan dijalankan oleh database Oracle, seperti berikut:

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


public class ExcelFile {

  public static String read(String dataFile) {
    try {
      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(new File(dataFile)));
      HSSFWorkbook wb = new HSSFWorkbook(fs);
      HSSFSheet sheet = wb.getSheetAt(0);
      HSSFRow row = sheet.getRow(1);
      String str = row.getCell(0).getRichStringCellValue().getString();
      return str;
    } catch (Exception e) {
      return e.getMessage();
    }
  }

}

Karena sedang coding dengan terburu-buru, aku membuat method read() dengan argumen berupa file Excel yang ingin dibaca. Tentu saja, pada kenyataannya, akan lebih baik jika ini berupa data BLOB (jika file Excel berada pada host yang berbeda) atau BFILE (jika file Excel berada pada host yang sama). Fungsi sederhana ini akan mengembalikan nilai String dari baris kedua dan kolom pertama. Setelah men-compile file ini, aku memasukkannya ke dalam database Oracle dengan perintah seperti berikut di command prompt:

loadjava -u snake/password ExcelFile.class

Kemudian, aku membuat PL/SQL wrapper untuk class Java, seperti berikut:

CREATE  OR REPLACE FUNCTION bacaExcel (namaFile IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'ExcelFile.read(java.lang.String) return java.lang.String';

Selanjutnya, masih dalam keadaan terburu-buru, aku harus memberikan permission agar stored procedure Java di schema SNAKE boleh membaca dan menulis file di direktori yang aku tentukan. Agar tidak rumit, aku login sebagai user yang memiliki akses administrasi Java, misalnya SYS, dan memberikan wewenang agar program Java di schema SNAKE boleh membaca dan menulis seluruh file:

call dbms_java.grant_permission('SNAKE', 'java.io.FilePermission', '<<ALL FILES>>', 'read,write' );

Sekarang, aku siap menggunakan stored procedure untuk membaca file Excel tersebut, misalnya seperti berikut:

SELECT bacaExcel('C:\sample.xls') FROM DUAL;

yang akan menghasilkan nilai dari cell A2 di sheet pertama di file C:\sample.xls. Proses pembuatan stored procedure menjadi cepat dan sederhana berkat Java yang didukung oleh komunitas open-source sehingga memiliki banyak API untuk berbagai kebutuhan 🙂 Jadi tidak harus selalu PL/SQL, bukan?

Day 36: Hierarchical Queries

Learning In HomeOriginal Date: 18 Februari 2009

Sebagai latihan terakhir di bulan ini yang berkaitan dengan database Oracle, aku akan mencoba memakai hierarchical queries. Sebagai contoh, aku memiliki tabel tbl_kategori dengan struktur sebagai berikut:

CREATE TABLE TBL_KATEGORI ("ID" NUMBER(5,0), "NAMA" VARCHAR2(100 BYTE), "SUPER_KATEGORI" NUMBER(5,0));

Pada tabel ini, setiap kategori dapat memiliki sub-kategori dengan tingkatan yang tidak dibatasi. Untuk menghasilkan query yang secara otomatis mengurutkan berdasarkan hierarki, aku menggunakan perintah:


SELECT * FROM tbl_kategori START WITH super_kategori IS NULL CONNECT BY PRIOR id = super_kategori;

Pada bagian START WITH, aku memberikan kondisi super_kategori IS NULL, yang menunjukkan bahwa yang menjadi root (kategori paling atas) adalah kategori dengan nilai field super_kategori berupa NULL.

Pada bagian CONNECT BY, aku memberikan kondisi PRIOR id = super_kategori yang menunjukkan bagaimana setiap kategori saling berhubungan membentuk hierarki.

Salah satu pseudocolumn yang tersedia saat memakai hierarcial query adalah LEVEL. Sebagai contoh, aku bisa hanya men-select kategori yang berada pada level 2 saja (tepat dibawah root):


SELECT
*
FROM
tbl_kategori
WHERE
LEVEL = 2
START WITH
super_kategori IS NULL
CONNECT BY
PRIOR id = super_kategori;

Pseudocolumn lain adalah CONNECT_BY_ISLEAF yang akan bernilai 1 jika baris tersebut tidak memiliki hierarki di bawahnya lagi. Sebagai contoh, aku dapat men-select semua kategori yang tidak memiliki sub-kategori lagi, dengan perintah:


SELECT
*
FROM
tbl_kategori
WHERE
connect_by_isleaf = 1
START WITH
super_kategori IS NULL
CONNECT BY
PRIOR id = super_kategori;

Day 36: Oracle Spatial

Learning In HomeOriginal Date: 17 Februari 2009

Untuk menangani data spatial seperti yang dipergunakan dalam GIS, Oracle menyediakan fitur Oracle Spatial. Contoh penggunaannya, misalnya, untuk mengelola data pemasaran dalam bentuk lokasi geografis. Data spatial disimpan dalam sebuah kolom yang bertipe SDO_GEOMETRY yang didefinisikan sebagai:


CREATE TYPE sdo_geometry AS 
  OBJECT (
 SDO_GTYPE NUMBER, 
 SDO_SRID NUMBER,
 SDO_POINT 
   SDO_POINT_TYPE,
 SDO_ELEM_INFO 
   SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES 
   SDO_ORDINATE_ARRAY
);

Object tersebut memakai SDO_POINT_TYPE, SDO_ELEM_INFO_ARRAY, dan SDO_ORDINATE_ARRAY yang didefinisikan sebagai:


CREATE TYPE sdo_point_type 
  AS OBJECT (
   X NUMBER,
   Y NUMBER,
   Z NUMBER);
CREATE TYPE sdo_elem_info_array 
  AS VARRAY (1048576) of NUMBER;
CREATE TYPE sdo_ordinate_array 
  AS VARRAY (1048576) of NUMBER;

Nilai SDO_GTYPE pada SDO_GEOMETRY adalah 4 digit angka yang menentukan jenis geometri, dalam format dltt. Nilai d menunjukkan jumlah dimensi (2, 3, atau 4). Nilai l menunjukkan dimensi mana yang mengandung nilai yang akan diukur. Nilai tt menunjukkan jenis geometri, dengan nilai antara lain: 00 (UNKNOWN), 01 (POINT), 02 (LINE atau CURVE), 03 (POLYGON), 04 (COLLECTION), 05 (MULTIPOINT), 06 (MULTILINE atau MULTICURVE), dan 07 (MULTIPOLYGON).

Nilai SDO_SRID pada SDO_GEOMETRY menunjukkan jenis koordinat yang dipergunakan. Jika nilai ini NULL, maka tidak ada koordinat yang dipakai.

Nilai SDO_POINT pada SDO_GEOMETRY dipakai untuk menyimpan koordinat titik untuk jenis geometry POINT, dimana nilai SDO_ELEM_INFO dan SDO_ORDINATES bernilai null.

Nilai SDO_ELEM_INFO pada SDO_GEOMETRY menjelaskan nilai SDO_ORDINATES. SDO_ELEM_INFO terdiri atas kumpulan tiga angka berurutan yang berisi informasi SDO_STARTING_OFFSET, SDO_ETYPE, dan SDO_INTERPRETATION. Nilai SDO_STARTING_OFFSET merujuk ke posisi array di SDO_ORDINATES dimana elemen pertama dimulai dengan 1 dan bukan 0.

Sebagai latihan, aku akan membuat sebuah tabel yang menampung data spatial:


CREATE TABLE TBL_GEOMETRI (
ID NUMBER(10),
DATA_GEOMETRI SDO_GEOMETRY
);

Lalu, aku menambahkan sebuah data geometri berupa bentuk persegi panjang dengan titik kiri bawah pada koordinat kartesian (10,3) dan titik kanan atas pada koordinat (20,10). Aku akan menggunakan SDO_GTYPE = 2003 (POLYGON). SDO_ORDINATES berisi titik kiri bawah dan titik kanan atas secara berurutan, yaitu (10,3,20,10). SDO_ELEM_INFO akan menjelaskan makna titik tersebut, yaitu (1,1003,3). Kombinasi 1003 dan 3 menunjukkan kalau aku ingin mendeskripsikan sebuah persegi panjang berdasarkan posisi titik kiri bawah dan titik kanan atas. Berikut adalah SQL-nya:


INSERT INTO TBL_GEOMETRI VALUES (1,
SDO_GEOMETRY(2003, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(10,3,20,10))
);

Berikutnya aku akan membuat sebuah lingkaran dengan titik koordinat (25,5) dan jari-jari lingkaran = 4:


INSERT INTO TBL_GEOMETRI VALUES (2,
SDO_GEOMETRY(
2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,4),
SDO_ORDINATE_ARRAY(25,9,29,5,25,1)
)
);

Setelah ini, aku akan mencoba memakai fungsi yang ada di package SDO_GEOM untuk memanipulasi data geometri. Misalnya, aku ingin melihat hubungan antara kedua geometri yang baru aku masukkan:


SELECT
a.id,
b.id,
sdo_geom.relate(a.data_geometri, 'determine', b.data_geometri, 0.005)
FROM
tbl_geometri a, tbl_geometri b
WHERE
a.id = 1 AND b.id = 2;

Hasilnya adalah DISJOINT yang menunjukkan bahwa kedua geometri tersebut tidak saling menyentuh. Contoh lainnya, misalnya aku ingin mengetahui luas area untuk setiap geometri:


SELECT
a.id,
sdo_geom.sdo_area(a.data_geometri, 0.005)
FROM
tbl_geometri a;

Atau aku dapat melihat jarak terdekat antara kedua geometri tersebut, dengan perintah:


SELECT
a.id,
b.id,
sdo_geom.sdo_distance(a.data_geometri, b.data_geometri, 0.005)
FROM
tbl_geometri a, tbl_geometri b
WHERE
a.id = 1 AND b.id = 2;

Day 35: Oracle Ultra Search

Learning In HomeOriginal Date: 16 Februari 2009

Oracle Ultra Search merupakan teknologi yang dibangun berbasiskan Oracle Database dan Oracle Text. Ia memiliki sebuah komponen crawler, Ultra Search Crawler, yang akan meng-index data dari berbagai source di saat jadwalnya tiba. Sebelum mulai memakai Ultra Search, aku harus mengaktifkan Ultra Search middle tier yang merupakan aplikasi J2EE yang dijalankan dalam standalone OC4J bawaan database Oracle:


%ORACLE_HOME%/bin/searchctl start

Lalu, aku membuka halaman administrasi Ultra Search melalui browser, dimana alamat-nya secara default adalah http://localhost:5620/ultrasearch/admin/index.jsp

Karena SID yang aku pakai bukanlah default ORCL, maka aku perlu mengubah element URL di file %ORACLE_HOME%\oc4j\j2ee\OC4J_SEARCH\config\data-sources.xml. Awalnya, aku mengira ini sudah menyelesaikan masalah. Tapi Ultra Search Administration Page tetap complain tidak menemukan database ORCL, padahal aku sudah mengganti ke nama database yang benar di data-sources.xml. Akhirnya, setelah memeriksa lebih lanjut, aku menemukan bahwa kebanyakan file untuk menjalankan Ultra Search terletak di %ORACLE_HOME%\ultrasearch. Tidak lama kemudian, aku menemukan file %ORACLE_HOME%\ultrasearch\webapp\config\ultrasearch.properties. Setelah mengisi connection.url dengan nilai yang benar, akhirnya halaman utama administrasi Ultra Search dapat ditampilkan dengan baik.

Aku mencoba login dengan menggunakan user WKSYS (yang sudah aku unlock sebelumnya). User ini merupakan salah satu user yang memiliki hak akses untuk mengatur instance Ultra Search. Pada halaman instance, aku memilih link create, create instance untuk membuat sebuah instance baru. Setiap instance Ultra Search diasosiasikan dengan user database yang memiliki role WKUSER. Setelah selesai membuat instance, jika aku memilih link select, instance yang baru aku buat tersebut akan muncul pada daftar. Aku memilih nama instance tersebut, kemudian men-klik tombol apply sehingga Ultra Search tahu bahwa aku ingin melakukan administrasi untuk instance tersebut.

Pada halaman crawler, settings, aku dapat mengatur konfigurasi untuk Ultra Search Crawler, seperti jumlah prosesor dan jumlah thread yang akan dipergunakan saat proses crawler aktif.

Pada halaman sources, aku dapat menentukan apa saja yang akan menjadi target crawler. Sources dapat berupa web sources (halaman web tertentu), table sources (tabel di database Oracle), email sources, file sources, oracle sources dan user-defined. Aku mencoba mendefinisikan file source, yang berupa sebuah direktori yang terdiri atas kumpulan file Microsoft Word.

Setelah itu, pada halaman schedules, aku menentukan kapan saja crawler akan dijalankan untuk meng-index dokumen Word di source yang telah aku tentukan. Karena ini hanya sebagai latihan saja, pada schedule frequency, aku memilih manual launch. Aku tidak suka saat sedang belajar dan bermain game, tiba-tiba proses crawler bekerja dan meningkatkan beban komputerku. Untuk menjalankan schedule yang baru aku buat, aku men-klik pada tulisan di kolom status, kemudian men-klik tombol execute immediately. Aku dapat melihat log proses-nya di folder %ORACLE_HOME%\ultrasearch\log.

Setelah itu, aku dapat mulai menggunakan Oracle Ultra Search. Pada latihan kali ini, aku akan mencoba memakai Java API, dengan kode seperti berikut ini:


Instance instance = 
  new Instance();
instance.setConnection(cn);
instance.setInstanceName(
  "INSTANCE_LATIHAN");
instance.setCloseConnection(
  false);
InstanceMetaData metadata = 
  instance.getMetaData();

Query query = new Contains(
  "name", metadata);
Request req = new Request();
req.setQuery(query);
req.setExcerptLength(400);

Result result = instance.
  getResult(req);
System.out.println(
  "Estimate Hit Count = " + 
  result.estimateHitCount());
System.out.println(
  "Number of documents hit = " + 
  result.size());

DisplayUtil util = new 
  DisplayUtil();
util.setInstance(instance);
Writer w = new 
  OutputStreamWriter(System.out);

Iterator i = result.iterator();
while (i.hasNext()) {
  Document d = (Document) 
    i.next();				
  System.out.println(
    "Result Excerpt ==> " + 
    d.getExcerpt());
}

Day 34: Oracle interMedia

Learning In HomeOriginal Date: 15 Februari 2009

Oracle interMedia memungkinkan penyimpanan dan manajemen konten multimedia seperti gambar, audio dan video di dalam database Oracle. Data audio dikenal sebagai ORDAudio, data gambar dikenal sebagai ORDImage, dan data gambar dikenal sebagai ORDVideo. Data yang dapat terdiri dari baik audio, image, video , maupun text, dikenal sebagai ORDDoc (Heterogenous Media).

Aku akan mencoba memakai fitur Oracle interMedia untuk menyimpan data gambar. Aku mulai dengan membuat tabel sederhana seperti berikut:


CREATE TABLE TBL_GAMBAR (
ID_GAMBAR NUMBER(10),
GAMBAR ORDSYS.ORDImage
)

Lalu, aku akan menyimpan sebuah gambar ke dalam tabel tersebut dengan melalui JDBC. Caranya tidak berbeda jauh seperti menyimpan data BLOB, hanya saja sekarang jadi lebih gampang. Sebelum menjalankan kode berikut, aku memastikan file %ORACLE_HOME%\ord\jlib\ordim.jar ada dalam CLASSPATH:


OracleConnection cn = 
  (OracleConnection) ods.
  getConnection();
cn.setAutoCommit(false);
		
OracleStatement os = 
  (OracleStatement) cn.
  createStatement();
os.executeUpdate("INSERT INTO 
  TBL_GAMBAR VALUES 
  (1, ORDSYS.ORDImage.init())");
cn.commit();
			
OracleResultSet rs = (OracleResultSet) 
  os.executeQuery("SELECT GAMBAR 
    FROM TBL_GAMBAR 
    WHERE ID_GAMBAR = 1 
    FOR UPDATE");
rs.next();
OrdImage gambar = (OrdImage) 
  rs.getORAData(1, 
    OrdImage.getORADataFactory());
gambar.loadDataFromFile(
  "c:\\gambar1.jpg");
			
OraclePreparedStatement ps = 
  (OraclePreparedStatement) 
  cn.prepareStatement(
    "UPDATE TBL_GAMBAR SET GAMBAR = ? 
     WHERE ID_GAMBAR = 1");
ps.setORAData(1, gambar);
int totalUpdated = ps.executeUpdate();
cn.commit();

Berikutnya, aku akan mencoba memproses gambar yang aku upload tadi di dalam server database Oracle. Aku akan mengecilkan ukurannya menjadi 50×50 dan mengubahnya ke dalam format GIF dengan contoh kode program seperti berikut ini:


OracleResultSet rs = (OracleResultSet) 
  os.executeQuery("SELECT GAMBAR 
  FROM TBL_GAMBAR WHERE 
  ID_GAMBAR = 1 FOR UPDATE");
rs.next();
	
OrdImage gambar = (OrdImage) 
rs.getORAData(1, 
  OrdImage.getORADataFactory());
gambar.process(
"maxScale=50 50, fileFormat=GIFF");
cn.commit();

Walaupun kode di atas diberikan oleh client, proses pengolahan gambar tetap terjadi di sisi server. Oleh sebab itu, saat aku membaca nilai gambar tersebut di transaksi yang berbeda, aku tetap akan memperoleh sebuah gambar GIF berukuran mendekati 50×50 (sesuai ratio):


OrdImage gambar = (OrdImage) 
  rs.getORAData(1, 
    OrdImage.getORADataFactory());
BLOB blob_gambar = 
  gambar.getContent();
InputStream is = 
  blob_gambar.getBinaryStream(1l);
			
FileOutputStream fos = new 
  FileOutputStream("c:\\test.gif");
byte[] b = new byte[200];
while (is.read(b) > 0) {
  fos.write(b);
}
is.close();
fos.close();

Day 33: SQL Untuk Modeling

Learning In HomeOriginal 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 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.

Day 31: SQL Untuk Data Warehousing

Learning In HomeOriginal Date: 12 Februari 2009

Tidak terasa sudah sebulan berada di Pontianak, dan sebentar lagi hari Valentine akan tiba. Dalam suasana penuh cinta ini, aku akan menikmatinya dengan mempelajari SQL yang kerap dipakai dalam query yang berkaitan dengan data warehousing. Aku akan mulai dengan klausa GROUP BY CUBE dan fungsi GROUPING. Anggap saja aku punya tabel TBL_PEMBELIAN dan tabel TBL_KATEGORI. Lalu, aku ingin mendapatkan laporan pembelian per-hari untuk setiap kategori yang ada. Untuk itu, aku dapat menggunakan query berikut:


SELECT pb.tanggal, kat.nama, COUNT(*)
FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat
WHERE pb.barang_id = kat.id GROUP BY CUBE (pb.tanggal, kat.nama);

GROUP BY CUBE pada query di atas selain mengembalikan kombinasi tanggal dan kategori, juga mengembalikan total untuk masing-masing kombinasi dalam bentuk nilai NULL seperti:

----------------------------------
BA
RIS TANGGAL    NAMA      COUNT(*)
----------------------------------
1  NULL       NULL       16
2  NULL       KATEGORI1  8
3  NULL       KATEGORI2  8
4  01/01/2009 NULL       5
5  01/01/2009 KATEGORI1  3
6  01/01/2009 KATEGORI2  2
7  02/01/2009 NULL       11
8  02/01/2009 KATEGORI1  5
9  02/01/2009 KATEGORI2  6
----------------------------------

Nilai ‘NULL’ disini memiliki arti khusus, yaitu subtotal dari sebuah baris. Sebagai contoh, baris 4 adalah baris yang menunjukkan jumlah transaksi pada tanggal 01/01/2009 yaitu 5 (3+2). Begitu juga dengan baris 7. Baris 2 menunjukkan total COUNT untuk seluruh kategori 1, yaitu 8 (3+5). Baris 3 menunjukkan total COUNT untuk seluruh kategori 2, yaitu 8 (2+6).

Agar hasil-nya lebih rapi, aku dapat menggunakan fungsi GROUPING dan DECODE untuk memberi tulisan tertentu pada label untuk nilai subtotal, seperti:


SELECT decode(grouping(pb.tanggal),1, 'Total', pb.tanggal),
decode(grouping(kat.nama), 1, 'Seluruh Kategori', kat.nama),
COUNT(*) FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat WHERE pb.barang_id = kat.id GROUP BY CUBE (pb.tanggal, kat.nama);

Jika aku tidak menginginkan hasil pada baris 2, dan 3, dimana terdapat subtotal untuk masing-masing tanggal, maka aku dapat menggunakan GROUP BY ROLLUP yang tidak berbeda jauh dengan GROUP BY CUBE, sehingga query-nya menjadi:


SELECT decode(grouping(pb.tanggal),1, 'Total', pb.tanggal),
decode(grouping(kat.nama), 1, 'Seluruh Kategori', kat.nama),
COUNT(*) FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat WHERE pb.barang_id = kat.id GROUP BY ROLLUP
(pb.tanggal, kat.nama);

Query yang mengandung fungsi GROUPING() dapat menjadi sulit dibaca jika banyak atribut yang harus diperiksa dengan fungsi tersebut. Oleh sebab itu, fungsi GROUPING_ID() hadir untuk mempermudah. GROUPING_ID() akan mengambil nilai 0 dan 1 dari GROUPING(), lalu merangkainya sesuai urutan atribut menjadi deretan angka 0 dan 1 sebagai bilangan biner. GROUPING() akan menghasilkan konversi bilangan biner ke bilangan desimal dari deretan angka 0 dan 1 ini. Hal ini terlihat dari hasil query berikut:


SELECT
GROUPING(pb.tanggal) as "TANGGAL",
GROUPING(kat.nama) as "KATEGORI",
GROUPING_ID(pb.tanggal, kat.nama)
FROM
tbl_pembelian pb, tbl_kategori kat
WHERE
pb.barang_id = kat.id
GROUP BY
ROLLUP (pb.tanggal, kat.nama);

-------------------------
  TANG KATE   GROUPING
  GAL  GORI   _ID
-------------------------
  0    0         0
  0    0         0
  0    1         1
  0    0         0
  0    0         0
  0    0         0
  0    1         1
  ...  ...       ...
  1    1         3
--------------------------

Dengan demikian, aku dapat memilih level detail subtotal yang ingin ditampilkan berdasarkan GROUPING_ID, dengan menambahkan klausa seperti berikut ini di akhir query:


HAVING
GROUPING_ID(pb.tanggal, brg.nama, brg.harga) >= 1;