Window Functions in PL/SQL

A window function is sometimes referred to as an “Analytic Function“. It is generally used to perform calculations on some specific set of rows related to each row in our result set. There are some real-world applications of the window function, they are sales analysis, inventory management, time series analysis, and so on. In short, the window function provides an analytical insight into the data. We can optimize our operations.

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

A Windows function 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.
• PPARTITION 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() )

Let’s set up an Environment:

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

Aggregate Window Function in PL/SQL

The functions that perform calculations like sum and average across the rows in our table are related to the current row.

1. SUM() – Returns the total value for a column without our present window.
2. AVG() – Returns the average value of a column within our present window.
3. COUNT() – Returns the total number of rows within our present window.
4. MAX() – Returns the maximum value in our present windows.
5. 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.

Ranking Window Functions in PL/SQL

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

1. ROW_NUMBER(): Assigns a unique integer id to each row within a partition.
2. 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.
3. 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.

Analytic Window Functions in PL/SQL

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

1. LEAD(): It returns the value from the row that precedes the current row within the same partition.
2. 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.

Conclusion

Overall, window functions are used to perform aggregate, ranking, and analytic functions on a group of rows but they will produce a result for each row. The calculation becomes way easier with window functions. We can perform sales analysis, inventory management, and so on with the help of the window function. We have discussed analytic, aggregate, and ranking window functions. We have covered all the basic points with clear and concise examples. Now you can easily write queries related to it and can get the desired result.