PL/SQL Analytic functions

Oracle PL/SQL Analytic functions are a great way to analyze data in your database. Oracle PL/SQL analytic functions are a set of functions that perform calculations on a set of rows and return a single value for each row in the result set. These functions are used in conjunction with the SELECT statement to analyze data in a more sophisticated way.

With Oracle PL/SQL analytic functions, you can calculate rank, density, cumulative distribution, and more.
In this article, we will take a look at the syntax and examples of some of the most popular Oracle PL/SQL Analytic functions.

ROW_NUMBER assigns a unique number to each row within a result set, based on the order specified in the ORDER BY clause.

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) as "Rank", last_name, salary 
FROM employees;

RANK assigns a rank to each row within a result set, based on the order specified in the ORDER BY clause. If two or more rows have the same value, they will receive the same rank.

SELECT RANK() OVER (ORDER BY salary DESC) as "Rank", last_name, salary
FROM employees;

DENSE_RANK assigns a rank to each row within a result set, based on the order specified in the ORDER BY clause. If two or more rows have the same value, the next rank will not be skipped.

SELECT DENSE_RANK() OVER (ORDER BY salary DESC) as "Rank", last_name, salary
FROM employees;

LISTAGG this function aggregates a list of values into a single value.

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY last_name)
FROM employees;

PERCENT_RANK calculates the percentage rank of a row within a group of rows.

SELECT PERCENT_RANK(4500, .05) WITHIN GROUP (ORDER BY salary, comm) "Percent-Rank" 
FROM employees;

CUME_DIST calculates the cumulative distribution of a row within a group of rows.

SELECT CUME_DIST(4500, .05) WITHIN GROUP (ORDER BY salary, comm) "Cume-Dist" 
FROM employees;

NTILE divides the result set into a specified number of groups or “tiles.”

SELECT NTILE(3) OVER (ORDER BY salary DESC) as "Quartile", last_name, salary
FROM employees;

LAG() and LEAD(): accesses data from a previous or next row in the same result set

SELECT last_name, salary, LAG(salary, 1) OVER (ORDER BY last_name) as "Previous Salary"
FROM employees;

These are just a few examples of the many analytic functions available in PL/SQL. Each function can be customized and combined with others to suit specific needs and provide more detailed analysis of your data.