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