How to select specific data from a table in MySQL and subtract it from the next date available in a subsequent row?

Question

This question evolved from How to create a query in MySQL to subtract consecutive rows based on the date and a distinctive field?; I have the following table:

+--------+------------+----------+
| animal | date       | quantity |
+--------+------------+----------+
| dog    | 2015-01-01 |      400 |
| cat    | 2015-01-01 |      300 |
| dog    | 2015-01-02 |      402 |
| rabbit | 2015-01-01 |      500 |
| cat    | 2015-01-02 |      304 |
| rabbit | 2015-01-02 |      508 |
| rabbit | 2015-01-03 |      524 |
| rabbit | 2015-01-04 |      556 |
| rabbit | 2015-01-05 |      620 |
| rabbit | 2015-01-06 |      748 |
+--------+------------+----------+

Thanks to the users that contributed (in special https://stackoverflow.com/users/1816093/drew) now I am able to run the following query:

select
animal,
date,
quantity,
quantity_diff
from
(   SELECT
a.animal,
a.Date AS actual_date,
past_date.Date AS date,
a.quantity AS quantity,
(a.quantity - past_date.quantity) AS quantity_diff,
1 as drewOrder
FROM inventory a
JOIN
(SELECT b.animal, b.date AS date1,
(SELECT MAX(c.date)
FROM inventory c
WHERE c.date < b.date AND c.animal = b.animal
GROUP BY c.animal)
AS date2 
FROM inventory b)
AS original_date ON original_date.animal = a.animal
AND original_date.date1 = a.date
JOIN
inventory past_date
ON past_date.animal = a.animal
AND past_date.date = original_date.date2
union
select distinct null,animal,null,null,null,2 as drewOrder from inventory
) x
where x.animal='rabbit' group by quantity_diff
order by x.animal,x.drewOrder,x.actual_date;

This is what I get:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      508 |             8 |
| rabbit | 2015-01-02 |      524 |            16 |
| rabbit | 2015-01-03 |      556 |            32 |
| rabbit | 2015-01-04 |      620 |            64 |
| rabbit | 2015-01-05 |      748 |           128 |
+--------+------------+----------+---------------+

http://sqlfiddle.com/#!9/c77d8/121

...I am pretty close but i just can't get it like I want it; which is like this:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      500 |             8 |
| rabbit | 2015-01-02 |      508 |            16 |
| rabbit | 2015-01-03 |      524 |            32 |
| rabbit | 2015-01-04 |      656 |            64 |
| rabbit | 2015-01-05 |      620 |           128 |
| rabbit | 2015-01-06 |      748 |          null |
+--------+------------+----------+---------------+

I also should be able to change the "animal" and get its respective output:

cat:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| cat    | 2015-01-01 |      300 |             4 |
| cat    | 2015-01-02 |      304 |          null |
+--------+------------+----------+---------------+

dog:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| dog    | 2015-01-01 |      400 |             2 |
| dog    | 2015-01-02 |      402 |          null |
+--------+------------+----------+---------------+

Answer 1

Well there is a simpler way to do it without that big query with joins and unions. You need an Window Function for your specific problem it would be a LEAD function.

LEAD(`quantity`) over (Partition By `date` order by `date`)

But unfortunately MySql doesn't support those functions so, you have to mimic it with query variables.

The solution as you want would be:

select animal,
       `date`,
       quantity,
       lead-quantity quantity_diff
from ( select i.animal, 
              i.`date`,
              @qt as lead,
              @qt := i.quantity as quantity
        from inventory i
       where i.animal = 'rabbit' 
       order by `date` desc
     ) tab
order by `date`;

You can check it working here: http://sqlfiddle.com/#!9/c77d8/182

And for the cat: http://sqlfiddle.com/#!9/c77d8/183

And for the dog: http://sqlfiddle.com/#!9/c77d8/184

I was frustrated to know that MySql doesn't support the Window Functions until I find this article: Analytic functions: FIRST_VALUE, LAST_VALUE, LEAD, LAG which lead me to learn how to mimic it. I suggest you to favorite it as I did!

Hope it helps!