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';
select distinct e.first_name, count(*) over(partition by e.first_name from employees e order by 2 desc, 1;
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 clause – where we define how our group will be returned – base 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!