Oracle Analytical Functions

In this blog post, we will talk about Oracle Analytical Functions -

We will discuss the following one by one , as follows

• Meaning of Analytical functions
• Difference between Analytical and Aggregate functions
• Different types of Analytical function which are commonly used with syntax
• Restrictions of Analytical Function

Analytical Function , what it means -  The reason, some functions are called Analytic because, not only they can compute aggregate values for a specific region of data, but also can calculate the changes in the aggregate values over the time.

For example, an Aggregate function may give you an average salary and is always return one single row for a region of data. However, an Analytical function can give you a moving or incremental average and can return multiple row for a specific region of data. 

Changes in the averages over the time can be calculated using Analytical functions very easily. 

Most commonly used Analytical functions are as follows - 

• RANK
• DENSE_RANK
• PERCENT_RANK
• LAG
• LEAD
• FIRST_VALUE and LAST_VALUE

RANK -  Rank calculates the Rank of the data in a specified result set. Lets use Rank to find the Rank of a particular salary in employees table. See the screenshot -

Now lets try finding Rank of all the rows within each department. See, for each department, function has returned a specific rank. If employees being in the same department, gets the same salary, they will get the same rank. see, for employees 128 and 136, both of them get rank -2. 

Another thing to notice, see in case of Department - 50, Employee - 135 got the Rank -4, but not Rank -3. Rank 3 is skipped. Similarly, Employee - 191 got the Rank - 6 but not Rank -5. Rank -5 is skipped.

Partition_by clause, is used to segregate departments here ( partition by department ) and order by clause is used to sort on the basis of salary. However, in general syntax, both partition by and order by clause are optional.

DENSE_RANK - Dense Rank will find the Rank , same as Rank does, but in case of Dense Rank, no Rank is skipped. Lets see the example, then we will discuss -

Here, Employee - 135 got the Rank -3, and Employee - 191 got the Rank - 4. No Ranking Number is skipped. That's the difference between Rank and Dense_Rank.

PERCENT_RANK - Percent Rank is another form of Rank or Dense Rank, just it returns a cumulative distribution. Lets see the example..

LAG and LEAD - LAG will give you the previous row and LEAD will return the row after the current row. Lets see the example, it will be clear -

Here the format used as LAG ( value_expression, offset, default ) over ( partition by clause order by clause )

LAG has displayed just the previous order date of the Agent, here ( offset = 1 ). When offset =2, it will display previous of previous order date and so on.

default is optional, otherwise in this example, you can use any default Date.
Hope this clears.

Similarly, LEAD will display the Next Order Date, depends upon your offset value. See the example -

FIRST_VALUE and LAST_VALUE - As the name suggests, they will return the first and last of the record upon which analytic function worked. Lets see the example, it is pretty much easy -

Windowing Clause -  Analytical functions syntax comes with an optional parameter, which is a window clause over(partition by clause , order by clause, window). Hence, some time, Analytical functions are also called Window functions.

Types of Windows , we can use are as follows -

1. Rows or Range between startpoint and endpoint.
2. startpoint or endpoint can be one of the following -

     a. Unbounded preceding - As name suggests, start point would be the beginning of the ordered set in consideration.
     b. Unbounded following - As name suggests, endpoint would be the end of the ordered set in consideration.
     c.  Current row - Points at the current position.
 

So, Window clause can look like ( for exampale )

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.


Default Windowing clause is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW


Lets see an example -

That's all folks. Thank you .