EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Analytic Functions
 

Oracle Analytic Functions

Priya Pedamkar
Article byPriya Pedamkar

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:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

Oracle analytic functions 1

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:

Oracle analytic functions 2

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:

Oracle analytic functions 3

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:

Oracle analytic functions 4

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:

LAG

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:

Nth Value

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:

NTILE

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:

Oracle analytic functions 8

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:

RANK

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:

Oracle analytic functions 10

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:

Oracle analytic functions 11

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

Recommended Articles

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW