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 ‘w3wiki’. 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 w3wiki
    ) 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:

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.

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.