How to Compute a Moving Average in PL/SQL?

A moving average is a technique we use to analyze and determine some specific trends in our provided data. Through moving averages, we can analyze and determine trends in our data along with some other benefits like noise reduction in our data.

In this article, we are going to learn about “how to compute a moving average in PL/SQL” by understanding various methods with the help of different examples of calculating moving averages along with some clear and concise explanations.

How to Compute a Moving Average in PL/SQL?

• A moving average is a statistical technique used to analyze and determine some specific trends in our given data set.
• It creates a series of averages of different subsets of the full data set.
• We can reduce noise in our data set as well as it also helps us in making predictions by analyzing recent trends of our data.
• Some different methods we will cover in this article are mentioned below :

1. Basic/ Naive Approach
2. Using AVG() function

Let’s set up an Environment:

To understand How to Compute a Moving Average in PL/SQL we need a table on which we will perform various operations and queries. Here we will consider a table called Geeksforgeeks which contains id, name, score, and month as Columns.

Table – geeksforgeeks

1. Using Basic Approach

In this example, we are going to implement very basic or naive approach. We will iterate through each record using for – loop. While iterating we will accumulate score and create a moving average based on a specified window size or number of specified columns. It is easy to understand approach. However, it has limited error handling. It lacks some robust error handling mechanism as we have in some standard PL/SQL queries. We will overcome this in our next method.

Query:

DECLARE  
    --variable declaration
    v_nth_column NUMBER := 2;  
    v_summation NUMBER := 0;  
    v_counter NUMBER := 0;  
BEGIN 
    --starting of for loop
    FOR i IN (SELECT name,score,   
                        ROW_NUMBER() OVER (ORDER BY month) AS row_num  
                   FROM geeksforgeeks  
                ORDER BY month)  
    LOOP  
        v_summation := v_summation + i.score;  
        v_counter := v_counter + 1;  
  
        IF v_counter >= v_nth_column THEN  
            DBMS_OUTPUT.PUT_LINE('Name: ' || i.name ||', Score: ' || i.score || ', Moving Average: ' || v_summation / v_nth_column);  
            v_summation := v_summation - i.score; 
        ELSE
            DBMS_OUTPUT.PUT_LINE('Name: ' || i.name ||', Score: ' || i.score || ', Moving Average: ' || v_summation/v_counter); 
        END IF;  
    END LOOP;
    --ending loop
END; 

Output:

Basic Approach

Explanation: In the above query, we have calculated moving average by dividing the cumulative sum of scores column by the specified nth column( which is 2 in this case) . We have also specified two other variable, first one is ‘v_counter‘ and ‘v_summation’.

These variables, stores count of number of columns iterated till now and summation of score columns respectively. If the count of number of columns exceeds the specified nth column, then we will compute the moving average and display the message accordingly.

Otherwise, we will simply display the summation divided by the number of column till now iterated. In the above image, we can observe the smooth running of our query.

2. Using AVG() function

In this example, we are going to calculate the moving average using AVG() function. Unlike the previous one, we will compute the moving average keeping all the corner cases in our mind. Instead of explicitly managing cumulative sum and count of the column we will utilize AVG() function of SQL to do so. It is a more concise approach then the first one.

Query:

DECLARE 
    v_nth_column NUMBER := 3;  
BEGIN 
    FOR i IN (SELECT name,score, 
                      AVG(score) OVER (ORDER BY month  
                                             ROWS BETWEEN (v_nth_column) PRECEDING AND CURRENT ROW) AS moving_avgerage 
                 FROM geeksforgeeks) 
    LOOP 
        DBMS_OUTPUT.PUT_LINE('Name: ' || i.name ||', Score: ' || i.score || ', Moving Average: ' || i.moving_avgerage); 
    END LOOP; 
END;

Output:

Using AVG()

Explanation: In the above query, we have calculated the moving average by averaging the values of the column column based on the specified number of preceding rows ( in this case it is 3). We have used for loop to iterate over each result, printing each rows given information like name, score along with their moving average. We can refer to the image, in order to see the smooth working of our query.

Conclusion

Overall, moving average is a statistical technique of calculating some recent trends of our specified dataset. It helps in identifying trends, predictions etc. We can follow some basic approach to compute the moving average but as we have seen in the article, it has some consequences. It lacks some basic error handling. To overcome this, we have used AVG() function of SQL. Instead of explicitly managing sum or count of the columns, we can use AVG() function instead. In this article, we have covered both the approaches explaining pros and cons. Now, you have a good knowledge of computing moving average in PL/SQL. Now you can write queries related to it and can obtain the desired output.