OLAP function
OLAP Stands for "Online Analytical
Processing" OLAP allows users to analyze database information from
multiple database systems at one time. While relational databases are
considered to be two-dimensional, OLAP data is multidimensional,
meaning the information can be compared in many different ways.
For example, a company might compare their computer sales in June with
sales in July, then compare those results with the sales from another
location, which might be stored
in a different database.
OLAP functions provide the capability to perform analytic tasks on
data, such as computing moving averages, ranks, and cumulative values.
You can include an OLAP function in a select-list or on the ORDER BY
clause of a SELECT statement. An OLAP function cannot be used as an
argument of an aggregate function. Therefore, you cannot have functions
like SUM (RANK ()).
Type of Analytical Functions
Ranking function
This family consists of rank, dense_rank, row_number, ntile, percent_rank and cume_dist
Window Function
Window functions consist of all agregate functions (sum, max, avg…) cumulative sum shows how to use sum to calculate a cumulative sum. How to sum a value of the last, the current and next row. This shows that how to select the last non-null value, first value.
Reporting Function
These are queries that ask something like: find that contribute 10% or more to total sales. The most prominent function for this is ratio_to_report.
Lag/Lead Functions
Lag /Lead functions make it possible to access values in other rows than the current one. The corresponding functions are lag and lead.
Basic rules for using OLAP functions
The following rules apply to all of the supported OLAP functions
Within SQL queries, OLAP functions can be used
• In the select list
• In expressions
• As arguments of scalar functions
• In the WHEN clause
• In the final ORDER BY clause (by using aliases or positional references to OLAP functions elsewhere in the query)
• In a query that contains a BREAK BY...SUMMING clause
• In subqueries
OLAP functions cannot be used
•In the same query as a RISQL display function
•In the search condition of a WHERE clause
•As arguments for SET (aggregate) functions. For example, the following expression is not valid:
Sum(rank() over(order by dollars))
•As arguments to other OLAP functions in the same query block. However, the results of OLAP functions in a subquery block can
be referenced by other OLAP functions in an outer query block.
•In precomputed view definitions. However, queries that contain OLAP functions can be rewritten with the Vista query rewrite
system (and candidate views can be generated based on such rewrites).
OLAP function must be grouping columns
Columns referenced by an OLAP function
must be grouping columns or aggregation functions from the same query
block in which the OLAP function and the GROUP BY clause appear. OLAP
processing occurs after the grouping and aggregation operations and
before the final ORDER BY clause is applied; therefore, it must be
possible to derive the OLAP expressions from those intermediate
results.
If there is no GROUP BY clause in a query block, OLAP functions can reference other columns in the select list.
Example
Rank ():-
SQL >select empno, ename, Sal, rank() OVER(ORDER BY sal desc) Rank FROM emp| EMPNO | ENAME | SAL | RANK |
| 7839 | KING | 5000 | 1 |
| 7902 | FORD | 3000 | 2 |
| 7788 | SCOTT | 3000 | 2 |
| 7566 | JONES | 2975 | 4 |
| 7698 | BLAKE | 2850 | 5 |
| 7782 | CLARK | 2450 | 6 |
| 7499 | ALLEN | 1600 | 7 |
| 7844 | TURNER | 1500 | 8 |
| 7934 | MILLER | 1300 | 9 |
| 7654 | MARTIN | 1250 | 10 |
| 7521 | WARD | 1250 | 10 |
| 7876 | ADAMS | 1100 | 12 |
| 7900 | JAMES | 950 | 13 |
| 7369 | SMITH | 800 | 14 |
Dense_rank ():-
SQL>select empno, ename, sal, dense_rank () OVER(ORDER BY sal DESC) Rank FROM emp
| EMPNO | ENAME | SAL | RANK |
| 7839 | KING | 5000 | 1 |
| 7902 | FORD | 3000 | 2 |
| 7788 | SCOTT | 3000 | 2 |
| 7566 | JONES | 2975 | 3 |
| 7698 | BLAKE | 2850 | 4 |
| 7782 | CLARK | 2450 | 5 |
| 7499 | ALLEN | 1600 | 6 |
| 7844 | TURNER | 1500 | 7 |
| 7934 | MILLER | 1300 | 8 |
| 7654 | MARTIN | 1250 | 9 |
| 7521 | WARD | 1250 | 9 |
| 7876 | ADAMS | 1100 | 10 |
| 7900 | JAMES | 950 | 11 |
| 7369 | SMITH | 800 | 12 |
ROW_NUMBER ():-
SQL>select empno, ename, sal, ROW_NUMBER () OVER(ORDER BY sal) Rowno FROM emp
| EMPNO | ENAME | SAL | ROWNO |
| 7369 | SMITH | 800 | 1 |
| 7900 | JAMES | 950 | 2 |
| 7876 | ADAMS | 1100 | 3 |
| 7521 | WARD | 1250 | 4 |
| 7654 | MARTIN | 1250 | 5 |
| 7934 | MILLER | 1300 | 6 |
| 7844 | TURNER | 1500 | 7 |
| 7499 | ALLEN | 1600 | 8 |
| 7782 | CLARK | 2450 | 9 |
| 7698 | BLAKE | 2850 | 10 |
| 7566 | JONES | 2975 | 11 |
| 7788 | SCOTT | 3000 | 12 |
| 7902 | FORD | 3000 | 13 |
| 7839 | KING | 5000 | 14 |
Cumulative sum ():-
SQL>select ename,sal,deptno,sum(sal) over (order by sal) CUM_SAL from emp;
| ENAME | SAL | DEPTNO | CUM_SAL |
| SMITH | 800 | 20 | 800 |
| JAMES | 950 | 30 | 1750 |
| ADAMS | 1100 | 20 | 2850 |
| WARD | 1250 | 30 | 5350 |
| MARTIN | 1250 | 30 | 5350 |
| MILLER | 1300 | 10 | 6650 |
| TURNER | 1500 | 30 | 8150 |
| ALLEN | 1600 | 30 | 9750 |
| CLARK | 2450 | 10 | 12200 |
| BLAKE | 2850 | 30 | 15050/font> |
| JONES | 2975 | 20 | 18025 |
| SCOTT | 3000 | 20 | 24025 |
| FORD | 3000 | 20 | 24025 |
| KING | 5000 | 10 | 29025 |
Lead():-
SQL>select ename, sal, deptno, lead(deptno,1,0) over(order by deptno) from emp
| ENAME | SAL | DEPTNO | LEAD_DEPTNO |
| CLARK | 2450 | 10 | 10 |
| KING | 5000 | 10 | 10 |
| MILLER | 1300 | 10 | 20 |
| SMITH | 800 | 20 | 20 |
| ADAMS | 1100 | 20 | 20 |
| FORD | 3000 | 20 | 20 |
| SCOTT | 3000 | 20 | 20 |
| JONES | 2975 | 20 | 20 |
| ALLEN | 1600 | 30 | 30 |
| BLAKE | 2850 | 30 | 30 |
| MARTIN | 1250 | 30 | 30 |
| JAMES | 950 | 30 | 30 |
| TURNER | 1500 | 30 | 30 |
| WARD | 120050 | 30 | 40 |
| AROHA | 40 | 0 | |
Lag():-
SQL>select ename, sal, deptno, lag(deptno,1,0) over(order by deptno) from emp
| ENAME | SAL | DEPTNO | LAG_DEPTNO |
| CLARK | 2450 | 10 | 0 |
| KING | 5000 | 10 | 10 |
| MILLER | 1300 | 10 | 10 |
| SMITH | 800 | 20 | 10 |
| ADAMS | 1100 | 20 | 20 |
| FORD | 3000 | 20 | 20 |
| SCOTT | 3000 | 20 | 20 |
| JONES | 2975 | 20 | 20 |
| ALLEN | 1600 | 30 | 20 |
| BLAKE | 2850 | 30 | 30 |
| MARTIN | 1250 | 30 | 30 |
| JAMES | 950 | 30 | 30 |
| TURNER | 1500 | 30 | 30 |
| WARD | 120050 | 30 | 30 |
| AROHA | 40 | 30 | |
Rollup():-
SQL>select dname, loc, deptno from dept group by rollup (dname,loc,deptno)
| DNAME | LOC | DEPTNO |
| SALES | CHICAGO | 30 |
| SALES | CHICAGO | |
| SALES | ||
| RESEARCH | DALLAS | 20 |
| RESEARCH | DALLAS | |
| RESEARCH | ||
| MARKETING | DETROIT | 50 |
| MARKETING | DETROIT | |
| MARKETING | ||
| ACCOUNTING | NEWYORK | 10 |
| ACCOUNTING | NEWYORK | |
| ACCOUNTING | ||
| OPERATIONS | BOSTON | 40 |
| OPERATIONS | BOSTON | |
| OPERATIONS | ||
Cube():-
SQL>select dname, loc, deptno from dept group by cube(dname,loc,deptno)
| DNAME | LOC | DEPTNO |
| 10 | ||
| 20 | ||
| 30 | ||
| 40 | ||
| 50 | ||
| BOSTON | ||
| BOSTON | 40 | |
| DALLAS | ||
| DALLAS | 20 | |
| CHICAGO | ||
| CHICAGO | 30 | |
| DETROIT | ||
| DETROIT | 50 | |
| NEW YORK | ||
| NEW YORK | 10 | |
| SALES | ||
| SALES | 30 | |
| SALES | CHICAGO | |
| SALES | CHICAGO | 30 |
| RESEARCH | ||
| RESEARCH | 20 | |
| RESEARCH | DALLAS | |
| RESEARCH | DALLAS | 20 |
| MARKETING | ||
| MARKETING | 50 | |
| MARKETING | DETROIT | |
| MARKETING | DETROIT | 50 |
| ACCOUNTING | ||
| ACCOUNTING | 10 | |
| ACCOUNTING | NEW YORK | |
| ACCOUNTING | NEW YORK | 10 |
| OPERATIONS | ||
| OPERATIONS | 40 | |
| OPERATIONS | BOSTON | |
| OPERATIONS | BOSTON | 40 |