How to create a rolling average?
-
Hello There,
I am sure this question isn't as hard as I am making it but this newbie still needs some assistance.
How would I go about create a rolling / moving average? I found out that it is called 'windowing' and it will be available in SQLServer 2005 however, I need a workaround now.
For example, I have 7 exam grades, each exam is taken on a different day. For the last 5 tests find the average. If the average is below the average of ALL previous groups of 5 Grades return the set of: student_name, class_name , grade , exam_date , teacher_name for the last 5 exams.
If I have seven grades, then my first group of 5 grades would be grades 7 to 3, the second group would be grades 6 to 2, and the third group would be grades 5 to 1 and in this case there would not be a fourth group of five. I have to determine the number of groups and their averages then compare them to the lastest average and then report out the required info.
-
This was removed by the editor as SPAM
-
These are never fun in any SQL environment. Using a reporting tool, like reporting services, or crystal reports can do this easliy. But if your stuck with TSQL you could create a new variable: case when grade between 7 and 3 then 'First' when grade between 6 and 2 then 'Second' when grade between 5 and 1 then 'Third' end as Grade
Write this with your other data to a temp table, then select it back out using the With Rollup function. You'll probably get a lot of rollups you don't want, but you should be able to get close.
-
Jeniffer:
I think that the rolling average problem is really an algorithm/script coding challenge where you need multiple code objects that build into the needed script.
E.g. You need a function/sp which will recieve an exam name and then fetch the latest 5 exam results to average.
You then need another function and Sp that would do the same for all previous sets of 5 exams and store the averages in a temporary table for comparison with the latest average.
Alternatively, you could have a trigger that does the data query and average calculations (for sets of five) each time exam results are inserted and then poplates a running/rolling average table that you can access to compare with the latest average of five. This will avoid the temp table re-calculation.
In the end this problem is at the heart of database programming to handle data reporting needs.
Anthony
-
Jeniffer:
If you could post your table schema and a few rows of data, then i can write a view on it for the desired result, though this looks a bit complicated but my first guess is that it's not so bad.
Good luck,
Anand.
-
This easier to do than to post - so the short version without the pseudocode which I did not copy
This depends on the fact that you have a restricted group memebership. The maximum number of grades in a group is 5 which means that a grade can be in any one of nine groups - consider the grade with the number 7 it can appear in groups numbered (3,4,5,6,7,8,9,10,11) i.e. (-4,-3,-2,-1,GradeID,+1,+2,+3,+4)
So add an identity field seed 1 increment 1 to you original table
Create a new table Table2 with 2 fields GradeID and GroupID
Create a cursor to traverse table 1
use
for i = gradeID-4 to GradeID+4
insert GradeID,i
next
This will give you a table with n*9 rows where n is the number of grades
remove the phantom groups i.e. those with groupIDs less than 1 or greater than 'n'
this should leave you with a table of n*9-8 records
you can then join table2 with your grades table and use group functions to get what you want
-
Hi Everyone, Thanks for all the ideas and responses. I used something very close to Anthony's suggestion. Thanks Again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply