Oracle Openworld Directory Index

Vlamis Software Solutions, Inc.
Founded in 1992 in Kansas City, Missouri
A Member of Oracle Partner Program since 1995 along with various Oracle Beta Programs, including 9i
Designs and implements databases/data marts/data warehouses using RDBMS and Multidimensional tools
Specializes in Data Transformation, Data Warehousing, Business Intelligence, Oracle Financials and Applications Development
Founder Dan Vlamis is former developer at Oracle-Waltham office for Sales Analyzer Application
Oracle Certified Solutions Provider
Agenda
Answering Business Questions
Transforming Tables into Multidimensional Data Structures
Recent Changes in SQL
Analytic Functions and Families
Analytic Functions - Features and Examples
New in 9i
Summary

Answering Business Questions
Standard transactional query might ask, “When did order 84305 ship?”
The transactional query involves simple data selection and retrieval.
An analytical query might ask, “How do sales in the Southwestern region for this month compare with plan? Or with sales a year ago?”
The analytical query involves inter-row calculations, time series analysis, and access to aggregated historical and current data.

Transactional Query versus Analytical Query
Types of Data Structures
Metadata Definitions and Objects
Measures (Multi-dimensional) = Facts (relational)
Sales units or dollars, unit cost
Dimensions = identify and categorize data
Product, Geography, Time, Sales Channel
Level = position in hierarchy of a dimension
Week rolls into Quarter, which rolls into Year
Attributes = supplementary info about a dimension member
Color, telephone number, size, shape, weight
Analytic Function Families
Ranking family - RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, ROW_NUMBER, and NTILE functions.
Moving Aggregate family - SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE
Reporting Aggregate family – SUM, AVG, MIN, MAX, COUNT (with/without DISTINCT), VARIANCE, STDDEV, RATIO_TO_REPORT
LAG/LEAD family
Linear Regression family - slope, intercept, correlation coefficient
Inverse Percentile family - PERCENTILE_DISC
Analytic Function Families—Sample Questions
Ranking (“Find the top 10 sales reps in each region.”)
Moving aggregates (“What is the 200-day moving average of our company’s stock price?”)
Period-over-period comparisons (“What is the percentage growth of January 1999 over January 1998?”)
Ratio-to-report (“What are January’s sales as a percentage of the entire year’s?”)

Analytic Function Benefits
Analytic functions are not intended to replace OLAP environments; rather, they may be used by OLAP products like Oracle's Express to:

Improved Query Speed
Enhanced Developer Productivity
Minimized Learning Effort
Standardized Syntax

Analytic Function Benefits
Analytic functions lend statistical muscle that has in the past called for joins, unions, and complex programming.
Performance is improved (sometimes significantly) because the functions are performing work that previously required self-joins and unions.
Using Analytic functions requires far less SQL coding than previously required to accomplish the same task because one SQL statement takes the place of many.
Analytic functions allow division of results into ordered groups
Analytic SQL Processing Concepts
Processing Order – 3 stages
Result Set Partitions -- unrelated to Oracle's table partitions feature
Window -- For each partition
Current Row

The Current Row is inside a Window, a Window is inside a Partition, and a Partition is inside of the Result Set.

Processing Order – 3 stages
Analytic Function Query Partitions
Query result sets are divided into ordered groups called Partitions*
Partitioning takes place after GROUP BY.
Result sets may be divided into as many partitions as makes sense for the values being derived.
Partitioning may be performed using expressions or column values.
Each result set may represent a single Partition, a few larger Partitions, or many small Partitions.
Each Partition may be represented by a sliding Window defining the range of rows used for calculations on the Current Row.
*unrelated to database table partitioning
Window and Current Row
Analytic Function Windows
Windows may be defined representing a number of physical rows
Each Window has a starting row and an ending row and may slide either direction
A moving average would slide both directions so that the averaging made sense.
Windows may represent 1 or more rows in a partition (or the entire partition).
Current Row
Each analytic function is based upon a current row within a Window (defined by OVER or ORDER BY clause)
Current Row is the reference point setting the start and end of a window
A moving average defines a window that begins in a range or rows surrounding the current row.
Standard SQL Example – three unions
SQL> -- Here is the pure SQL way with accesses to the employee table
SQL>
SQL> SELECT deptno, job, SUM(sal) sal ? Provides the dept-job totals
2 FROM emp
3 GROUP BY deptno, job
4 UNION
5 SELECT deptno, NULL job, SUM(sal) sal ? Provides the dept sub totals
6 FROM emp
7 GROUP BY deptno
8 UNION
9 SELECT TO_NUMBER(NULL) deptno, NULL job, SUM(sal) sal ? The grand total
10 FROM emp
11 ORDER BY deptno, job;
Standard SQL Example – three unions result set
DEPTNO JOB SAL
----------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025
13 rows selected.
Analytic SQL Example – same three unions
SQL>
SQL> -- Here is the new way using ROLLUP and a single table access
SQL>
SQL> SELECT deptno, job, SUM(sal) sal
2 FROM emp
3 GROUP BY ROLLUP(deptno, job);
Analytic SQL Example – same three unions result set
DEPTNO JOB SAL
----------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025
13 rows selected.

Analytic SQL Example – some complications from NULL values
SQL> -- But what if someone isn't assigned a JOB or Dept? Confusing NULLS!
SQL>
SQL> SELECT deptno, job, SUM(sal) sal
2 FROM emp
3 GROUP BY ROLLUP(deptno, job);
Analytic SQL Example – NULL values in result set
DEPTNO JOB SAL
----------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 3750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 4350
30 1250 ? Is this a subtotal?
30 9400 ? What about this row?
PRESIDENT 5000
5000
29025
15 rows selected.
Analytic SQL Example – Grouping Function
SQL>
SQL> -- Use GROUPING Function. Example uses new 8i CASE function
SQL> -- and traditional old DECODE function
SQL>
SQL> SELECT
2 CASE WHEN GROUPING(deptno) = 1
3 THEN 'All Depts'
4 WHEN (GROUPING(deptno) = 0 AND deptno IS NULL )
5 THEN 'No Dept'
6 ELSE TO_CHAR(deptno) END AS deptno,
7 DECODE(GROUPING(job),
8 1, 'All Jobs',
9 0, NVL(job,'No Job')) AS job,
10 SUM(sal) sal
11 FROM
12 emp
13 GROUP BY
14 ROLLUP(deptno,job);
Analytic SQL Example – Grouping Function Result Set
DEPTNO JOB SAL
---------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 All Jobs 3750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 All Jobs 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 4350
30 No Job 1250
30 All Jobs 9400
No Dept PRESIDENT 5000
No Dept All Jobs 5000
All Depts All Jobs 29025
15 rows selected.
Analytic SQL Example – Cube Function
SQL>
SQL> -- Example using CUBE
SQL>
SQL> SELECT deptno, job, SUM(sal) sal
2 FROM emp
3 GROUP BY CUBE(deptno, job);
Analytic SQL Example – Cube Function Result Set
DEPTNO JOB SAL
---------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
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
PRESIDENT 5000
SALESMAN 5600
29025
18 rows selected.
Analytic SQL Example – Cube Function Subtotal and Total only
SQL>
SQL> -- And if we only wanted the subtotal and grand total lines
SQL>
SQL> SELECT
2 DECODE(GROUPING(deptno),1,'All Depts',deptno) deptno,
3 DECODE(GROUPING(job),1,'All Jobs',job) job,
4 SUM(sal) sal
5 FROM
6 emp
7 GROUP BY
8 CUBE(deptno, job)
9 HAVING
10 GROUPING(deptno) = 1 OR GROUPING(job) = 1;
Analytic SQL Example – Cube Function Subtotal and Total only Result Set
DEPTNO JOB SAL
---------- --------- ---------
10 All Jobs 8750
20 All Jobs 10875
30 All Jobs 9400
All Depts ANALYST 6000
All Depts CLERK 4150
All Depts MANAGER 8275
All Depts PRESIDENT 5000
All Depts SALESMAN 5600
All Depts All Jobs 29025
9 rows selected.
Rollup and Cube Limitations
Total Rows created during GROUP BY operation.
Total Rows can be filtered with HAVING operation.
Total Rows sorted by ORDER BY operation.
WHERE clause does not apply to Total Rows.

Analytic SQL Example – Rank Function
SQL>
SQL> -- Rank example
SQL>
SQL> SELECT ename, sal,
2 RANK() OVER (ORDER BY sal DESC NULLS LAST) AS null_rank_last,
3 RANK() OVER (ORDER BY sal DESC NULLS FIRST) AS null_rank_first
4 FROM emp;
Analytic SQL Example – Rank Function Result Set
ENAME SAL NULL_RANK_LAST NULL_RANK_FIRST
---------- --------- -------------------------- ---------------------------
KING 14 1
JONES 3000 1 2
SCOTT 3000 1 2
FORD 3000 1 2
BLAKE 2850 4 5
CLARK 2450 5 6
ALLEN 1600 6 7
TURNER 1500 7 8
MILLER 1300 8 9
WARD 1250 9 10
MARTIN 1250 9 10
ADAMS 1100 11 12
JAMES 950 12 13
SMITH 800 13 14
14 rows selected.
Analytic SQL Example – PARTITION BY clause
SQL>
SQL> -- Rank within department example (No skipping numbers i.e. DENSE)
SQL>
SQL> SELECT deptno, ename, sal,
2 DENSE_RANK() OVER (ORDER BY sal DESC) AS overall_rank,
3 DENSE_RANK() OVER (PARTITION BY deptno
4 ORDER BY sal DESC) AS dept_rank
5 FROM emp
6 ORDER BY deptno, sal DESC;
Analytic SQL Example – PARTITION BY clause Result Set
DEPTNO ENAME SAL OVERALL_RANK DEPT_RANK
----------- ---------- --------- ------------------------ ------------------
10 KING 5000 1 1
10 CLARK 2450 4 2
10 MILLER 1300 7 3
20 JONES 3000 2 1
20 FORD 3000 2 1
20 SCOTT 3000 2 1
20 ADAMS 1100 9 2
20 SMITH 800 11 3
30 BLAKE 2850 3 1
30 ALLEN 1600 5 2
30 TURNER 1500 6 3
30 WARD 1250 8 4
30 MARTIN 1250 8 4
30 JAMES 950 10 5
14 rows selected.
Analytic SQL Example – RATIO_TO_REPORT function
SQL>
SQL> -- Want to see what percentage of payroll each person is in their dept?
SQL>
SQL> SELECT
2 deptno, ename, sal,
3 RATIO_TO_REPORT(sal)
4 OVER (PARTITION BY deptno) AS pct_of_dept
5 FROM
6 emp
7 ORDER BY
8 deptno, sal DESC;
Analytic SQL Example – RATIO_TO_REPORT function Result Set
DEPTNO ENAME SAL PCT_OF_DEPT
----------- ---------- --------- -----------
10 KING 5000 .57
10 CLARK 2450 .28
10 MILLER 1300 .15
20 JONES 3000 .28
20 SCOTT 3000 .28
20 FORD 3000 .28
20 ADAMS 1100 .10
20 SMITH 800 .07
30 BLAKE 2850 .30
30 ALLEN 1600 .17
30 TURNER 1500 .16
30 WARD 1250 .13
30 MARTIN 1250 .13
30 JAMES 950 .10
14 rows selected.
Analytic SQL Example – In-Line Views and Top-N Statements
SQL>
SQL> -- Top-N example - get top two salary earners; notice ties are split
SQL>
SQL> SELECT *
2 FROM (SELECT ename, sal
3 FROM emp
4 ORDER BY sal DESC) emp
5 WHERE rownum < 3;
Analytic SQL Example – In-Line Views and Top-N Statements Result Set
ENAME SAL
---------- ---------
KING 5000
JONES 3000
Analytic SQL Example – In-Line Views and Top-N Statements Revised
SQL>
SQL> -- And to get the top 2 salary earners in each group - ties not split
SQL>
SQL> SELECT deptno, ename, sal
2 FROM (SELECT deptno, ename, sal,
3 RANK() OVER (PARTITION BY deptno
4 ORDER BY sal DESC) AS dept_rank
5 FROM emp) emp
6 WHERE dept_rank < 3;
Analytic SQL Example – In-Line Views and Top-N Statements Revised Result Set
DEPTNO ENAME SAL
----------- ---------- ---------
10 KING 5000
10 CLARK 2450
20 JONES 3000
20 SCOTT 3000
20 FORD 3000
30 BLAKE 2850
30 ALLEN 1600
7 rows selected.
"Top N" Queries using RANK/DENSE_RANK
"Top N" queries may be solved easily by using RANK or DENSE_RANK in dynamic view (query in FROM clause).
NULLs are treated like normal values and for ranking are treated as equal to other NULLs.
The ORDER BY clause may specify NULLS FIRST or NULLS LAST.
If unspecified, NULLS are treated as larger than any other value and appear depending upon the ASC or DESC part of the ORDER BY.
Conclusions
Analytic functions lend statistical muscle that has in the past called for joins, unions, and complex programming.
Performance is improved (sometimes significantly) because the functions are performing work that previously required self-joins and unions.
Using Analytic functions requires far less SQL coding than previously required to accomplish the same task because one SQL statement takes the place of many.
Analytic functions allow division of results into ordered groups



Fill Out a Survey and Get a Chance to Win a Compaq iPAQ!

We want to know what you think! Fill out the survey that was handed out at the beginning of the session for a chance to win a Compaq iPAQ. Remember to include your name and email in the available section and we will enter your name into two daily drawings to win an iPAQ