Search code examples
mysqlgraphing

Query to get change in value day-over-day?

I have some historical data in a MySQL database, and I'm making graphs of it. Currently, my graphs look like this:

enter image description here

I'd like, however, to get the change in the value over time - i.e. show the change since the last measurement. Something like this (obviously the data isn't correct):

enter image description here

How can I write a MySQL query that gets the delta of a value when compared with the previous row?

This is my current query, with help from juergen d:

SELECT `timestamp`, total_users, total_users - @prev AS col1, @prev := total_users, FROM stats, (select @prev := 0) p GROUP BY DAY(`timestamp`), floor(HOUR(`timestamp`)/2)
Solution

  • Generally

    Use a variable that holds the last records' value to calculate the delta

    select some_column, 
       some_column - @prev as delta, 
       @prev := some_column
from your_table, (select @prev := 0) p

    SQLFiddle demo

    The actual query

    SELECT DAY(`timestamp`) as day_ts, 
       floor(HOUR(`timestamp`)/2) as hour_ts
       sum(total_users)
       sum(total_users) - @prev AS col1, 
       @prev := sum(total_users)
FROM stats, (select @prev := 0) p 
GROUP BY day_ts, hour_ts