Oracle Analytical Functions

Introduction to Oracle

Oracle is a leading provider of database management systems, cloud services, and enterprise software solutions. It was founded in 1977 and has since become one of the largest technology companies in the world. Oracle's primary product is the Oracle Database, widely used by organizations of all sizes to store, manage, and retrieve their data.

In 1977 Oracle Corporation was founded in Santa Clara, California, by Larry Ellison, Bob Miner, and Ed Oates. Initially named Software Development Laboratories (SDL), the company primarily focused on developing and selling a relational database management system (RDBMS) named Oracle.

In 2009, Oracle completed the acquisition of Sun Microsystems, gaining control over Java and other vital technologies. This acquisition positioned Oracle as a major player in hardware and software.

The Oracle Database is a relational database management system (RDBMS) that offers a robust and scalable platform for handling large volumes of structured and unstructured data. It provides a comprehensive set of features for data management, including data modeling, data integrity, concurrency control, backup and recovery, and data security. The database supports SQL (Structured Query Language) for querying and manipulating data and provides a rich set of built-in functions and operators.

In addition to the Oracle Database, Oracle offers a wide range of enterprise software products and solutions, including

• Oracle Fusion Applications: A suite of cloud-based enterprise resource planning (ERP), human capital management (HCM), and customer experience (CX) applications.
  
• Oracle Cloud Infrastructure (OCI): A comprehensive cloud computing platform that provides infrastructure as a service (IaaS), platform as a service (PaaS), and software as a service (SaaS) offerings.
  
• Oracle Middleware: A collection of software products and tools that enable application and service integration, development, and management.
  
• Oracle E-Business Suite: An integrated suite of business applications for managing various business functions, including finance, supply chain, manufacturing, and customer relationship management.
  
• Oracle Java: Oracle is also the steward of the Java programming language and provides Java development tools, runtime environments, and enterprise frameworks.
  

Oracle Analytical Functions

Oracle provides powerful analytical functions that enable you to perform advanced calculations and analysis on your data within a SQL query. These functions operate on a group of rows and return a single result for each row in the query result set. Some commonly used Oracle analytical functions include:

• RANK: Assigns a rank to each row within the query result set based on the specified criteria.
• DENSE_RANK: Similar to the RANK function, it assigns consecutive ranks without gaps.
• ROW_NUMBER: Assigns a unique number to each row within the query result set.
• LAG: Returns the value from a previous row in the result set based on a specified offset.
• LEAD: Returns the value from a subsequent row in the result set based on a specified offset.
• FIRST_VALUE: Returns the value from the first row in the result set.
• LAST_VALUE: Returns the value from the last row in the result set.
• NTILE: Divides the result into specified numbers of equal-sized groups and assigns a group number to each row.
• SUM, AVG, MIN, MAX: These aggregate functions can also be used as analytical functions by combining them with the OVER clause, allowing you to calculate cumulative sums, moving averages, and more.

Rank

The RANK function assigns a rank to each row within a query result set based on the specified criteria. It can be helpful in scenarios where you want to determine the relative position of a row compared to others in terms of a particular ordering.

The basic syntax of the RANK function is as follows:

The following components are part of the Rank Function:

• RANK( ): This is the function itself. It doesn't require any arguments within the parentheses.
• OVER: This keyword indicates that the ranking operation is performed over a specific window of rows.
• PARTITION BY: This clause is optional and allows you to divide the rows into partitions based on one or more columns. The RANK function will then assign separate ranks within each partition.
• ORDER BY: This clause specifies the column(s) based on which the ranking is determined. The rows are ordered in ascending (ASC) or descending (DESC) order according to the specified expression.

Here's an example that demonstrates the usage of the RANK function:

In this example, the query retrieves the employee name, department, and salary and assigns a rank to each employee's salary within their respective department. The ORDER BY salary DESC specifies that the ranking is based on the salary in descending order.

The result would include the columns employee_name, department, salary, and salary_rank, where salary_rank represents the rank of each employee's salary within their department.

Note: If multiple rows have the same values and are assigned the same rank, the subsequent rank will be skipped. For example, if two employees have the highest salary, they will be assigned a rank of 1, and the next rank will be 3, not 2.

Dense_Rank

The Oracle analytical function DENSE_RANK is similar to the RANK function but assigns consecutive ranks to rows without any gaps. If multiple rows have the same values and are assigned the same rank, the next rank is not skipped.

The syntax for the DENSE_RANK function is similar to the RANK function:

Following is the list of components which is a part of the Dense_Rank Function:

• DENSE_RANK(): This is the function itself. It doesn't require any arguments within the parentheses.
• OVER: This keyword indicates that the ranking operation is performed over a specific window of rows.
• PARTITION BY: This clause is optional and allows you to divide the rows into partitions based on one or more columns. The DENSE_RANK function will assign separate ranks within each partition.
• ORDER BY: This clause specifies the column(s) based on which the ranking is determined. The rows are ordered in ascending (ASC) or descending (DESC) order according to the specified expression.

Here's an example that demonstrates the usage of the DENSE_RANK function:

In this example, the query retrieves the employee name, department, and salary and assigns a dense rank to each employee's salary within their respective department. The ORDER BY salary DESC specifies that the ranking is based on the salary in descending order.

The result would include the columns employee_name, department, salary, and salary_dense_rank, where salary_dense_rank represents the dense rank of each employee's salary within their department.

Note: Unlike the RANK function, the DENSE_RANK function ensures that the ranks are consecutive without gaps, even if multiple rows have the same values and receive the same rank.

Row_Number

The ROW_NUMBER function in Oracle is an analytical function that assigns a unique sequential number to each row in the result set of a query. Unlike the RANK and DENSE_RANK functions, the ROW_NUMBER function does not consider any specific ordering criteria. It simply assigns a unique number to each row based on its position in the result set.

The basic syntax of the ROW_NUMBER function is as follows:

Following is the list of components of the Row_Number Function:

• ROW_NUMBER(): This is the function itself. It doesn't require any arguments within the parentheses.
• OVER: This keyword indicates that the row numbering operation is performed over a specific window of rows.
• ORDER BY: This clause specifies the column(s) based on which the rows are ordered. The rows are ordered in ascending (ASC) or descending (DESC) order according to the specified expression.

Here is the example which shows the usage of the ROW_NUMBER Function

In this example, the query retrieves the employee name, department, and salary and assigns a unique row number to each employee based on their salary in descending order.

The result would include the columns employee_name, department, salary, and row_number, where row_number represents the sequential number assigned to each row.

Note: The ROW_NUMBER function provides no ranking or dense ranking functionality. It simply assigns a unique number to each row based on its position in the result set. Multiple rows with the same values will still receive distinct row numbers.

LAG Function

The LAG function in Oracle is an analytical function that allows you to access the value of a specific column from a previous row within the result set. It can calculate the difference or change between the current and previous rows.

The basic syntax of the LAG function is as follows:

Following are the components of the LAG Function

• LAG: This is the function itself.
• column_expression: This specifies the column whose value you want to retrieve from the previous row.
• Offset: This optional parameter indicates how many rows back you want to look for the previous value. The default is 1, meaning the previous row.
• default_value: This is an optional parameter specifying the value to return if no previous row is available. If not provided, it returns NULL by default.
• OVER: This keyword indicates that the LAG operation is performed over a specific window of rows.
• ORDER BY: This clause specifies the column(s) based on which the rows are ordered. It defines the order in which the LAG function evaluates the rows.

Following is the code by which we can show the usage of the LAG function:

In this example, the query retrieves the order date and total amount and uses the LAG function to retrieve the total amount from the previous order. The ORDER BY order_date clause specifies that the LAG function should evaluate the rows based on the order date.

The result would include the columns order_date, total_amount, and previous_amount, where previous_amount represents the total amount from the previous order.

Note: If no previous order is available (for the earliest order in the result set), the LAG function will return the default value specified (in this case, 0).

LEAD Function in Oracle

The LEAD function in Oracle is an analytical function that allows you to access the value of a specific column from a subsequent row within the result set. It can calculate the difference or change between the current and next rows.

The basic syntax of the LEAD function is as follows:

The following are the main components of the Lead Function:

• LEAD: This is the function itself.
• Column_expression: This specifies the column whose value you want to retrieve from the next row.
• Offset: This optional parameter indicates how many rows ahead you want to look for the next value. The default is 1, meaning the next row.
• Default_value: This optional parameter specifies the value to return if no next row is available. If not provided, it returns NULL by default.
• OVER: This keyword indicates that the LEAD operation is performed over a specific window of rows.
• ORDER BY: This clause specifies the column(s) based on which the rows are ordered. It defines the order in which the LEAD function evaluates the rows.

Following is the example by which we can show the usage of the LEAD function:

In this example, the query retrieves the order date and total amount and uses the LEAD function to retrieve the total amount from the following order. The ORDER BY order_date clause specifies that the LEAD function should evaluate the rows based on the order date.

The result would include the columns order_date, total_amount, and next_amount, where next_amount represents the total amount from the following order.

Note: If no next order is available (for the latest order in the result set), the LEAD function will return the default value specified (in this case, 0).

FIRST_VALUE Function

The Oracle analytical function FIRST_VALUE retrieves the value of a specific column from the first row within a result set based on a specified ordering. It allows you to access the value of a column associated with the earliest or lowest-ranked row within a given order.

The basic syntax of the FIRST_VALUE function is as follows:

Following are the components of this function

• FIRST_VALUE: This is the function itself.
• column_expression: This specifies the column whose value you want to retrieve from the first row.
• OVER: This keyword indicates that the FIRST_VALUE operation is performed over a specific window of rows.
• ORDER BY: This clause specifies the column(s) based on which the rows are ordered. The rows are ordered in ascending (ASC) or descending (DESC) order according to the specified expression.

Here's an example that demonstrates the usage of the FIRST_VALUE function:

In this example, the query retrieves the employee name, department, and salary and uses the FIRST_VALUE function to retrieve the salary from the first row based on ascending salary order.

The result would include employee_name, department, salary, and first_salary, where first_salary represents the salary value from the first row in the specified ordering.

NTILE Function

The Oracle analytical function NTILE is used to divide a result set into a specified number of equal-sized groups or buckets. It assigns a group number to each row based on the specified number of groups.

The basic syntax of the NTILE function is as follows:

The following are the components of the NTILE Function:

• NTILE: This is the function itself.
• Number_of_buckets: This specifies the desired number of equal-sized groups or buckets into which the result set should be divided.
• OVER: This keyword indicates that the NTILE operation is performed over a specific window of rows.
• ORDER BY: This clause specifies the column(s) based on which the rows are ordered. The rows are ordered in ascending (ASC) or descending (DESC) order according to the specified expression.

Here's an example that demonstrates the usage of the NTILE function:

The query retrieves the employee name, department, and salary in this example. It uses the NTILE function to divide the result set into 4 equal-sized groups based on descending salary order.

The result would include the columns employee_name, department, salary, and salary_bucket, where salary_bucket represents the group number assigned to each employee's salary based on the specified number of buckets.

The NTILE function ensures that the number of rows in each group is as equal as possible. Some groups may have one additional row if the number of rows is not evenly divisible by the specified number of buckets.

SUM, AVERAGE, MIN, MAX Function

In Oracle, the aggregate functions SUM, AVG, MIN, and MAX can also be used as analytical functions by combining them with the OVER clause. The basic syntax for using these aggregate functions as analytical functions is as follows:

Calculate the cumulative sum of sales within each department:

Find the average salary for each job title over the previous three rows:

Determine the minimum and maximum temperatures for each day within a specified range:

These examples showcase how the SUM, AVG, MIN, and MAX aggregate functions can be used as analytical functions in Oracle to perform calculations over specific windows or partitions of rows within a result set.