How to use Basic Approach In SQL

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

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.

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.