Examples of Computing Differences Between Columns on Different Rows
In this, we will see various examples related to how to compute a difference between two columns on different rows in PL/SQL.
Example 1: Computing the difference between two scores in different rows without any condition.
As specified earlier, we will use a cursor to store our table’s necessary information such as id, score, and difference. We will join the table with the table itself but with the row with an id+1. Through this, we can easily compute the difference between the two scores. Then, we will enter our main block and open our created cursor and we will display all the information stored in the cursor stored earlier. At the end, we will close our cursor.
Query:
DECLARE
--defining our cursor and storing the tables information along with is delta value.
CURSOR delta_info IS
SELECT g1.id, g1.score, g1.score - g2.score AS delta
FROM w3wiki g1
-- joining the table geeksfirgeeks with current row with a row with id+1
JOIN w3wiki g2 ON g1.id = g2.id + 1;
-- defining our variables
v_id NUMBER;
v_score NUMBER;
v_delta NUMBER;
BEGIN
-- displaying the column headeres
DBMS_OUTPUT.PUT_LINE('id | score | delta');
DBMS_OUTPUT.PUT_LINE('------------------');
--opening the cursor
OPEN delta_info;
LOOP
FETCH delta_info INTO v_id, v_score, v_delta;
EXIT WHEN delta_info%NOTFOUND;
-- displaying the necessary infromation with delta value
DBMS_OUTPUT.PUT_LINE(v_id||' |'|| v_score || ' | ' || v_delta );
END LOOP;
--closing the cursor
CLOSE delta_info;
END;
Output:
id | Score | delta |
---|---|---|
2 | 300 | 100 |
3 | 500 | 200 |
4 | 800 | 300 |
5 | 1200 | 400 |
Explanation: In the above image, as we can see there is no data for id 1. This is because there is no previous value to id 1. So we have nothing to calculate the delta between two scores of different rows. We can also see that all the scores are displayed with their corresponding delta values. As we know that id 1 has a 200 score and id 2 has a 300 score. So its delta difference is 100 and we can see that all the rows have done the same calculation for their delta values. You can refer to the above-displayed output to have a more clear understanding of the output.
Example 2: Computing difference between two scores in different rows with a specified condition (odd id’s).
We will use a similar approach to calculate the delta. The difference is that, in this example, we will specify a condition in the statement. We will calculate the delta for some specific id’s such as odd id’s (say 1,3,5). Lets see the query for more clearer understanding.
Query:
DECLARE
--defining our cursor and storing the tables information along with is delta value.
CURSOR delta_info IS
SELECT g1.id, g1.score, g1.score - g2.score AS delta
FROM w3wiki g1 ,w3wiki g2 WHERE g1.id = g2.id + 2 and g1.id IN (1,3,5);
-- joining the table geeksfirgeeks with current row with a row with id+1
-- defining our variables
v_id NUMBER;
v_score NUMBER;
v_delta NUMBER;
BEGIN
-- displaying the column headeres
DBMS_OUTPUT.PUT_LINE('id | score | delta');
DBMS_OUTPUT.PUT_LINE('------------------');
--opening the cursor
OPEN delta_info;
LOOP
FETCH delta_info INTO v_id, v_score, v_delta;
EXIT WHEN delta_info%NOTFOUND;
-- displaying the necessary infromation with delta value
DBMS_OUTPUT.PUT_LINE(v_id||' |'|| v_score || ' | ' || v_delta );
END LOOP;
--closing the cursor
CLOSE delta_info;
END;
Output:
id | score | delta |
---|---|---|
3 | 500 | 300 |
5 | 1200 | 700 |
Explanation: In the above query, we have incremented the id with +2. This will help us to get the odd id’s only. As we can see in the output table, for id 1 score is 200, and for id 3 score is 500. Their difference is 300. Same for id 5, (1200-500 ) 700 is displayed. We have made a little modification in our query, in lines 5 and 6. You can refer to the output table and query, for more clearer understanding.
Compute a Difference(Delta) Between Two Columns on Different Rows in PL/SQL
Calculating the difference between two columns is useful in data analysis. It can help identify trends in various sectors such as finance, healthcare, and inventory management. It can also be used to analyze day-to-day changes in stock prices, and medical diagnoses, and identify bottleneck products.
In this article, we are going to explore different ways to compute a difference(delta) between two columns on different rows in PL/SQL. We will go through various real-world use cases with some clear and concise examples.