SAMEER WADHWA

In this article I have tried to aware you about some of the analytic functions provided by oracle 8i.These funtions are very powerful and ease to use.

· ROLLUP AND CUBE AGGREGATE FUNCTIONS

· RANKED FUNCTION

· CASE

· LAG AND LEAD FUNCTION

· RATIO_TO_REPORT

ROLLUP AND CUBE AGGREGATE FUNCTIONS

To understand the power of ROLLUP and CUBE functions ,consider the following SQL statement :-

ora816 SamSQL :> compute sum of totsal on deptno

ora816 SamSQL :> break on deptno

ora816 SamSQL :> select deptno,job,sum(sal) totsal from emp group by deptno,job;

DEPTNO JOB TOTSAL

---------- --------- ----------

10 CLERK 1300

MANAGER 2450

PRESIDENT 5000

********** ----------

sum 8750

20 ANALYST 6000

CLERK 1900

MANAGER 2975

********** ----------

sum 10875

30 CLERK 950

MANAGER 2850

SALESMAN 5600

********** ----------

sum 9400

Now see the use of ROLLUP Function

ora816 SamSQL :> select deptno,job,sum(sal) totsal from emp group by ROLLUP(deptno,job);

DEPTNO JOB TOTSAL

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750 Total of Deptno 10

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

30 9400

29025 Grand Total

So if you compare the two output you will notice that you are getting the same output. By using rollup you can avoid compute and break clausesfrom SQL. This will mostly helpful in PL/SQL . We do not have to put logic for computing values on break of groups.

Now see the use of CUBE Function

ora816 SamSQL :> select deptno,job,sum(sal) totsal from emp group by CUBE(deptno,job);

Fri Mar 23

NuGenesis Report

DEPTNO JOB TOTSAL

---------- --------- ----------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750 Total of Deptno 10

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

30 9400

ANALYST 6000

CLERK 4150

MANAGER 8275 Total w.r.t JOB

PRESIDENT 5000

SALESMAN 5600

Grand Total 29025

Cube also do a total with respect to second group for example JOB in our case . Also at end you will see the grand total

Conclusion : Rollup and Cube are the aggregate function which allows developers and dbas to avoid compute and break clauses and simplify logic of programming

Ranked Function in 8i (816)

Suppose you have a data in table which you want to rank in a specified order for example you have a table test and you want to rank a value of repcol.

Ora816 SamSQL> select * from test;

REPCOL VALUE

---------- ----------

A 100

A 200

A 300

B 1000

B 900

B 800

A 500

B 400

B 500

Ora816 SamSQL> select repcol,value,rank() over ( partition by repcol

2 order by value desc ) ranked_value

3 from test;

REPCOL VALUE RANKED_VALUE

---------- ---------- ------------

A 500 1

A 300 2

A 200 3

A 100 4

B 1000 1

B 900 2

B 800 3

B 500 4

B 400 5

The above value is ranked by the rank function provided by 8.1.6

Use of Case in SELECT

Case Statement are similar to decode , it is more flexible and gives better performace

Ora816 SamSQL> ed

Wrote file afiedt.buf

1 select sum(case when repcol='A' then value else 0 end) sum_of_A,

2 sum(Case when repcol='B' then value else 0 end) Sum_of_B,

3 sum(case when value = 500 then 1 else 0 end) Value_Eq_500,

4 sum(case when value > 100 then 1 else 0 end) Value_Gre_100

5* from test

Ora816 SamSQL> /

SUM_OF_A SUM_OF_B VALUE_EQ_500 VALUE_GRE_100

---------- ---------- ------------ -------------

1100 3600 2 8

Use of Group by in CASE

Ora816 SamSQL> ed

Wrote file afiedt.buf

1 select repcol,sum(case when repcol='A' then value else 0 end) sum_of_A,

2 sum(Case when repcol='B' then value else 0 end) Sum_of_B,

3 sum(case when value = 500 then 1 else 0 end) Value_Eq_500,

4 sum(case when value > 100 then 1 else 0 end) Value_Gre_100

5 from test

6* group by repcol

Ora816 SamSQL> /

REPCOL SUM_OF_A SUM_OF_B VALUE_EQ_500 VALUE_GRE_100

---------- ---------- ---------- ------------ -------------

A 1100 0 1 3

B 0 3600 1 5

Ora816 SamSQL> ed

Wrote file afiedt.buf

1 select (case when value between 100 and 300 then '100-300'

2 when value between 400 and 700 then '400-700'

3 when value between 800 and 900 then '800-900'

4 when value > 900 then '>900' end) VALUE_RANGE,

5 count(*) as VALUE_COUNT

6 from test

7 group by

8 (case when value between 100 and 300 then '100-300'

9 when value between 400 and 700 then '400-700'

10 when value between 800 and 900 then '800-900'

11* when value > 900 then '>900' end )

Ora816 SamSQL> /

VALUE_R VALUE_COUNT

------- -----------

100-300 3

400-700 3

800-900 2

>900 1

Ora816 SamSQL> ed

Wrote file afiedt.buf

1 select (case when value between 100 and 300 then '100-300'

2 when value between 400 and 700 then '400-700'

3 when value between 800 and 900 then '800-900'

4 when value > 900 then '>900' end) VALUE_RANGE,value

5* from test

Ora816 SamSQL> /

VALUE_R VALUE

------- ----------

100-300 100

100-300 200

100-300 300

>900 1000

800-900 900

800-900 800

400-700 500

400-700 400

400-700 500

9 rows selected.

Lag and Lead Functions

Ora816 SamSQL> ed

Wrote file afiedt.buf

1 select repcol,

2 value,

3 lag(value,1) over (order by repcol) lag_value ,

4 lead(value,1) over (order by repcol) lead_value

5* from test

Ora816 SamSQL> /

REPCOL VALUE LAG_VALUE LEAD_VALUE

---------- ---------- ---------- ----------

A 100 200

A 200 100 500

A 500 200 300

A 300 500 1000

B 1000 300 500

B 500 1000 900

B 900 500 400

B 400 900 800

B 800 400

9 rows selected.

The LAG function provides access to a row at a given offset prior to the position and the LEAD function provides access to a row at a given offset after the current position.

The functions have the following syntax:

{LAG | LEAD}

   (, [ [, ]]) OVER

      ([PARTITION BY [,...]]

       ORDER BY  [collate clause>]

      [ASC | DESC] [NULLS FIRST | NULLS LAST] [,...])

is an optional parameter and defaults to 1. is an optional parameter and is the value returned if the falls outside the bounds of the table or partition.

Ratio_to_report

The RATIO_TO_REPORT function computes the ratio of a value to the sum of a set of values. If the expression value expression evaluates to NULL, RATIO_TO_REPORT also evaluates to NULL, but it is treated as zero for computing the sum of values for the denominator. Its syntax is:

RATIO_TO_REPORT

() OVER

        ([PARTITION BY [,...]])

where

· and can be any valid expression involving column references or aggregates.

· The PARTITION BY clause defines the groups on which the RATIO_TO_REPORT function is to be computed. If the PARTITION BY clause is absent, then the function is computed over the whole query result set.

Ora816 SamSQL> ed

Wrote file afiedt.buf

1 select repcol,

2 value,

3 sum(value) over() sumofvalue,

4 ratio_to_report (sum(value)) over() RATIO

5 from test

6* group by repcol ,value

Ora816 SamSQL> /

REPCOL VALUE SUMOFVALUE RATIO

---------- ---------- ---------- ----------

A 100 4700 .021276596

A 200 4700 .042553191

A 300 4700 .063829787

A 500 4700 .106382979

B 400 4700 .085106383

B 500 4700 .106382979

B 800 4700 .170212766

B 900 4700 .191489362

B 1000 4700 .212765957

9 rows selected.

Ora816 SamSQL> ed

Wrote file afiedt.buf

1 select repcol,

2 value,

3 sum(value) over(partition by repcol) sumofvalue,

4 ratio_to_report (sum(value)) over(partition by repcol) RATIO

5 from test

6* group by repcol ,value

Ora816 SamSQL> /

REPCOL VALUE SUMOFVALUE RATIO

---------- ---------- ---------- ----------

A 100 1100 .090909091

A 200 1100 .181818182

A 300 1100 .272727273

A 500 1100 .454545455

B 400 3600 .111111111

B 500 3600 .138888889

B 800 3600 .222222222

B 900 3600 .25

B 1000 3600 .277777778

9 rows selected.

Conclusion : These analytic functions are very powerful and very useful. Thanks to Oracle to provides us such a great tool.

References:

Oracle Server concept manual

Oracle Datawarehouse manual.

Please feel free to send comments or feedback at wadhwa_s@hotmail.com.

Thanks for reading this article.

SAMEER WADHWA