08_SQL OLAP DBMS DB2 GROUP BY CUBE ROLL UP .pptx

Materi Query SQL OLAP Pada DBMS DB2

1. SQL OLAP O n L ine A nalytical P rocessing Think : building and delivering information via SQL

2. SQL OLAP FUNCTIONs RANK : memberi ranking pada setiap baris berdasarkan urutan tertentu DENSE_RANK: MEMBERI RANKING pada partisi tertentu (DENSE: RINGKAS, PADAT) ROW_NUMBER: MEMBERI NOMOR BARIS berdasarkan satu atau lebih kolom ROLLUP CUBE GROUPING SETS GROUPING

3. Database fundamentals > SQL > Language elements > Expressions > OLAP specification On-Line Analytical Processing (OLAP) functions provide the ability to: return ranking {RANK, DENSE_RANK} row numbering { row_number } existing aggregate function information as a scalar value in a query result.

4. Tabel employee

5. RANK()

6. SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK () OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE SALARY+BONUS > 50000 ORDER BY LASTNAME; PRIORITAS PERTAMA DIURUTKAN BY LASTNAME DEFAULT PENGURUTAN ADALAH ascending. Desc  bottom up  dari z ke a, dari besar ke kecil Asc  top-down  dari A KE Z, DARI KECIL KE BESAR

7. SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK () OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE SALARY+BONUS > 50000; PRIORITAS PERTAMA DIURUTKAN BERDASARKAN RANK_SALARY Desc  dari BESAR KE KECIL. SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE SALARY+BONUS > 50000 ORDER BY SALARY+BONUS DESC; SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE SALARY+BONUS > 50000 ORDER BY RANK_SALARY;

8. SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK () OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE join departement on employee. Workdept = departement.deptno WHERE SALARY+BONUS > 50000 ORDER BY RANK_SALARY; SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE SALARY+BONUS > 50000 ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC) ;

9. SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK () OVER (ORDER BY LASTNAME) AS RANK_LASTNAME FROM EMPLOYEE WHERE SALARY+BONUS > 50000 order by rank_lastname ;  by default ascending ( asc : dari a ke z ) PRIORITAS PERTAMA DIURUTKAN BERDASARKAN LASTNAME MENGGUNAKAN FUNGSI RANK().

10. Query untuk mencari rangking dari rata-rata total_salary (salary ditambah bonus) per departemen . Tampilkan kolom workdept , rata-rata total_salary , dan rankingnya . SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY , RANK () OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL FROM EMPLOYEE GROUP BY WORKDEPT ORDER BY RANK_AVG_SAL;

11. DENSE_RANK MEMBERI RANKING SECARA PARSIAL . MEMBERI RANKING PADA KELOMPOK DATA TERTENTU.

12. Beri ranking pada setiap kelompok departement (WORKDEPT) berdasarkan EDLEVEL tertinggi . Urutkan berdasarkan WORKDEPT. SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL, DENSE_RANK() OVER (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL FROM EMPLOYEE ORDER BY WORKDEPT;

13. Beri ranking pada setiap kelompok departement (WORKDEPT) berdasarkan EDLEVEL tertinggi . Urutkan berdasarkan LASTNAME. SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL, DENSE_RANK() OVER (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL FROM EMPLOYEE ORDER BY LASTNAME;

14. Beri ranking pada setiap kelompok departement (WORKDEPT) berdasarkan EDLEVEL tertinggi . Urutkan berdasarkan WORKDEPT dan LASTNAME. SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL, DENSE_RANK() OVER (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL FROM EMPLOYEE ORDER BY WORKDEPT, LASTNAME;

15. CONTOH: Mencari 5 ranking total salary ( salary+bonus ) tertinggi . SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY FROM ( SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK () OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE) AS RANKED_EMPLOYEE WHERE RANK_SALARY < 6 ORDER BY RANK_SALARY;

16. SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE (SALARY + BONUS) > 90000; SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE TOTAL_SALARY > 90000; SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY, RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY FROM EMPLOYEE WHERE RANK_SALARY > 6; "TOTAL_SALARY" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.18.60

17. LEAD() FIRST_VALUE()

18. SELECT EMPNO, WORKDEPT, JOB, SALARY, LEAD(SALARY, 0) OVER (PARTITION BY WORKDEPT ORDER BY SALARY) AS L_SALARY FROM EMPLOYEE ORDER BY WORKDEPT, SALARY; SELECT EMPNO, WORKDEPT, JOB, SALARY, LEAD(SALARY, 1) OVER (PARTITION BY WORKDEPT ORDER BY SALARY) AS L_SALARY FROM EMPLOYEE ORDER BY WORKDEPT, SALARY; SELECT EMPNO, WORKDEPT, JOB, SALARY, LEAD(SALARY, 2) OVER (PARTITION BY WORKDEPT ORDER BY SALARY) AS L_SALARY FROM EMPLOYEE ORDER BY WORKDEPT, SALARY;

19. LEAD(SALARY, 0) LEAD(SALARY, 1)

20. SELECT EMPNO, WORKDEPT, LASTNAME, FIRSTNME, JOB, SALARY, LEAD(SALARY, 1) OVER ( PARTITION BY WORKDEPT ORDER BY SALARY) - SALARY AS DELTA_SALARY FROM EMPLOYEE ORDER BY WORKDEPT, SALARY;

21. SELECT EMPNO, WORKDEPT, JOB, SALARY, FIRST_VALUE(SALARY) OVER (PARTITION BY WORKDEPT ORDER BY SALARY) AS L_SALARY FROM EMPLOYEE ORDER BY WORKDEPT, SALARY;

22. SELECT JOB, HIREDATE, EMPNO, LASTNAME, FIRSTNME, SALARY, FIRST_VALUE(SALARY) OVER (PARTITION BY JOB ORDER BY HIREDATE) AS FIRST_SALARY, SALARY - FIRST_VALUE(SALARY) OVER (PARTITION BY JOB ORDER BY HIREDATE) AS DELTA_SALARY FROM EMPLOYEE ORDER BY JOB, HIREDATE;

23. ROW_NUMBER

24. Memberi nomor pada setiap baris berdasarkan urutan ascending WORKDEPT dan LASTNAME. SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER, LASTNAME , WORKDEPT, SALARY FROM EMPLOYEE ORDER BY WORKDEPT, LASTNAME ;

25. ROLLUP() cube() will result in the data being summarized at the CERTAIN levels.

26. Contoh ROLLUP Menjumlahkan Penjualan Pada Tabel Trans Berdasarkan Country Dan Region

27. Contoh cube Menjumlahkan Penjualan Pada Tabel Trans Berdasarkan Country Dan Region

28. WHat’s THE DIFFERENT ? select workdept , job, SUM(SALARY) from employee GROUP BY ROLLUP (WORKDEPT,JOB) ORDER BY WORKDEPT; select workdept , job, SUM(SALARY) from employee GROUP BY (WORKDEPT,JOB) ORDER BY WORKDEPT; select workdept , job, SUM(SALARY) from employee GROUP BY CUBE (WORKDEPT,JOB) ORDER BY WORKDEPT; select workdept , job, SUM(SALARY) from employee GROUP BY GROUPING SETS (WORKDEPT, JOB) ORDER BY WORKDEPT;

29. WHat’s THE DIFFERENT ? select workdept , job, SUM(SALARY) from employee GROUP BY ROLLUP(WORKDEPT,JOB) ORDER BY WORKDEPT;

30. WHat’s THE DIFFERENT ? select workdept , job, SUM(SALARY) from employee GROUP BY (WORKDEPT, JOB) ORDER BY WORKDEPT;

31. WHat’s THE DIFFERENT ? select workdept , job, SUM(SALARY) from employee GROUP BY CUBE(WORKDEPT, JOB) ORDER BY WORKDEPT;

32. WHat’s THE DIFFERENT ? select workdept , job, SUM(SALARY) from employee GROUP BY GROUPING SETS(WORKDEPT, JOB) ORDER BY WORKDEPT;

33. GROUPING SETS

34. GROUPING SETS()

35. Multiplicative vs. Additive Multiplicative : Perkalian Additive : Penjumlahan

36. Grouping set index Pada TABEL FAKTA DAPAT DIBERI INDEX UNTUK MENGOPTIMALKAN EKSEKUSI QUERY OLAP GROUPING SET.

37. CONTOH PERBEDAAN ROLLUP dan CUBE

38. select * from sales; --41 record select region, sales_date , sum(sales) as hasil_penjualan from sales group by region, sales_Date ; select region, sales_date , sum(sales) as hasil_penjualan from sales group by rollup( region,sales_Date ); select region, sales_date , sum(sales) as hasil_penjualan from sales group by rollup( sales_Date,region ); select region, sales_date , sum(sales) as hasil_penjualan from sales group by cube( region,sales_Date );

39. TERIMAKASIH