How can I subtract the value of the last date today from the value of the last date of the previous-CodePudding

How can I subtract the value of the last date today from the value of the last date of the previous day in mysql?

suppose today is 2022/05/27 then

80 -20 = 60

datetime,value

2022/05/27 15:25:30 ,20

2022/05/27 11:20:40 ,34

2022/05/26 9:42:10,80

2022/05/26 5:10:36,150

CodePudding user response：

You are probably using MySQL versions below 8.0, which lack the latest features unfortunately. So I wrote the code in 5.7 and tested it in workbench. To make things more interesting, I took the liberty of adding two rows on top of your original table.

insert test values('2022/05/25 15:10:36',200);
insert test values('2022/05/25 11:10:36',280);

select concat_ws(' minus ',t1.dt,t2.dt) as date_gap,
t1.`value`-t2.`value` as value_gap from
    (select date(`datetime`) dt,`value` from test where `datetime` in 
        (select max(`datetime`)mx from test group by date(`datetime`) ) 
    ) t1
 join
    (select date(`datetime`) dt,`value` from test where `datetime` in 
        (select max(`datetime`)mx from test group by date(`datetime`) ) 
    ) t2
on t1.dt = t2.dt - interval 1 day
;

CodePudding user response：

If your mysql version support window function, we can try to use the ROW_NUMBER window function to get the latest datetime per day in a subquery, then use LAG window function to get the previous value to calculate your logic.

SELECT yesterdayValue - value
FROM (
  SELECT *,LAG(value) OVER(ORDER BY datetime) yesterdayValue
  FROM (
   SELECT *,
        ROW_NUMBER() OVER(PARTITION BY DATE_FORMAT(datetime,'%d/%m/%Y') ORDER BY datetime desc) rn
   FROM T 
  ) t1
  WHERE rn = 1 
) t1
WHERE DATE(datetime) = CURDATE()

sqlfiddle