Analytic Functions in Oracle

Hi,

Today we discuss about Analytic function.

Oracle analytic functions calculate an aggregate value based on a group of rows and return multiple rows for each group.

1. FIRST_VALUE()

It that allows you to get the first value in an ordered set of value

The following illustrates the syntax of the Oracle FIRST_VALUE() function:

FIRST_VALUE (expression) [ {RESPECT | IGNORE} NULLS ])
OVER (
    [ query_partition_clause ] 
    order_by_clause
    [frame_clause]
)

Example:

SELECT  product_id,product_name,list_price,
FIRST_VALUE(product_name)      
OVER (ORDER BY list_price) 
first_product 
FROM      
products
WHERE      
category_id = 1;

1. LAST_VALUE()

It that allows you to get the first value in an ordered set of value

The following illustrates the syntax of the Oracle FIRST_VALUE() function:

LAST_VALUE (expression) [ {RESPECT | IGNORE} NULLS ])
OVER (
    [ query_partition_clause ] 
    order_by_clause
    [frame_clause]
)

Example:

SELECT  product_id,product_name,list_price,
LAST_VALUE(product_name)      
OVER (ORDER BY list_price) 
last_product 
FROM      
products
WHERE      
category_id = 1;

NTH_VALUE():

The Oracle NTH_VALUE() function is an analytic function that returns the Nth value in a set of values.

The following shows the syntax of the NTH_VALUE() function:

NTH_VALUE (expression, N)
[ FROM { FIRST | LAST } ]
[ { RESPECT | IGNORE } NULLS ] 
OVER (
    [ query_partition_clause ] 
    order_by_clause
    [frame_clause]
)

Example:

SELECT
    product_id,
    product_name,
    list_price,
    NTH_VALUE(product_name,2) OVER (
        ORDER BY list_price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) AS second_most_expensive_product
FROM
    products;

RANK() :

The RANK() function is an analytic function that calculates the rank of a value in a set of values.

The RANK() function returns the same rank for the rows with the same values. It adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

The RANK() function is useful for top-N and bottom-N queries.

The following illustrates the syntax of the RANK() function:

RANK()
	OVER ([ query_partition_clause ] order_by_clause)

Example:

SELECT 
	col, 
	RANK() OVER (ORDER BY col) my_rank
FROM 
	rank_demo;

Output:

DENSE_RANK() :

Introduction to Oracle DENSE_RANK() function

The DENSE_RANK() is an analytic function that calculates the rank of a row in an ordered set of rows. The returned rank is an integer starting from 1.

Unlike the RANK() function, the DENSE_RANK() function returns rank values as consecutive integers. It does not skip rank in case of ties. Rows with the same values for the rank criteria will receive the same rank values.

The following shows the syntax of DENSE_RANK():

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

Example:

SELECT
	col,
	DENSE_RANK () OVER ( 
		ORDER BY col ) 
	col
FROM
	dense_rank_demo;

Output:

PERCENT_RANK() :

The PERCENT_RANK() function is similar to the CUME_DIST() function. The PERCENT_RANK() function calculates the cumulative distribution of a value in a set of values. The result of PERCENT_RANK() function is between 0 and 1, inclusive. Tie values evaluate to the same cumulative distribution value.

The following illustrates the syntax of the Oracle PERCENT_RANK() function:

PERCENT_RANK() OVER (
    [ query_partition_clause ] 
    order_by_clause
)

Example:

SELECT 
    salesman_id,
    sales,  
    ROUND(
       PERCENT_RANK() OVER (
          ORDER BY sales DESC
       ) * 100,2) || '%' percent_rank
FROM 
    salesman_performance
WHERE 
    YEAR = 2017;

Output:

CUME_DIST() :

Sometimes, you want to pull the top or bottom x% values from a data set e.g., top 5% salesman by volume. To do this, you can use the Oracle CUME_DIST() function.

The CUME_DIST() function is an analytic function that calculates the cumulative distribution of a value in a set of values. The result of CUME_DIST() is greater than 0 and less than or equal to 1. Tie values evaluate to the same cumulative distribution value.

The following shows the syntax of the Oracle CUME_DIST() function:

CUME_DIST() OVER (
    [ query_partition_clause ] 
    order_by_clause
)

Example:

SELECT 
    salesman_id,
    sales,  
    ROUND(cume_dist() OVER (ORDER BY sales DESC) * 100,2) || '%' cume_dist
FROM 
    salesman_performance
WHERE 
    YEAR = 2017;

Output:

Thank you for Reading. Feel Free to ask you double in comment section.

Suggest us some interesting ideas about this topic.