I need SQL query that calculates specific delta metric

March 24, 2023

I need help to write a SQL query as I don’t have enough knowledge.

Let say I have this table

Time |Item| TOTAL
-----------------
000  |   1|   111
-----------------
000  |   2|   113
-----------------
001  |   1|   113
-----------------
001  |   2|   143
-----------------
002  |   1|   173
-----------------
002  |   2|   183

I need an SQL which based on the time will calculate the delta between the Total of the Item of that time and the previous.

Time |Item| TOTAL
-----------------
000  |   1|     0
-----------------
000  |   2|     0
-----------------
001  |   1|     2
-----------------
001  |   2|    30
-----------------
002  |   1|    60
-----------------
002  |   2|    40

So for Time=0, Item=1 the total will be 0 as we don’t have previous time.
For Time=1, Item=1 the total will be 2 (113 (Item=1, Time=1)-111(Item=1, Time=0))
For Time=1, Item=2 the total will be 30 (143 (Item=2, Time=1)-113(Item=2, Time=0))
and so on.

Any help is appreciated.

>Solution :

If MySQL 8.0, then you could use the LAG() window function.

create table table1 (
  read_time integer, 
  item integer, 
  total integer
  );
  
insert into table1 values 
(0, 1, 111), 
(0, 2, 113), 
(1, 1, 113), 
(1, 2, 143), 
(2, 1, 173), 
(2, 2, 183);

select read_time, 
 item, 
 coalesce(total - prior_total, 0) as my_total
from (
  select read_time, 
   item, 
   total, 
   lag(total, 1) over (partition by item order by read_time) as prior_total
   from table1
  )z
order by 1,2;