Difference between mysql rows

Question

I have a mysql table that's something like this:

date               product ID    sold
-----              ----------    ----
2013-04-20 09:00   ABC           10
2013-04-20 09:00   DEF           15
2013-04-20 09:00   HIJ           15
2013-04-20 10:00   ABC           5
2013-04-20 10:00   DEF           10
2013-04-20 10:00   HIJ           20
and so on..

I'd like to get the difference between the amount each product sold for every hour, sorted by the descending difference, so the result for the above would be:

2013-04-20 10:00   HIJ           5
2013-04-20 10:00   ABC           -5
2013-04-20 10:00   DEF           -5

I've tried a few things, like joining the table with itself, but I can't get it right. How would I go about doing this?

Answer 1

This can be done simply with a correlated subquery, although it might not be as efficient on large tables:

select `date`, `productId`, ifnull(sold-(
  select sold
  from sales i
  where i.date < s.date
  and i.productId = s.productId
  limit 1
), sold) as diff
from sales s;

One caveat: That query, and the others in the other answers rely on one thing: the data in the table already being grouped by hour, which will not be the case in most scenarios.

Here's the fiddle.

Answer 2

It's easy with temporary tables. First summarize your data via:

CREATE TABLE sales_summary
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id CHAR(3) NOT NULL,
    date DATETIME NOT NULL,
    sold INT NOT NULL
)
SELECT
    NULL AS id,
    product_id,
    DATE_FORMAT(date, '%Y-%m-%d %H:00:00') date,
    SUM(sold) AS sold
FROM
    sales
GROUP BY
    product_id,
    DATE_FORMAT(date, '%Y-%m-%d %H:00:00')
ORDER BY
    1,2;

From that summary table, you can create your report:

SELECT
    b.product_id,
    a.date AS prev_hour,
    b.date AS this_hour,
    b.sold - a.sold AS diff_sold,
    a.sold AS prev_sold,
    b.sold AS this_sold
FROM
    sales_summary a
INNER JOIN
    sales_summary b ON b.id = a.id + 1 AND b.product_id = a.product_id 
ORDER BY
    a.product_id,
    a.date DESC;

which returns:

+------------+---------------------+---------------------+-----------+-----------+-----------+
| product_id | prev_hour           | this_hour           | diff_sold | prev_sold | this_sold |
+------------+---------------------+---------------------+-----------+-----------+-----------+
| ABC        | 2013-04-20 10:00:00 | 2013-04-20 11:00:00 |        40 |        10 |        50 |
| ABC        | 2013-04-20 09:00:00 | 2013-04-20 10:00:00 |       -10 |        20 |        10 |
| DEF        | 2013-04-20 09:00:00 | 2013-04-20 10:00:00 |       -10 |        30 |        20 |
| HIJ        | 2013-04-20 09:00:00 | 2013-04-20 10:00:00 |        10 |        30 |        40 |
+------------+---------------------+---------------------+-----------+-----------+-----------+

A working example is at http://sqlfiddle.com/#!2/897d9/1/0

Answer 3

You could use a query like this:

SELECT t.date, t.productID, t.sold-tp.sold
FROM (
  SELECT t1.date, t1.productID, t1.sold, MAX(t2.date) date_prec
  FROM
    yourtable t1 INNER JOIN yourtable t2
    ON t1.productID=t2.productID AND t1.date>t2.date
  GROUP BY
    t1.date, t1.productID, t1.sold) t INNER JOIN yourtable tp
  ON t.productID=tp.productID and t.date_prec=tp.date

Please see fiddle here.

In the subquery I'm joining yourtable with itself, on the same product ID and with the condition that t1.date>t2.date. Grouping by t1.date, productID and sold you can get the previous datetime, which is MAX(t2.date). I'm then joining this subquery with yourtable again, in order to get the value of sold of the previous day.

EDIT

You might also want to use this:

SELECT t.date, t.productID, t.sold-tp.sold
FROM
  yourtable t INNER JOIN yourtable tp
  ON t.productID = tp.productID
     AND t.date = tp.date + INTERVAL 1 HOUR

here I'm returning the difference between sold and sold of the previous hour.

Fiddle is here.