CODEWITHSUNDEEP
sqlsql-server
A. Stam
A. Stam

Reputation: 2222

Calculate rolling summary statistics in SQL

I have a table, let's say it contains results on a test taken by students. Every test has been done by one single student, and they can take the test as often as they want. The source table might look like this:

  TestID   StudentID   Pass   Score  
 -------- ----------- ------ ------- 
  1        1           0      4      
  2        1           0      6      
  3        2           1      8      
  4        1           1      9      
  5        1           0      2

For each time the test was taken, I want to calculate summary statistics on all previous results by that student. This is what (the tail of) the result table would look like:

  TestID   StudentID   Times Taken   AvPass   AvScore  
 -------- ----------- ------------- -------- --------- 
  4        1           2             0        5        
  5        1           3             .33      6.33

I have no idea how to put this in SQL terms, let alone efficiently code it. Would greatly appreciate any help!

Upvotes: 0

Views: 169

Answers (1)

MK_
MK_

Reputation: 1169

I believe that Tim Biegeleisen's answer is not exactly what the question was referring to as it lacks the 'rolling summary' part. I got the results you look for with the following:

SELECT TestId, StudentId, COUNT(*) as TimesTaken
  , ISNULL(AVG(CAST(Pass as decimal)) OVER (PARTITION BY StudentID ORDER BY TestId ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) as AvPass
  , ISNULL(AVG(CAST(Score as decimal)) OVER (PARTITION BY StudentID ORDER BY TestId ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) as AvScore
FROM dbo.RollingSummary
GROUP BY TestId, StudentId, Pass, Score
ORDER BY TestId, StudentId

The result:

TestId  StudentId   TimesTaken  AvPass  AvScore
1   1   1   0.000000    0.000000
2   1   1   0.000000    4.000000
3   2   1   0.000000    0.000000
4   1   1   0.000000    5.000000
5   1   1   0.333333    6.333333

EDIT: This is also highly efficient as window functions are very performant.

Upvotes: 4

Related Questions