Find growth over previous

Question

Be forewarned: I'm new to MySQL so expect stupid follow-up questions.

I am writing an application that keeps track of exam scores for students. Each student will have multiple exams taken at different times. I want to be able to calculate the change in the exam scores between two consecutive exams for the same student. Here is the basic structure of my table...

--------------------------------------------
| score_id | student_id |   date   | score |
--------------------------------------------
|         1|           1| 2011-6-1 |    15 |
|        21|           1| 2011-8-1 |    16 |
|       342|           1| 2012-3-1 |    18 |
|         4|           2| 2011-6-1 |    21 |
|        16|           2| 2011-8-1 |    20 |
|       244|           2| 2012-3-1 |    20 |
--------------------------------------------

What I would like to return from my Query is...

---------------------
| score_id | growth |
---------------------
|         1|    NULL|
|        21|       1|
|       342|       2|
|         4|    NULL|
|        16|      -1|
|       244|       0|
---------------------

It is a similar question to the on asked here , but the dates are not always a specific time apart from one another.

You have framed the question very well. It's pleasant to read. GOod job. — Mariappan Subramanian, Jul 04 '13 at 13:19

score 1 · Accepted Answer · answered Jul 04 '13 at 13:17

1

If the score ids are sequential for each student, then a simple join will do:

select s.score_id, s.score - sprev.score
from scores s left outer join
     scores sprev
     on s.student_id = sprev.student_id and
        s.score_id = sprev.score_id + 1;

I would be surprised, though, if the input data were actually so ordered. In that case, you need to find the previous student score. I think a correlated subquery is the clearest way to write this:

select score_id, score - prevscore
from (select s.*,
             (select score
              from scores s2
              where s.student_id = s2.student_id and
                    s.date > s2.date
              order by date desc
              limit 1
             ) as prevscore
      from scores s
     ) s

answered Jul 04 '13 at 13:17

Gordon Linoff

1,242,037
58
646
786

+1 for including a solution that its not based on the fact that score_id sequential for each student – Stephan Jul 04 '13 at 13:25
No, the're not sequential. I'll update my example to show that. – Christian Wimber Jul 04 '13 at 13:27
This works well. Unfortunately, I just realized that I'm keeping the date in a related table. Oh well, I'll get it working. – Christian Wimber Jul 04 '13 at 17:58
I'm trying to put this in a view, but it's throwing the 'sub-query in FROM statement' error at me. How could I separate this query into several other views so that I could rebuild it through them? – Christian Wimber Jul 04 '13 at 20:03

score 0 · Answer 2 · answered Jul 04 '13 at 13:16

This is very inefficient, not sure if it can be optimised, but i dont see how you can do what you want without doing an extra query per row thats found, but i haven't spent a lot of time thinking of optimisation routes, so this is just A solution, not THE BEST solution

SELECT score_id, (score-(SELECT score FROM table AS tbl2 WHERE tbl2.student_id=1 AND tbl2.score_id < tbl1.score_id ORDER BY tbl2.score_id DESC LIMIT 1)) AS growth FROM table as tbl1 WHERE tbl1.student_id=1

score 0 · Answer 3 · answered Jul 04 '13 at 13:23

0

try this

SELECT t1.score_id, t1.score - t2.score AS Growth
FROM Table1 t1
LEFT JOIN Table1 t2 on t1.score_id = t2.score_id + 1 and t1.student_id = t2.student_id

fiddle this

answered Jul 04 '13 at 13:23

Luis LL

2,912
2
19
21

Sam · Answer 4 · 2013-07-04T13:59:54.880

This will be a complicated query (though not impossible - see other answers). For these kind of things I would expect that reading data happens more then adding. Therefore, I would put the most complex part in the add functionality

If you have control over what your database looks like, I would suggest adding an extra column to your score database:

------------------------------------------------------------
| score_id | prev_score_id | student_id |   date   | score |
------------------------------------------------------------
|         1|          NULL |           1| 2011-6-1 |    15 |
|         2|             1 |           1| 2011-8-1 |    16 |
|         3|             2 |           1| 2012-3-1 |    18 |
|         4|          NULL |           2| 2011-6-1 |    21 |
|         5|             4 |           2| 2011-8-1 |    20 |
|         6|             5 |           2| 2012-3-1 |    20 |
------------------------------------------------------------

When adding the new score record, you can easily find the prev_score_id:

select score_id
from score
where studentId = :studentId
and   date = ( select max(date)
               from score
               where studentId = :studentId
               and   date < :date )

Now, your query becomes a lot easier:

select current.score_id, current.score - previous.score as growth
from score current
left join score previous on current.prev_score_id = previous.score_id

Edit: just noticed mysql doesn't have the fancy over partition by stuff. Edited my query so doesn't use it.

score 0 · Answer 5 · answered Jul 04 '13 at 14:25

0

Here you go,

SELECT STUDENT.SCORE_ID,TABLE1.CALSCORE FROM STUDENT LEFT OUTER JOIN
(SELECT S2.SCORE_ID,MIN(CASE WHEN S1.SCORE=S2.SCORE THEN NULL 
ELSE (S2.SCORE - S1.SCORE) END) CALSCORE
FROM STUDENT S1 INNER JOIN STUDENT S2
ON S1.student_id=S2.student_id
AND S1.DATE1 < S2.DATE1 AND S1.SCORE_ID < S2.SCORE_ID
GROUP BY S2.SCORE_ID) TABLE1 ON 
STUDENT.SCORE_ID=TABLE1.SCORE_ID

sqlfiddle

answered Jul 04 '13 at 14:25

Mariappan Subramanian

9,527
8
32
33

It returns the change from 20 to 20 as -1 – Christian Wimber Jul 04 '13 at 16:54

Find growth over previous

5 Answers5

sqlfiddle