Analytics for Non Analytics

Summary

This paper gives overview of the most important analytical functions, that are often used in every day life in DSS or DW business.

Analytic Function

Analytic Function was first introduced in Oracle 8i with some new functionality in further releases. Check the links at the end for further examples and documentation.

This description is based on Oracle 10g

Feature	Description	Example
Set up	Use this script to set up the environment.	analytic_functions.sql
	The script creates two table prod and month and run all the examples	setup
Data	Check the complete data of the product table …	prods
	and the time dimension table	months
ROLLUP, CUBE	Use rollup and cube to aggregate on more than one grouping set. Use GROUPING to distinct between NULLs and additional generated rows
	Group by quarter and product, get subtotals ..	rollup
	get even more subtotals (all combinations) ..	cube
	and select the subtotal only	cube2
SUM() OVER	Compute the running sum (balance) of quantity. all products or per product	runsum
RATIO_TO_REPORT	Percentage and additive percentage …	perc
	or use a trivial way to compute percentage	perctriv
RANK	Use RANK to count top 5 months per product. Get 1,1,3 in case of ties …	rank
DENSE_RANK	or 1,1,2 with dense_rank …	morerank
ROW_NUMBER	but use row_number to get only one rank 1 in all cases	surerank
MAX() OVER MIN() OVER	Find the best month per product. Caution, you get more records in case of more then one record with max value	max
	Use row_number to eliminate this problem	suremax
LAG, LEAD	Get the value from last or next record	lastnext
RANGE	Trap again – if there are sparse records (e.g. missing data for a month you get wrong value. (i.e. value of last/next record, not of last/next month). Use range to work around it. RANGE defines a logical offset (not a row offset)	surenext
NULLS LAST, FIRST	Define the sort order of NULL as appropriate	nullfirst
RANGE	Running sum over 3 months beware: current month plus 2 preceding months	months3
	Running AVG over 3 months	runavg
INTERVAL	There are two types of interval DAY to SECOND …	dsinterval
	and YEAR to MONTH	yminterval
	A number may by converted to an interval	intkonv
CUME_DIST	Top 25% products, i.e. products whose sales are greater or equal than the sales for 75% of the other products.	top25
	percentile of the month per product	top252
NTILE	Divide the months per product in 5 groups based on qty	ntile
RANK WITHIN GROUP	What if scenario	hyprank
ROW_NUMBER	Get snapshot from sparse data	getsnap
LEFT OUTER JOIN	Get all months and all products. Outer join is not good enough. If product has no sales in a month it Is not shown.	ojoin
PARTITIONED BY	You must use the partitioned outer join to get all combinations	pojoin
KEEP	Get initial and final price per product	initprice
	Get a sample data in a GROUP BY query	sample

References

Business-Savvy SQL Oracle Magazine, March-April 2002

Search Ask Tom for “analytic function” or for “analytics rock”

Oracle Documentation 10g, 9i

Jaromir D.B. Nemec

11.10.2004

The author is a freelancer  specialized on Oracle based decision support systems. He can be reached on http://www.db-nemec.com