Context:
- Activity has a grade
- Activities belong to a subject, and the subject_avg is simply the average of its activities grades in a determined time range
- The global_avg is the avg of many subject_avg (i.e, not to be confused with the average of all activity grades)
Problem:
- "Efficiently" calculate global_avg in variable time windows
"Efficiently" calculating subject_avg for a single subject, by accumulating the amount and grade of its activities:
| date | grade | |
|---|---|---|
| act1 | day 1 | 0.5 |
| act2 | day 3 | 1 |
| act3 | day 3 | 0.8 |
| act4 | day 6 | 0.6 |
| act5 | day 6 | 0 |
| avg_sum | activity_count | |
|---|---|---|
| day 1 | 0.5 | 1 |
| day 3 | 2.3 | 3 |
| day 6 | 2.6 | 5 |
I called it "efficiently" because if I need subject_avg between any 2 dates, I can obtain it with simple arithmetic over the second table:
subject_avg (day 2 to 5) = (2.3 - 0.5) / (3 - 1) = 0.6
Calculating global_avg:
subjectA
| avg_sum | activity_count | |
|---|---|---|
| day 1 | 0.5 | 1 |
| day 3 | 2.3 | 3 |
| day 6 | 2.6 | 5 |
subjectB
| avg_sum | activity_count | |
|---|---|---|
| day 4 | 0.8 | 1 |
| day 6 | 1.8 | 2 |
global_avg (day 2 to 5) = (subjectA_avg + subjectB_avg)/2 = (0.6 + 0.8) / 2 = 0.7
I have hundred of subjects, so I need to now: Is there any way I could pre-process the subject_avgs so that I don't need to individually calculate its averages in the given time window before calculating global_avg?