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;

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: