Pages

Tuesday, May 22, 2012

I had to write some reports for a personal application of mine last night and needed to expand beyond my usual set of analytic functions that I normally use. I thought it would be a good idea to do a quick blog post on these analytic functions.

If you've never heard of or used Oracle analytic functions please read this article first. It's by far the best article I've read at explaining what analytic functions are and how to use them. The Oracle analytic function documentation can be found here.

RATIO_TO_REPORT

RATIO_TO_REPORT compares the current value against the sum of the other set of values.
The following example shows the percentage of each employees salary when compared to the sum of their department's salary. You'll notice that you can calculate the same value using a different method (sal_dept_ratio2) which may be why I haven't seen RATIO_TO_REPORT used that often.

NTH_VALUE returns the nth row in the window clause. It's extremely important that you explicitly define the window or your values may not make sense. I'll write another post explaining this later. Update: NTH_VALUE Windowing Clause

The following example shows the 2nd highest salary in each department.

NTILE allows you to divide your values into buckets and see which bucket the current row belongs to. For educational institutions this is a very good function to let students know that they're in the top x% of the class.

Update (12-Apr-2014):NTILE vs WIDTH_BOX describes the different ways to bucket data in Oracle. It also explains why SAL 1250 exists in both buckets 2 and 3.

The following example shows the 3 tiers (or buckets) of salaries across the entire company. It allows you to easily see who's in the top 33% of salaries.