Window functions in PL/SQL

Window functions in PL/SQL (specifically in Oracle SQL) are analytic functions that allow calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not collapse the result set; they return a result for every row while calculating based on a defined "window" of rows.

Oracle PL/SQL supports a wide range of window functions, which include ranking functions, aggregation over windows, and value-based window functions. Below, I'll explain the different types of window functions with examples.

Basic Syntax of Window Functions in PL/SQL

function_name([arguments])
OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression]
[ROWS or RANGE clause]
)

1. ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to rows within a partition or result set. It can be used to rank rows but does not allow gaps in numbering.

Example:

SELECT
employee_name,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM
employees;

This query will assign a unique row number to each employee within their department based on their salary.

1. RANK()

RANK() assigns a rank to rows within a partition, leaving gaps if there are ties.

Example:

SELECT
employee_name,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM
employees;

This query ranks employees within their departments based on salary. If two employees have the same salary, they will receive the same rank, and the next rank will be skipped (gaps).

1. DENSE_RANK()

DENSE_RANK() works similarly to RANK(), but it does not leave gaps between ranks.

Example:

SELECT
employee_name,
department_id,
salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM
employees;

This query assigns ranks to employees based on salary within departments, but unlike RANK(), it does not skip any ranks.

1. NTILE()

NTILE() divides the result set into a specified number of roughly equal buckets or groups.

Example:

SELECT
employee_name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM
employees;

This query divides the employees into 4 quartiles based on their salary.

1. LAG()

LAG() is used to access data from a previous row in the result set without using a self-join. This is useful for comparing a row with the previous row.

Example:

SELECT
employee_name,
hire_date,
LAG(hire_date, 1) OVER (ORDER BY hire_date) AS previous_hire_date
FROM
employees;

This query shows the hire date of the current employee alongside the hire date of the previous employee in the result set.

1. LEAD()

LEAD() is the opposite of LAG(). It allows you to access data from a subsequent row in the result set.

Example:

SELECT
employee_name,
hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS next_hire_date
FROM
employees;

This query shows the hire date of the current employee alongside the hire date of the next employee.

1. FIRST_VALUE()

FIRST_VALUE() returns the first value in the result set or partition, based on the ordering defined in the OVER() clause.

Example:

SELECT
employee_name,
department_id,
salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary
FROM
employees;

This query returns the highest salary within each department.

1. LAST_VALUE()

LAST_VALUE() returns the last value in the result set or partition, based on the ordering defined in the OVER() clause.

Example:

SELECT
employee_name,
department_id,
salary,
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary
FROM
employees;

This query returns the lowest salary within each department.

1. SUM()

SUM() is an aggregate function that can be used as a window function to calculate cumulative sums.

Example:

SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY salary DESC) AS cumulative_salary
FROM
employees;

This query calculates a running total of the salaries.

1. AVG()

AVG() calculates the average of values in the current window.

Example:

SELECT
employee_name,
salary,
AVG(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS avg_salary
FROM
employees;

This query calculates the average salary for employees in each department.

1. ROWS and RANGE Clauses

The ROWS and RANGE clauses allow you to define a sliding window of rows relative to the current row. They are used to specify how many rows before and after the current row to include in the calculation.

ROWS Example:

SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_sum
FROM
employees;

This calculates a moving sum that includes the current row, the row before, and the row after.

RANGE Example:

SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY salary RANGE BETWEEN INTERVAL '1000' PRECEDING AND '1000' FOLLOWING) AS salary_range_sum
FROM
employees;

This calculates the sum of salaries within a specified range (1000 units before and after the current salary).

Conclusion

Window functions in PL/SQL provide powerful tools for performing advanced calculations across rows without collapsing them into aggregate results. These functions are widely used for ranking, calculating running totals, accessing previous or next rows, and performing more advanced aggregations.