Window Functions in PL/SQL

Last Updated : 24 Oct, 2024

In Oracle PL/SQL, analyzing and managing complex data relationships often involves performing calculations across sets of rows. This is where window functions, sometimes referred to as "Analytic functions," come into play. They enable powerful data analysis, such as sales forecasting, time-series analysis, and ranking without modifying the underlying data structure.

In this article, we are going to discuss about working of "Window Functions in PL/SQL". We will go through various real-world use cases with some clear and concise examples.

Window Functions in Oracle PL/SQL

A Windows function in Oracle PL/SQL performs aggregate operations, ranking functions, and analytic functions on groups of rows, but they will produce a result for each row. We will use two main clauses that will help our window function to perform operations:

OVER(): This clause will construct a window.
PARTITION BY: This will form rows into groups of rows.

We will also use the ORDER BY clause inside OVER() to arrange the resultant data into a specific order.

Syntax:

DECLARE --declare any variables here (if required)  BEGIN     FOR  i IN (         SELECT coulmn_name(s),          window_function(cloumn_01)        OVER([PARTITION BY column_01] [ORDER BY column_02]) AS dummy_column        FROM table_name;              ) LOOP         DBMS_OUTPUT.PUT_LINE('Display the Message Here');     END LOOP; END;

window function: aggregate or ranking function (like AVG() or ROW_NUMBER() )

Setting Up the Environment for Window Functions

To understand Window Functions in PL/SQL we need a table on which we will perform various operations and queries. Here we will consider a collection called geeksforgeeks which contains information such as emp_id, name department, that, and salary as Columns.

EMP_ID	NAME	DEPARTMENT	SALARY
108	Vishu	IT	60000
209	Ayush	HR	56000
110	Sumit	IT	54000
208	Niraj	HR	56000
109	Vivek	IT	65000
210	Harsh	HR	62000

1. Aggregate Window Function in Oracle PL/SQL

Aggregate window functions allow you to perform calculations like SUM, AVG, MAX, MIN, and COUNT over a specified window of rows. The functions that perform calculations like sum and average across the rows in our table are related to the current row.

SUM(): Returns the total value for a column without our present window.
AVG(): Returns the average value of a column within our present window.
COUNT(): Returns the total number of rows within our present window.
MAX(): Returns the maximum value in our present windows.
MIN(): Returns the minimum value in our present windows.

Example of Aggregate Window Functions in PL/SQL

In this example, we will calculate the average salary, maximum salary, and minimum salary for our table 'geeksforgeeks'. We will calculate all the values separately for each department.

Query:

DECLARE BEGIN     DBMS_OUTPUT.PUT_LINE('EMP_ID   | Name    | Department  | Salary | Avg_Salary | Max_Salary | Min_Salary ');     DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------');     FOR i IN (         SELECT emp_id, name, department, salary,                AVG(salary) OVER (PARTITION BY department ) AS avg_sal,                MAX(salary) OVER (PARTITION BY department ) AS max_sal,                MIN(salary) OVER (PARTITION BY department ) AS min_sal         FROM geeksforgeeks     ) LOOP      DBMS_OUTPUT.PUT_LINE(i.emp_id || '      | ' || i.name || '   |   ' || i.department || '         | ' || i.salary||' |   '||CEIL(i.avg_sal)||'    |   '||i.max_sal||'    |   '||i.min_sal); END LOOP; END;

Output:

EMP_ID	Name	Department	Salary	Avg_Salary	Max_Salary	Min_Salary
209	Ayush	HR	56000	58000	62000	56000
208	Niraj	HR	56000	58000	62000	56000
210	Harsh	HR	62000	58000	62000	56000
109	Vivek	IT	65000	59667	65000	54000
108	Vishu	IT	60000	59667	65000	54000
110	Sumit	IT	54000	59667	65000	54000

Explanation: In the above query, we have performed average, max, and min for the salary column. In this query, our window functions are AVG(), MAX(), and MIN(). We have also used partition by function to separate different departments. We can see that for each department average, maximum, and minimum salaries are the same. You can refer to the above-shown image for more clear understanding.

2. Ranking Window Functions in Oracle PL/SQL

Ranking functions like ROW_NUMBER, RANK, and DENSE_RANK assign ranks to rows within a partition. These functions can be used for ordering and ranking employees based on salary.

This function assigns a specific rank to each row based on some internal criteria.

ROW_NUMBER(): Assigns a unique integer id to each row within a partition.
RANK(): Assigns a unique integer to each value within a partition. If two rows have the same value for our specified column then they will have the same rank. It will also create gaps when they are tied.
DENSE_RANK(): It is similar to rank but it will not create gaps for the ties.

Example of Ranking Window Functions in PL/SQL

In this example, we will compute row number, rank, and dense rank for each row within a partition. We will see the working of the above functions as window functions.

Query:

DECLARE     CURSOR gfg_deatils IS         SELECT emp_id, name, department, salary,                ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary ) AS row_num,                RANK() OVER (PARTITION BY department ORDER BY salary ) AS rank,                DENSE_RANK() OVER (PARTITION BY department ORDER BY salary ) AS dense         FROM geeksforgeeks; BEGIN     DBMS_OUTPUT.PUT_LINE('EMP_ID   | Name    | Department  | Salary | Row_Number | Rank   | Dense_Rank ');     DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');     FOR i IN gfg_deatils LOOP         DBMS_OUTPUT.PUT_LINE(i.emp_id || '      | ' || i.name || '   |   ' || i.department || '         | ' || i.salary||' |   '||i.row_num||'        |   '||i.rank||'    |   '||i.dense);     END LOOP; END;

Output:

EMP_ID	Name	Department	Salary	Row_Number	Rank	Dense_Rank
208	Niraj	HR	56000	1	1	1
209	Ayush	HR	56000	2	1	1
210	Harsh	HR	62000	3	3	2
110	Sumit	IT	54000	1	1	1
108	Vishu	IT	60000	2	2	2
109	Vivek	IT	65000	3	3	3

Explanation: As explained earlier, we can see that row numbers are unique for each row in a partition. The ranks are the same for the same salaries and it also leaves a gap when two ranks are the same. The dense ranks are also the same for the same salaries but it does not leave a gap unlike in rank. We can see that for id 210 rank is 3 and the dense rank is 2. The query is very similar to the previous one. You can refer to the image for more detailed understanding.

3. Analytic Window Functions in Oracle PL/SQL

In the analytic window function in PL/SQL, we will cover two main functions i.e. LEAD() and LAG().

LEAD(): It returns the value from the row that precedes the current row within the same partition.
LAG(): It returns the value from the row that succeeds the current row within the same partition.

Example of Analytic Window Functions in PL/SQL

In this example, we will calculate the preceding and succeeding salary for each row. If there are no preceding or succeeding salaries, then we will simply display the empty space.

Query:

DECLARE     CURSOR gfg_deatils IS         SELECT emp_id, name, department, salary,                LEAD(salary) OVER (PARTITION BY department ORDER BY salary ) AS lead_salary,                LAG(salary) OVER (PARTITION BY department ORDER BY salary ) AS lag_salary         FROM geeksforgeeks; BEGIN     FOR i IN gfg_deatils LOOP         DBMS_OUTPUT.PUT_LINE('Employee ID: ' || i.emp_id || ' Name: '||i.name||', Department: ' || i.department || ', Salary: ' || i.salary || ', Lead Salary: ' || i.lead_salary || ', Lag Salary: ' || i.lag_salary);     END LOOP; END;

Output:

Employee ID	Name	Department	Salary	Lead Salary	Lag Salary
208	Niraj	HR	56000	56000	NULL
209	Ayush	HR	56000	62000	56000
210	Harsh	HR	62000	NULL	56000
110	Sumit	IT	54000	60000	NULL
108	Vishu	IT	60000	65000	54000
109	Vivek	IT	65000	NULL	60000

Explanation: In the above image, we can see that all the rows are displayed with their corresponding preceding and succeeding salaries. We can also notice that the rows that do not have any preceding and succeeding salaries are empty like in id's 210 and 109 preceding salaries are absent and similar for id's 208, 110 succeeding salaries are absent. You can refer to the image for more clear understanding.

Important Points About PL/SQL Window Functions

Unlike aggregate functions, window functions return a value for each row, performing calculations across a set of rows related to the current row, without reducing the number of rows in the output.
The OVER() clause is mandatory for window functions. It defines the window (set of rows) over which the function operates.
If neither PARTITION BY nor ORDER BY is specified inside OVER(), the window function will consider all rows as a single partition, which may not be the intended behavior.
Window functions cannot be used directly in the WHERE clause. Instead, use them in a subquery or a common table expression (CTE).

PL/SQL Functions

vishuvaishnav3001

Improve

Article Tags :

Window Functions in PL/SQL

Window Functions in Oracle PL/SQL

Setting Up the Environment for Window Functions

1. Aggregate Window Function in Oracle PL/SQL

Example of Aggregate Window Functions in PL/SQL

2. Ranking Window Functions in Oracle PL/SQL

Example of Ranking Window Functions in PL/SQL

3. Analytic Window Functions in Oracle PL/SQL

Example of Analytic Window Functions in PL/SQL

Important Points About PL/SQL Window Functions

Similar Reads