How to use AVG() function In SQL
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 w3wiki)
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.
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.