Oracle Analytic Functions

Introduction to Oracle Analytic Functions

Analytic functions in Oracle can be defined as functions similar to aggregate functions (Aggregate functions is used to group several rows of data into a single row) as it works on subset of rows and is used to calculate aggregate value based on a group of rows but in case of aggregate functions the number of rows returned by the query is reduced whereas in case of aggregate function the number of rows returned by the query is not reduced after execution.

List of Oracle Analytic Functions

Given below is the list of Oracle Analytic Functions:

1. DENSE_RANK

It is a type of analytic function that calculates the rank of a row. Unlike the RANK function this function returns rank as consecutive integers.

Example:

In this example we are going to find the rank of the column city in EMPLOYEE table.

Code:

SELECT
city,
DENSE_RANK () OVER (
ORDER BY city)
city_rank
FROM
EMPLOYEE;

Output:

As we can see in the output screen shot the ranks displayed are consecutive.

2. FIRST_VALUE

It is an analytic function as the name suggests is used to provide the value of the first row in an ordered set of rows.

Example:

In this example we are going to look into the lowest age based on city in the table employee.

Code:

select employee_id ,
age,
city,
FIRST_VALUE(age)
OVER(PARTITION BY city
ORDER BY employee_id
)FIRST_
from employee;

Output:

As we can see in the screen shot the last column shows us the lowest age for each city.

3. LAST_VALUE

It is also an analytical function which is used to get the value of the last row in an ordered set of rows.

Example:

In this example we will try to get the highest age based on the city in the employees table.

Code:

select employee_id ,
age,
city,
LAST_VALUE(age)
OVER(PARTITION BY city
ORDER BY employee_id
RANGE BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)HIGHEST_AGE
from employee;

The clause ‘RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING’ means that the window frame starts at the first row and ends in the last row of the result set.

Output:

As we can see in the screen shot the last row displays the highest age.

4. LEAD

It is a type of analytic function that allows us to access a following row from the current row based on an offset value without using self join.

Example:

In this example we are going to get the following age of the employees from the city of Delhi.

Code:

SELECT
city,
age,
LEAD(age) OVER (
ORDER BY city
) following_employee_age
FROM
employee
WHERE
city = 'Delhi';

Output:

In the screen shot the last value is null because the offset went beyond the scope of the result set.

5. LAG

It is a type of analytic function that allows us to access a prior row from the current row based on an offset value without using self join.

Example:

Code:

SELECT
city,
age,
LAG(age) OVER (
ORDER BY city
) following_employee_age
FROM
employee
WHERE
city = 'Delhi';

Output:

In the screen shot we can see that the first value of the last column is null because there was no previous row for the first row.

6. Nth Value

It is an analytic function and as the name suggests that it returns the Nth value among set of values.

Example:

In this example we will find the price of the second most expensive car based on the vehicle manufacturer name from the table vehicle.

Code:

SELECT
VEHICLE_ID,
vehicle_name,
price,
NTH_VALUE(price,2) OVER (
PARTITION BY VEHICLE_NAME
ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING
) AS second_expensive_car
FROM
vehicle;

Output:

In the screen shot we can see that the last column shows the second most expensive price for each category of vehicle name.

7. NTILE

It is an analytical function that divides an ordered set into buckets and assigns a bucket number to each row.

Example:

In this example we are going to assign a bucket number to each vehicle and price column of the vehicle to a bucket number.

Code:

SELECT
vehicle_name,
price,
NTILE(4) OVER(
ORDER BY price DESC
) bucket_number
FROM
vehicle;

Output:

As we can see in the above screen shot a number has been assigned to each row against the price column.

8. ROW_NUMBER

It is an analytical function and unlike NTILE this function assigns a unique sequential number to each row of the result set.

Example:

In this example we are assigning a row number to the result set consisting of vehicle id, vehicle name, price, city and sale when the result set is ordered by the sale column and sorted in descending order.

Code:

SELECT
ROW_NUMBER() OVER(
ORDER BY price DESC
) row_number,
vehicle_id,
vehicle_name,
city
FROM
vehicle;

Output:

As we can see in the screen shot the row number is assigned to each row of the result set.

9. RANK

It is an analytical function that is used to calculate the rank of a value in an ordered set of values. One important point that makes it different from DENSE_RANK is that the ranks from this function may not be consecutive numbers.

Example:

In this example we are going to find the rank of each vehicle based on its price in descending order.

Code:

SELECT vehicle_id, vehicle_name,
sale, RANK() OVER(ORDER BY sale desc)
RANK_NUMBER from
vehicle;

Output:

As we can see in the screen shot the rank numbers are not consecutive.

10. CUME_DIST

It is also an analytical function that is as the name suggests used to calculate the cumulative distribution of a certain value among a set of values.

Example:

In this example we are going to get the sale percentile for each vehicle.

Code:

SELECT
vehicle_id,
sale,
ROUND(cume_dist() OVER (ORDER BY sale DESC) * 100,2) || '%' cumulative_dist
FROM
vehicle;

Output:

As we can see in the screenshot 22 percentile of vehicles have sale of more than 500.

11. PERCENT_RANK

It is an analytical function that as the name suggests is used to calculate a percentage rank for a value among an ordered set of values.

Example:

In this example we will calculate the percent rank for sale of each vehicle_id in table vehicle.

Code:

SELECT
ehicle_id,
sale,
ROUND(PERCENT_RANK() OVER (ORDER BY sale DESC) * 100,2) || '%' percent_rank
FROM
vehicle;

Output:

As we can see in the screen shot the percent rank range includes both zero percent and 100 percent.

This is a guide to Oracle Analytic Functions. Here we discuss the introduction to Oracle Analytic Functions along with list of analytic functions. You may also have a look at the following articles to learn more –

1. Oracle LOCK TABLE
2. IF THEN ELSE in Oracle
3. Oracle CARDINALITY
4. Oracle While Loop