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 w3wiki;
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:

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.

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.