php Mysql subtraction of a previous index-CodePudding

I am making a project in which it receives data from a water consumer and then I display these values in a graph with chart.js. The problem is that I have to do some calculations in the query.

I have done the first one that is the index of the reading divided by the pulse per unit (/1000)

But I still have to do another one that is the next index minus the old index, basically OldIndex = (IndexReleve/ImpParUnit) so (NewIndex - OldIndex )

But everything in the same query I have a problem and I don't know if there is a solution...

Thanks in advance for your help

exemple of the input data oldindex = 343.32 NewIndex = (IndexReleve/ImpParUnit) - oldindex = 471.56 - 343.32 = 128.24

OldIndex = 128.24 NewIndex = (IndexReleve/ImpParUnit) - oldindex = 921.56 - 128.24 = 793.32 etc...

Releves = the values received from the water consumer Emplacement = the place of each water consumer because there are different parks

SELECT 
   ROUND((IndexReleve/ImpParUnit),2) As IndexReel,
   CONCAT(DATE_FORMAT(DateHeure, '%d/%m/%Y'), 
   '\n', 
   DATE_FORMAT(DateHeure,' %H:%i:%s') ) as DateHeure 
FROM Releves,Emplacements

CodePudding user response：

If you need to compare new values with older values, you can use MySQL lag window function, that gets the previous values lagged by n time steps.

You can find the official documentation and a relevant example that may suit your case at this reference: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag

CodePudding user response：

In your case you can try

select 
   t.IndexReel,t.IndexReel-LAG(t.IndexReel,1) over() as whatuwant,
   ROUND((IndexReleve/ImpParUnit),2) As IndexReel,
   CONCAT(DATE_FORMAT(DateHeure, '%d/%m/%Y'), 
   '\n', 
   DATE_FORMAT(DateHeure,' %H:%i:%s') ) as DateHeure 
FROM Releves,Emplacements) t