DB2 – OLAP syntax

DB2 menyediakan beberapa function yang mungkin tidak pernah kita lihat dalam kehidupan sehari-hari di SQL.

Berikut saya mencoba mengumpulkan beberapa function yang berguna untuk menghasilkan report yang diinginkan.

Saya menggunakan DB2 ESE 9.7 dan database aromadb. Jika belum memiliki database ini dapat didownload http://www3.software.ibm.com/ibmdl/pub/software/dw/dm/db2/dm0607cao/Aroma_Data.zip

1. Membuat nomor baris
db2 select row_number() over() as row_num, quantity from aroma.sales fetch first 10 rows only

Berikut hasilnya
ROW_NUM              QUANTITY
——————– ———–
1           8
2           9
3          40
4          16
5          11
6          30

2. Melakukan penjumlahan per record
Biasanya kita hanya melakukan penjumlahan dari kolom tertentu, dengan menggunakan over(), maka kita dapat melihat progress pertambahan masing-masing record.

db2 select prodkey,sum(quantity) as qty,sum(sum(quantity)) over(order by prodkey) as sum_qty from aroma.sales group by prodkey

Berikut hasilnya
PRODKEY     QTY         SUM_QTY
———– ———– ———–
0       88402       88402
1       92449      180851
3        1300      182151
4        1077      183228
5        1356      184584
10       92622      277206
11       91215      368421
12       89570      457991

Jika dilihat, kolom sum_qty melakukan penambahan dari record qty. Jika diinginkan penjumlahan ini bisa dibagi-bagi menjadi beberapa bagian, misalnya tiap seminggu sekali.

db2 SELECT week,date,SUM(quantity) AS total_qty,
SUM(SUM(quantity)) OVER(PARTITION BY week ORDER BY date
ROWS UNBOUNDED PRECEDING) AS run_qty
FROM aroma.period a, aroma.sales b
WHERE a.perkey = b.perkey
AND year = 2006
AND month = ‘JAN’
GROUP BY week, date
ORDER BY week, date;

Berikut hasilnya
WEEK        DATE       TOTAL_QTY   RUN_QTY
———– ———- ———– ———–
1 01/01/2006        1492        1492
1 01/02/2006        1264        2756
1 01/03/2006        1088        3844
1 01/04/2006        1167        5011
1 01/05/2006        1346        6357
1 01/06/2006        1081        7438
1 01/07/2006        1325        8763
2 01/08/2006        1352        1352
2 01/09/2006        1258        2610
2 01/10/2006        1053        3663
2 01/11/2006        1145        4808

3. Menentukan ranking
db2 select row_number() over() as row_num,prod_name, quantity, rank() over(order by quantity desc)as rank
from aroma.sales a, aroma.product b
where a.prodkey = b.prodkey
order by rank;

Berikut hasilnya
ROW_NUM              PROD_NAME                      QUANTITY    RANK
——————– —————————— ———– ——————–
1 Lotta Latte                             49                    1
2 Easter Sampler Basket                   49                    1
3 Aroma Sheffield Steel Teapot            49                    1
4 English Breakfast                       49                    1
5 Lotta Latte                             49                    1
6 English Breakfast                       49                    1

Jika kita lihat disini, ranknya bernilai sama. Hal ini dapat terjadi karena value dari yang kita jadikan peringkat disini (quantity) bernilai sama. Dengan menggunakan rank(), jika quantity pada row_num 7 bernilai berbeda, maka rank akan langsung loncat ke 7, jika kita menginginkan rank selanjutnya bernilai 2, maka dapat menggunakan function dense_rank()

Berikut syntaxnya
select row_number() over() as row_num,prod_name, quantity, dense_rank() over(order by quantity desc)as rank
from aroma.sales a, aroma.product b
where a.prodkey = b.prodkey
order by rank;

Semoga bermanfaat.

This entry was posted in DB2 and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>