'php Mysql subtraction of a previous index

I am making a project in which it receives data from a water consumer and then I display these values in a graph with

phpmysql


Solution 1:[1]

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

Solution 2:[2]

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

Without LAG try this:

select DateHeure,ANY_VALUE(IndexReel),ANY_VALUE(whatuwant) from ( 
    select ANY_VALUE(DateHeure) as DateHeure,ANY_VALUE(IndexReel) as IndexReel,sum(IndexReel) as whatuwant from (
        select @index:=@index +1 as idx,t.IndexReel,'\n',t.DateHeure from (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) t,(select @index:=0) r UNION ALL     
        select @index2:=@index2 +1 as idx,0-t.IndexReel as IndexReel,'\n',t.DateHeure from (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) t,(select @index2:=1) r
    ) t group by idx 
) t group by DateHeure

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 lemon
Solution 2

Related Questions