ad
Tableau Desktop 8.2 Video Tutorials
Getting Started
Connecting to Data
Visual Analytics
Dashboards and Stories
Mapping
Calculations
Advanced Chart Types
How To
Why is Tableau Doing That?
OLAP Functions
Ø Some features for query processing
in oracle include the use of online analytical processing (OLAP) upon the
database.
Ø OLAP features are useful for data
warehousing and data mart applications.
Ø The OLAP operations are performance
enhancements.
- Top N Queries
- Group By
- CUBE
- ROLLUP
ROLLUP Option:
Ø It is a group by operation and is
used to produce subtotals at any level of the aggregation.
Ø The generated sub totals “Rolled Up”
to produce grant total.
Ø The totaling is based on a one
dimensional data hierarchy of grouped information.
Syntax:
GROUP BY ROLLUP (column 1, column 2,…)
Illustrations:
- Select Deptno, Sum(sal) From
Emp group by Rollup(Deptno);
- Select Job, Sum(sal) From Emp
Group by Rollup(Job);
- Select Job,Avg(Sal) From Emp
Group by Rollup(Job);
Passing Multiple Columns To Rollup:
Ø When multiple columns are passed to
Rollup, the Rollup, groups the rows into blocks with the same column values.
Ex:
- Select Deptno, Job, Sum(sal)
Salary From Emp Group by Rollup(Deptno, Job);
- Select Job, Deptno, Sum(sal) Salary
From Emp Group by Rollup(Job, Deptno);
- Select Job, Deptno, Avg(sal)
Average From Emp Group by Rollup(Job, Deptno);
Ø NULL Values in the output of Rollup
operations typically mean that the row contains subtotal or grant total
information.
Ø Use NVL () function for proper
meaning.
CUBE Option:
Ø It is an extension similar to
rollup.
Ø Cube allows specified set of
grouping columns and creates sub totals for all possible combinations of them.
Ø The Result of cube is a summary that
shows subtotals for every combination of columns or expressions in the group by
clause.
Ø The implementation of cube is also
called as N-dimensional cross tabulation.
Ex:
- Select Deptno, Job, Sum(sal)
Salary From Emp Group by Cube(Deptno, Job);
- Select Job, Deptno, Sum(Sal)
Salary From Emp Group by Cube(Job,
Deptno);
Applying Grouping() Function:
Ø The Grouping() Function accepts a
column and returns 0 or 1.
Ø Grouping() Function returns 1 when
the column value is NULL, and returns 0 when the column value is NOT NULL.
Ø Grouping() Function is used only
upon queries that use ROLLUP or CUBE.
Ex:
- Select Grouping(Deptno),
Deptno, Sum(sal) From Emp Group by Rollup(Deptno);
- Select Grouping(Job), Job,
Sum(sal) From Emp Group by Rollup(Job);
Grouping() with DECODE():
Ø The Decode() function can be used to
convert 1 and 0 returned through Grouping() into a meaningful output.
Ex:
- Select Decode(Grouping(deptno),
1,’ALL Departments’, Deptno) Departments, Sum(sal) From Emp Group by
Rollup(deptno);
- Select Decode(Grouping(Job),
1,’All Designations’, Job) Designations, Sum(sal) From Emp Group by Rollup(Job);
Decode() and Grouping() To Converting Multiple
Column Values:
Ex:
- Select Decode(Grouping(Deptno),
1,’All Department’, Deptno) Departments, Decode(Grouping(Job), 1,’All
Designations’, Job) Designations, Sum(sal) From Emp Group by
Rollup(Deptno, Job);
Grouping() with Decode() and CUBE:
Ex:
- Select Decode(Grouping(Deptno),
1,’All Departments’, Deptno) Departments, Decode(Grouping(Job), 1,’All
Designation’, Job) Designations, Sum(sal) From Emp Group by CUBE(Deptno,
Job);
Applying Grouping SETS Clause:
Ø The Grouping Sets clause is used to
get subtotal rows.
Ex:
- Select Deptno,
Job, Sum(sal) From Emp Group by Grouping Sets(Deptno, Job);
Grouping ID() Function:
Ø The Function is used to filter rows
using a having clause to exclude rows that do not contain a subtotal or grand
total.
Ø The Function accepts one or more
columns and returns the decimal equivalent of the grouping bit vector.
Ø The Grouping BIT Vector is computed
by combining the results or a call to the grouping() function for each column
in order.
Computing the Grouping BIT Vector:
Ø Grouping() Function returns 1 when
the column value is NULL, Else returns 0, based on this concept.
Ø Grouping_ID() returns 0, when deptno
and job are not null’s.
Ø Grouping_ID() returns 1, If Deptno
is not null and job is null.
Ø Grouping_ID() returns 2, if Deptno
is NULL and Job is not null.
Ø Grouping_ID() returns 3, if Deptno
is null and job is null.
Ex:
- Select Deptno, Job,
Grouping(deptno) GDPT, Grouping(Job) GJOB, Grouping_ID(Deptno, Job) GRPID,
Sum(sal) From Emp Group by CUBE(Deptno, Job);
Grouping_ID() and Having Clause:
Ex:
- Select Deptno, Job,
Grouping_ID(Deptno, Job) GRPID, Sum(sal) From Emp Group by CUBE(Deptno,
Job) Having Grouping_ID(Deptno, Job) > 0;
Representing Column Multiple Times in a Group
by clause:
Ø A column can be represented multiple
times in a group by clause.
Ex:
- Select Deptno, Job, Sum(sal)
From Emp Group by Deptno, Rollup(deptno, Job);
- Select Deptno, Job, Sum(sal)
From Emp Group by Deptno, Cube(Deptno, Job);
Applying Group_ID Function:
Ø The Group_ID() Function is used to
remove the duplicate rows returned by group by clause.
Ø The Group_ID() Does not accept any
parameters.
Ø If ‘N’ Duplicates exist for a
particular grouping, group_ID() returns numbers in the range 0 to N-1.
Ex:
- Select Deptno, Job, Group_ID(),
Sum(sal) From Emp Group by Deptno, Rollup(Deptno, Job);
- Select Deptno, Job, Group_ID(),
Sum(sal) From Emp Group by Deptno, CUBE(Deptno, Job);
- Select Deptno, Job, Group_ID(),
Sum(sal) From Emp Group by Deptno, Rollup(Deptno, Job) Having Group_ID() =
0;
Hierarchical or Recursive Queries
Ø Hierarchical queries are queries
that are executed upon tables that contain hierarchical data.
Ø To execute the hierarchical queries,
we need the following clauses.
START WITH:
Ø It specifies the root rows of the
hierarchy.
CONNECT BY:
Ø It is used to specify the
relationship between parent rows and child rows of the hierarchy.
WHERE:
Ø It is used to restrict the rows
returned by the query without affecting other rows of the hierarchy.
Steps Followed By Oracle:
Ø Oracle selects the root rows of the
hierarchy, which satisfy the condition of the START WITH clause.
Ø Then oracle selects the child rows
of each ROOT ROW.
Ø Each child row must satisfy the
condition of the CONNECT BY clause, with respect to one of the ROOT ROWS.
Ø Oracle selects successive
generations of child rows by identifying the relation in the CONNECT BY clause.
Ø Oracle selects children by
evaluating the CONNECT BY condition with respect to the current parent row
selected.
Ø If the query contains a where
clause, oracle removes all rows from the hierarchy that do not satisfy the
condition of the where clause.
Restrictions:
Ø They cannot be used to perform
joins.
Ø They cannot select data from a view,
whose query perform a join.
Ø If Order by clause is used, then the
rows are returned as per the specification in the order by clause.
Ø To define hierarchical queries
properly we must use the following clauses.
- START WITH Clause.
- CONNECT BY Clause.
START WITh Clause:
Ø It identifies the rows to be used as
the root of a hierarchical query.
Ø It specifies a condition that the
roots must specify.
Ø If START WITH is omitted, oracle
uses all rows in the table as ROOT rows.
Ø A START WITH Condition can contain a
subquery.
CONNECT BY Clause:
Ø This Clause specifies the
relationship between parent and child rows, in a hierarchical query.
Ø This clause contains a condition
that defines relationship.
Ø This condition can be any condition
as defined by the syntax description.
Ø Within the condition, some part of
the condition must use the PRIOR operator, which refers to the parent row.
Ø The format of PRIOR Operator is
- PRIOR Expr Comparison operator Expr.
- Expr Comparison Operator PRIOR Expr.
Ø The Clause can contain other
conditions to further filter the rows selected by the query.
Ø It cannot contain a sub query.
Ex:
- Select Ename, Empno, Mgr, Job
From Emp CONNECT BY PRIOR Empno = MGR;
- Select Ename, Empno, Mgr, Job
From Emp START WITH Job =’PRESIDENT’ CONNECT BY PRIOR Empno = MGR;
- Select Ename,Empno, Mgr, Job
From Emp START WITH Ename =’KING’ CONNECT BY PRIOR Empno = MGR;
- Select Ename, Empno, Mgr, Job,
Sal From Emp START WITH Sal = 5000 CONNECT BY PRIOR Empno = MGR;
- Select Ename, Empno, Mgr, Job,
Sal From Emp START WITH Sal = (Select Max(Sal) From Emp ) CONNECT BY PRIOR
Empno = MGR;
- Select Ename, Empno, Mgr, Job,
Sal From Emp START WITH Sal = (Select Max(Sal) From Emp where Deptno =
(Select deptno from dept where Dname =’ACCOUNTING’ )) CONNECT BY PRIOR
Empno = MGR;
- Select Ename, Empno, Mgr, Job, Sal
From Emp START WITH Ename =’KING’ CONNECT BY PRIOR Empno = MGR and Job =’MANAGER’;
New Features in Hierarchical Queries:
New
Operator:
CONNECT_BY_ROOT
New
Pseudo Columns:
CONNECT_BY_ISCYCLE
CONNECT_BY_ISLEAF
New
Function:
SYS_CONNECT_BY_PATH
New
Keywords:
NOCYCLE
SIBLINGS
CONNECT_BY_ROOT Operator:
Ø CONNECT_BY_ROOT is a UNARY Operator
that is valid only in hierarchical queries.
Ø We should qualify a column with this
operator, then oracle returns the column value using data from the ROOT Row.
Ø It extends the functionality of the
CONNECT BY [PRIOR] condition of hierarchical queries.
Restriction:
Ø We cannot specify this operator in
the START WITH Condition or the CONNECT BY condition.
Ex:
- Select Ename Name,
CONNECT_BY_ROOT(Ename) Boss From Emp START WITH Empno = 7839 CONNECT BY
PRIOR Empno = MGR;
SYS_CONNECT_BY_PATH Funtion:
Ø The function returns the path or a
column value from ROOT to node, with column values separated by ‘char’ for each
row returned by CONNECT BY condition.
Ø Can work on any data type CHAR or
VARCHAR2.
Ex:
- Select Ename,
SYS_CONNECT_BY_PATH(Ename, ‘/’) “path” From Emp START WITH Ename =’KING’ CONNECT
BY PRIOR Empno = MGR;
NOCYCLE Keyword:
Ø Cycles are not allowed in a true
tree structure. But some hierarchical data may contain cycles.
Ø To allow the “START WITH … CONNECT
BY… PRIOR“ construct to work properly even if cycles are present in the data
NOCYCLE is used.
Ø The NOCYCLE parameter in the CONNECT
BY Condition causes oracle to return the rows in spite of the recursive loop.
Ex:
- Select Ename,
SYS_CONNECT_BY_PATH(Sal, ‘/’) “path”
From Emp START WITH Ename =’KING’ CONNECT BY NOCYCLE PRIOR Empno =
MGR;
SIBLINGS Keyword:
Ø The keyword is valid only when we
specify the hierarchical query using CONNECT BY Clause.
Ø ORDER SIBLINGS BY clause preserves
any ordering specified in the hierarchical query clause.
Ø The ORDER BY clause finally gets
applied on the query.
Ø ORDER SIBLINGS BY clause is
generally used when we want to order rows of siblings of the same parent.
Ex:
- Select Ename, Empno, MGR From
Emp START WITH Empno = 7839 CONNECT BY PRIOR Empno = MGR;
- Select Ename, Empno, MGR From
Emp START WITH Empno = 7839 CONNECT BY PRIOR Empno = MGR Order by Ename;
- Select Ename, Empno, MGR From
Emp START WITH Empno= 7839 CONNECT BY PRIOR Empno = MGR ORDER SIBLINGS BY
Ename;