OLAP features in Oracle|ROLLUP| CUBE| GROUPING| GROUP ID| RANK| NULLs|DECODE

 

 CHAPTER 7-OLAP features in oracle

à OLAP (Online analytical processing)

à OLAP provides extra features to queries up on database.

à The OLAP operations are perform enhancements like top n query, group (), cube, ROLLUP…

ROLLUP

à It is used with group by clause to display summarized data.

à ROLLUP grouping produces subtotals and grand total.

à Select deptno, sum (sal) from emp group by ROLLUP (deptno);

Select job, sum (sal) from emp group by ROLLUP (job);

à Passing multiple columns to ROLLUP

Select deptno, job, sum (sal) from emp group by ROLLUP (deptno, job);

1^st it groups into deptno wise and then calculate totals of job wise in that deptno.

CUBE

à The result of a cube in a summery that stores subtotals of every combination of column or expression.

à It is similar to ROLLUP but extension of ROLLUP.

à Select deptno, job, sum (sal) from emp group by CUBE (deptno, job);

It 1^st display grand total, job wise sub totals and then deptno wise totals with job wise.

GROUPING functions

à Grouping function written ‘1’ if column is null, otherwise it written ‘0’.

Select grouping(job), deptno, job, sum (sal) from emp group by cube (deptno, job);

Select grouping(job), job, sum (sal) from emp group by ROLLUP (job);

 

à GROUPING function with set clause

It is used to get the subtotal rows

Select deptno, job, sum (sal) from emp group by grouping sets (deptno, job);

It displays deptno wise sub totals and next job wise sub totals only.

à GROUPING ID function

This function is use having clause to filter rows.

This function accepts one or more columns and written the decimal equallant of the GROUPING BIT VECTOR.

GROUPING BIT VECTOR means

If we take columns deptno, job then

It written ‘0’ when deptno, job are NOTNULL

It written ‘1’ when deptno is NOTNULL and job is NULL

It written ‘2’ when deptno is NULL and job is NOTNULL

It written ‘3’ when deptno, job are NULL.

Select deptno, job, grouping (deptno), grouping (job), grouping_id (deptno, job), sum (sal) from emp group by rollup (deptno, job);

GROUPING along with having clause

Select deptno, job, grouping (deptno), grouping (job), grouping_id (deptno, job), sum (sal) from emp group by rollup (deptno, job) having grouping_id (deptno, job)>0;

Represent column multiple times

Select deptno, job, sum (sal) from emp group by deptno, rollup (deptno, job);

à GROUP_ID function

It is used to remove duplicate rows written by group by clause.

Select deptno, job, group_id (), sum (sal) from emp group by deptno, rollup (deptno, job) having group_id()=0;

à Analytical function

Analytical function computes an aggregate value based on group of rows.

The group of rows are called as window and is defined by Analytic clause.

It can appear only in select list, order by clause.

Analytical function categories

Ranking function: -it enables us to calculate ranks, percentages.

Inverse percentile function: - it enables to calculate a value corresponding to ‘per’.

Window function: - it enables to calculate fimilative and moving aggregates.

 

Normal ranking example

Select ename, deptno, sal, RANK () OVER (order by sal) EMP_RANK from emp group by deptno, ename, sal order by EMP_RANK;

It gives rank order by sal if two persons have same sal then it gives same rank for two persons and omit next rank and give next rank to after that sal.

Ranking with partition example

Select ename, deptno, sal, RANK () OVER (PARTITION by deptno order by sal desc) EMP_RANK from emp order by deptno, sal desc;

It same as normal ranking but it partitioned with deptno wise sal ranking.

Ranking with partition and filter example

select* from (Select ename, deptno, sal, RANK () OVER (PARTITION by deptno order by sal desc) EMP_RANK from emp) where EMP_RANK<=3 order by deptno, sal desc;

ROW WINDOW function

Select ename, deptno, sal, sum (sal) OVER (PARTITION by deptno order by ename ROWS 1 PRECEDING) EMP_RANK from emp order by deptno, ename;

ROWS 1 PRECEDING means adding of sal with above sal , and it is partitioned by deptno under this order by employee name wise.

 

RANGE WINDOW function

Range window collect rows together based on where clause.

Select ename, deptno, sal, count (*) OVER (order by sal asc range 5000 PRECEDING) SAL_COUNT from emp ;

Range 5000 PRECEDING means gives rank sal up to 5000 then after 5000 again rank start from 1.

In this rank given by if two sal have same it give rank after above rank for both sal and next continue ranks…

NULLs FIRST

Select empno, comm, RANK () OVER (order by comm desc NULLS FIRST) RANK, DENSE_RANK () OVER (order by comm desc NULLS FISRT) DENSE_RANK from emp group by empno, comm;

NULLs LAST

Select empno, comm, RANK () OVER (order by comm desc NULLS LAST) RANK, DENSE_RANK () OVER (order by comm desc NULLS LAST) DENSE_RANK from emp group by empno, comm;

DECODE function

Select ename, deptno, DECODE (deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATION', 'OTHERS') Department from emp;

Grouping with DECODE function

Select DECODE (grouping(deptno),1,'ALL Departments', deptno) Department, sum(sal) from emp group by ROLLUP (deptno);

select DECODE (grouping(deptno),1,'ALL Departments', deptno) Department, sum(sal), DECODE (grouping(job), 1, deptno||' Total', job) Designation from emp group by ROLLUP (deptno, job);

select DECODE (grouping(deptno),1,'ALL Departments', deptno) Department, sum(sal), DECODE (grouping(job),1, deptno||' DEPT Total', job) Designation from emp group by CUBE (deptno, job);