Subtract value from previous row value if it is greater than the max value

user_2968632 0
Viewed 1847

Im using Postgresql & Sequelize. I have to find the consumption from the reading table. Currently, I have the query to subtract the value from the previous row. But the problem was If the value is less than the previous value means I have to ignore the row and need to wait for the greater value to make the calculation.

Current Query

select "readingValue",
       "readingValue" - coalesce(lag("readingValue") over (order by "id")) as consumption
from public."EnergyReadingTbl";

Example Record & Current Output

id      readingValue        consumption

65479   "35.8706703186035"  "3.1444168090820"
65480   "39.0491638183594"  "3.1784934997559"
65481   "42.1287002563477"  "3.0795364379883"
65482   "2.38636064529419"  "-39.74233961105351"
65483   "5.91744041442871"  "3.53107976913452"
65484   "9.59204387664795"  "3.67460346221924"
65485   "14.3925561904907"  "4.80051231384275"
65486   "19.4217891693115"  "5.0292329788208"
65487   "24.2393398284912"  "4.8175506591797"
65488   "29.2515335083008"  "5.0121936798096"
65489   "34.2519302368164"  "5.0003967285156"
65490   "38.6513633728027"  "4.3994331359863"
65491   "43.7513643778087"  "5.1000010050060"

In this picture, the last max value was 42.1287002563477. I have to wait until to get the greater value than 42.1287002563477 to make the calculation like the next greater value - 42.1287002563477. In this, 43.7513643778087 - 42.1287002563477.

Expected Output

id      readingValue        consumption

65479   "35.8706703186035"  "3.1444168090820"
65480   "39.0491638183594"  "3.1784934997559"
65481   "42.1287002563477"  "3.0795364379883"
65482   "2.38636064529419"  "0"
65483   "5.91744041442871"  "0"
65484   "9.59204387664795"  "0"
65485   "14.3925561904907"  "0"
65486   "19.4217891693115"  "0"
65487   "24.2393398284912"  "0"
65488   "29.2515335083008"  "0"
65489   "34.2519302368164"  "0"
65490   "38.6513633728027"  "0"
65491   "43.7513643778087"  "1.1226641214710"

Is there any chance to resolve this issue in the query?

sequelize.js postgresql
1 Answers

You can use the ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING to limit the frame of the window function, so you can substract the MAX up to the current row with the MAX value of the rows up to but excluding the current row:

SELECT readingValue,
       MAX(readingValue) OVER (ORDER BY id) - MAX(readingValue) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM e;

┌──────────────────┬─────────────────┐
│   readingvalue   │    ?column?     │
├──────────────────┼─────────────────┤
│ 35.8706703186035 │          (null) │
│ 39.0491638183594 │ 3.1784934997559 │
│ 42.1287002563477 │ 3.0795364379883 │
│ 2.38636064529419 │               0 │
│ 5.91744041442871 │               0 │
│ 9.59204387664795 │               0 │
│ 14.3925561904907 │               0 │
│ 19.4217891693115 │               0 │
│ 24.2393398284912 │               0 │
│ 29.2515335083008 │               0 │
│ 34.2519302368164 │               0 │
│ 38.6513633728027 │               0 │
│ 43.7513643778087 │  1.622664121461 │
└──────────────────┴─────────────────┘
(13 rows)

Time: 0,430 ms
user_26504370
Related