CODEWITHSUNDEEP
phpmysqlsql
Michel Beyaert
Michel Beyaert

Reputation: 65

How to calculate difference between mysql-rows, kind of different

I've got one table that recieves results from a game. Each game has it's own ID and lasts only 7 days. Once a day, all the games put their results into this table (results for skill A, skill B, ... are each stored in a column).

What I need to be able to retrieve in MySQL: I need to display each game (game_id) and all it's results, and the progress between each days' result (might have gone up or down).

Something like this should be the result:

Game_id    date        result_a    progress    result_b    progress      
1234       2013-09-01  10          0           8           0
1234       2013-09-02  08          -2          6           -2
1234       2013-09-03  15          7           10          4
1234       2013-09-04  19          4           13          3
4321       2013-09-01  09          0           7           0
4321       2013-09-02  18          9           9           2
4321       2013-09-03  15          -3          11          2
4321       2013-09-04  09          -6          16          4

Any tips how to handle this easy (it might become something with around 4000 rows ...)?

http://sqlfiddle.com/#!2/70a96/4

Upvotes: 2

Views: 910

Answers (3)

AgRizzo
AgRizzo

Reputation: 5271

Try this - it worked in your sqlfiddle

SELECT g.id, g.game_id, g.registration_date as today, y.registration_date as yesterday
  ,g.result_a as result_a
  ,COALESCE(g.result_a - y.result_a,0) as progress_a
  ,g.result_b as result_b
  ,COALESCE(g.result_b - y.result_b,0) as progress_b
FROM games g
LEFT JOIN games y
  ON g.game_id = y.game_id
    AND g.registration_date > y.registration_date
WHERE NOT EXISTS 
  (SELECT *
  FROM games
  WHERE games.registration_date > y.registration_date 
    AND games.registration_date < g.registration_date)
ORDER BY g.game_id, g.registration_date, y.registration_date

If the dates are always sequential (i.e., the previous game is always yesterday, then the SQL query becomes much simpler:

SELECT g.id, g.game_id, g.registration_date as today, y.registration_date as yesterday
  ,g.result_a as result_a
  ,COALESCE(g.result_a - y.result_a,0) as progress_a
  ,g.result_b as result_b
  ,COALESCE(g.result_b - y.result_b,0) as progress_b
FROM games g
LEFT JOIN games y
  ON g.game_id = y.game_id
    AND DATEDIFF(g.registration_date, y.registration_date) = 1
ORDER BY g.game_id, g.registration_date, y.registration_date

Upvotes: 1

Styxxy
Styxxy

Reputation: 7517

Following query should result in the output you want (except of the order of columns). The trick here is to check if the previous game id is still the same (variable), and if so, subtract with the previous result data (variables) to get the result or else just return 0.

Why is the column order a little different? This is because otherwise the variables are already overridden with the current value so you will get always 0 as a result. That's why you have to set the "previous" variables after the comparison(s) and subtractions.

SELECT
    @progress_a := IF(@prevGameId = data.game_id,
                      data.result_a - @prevResultA,
                      0) AS 'progress_a',
    @prevResultA := data.result_a AS 'result_a',
    @progress_b := IF(@prevGameId = data.game_id,
                      data.result_b - @prevResultB,
                      0) AS 'progress_b',
    @prevResultB := data.result_b AS 'result_b',
    data.registration_date,
    (@prevGameId := data.game_id) AS 'game_id'
FROM (SELECT
          game_id,
          result_a,
          result_b,
          registration_date
      FROM games
      ORDER BY game_id, registration_date
     ) AS data
JOIN (SELECT
          @prevGameId := NULL,
          @prevResultA := NULL,
          @prevResultB := NULL
     ) dummy

The last JOIN is necessary to initialise the variables (if you can only fire 1 query). Otherwise you can use SET statements to initialise the variables (before the SELECT).

The SELECT within the FROM is necessary since the records of each game aren't guaranteed to be grouped. So I make sure they are ordered together and sorted on the registration date. Advantage is, if you cannot guarantee that each registration date (per game) is unique, that you can group even each game/registration_date.

Note: if you experience serious performance issues (shouldn't be, I think), you'll have to indeed either process it in the application or calculate the progress when you insert the data (store the "progress" data redundantly).

Upvotes: 1

Manikandan Sigamani
Manikandan Sigamani

Reputation: 1984

If you can work in PHP before committing this value to the DB, you can have all the values for the entire week and you can loop through each data, and then update the progress based on the difference between the current and the previous, and then update the table with the new progress values.

Please pardon me for writing just a pseudocode, as I m not good at PHP

//Get the values from the Database, only the columns, game_id, date, result_a, result_b
for(i=0;all values)
{
   progress[i] = result[i]- result[i-1]; 
}

//finally save the result as you have prgress value for all i's

Upvotes: 1

Related Questions