MySql Select - row subtract previous row

Question

I'm trying to extract stats from DB. Table's structure is:

UpdatedId product_name   revenue
980       Product1       1000
975       Product1       950
973       Product1       900
970       Product1       800
965       Product21      1200

So revenue = previous revenue + new revenue.

In order to make graphs, the goal is to get the output for Product1 like this

UpdateId  Difference
980       50 
975       50 
973       100 
970       0

I tried this query but MySQL gets stuck :)

select a.product_name, a.revenue, b.revenue, b.revenue- a.revenue as difference from updated_stats a, updated_stats b where a.product_name=b.product_name and b.revenue= (select min(revenue) from updated_stats where product_name=a.product_name and revenue > a.revenue and product_name= 'Product1')

Could you please tell me, how it should be queried? Thanks.

score 2 · Accepted Answer · answered Jun 25 '17 at 20:05

2

I would do this with a correlated subquery:

select u.*,
       (select u.revenue - u2.revenue
        from updated_stats u2
        where u2.product_name = u.product_name and
              u2.updatedid < u.updatedid
        order by u2.updatedid desc
        limit 1
       ) as diff
from updated_stats u;

Note: This returns NULL instead of 0 for 970. That actually makes more sense to me. But you can use COALESCE() or a similar function to turn it into a 0.

If updated_stats is even moderately sized, you will want an index on updated_status(product_name, updated_id, revenue). This index covers the subquery.

answered Jun 25 '17 at 20:05

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, thank you for your answer it was a solution, I added where clause at the end to choose the product 1 this is what I needed to do select u.*, (select u.revenue - u2.revenue from updated_stats u2 where u2.product_name = u.product_name and u2.updatedid < u.updatedid order by u2.updatedid desc limit 1 ) as diff from updated_stats u where product_name = 'Product1' ORDER BY `updateid` DESC – Geobo Jun 25 '17 at 20:51
This is a great solution for use with updates on Mollie transactions (eg refunding) from their Webhook. Since Mollie doesn't actually give the oneoff refund amount per tansaction, but the total of all the refunds. With this one you actually see every amount you refunded within a transaction. – KJS May 07 '19 at 20:06

MySql Select - row subtract previous row

1 Answers1