Create view with delta for each column in SQL-CodePudding

I have a table named pwrDay containing electric index counters (always growing).

jour	pwrconsohp	pwrconsohc	pwrprod
2021-09-26	35 736 527	18 073 331	12 629 677
2021-09-27	35 754 125	18 073 331	12 637 154
2021-09-28	35 780 113	18 073 331	12 646 963
2021-09-29	35 807 081	18 073 331	12 657 084
2021-09-30	35 833 193	18 073 331	12 668 804
2021-10-01	35 861 259	18 073 331	12 682 444
2021-10-02	35 888 342	18 073 331	12 693 908
2021-10-03	35 917 218	18 073 331	12 704 696
2021-10-04	35 944 869	18 073 331	12 706 056
2021-10-05	35 972 043	18 073 331	12 708 309

I need to extract the difference between previous and current row (maybe create a view?) The following query works for most days, but it's wrong every first day of month (or if I miss a control day):

SELECT  pwr.jour,
        (pwr.pwrconsoHP-ifnull(oldpwr.pwrconsoHP, 0)) as deltaconsoHP,
        (pwr.pwrconsoHC-ifnull(oldpwr.pwrconsoHC, 0)) as deltaconsoHC,
        (pwr.pwrProd-ifnull(oldpwr.pwrProd, 0)) as deltaProd    
FROM pwrDay pwr
LEFT OUTER JOIN pwrDay oldpwr ON 
(day(pwr.jour)-day(oldpwr.jour)=1 AND MONTH(pwr.jour)=MONTH(oldpwr.jour))
ORDER BY jour;

I also tried this query:

SELECT  pwr.jour,
        (pwr.pwrconsoHP-LAG(pwr.pwrconsoHP, 0)) as deltaconsoHP,
        (pwr.pwrconsoHC-LAG(pwr.pwrconsoHC, 0)) as deltaconsoHC,
        (pwr.pwrProd-LAG(pwr.pwrProd, 0)) as deltaProd
FROM pwrDay pwr
ORDER BY jour;

However, it doesn't run at all. I get this error message:

Erreur SQL (1305) : FUNCTION velbus.LAG does not exist

How can I write this query?

CodePudding user response：

SELECT  pwr.jour,
        (pwr.pwrconsoHP-LAG(pwr.pwrconsoHP, 0) OVER(order by jour)) as deltaconsoHP,
        (pwr.pwrconsoHC-LAG(pwr.pwrconsoHC, 0) OVER(order by jour)) as deltaconsoHC,
        (pwr.pwrProd-LAG(pwr.pwrProd, 0)  OVER(order by jour)) as deltaProd
FROM pwrDay pwr
ORDER BY jour;

give it a try ...