calculate difference of value for consecutive days for all objects in sql

I have a table with an object, index, date and value:

+--------------+-------+------------+------------+
| object       | index | date       | value      |
+--------------+-------+------------+------------+
| 32           | 1     | 2011-02-25 | 2100000000 | 
| 32           | 2     | 2011-02-25 | 27800000   | 
| 32           | 3     | 2011-02-25 | 5700000    | 
| 32           | 1     | 2011-02-26 | 2100000000 | 
| 32           | 2     | 2011-02-26 | 28700000   | 
| 32           | 3     | 2011-02-26 | 5800000    | 
| 32           | 1     | 2011-02-27 | 2200000000 | 
| 32           | 2     | 2011-开发者_如何学C02-27 | 29500000   | 
| 32           | 3     | 2011-02-27 | 5900000    | 
+--------------+-------+------------+------------+

and I need a query with the difference of the value between two consecutive days for every objectindex so something like this

+--------------+-------+------------+------------+
| object       | index | date       | value_24h  |
+--------------+-------+------------+------------+
| 32           | 1     | 2011-02-26 | 0          | 
| 32           | 2     | 2011-02-26 | 0          | 
| 32           | 3     | 2011-02-26 | 100000     | 
| 32           | 1     | 2011-02-27 | 100000000  | 
| 32           | 2     | 2011-02-27 | 800000     | 
| 32           | 3     | 2011-02-27 | 100000     | 
+--------------+-------+------------+------------+

Is this possible in mysql or do I better calculate these values in my program (python). Or would a different/better table layout help?

Thanks,

Michael

SELECT t2.object,t2.index,t2.date,t2.value-t1.value
FROM table t1, table t2
    WHERE t1.object=t2.object AND t1.index=t2.index
        AND t2.date=DATE_ADD(t1.date, INTERVAL 1 DAY);

You can try using variables:

SELECT object, `index`, `date`, value, diff
FROM (
    SELECT object, `index`, `date`, value,
        IF (@idx = `index`, value - @prev, 0) AS diff,
        @idx := `index`,
        @prev := value
    FROM tableName, (SELECT @idx := NULL, @prev := NULL) dm
    ORDER BY `index`, `date`
) rs
ORDER BY `date`, `index`

Note: This will work if you are querying a single object (as shown in the OP), if this is not the case, you will need to add another level of variables to track the object:

SELECT object, `index`, `date`, value, diff
FROM (
    SELECT object, `index`, `date`, value,
        IF (@obj = object AND @idx = `index`, value - @prev, 0) AS diff,
        @obj := object,
        @idx := `index`,
        @prev := value
    FROM tableName, (SELECT @obj := NULL, @idx := NULL, @prev := NULL) dm
    ORDER BY object, `index`, `date`
) rs
ORDER BY object, `date`, `index`