Analytical SQL

1
Analytical SQL

• Dan Hotka
• Author/Instructor/Oracle Expert

2
(No Transcript)
3
Dan is a Training Consultant

• 2-day Hands-on Workshops
• SQL Statement Tuning Tips and Techniques
• Advanced SQL Tuning Tips and Techniques
• Oracle/Unix Scripting Techniques
• Power User Courses
• Intro to Oracle for Business Intelligence Users
• Advanced Oracle for Business Intelligence Users
• 5-day Oracle Courses (using Element-K Course
  Materials)
• Check my website for a current list of offerings
• 1-day Seminars A Close Look at Oracle
• Oracle Block and Index Internals
• Variety of Tuning Tips
• Oracle New Features
• Register for my quarterly Newsletter

4
Agenda

• Introduction to Analytical SQL
• How it works
• Sample Database
• Syntax
• Rank Functions
• Partitioning Functions
• Percentiles Function
• Physical and logical offset Functions

5
Intro to Analytical SQL

• Introduced in Oracle9i
• Used with GROUP BY
• New Features
• Composit3 columns
• Concatenated groupings
• New Functions
• Grouping_ID
• Group_ID

6
Intro to Analytical SQL

• Utilizes intermediate result sets (inline views)
• This ppt features RANK and Partition
• Useful for a variety of reports including
  cross-tabular
• Analytical SQL is applied to the result set
• After the WHERE clause, BEFORE the ORDER BY
  clause
• Multiple Rows per group (differs from Aggregate
  functions)
• Creates an additional column in the result set

7
Inline View
8
Inline View
9
Intro to Analytical SQL
  SQLgt get query1 1 select sales_person,
sum(sales_amt), 2 RANK() OVER (order by
sum(sales_amt) desc) as RANK 3 from sales
group by sales_person SQLgt /   SALES_PERS
SUM(SALES_AMT) RANK
---------- --------------
----------
Matt 142500 1
Jeff
37000 2
Greg 27000
3
Kevin 10000 4
Stan
10000 4
 
10
Intro to Analytical SQL
SQLgt get query2 1 select sales_person,
sum(sales_amt), 2 RANK() OVER (order by
sum(sales_amt) desc) as RANK_ID 3 from sales
4 where RANK_ID lt 4 5 group by
sales_person SQLgt / where RANK_ID lt 4
ERROR at line 4 ORA-00904 invalid column
name  
11
Intro to Analytical SQL
SQLgt get query3 1 select from 2 select
sales_person, sum(sales_amt), 3
RANK()OVER(order by sum(sales_amt) desc) as
RANK_ID 4 from sales 5 group by
sales_person) 6 where RANK_ID lt 4 SQLgt
/   SALES_PERS SUM(SALES_AMT) RANK_ID
----------
-------------- ----------
Greg 142500
1
Jeff 37000 2
Matt
27000 3  
12
How it Works

• Basic Syntax
• ltANALYTICAL FUNCTIONgt(ltparametersgt)
  OVER(ltclausegt)
• Parentheses are required, even if no parameters
  are required.
•  
• The OVER syntax identifies the function as an
  analytic function.
• The OVER syntax can have as many as 3 subclasses
  but are not required.
•  
• The 3 subclauses are
• 1.        Partitioning sub clause, splits the
  result set into groups
• 2.        Ordering sub clause, performs sorting
  as required by the function being used
• 3.        Windowing sub clause, defines the
  sliding window which is a result set relative to
  the row currently being processed
•  

13
How it works
The Database   My sample database consists of 2
tables and a total of 36 rows. SQLgt desc
customers Name
Type ------------------------------------
---------------------------- CUSTOMER_ID
NUMBER CUSTOMER_NAME
VARCHAR2(10) CUSTOMER_CITY
VARCHAR2(10)   SQLgt desc sales Name
Type
-----------------------------------
---------------------------- CUSTOMER_ID
NUMBER SALES_PERSON
VARCHAR2(10) SALES_AMT
NUMBER(6) SALES_DATE
DATE  
14
Rank Functions
SQLgt get query4.sql 1 select from 2
(select customer_name, sum(sales_amt), 3
RANK() OVER (order by sum(sales_amt) desc) as
RANK_ID 4 from sales, customers 5
where sales.customer_id customers.customer_id
6 group by customer_name) 7 order by
RANK_ID SQLgt /   CUSTOMER_N SUM(SALES_AMT)
RANK_ID
---------- -------------- ----------
Dan
105000 1
May 33000
2
Janet 30000 3
Tim
30000 3
Steve 18000 5
John
10500 6

Highest sale, Lowest Ranking
15
Rank Functions
SQLgt get query5 1 select from 2
(select customer_name, sum(sales_amt), 3
RANK() OVER (order by sum(sales_amt) desc) as
RANK_ID 4 from sales, customers 5
where sales.customer_id customers.customer_id
6 group by customer_name) 7 where RANK()
OVER (order by sum(sales_amt)) lt 5 SQLgt / where
RANK() OVER (order by sum(sales_amt)) lt 5
ERROR at line 7 ORA-30483 window functions
are not allowed here  
Only want top 5 Analytical only in SELECT FROM
clauses
16
Rank Functions
SQLgt get query6 1 select from 2
(select customer_name, sum(sales_amt), 3
RANK() OVER (order by sum(sales_amt) desc) as
RANK_ID 4 from sales, customers 5
where sales.customer_id customers.customer_id
6 group by customer_name) 7 where RANK_ID
lt 5 SQLgt /     CUSTOMER_N SUM(SALES_AMT)
RANK_ID
---------- -------------- ----------
Dan
105000 1
Marlene 33000
2
Janet 30000 3
Tim
30000 3

17
Partitioning Example

• Partitioning allows grouping based on current row
• Allows for MIN MAX per sales person,
• Not the whole table!

18
Partitioning Example
SQLgt get query7 1 select distinct
sales_person, 2 sum(sales_amt)
OVER(PARTITION by sales_person) as "Total Sales",
3 min(sales_amt) OVER(PARTITION by
sales_person) as Cheapest, 4 max(sales_amt)
OVER(PARTITION by sales_person) as Expensive 5
from sales SQLgt /   SALES_PERS Total Sales
CHEAPEST EXPENSIVE
---------- ----------- ----------
----------
Greg 142500 2500 15000
Jeff
37000 1000 11000
Kevin 10000 10000
10000 Matt
27000 1000 11000
Stan 10000
10000 10000

19
Partitioning Example
SQLgtstart query8 set pagesize 20 compute sum of
sales_amt on sales_person compute sum of
AVG_SALE on sales_person compute sum of
DIFFERENCE on sales_person break on sales_person
skip 2 select sales_person, sales_amt,
round(avg(sales_amt) OVER()) as AVG_SALE,
sales_amt - round(avg(sales_amt) OVER()) as
Difference from sales order by sales_person /   SA
LES_PERS SALES_AMT AVG_SALE DIFFERENCE
----------
---------- ---------- ----------
Greg 10000
7550 2450
15000 7550 7450

10000 7550 2450
---------- ----------
----------
sum 142500 113250 29250
 
20
Percentiles Function

• Percentiles can be
• Aggregate function using Group By
• Analytical function
• Percentile is a fractional value of the total
• 50 is the median
• 25 is lower quartile
• 75 is upper quartile
• Percentile_Cont
• returns value between 2 closest values
• Percentile_Disc
• Returns the closest of the existing values

21
Percentiles Function

• Aggregate Function

SQLgt get query9 1 select PERCENTILE_CONT(0.5)WI
THIN GROUP(ORDER BY sales_amt)AS P_CONT 2
,PERCENTILE_DISC(0.5)WITHIN GROUP(ORDER BY
sales_amt)AS P_DISC 3 from sales SQLgt /  
P_CONT P_DISC
---------- ----------

10000 10000


22
Percentiles Function

• Analytical Function

SQLgt get query10 1 select sales_person 2
,PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY
sales_amt) AS P_CONT 3 ,PERCENTILE_DISC(0.5)
WITHIN GROUP(ORDER BY sales_amt) AS P_DISC 4
from sales 5 group by sales_person SQLgt
/   SALES_PERS P_CONT P_DISC
----------
---------- ----------
Greg 10000
10000



Jeff 1000 1000



Kevin
10000 10000

23
Physical and logical offset Functions

• Sliding window relative to current row
• Can be a physical offset using ROWS
• X number of rows on either side of the current
  row
• Can be a logical offset using RANGE
• X number of different values on either side of
  the current row
• Based on the current row and a number of rows or
  values on either side

24
Physical and logical offset Functions
SQLgt get query11 1 select sales_person,
sales_amt, 2 sum(sales_amt) OVER(ORDER BY
sales_amt 3 ROWS BETWEEN 5 PRECEDING and 5
FOLLOWING) as SLIDING_SALES 4 from sales SQLgt
/   SALES_PERS SALES_AMT SLIDING_SALES
----------
---------- -------------
Jeff 1000
18500
Jeff 1000 23500
Greg
2500 27500
Greg 2500 36500
Jeff
5000 63500
Jeff 5000
72500
Greg 10000 80000

25
Physical and logical offset Functions
SQLgt get query12 1 select sales_person,
sales_amt, 2 sum(sales_amt) OVER(ORDER BY
sales_amt 3 RANGE BETWEEN 5 PRECEDING and 5
FOLLOWING) as SLIDING_SALES 4 from sales SQLgt
/   SALES_PERS SALES_AMT SLIDING_SALES
----------
---------- -------------
Greg 2500
7500
Greg 2500 7500
Greg
2500 7500
Matt 5000 15000
Jeff
5000 15000
Jeff 5000
15000
Greg 10000 120000

26
Physical and logical offset Functions

• The next 2 queries combines Physical and Logical
  offset in a single query
• Unbounded Preceding is from the beginning of the
  result set to the current row
• Unbound Following is from the current row to the
  end of the result set

27
Physical and logical offset Functions
SQLgt get query14 1 select sales_person,
sales_amt, 2 sum(sales_amt) OVER(ORDER BY
sales_amt 3 ROWS BETWEEN UNBOUNDED
PRECEDING and CURRENT ROW) as ROWS_SALES, 4
sum(sales_amt) OVER(ORDER BY sales_amt 5
RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT
ROW) as RANGE_SALES 6 from sales SQLgt
/   SALES_PERS SALES_AMT ROWS_SALES RANGE_SALES
----------
---------- ---------- -----------
Matt 1000
1000 6000
Jeff 1000 2000 6000
Jeff
1000 3000 6000
Jeff 1000 4000
6000
Jeff
1000 6000 6000

Greg
2500 13500 13500

28
Physical and logical offset Functions
SQLgt get query15 1 select sales_person,
sales_date, sales_amt, 2 ROUND(AVG(sales_amt)
OVER(ORDER BY sales_date 3 RANGE BETWEEN
INTERVAL '2' MONTH PRECEDING 4 AND
INTERVAL '2' MONTH FOLLOWING)) as MOVING_AVG 5
from sales 6 /   SALES_PERS SALES_DAT
SALES_AMT MOVING_AVG
---------- --------- ----------
----------
Stan 01-FEB-02 10000 6900
Greg
01-FEB-02 10000 6900
Greg 01-FEB-02
10000 6900
Jeff 01-MAR-02 11000 7417
Jeff
01-MAR-02 11000 7417
Greg 01-APR-02
2500 7071
Jeff 01-APR-02 1000 7071

Date Compare 2 Month Sliding Window
29
More Information

• www. Ixora .com.au
• Evdbt.com www. Sagelogix .com
• www.jlcomp.demon.co.uk
• www.TUSC.com
• Presentation downloads
• www. DanHotka.com click on Downloads

30
Summary

• Data analysis has become easier
• Combining speed of development and power of SQL
• Analytical SQL
• Procedural Steps incorporated into SQL
• More information
• Oracle9i SQL Reference Manual
• Chapter 6

31
What have we learned?

• Introduction to Analytical SQL
• How it works
• Rank Functions
• Partitioning Functions
• Percentiles Function
• Physical and logical offset Functions

32
Dan is a Training Consultant

• 2-day Hands-on Workshops
• SQL Statement Tuning Tips and Techniques
• Advanced SQL Tuning Tips and Techniques
• Oracle/Unix Scripting Techniques
• Power User Courses
• Intro to Oracle for Business Intelligence Users
• Advanced Oracle for Business Intelligence Users
• 5-day Oracle Courses (using Element-K Course
  Materials)
• Check my website for a current list of offerings
• 1-day Seminars A Close Look at Oracle
• Oracle Block and Index Internals
• Variety of Tuning Tips
• Oracle New Features
• Register for my quarterly Newsletter