2

For the given data:

+---+--------+------+
|CID| number | rum  |
+---+--------+------+
| 1 | 1.0000 | NULL |
| 3 | 2.0000 | NULL |
| 5 | 2.0000 | NULL |
| 6 | 4.0000 | NULL |
+---+--------+------+

I want to calculate rum with percentage change of current and previous number.

rum = (currNumber - prevNumber) / prevNumber * 100

Expected result:

+---+--------+------+
|CID| number | rum  |
+---+--------+------+
| 1 | 1.0000 | NULL |
| 3 | 2.0000 |100.0 |
| 5 | 2.0000 |  0.0 |
| 6 | 4.0000 |100.0 |
+---+--------+------+

LAG function doesn't work here in MySQL.

Salman A
  • 262,204
  • 82
  • 430
  • 521
Tomrock D'souza
  • 121
  • 4
  • 13

2 Answers2

4

Assuming the rows are ordered based on CID you can find previous row using correlated subquery:

SELECT CID, number, (
    SELECT (c.number - p.number) / p.number * 100
    FROM t AS p
    WHERE p.CID < c.CID
    ORDER BY p.CID DESC
    LIMIT 1
) AS rum
FROM t AS c

SQL Fiddle

Salman A
  • 262,204
  • 82
  • 430
  • 521
1

You can emulate lag() with a variable:

set @num = null;

select 
    cid,
    number,
    (number- lag_number)/lag_number * 100 rum
from (
    select 
        cid, 
        @num lag_number, 
        @num := number number 
    from mytable 
    order by cid
) t
order by cid

Demo on DB Fiddle:

cid | number |  rum
--: | -----: | ---:
  1 | 1.0000 | null
  3 | 2.0000 |  100
  5 | 2.0000 |    0
  6 | 4.0000 |  100
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Using variables for this is a bad idea. – Salman A Nov 13 '19 at 09:16
  • There is an `order by` in a sub query. That is nonsense but MySQL allows you to do that but it may or may not honor the inner order by. There is no guarantee. – Salman A Nov 13 '19 at 09:23
  • @SalmanA: to my knowledge, the order by clause in the subquery is what guarantees that the variable will be correctly assigned the previous value. But I have to agree that your solution is smarter here anyway, hence +1. – GMB Nov 13 '19 at 09:46
  • @gmb https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/... search for "joining people with country". – Salman A Nov 13 '19 at 09:57