Diving into Oracle Analytical Functions part 1/2

Hi all ! A couple of weeks ago I felt the need to research a little further on Oracle Analytical Functions. They are so powerfull, yet, only a couple of PLSQL programmer friends were into them. Before beginning this post, let me just make a brief intro.

There are 6 types of Oracle functions:

  • single-row (as the name says, return only one row.)
  • aggregate ( computes an aggregate value based on a group of rows)
  • analytical (computes an aggregate value based on a group of rows, and returns multiple rows for each group)
  • object reference ( functions to manipulate reference to objects of specified types )
  • model (used in the model clause of the select statement)
  • user defined

Depending on what you are doing, you’ll probably just work with two or three of these Oracle functions types. On my case, I work on a daily basis with Oracle Aggregate functions and Oracle Analytical functions.  What distinguishes these two types is quite straightforward. Lets look at the example bellow based in the HR schema(Oracle 11g)

Imagine you want to count the number of employees whose first name is Alexander. Typically you would issue the sql statement below to take advantage of the Oracle Aggregate function count :

 select count(*) from employees e where e.first_name like 'Alexander'; 
However, imagine that you wanted to know how many times each first name occurs in the employees table. One of simplest ways to do this could be accomplished with the Oracle Analytical function count:
select distinct e.first_name, count(*) over(partition by e.first_name
from employees e
order by 2 desc, 1;
As you have noticed the count analytical function,computed an aggregate value based on a group of rows(employees first name), and returned multiple rows for each group(for each first name, we have a count of the number of repetitions). This is a very powerfull tool as it enables you to retrieve groups of data based on certain conditions.
Now that we have seen a bit of the concepts behind analytical functions let’s check the syntax difference before going any further

Oracle Analytical Functions

Oracle Aggregate Functions

Select  function(arguments) over(analytical_clause) from …

Select function(arguments) from .

The analytical clause can be further subdivided in three clause types :

  • query partition clause – where we just state the members of our group – base syntax : partition by(expression1,expression2,…)
  • order by clausewhere we define how our group will be returnedbase syntax : order by (expression1,expression2)
  • windowing clause – where we can define a range to restrict our  results – base syntax : range between x and y

The order of use of the clauses is query_partition_clause+order_by_clause+windowing_clause. I recommend that you check the links associated with the clauses above, as they contain a complete reference for usage.

We have already seen the query partition clause in action, lets now take a look at the other two clauses. Imagine that you want to show your employees , where they stand in terms of revenue, that is, how many employees earn less than 50 euro and more  than 100euros compared to their salary. We could write a query like this :

Take the employee_id 132 named TJ for example. This selected line would tell him that he has 3 colleagues which either earn less 50 euro than his salary or 150 more than his salary. To better understand this you could try :

So our conclusion is, that in reality employee TJ doesn’t have 3 , but 2 colleagues that fall within our condition. We have to exclude the employee himself from this count !

Now that we know some theory and basic logic of analytical functions, we can go a bit further and explore the RANK function, DENSE RANK and ROW_NUMBER functions. They will be topic for the second part of this post.

Hope you have enjoyed!