My table (name EPM1):
TIMESTAMP | INVERTER1POWER | INVERTER2POWER | POWERLIMIT |
---|---|---|---|
2021-09-30T17:19:42.309Z | 100 | 400 | 0.8 |
2021-09-30T17:20:42.309Z | 200 | 500 | 0.6 |
2021-09-30T17:21:42.309Z | 300 | 600 | 0.7 |
I have a query like this:
SELECT SUM(CASE WHEN (INVERTER1POWER+INVERTER2POWER) < (250000*POWERLIMIT) THEN INVERTER1POWER+INVERTER2POWER END) AS SUM1
FROM EPM1
WHERE TIMESTAMP >= TO_UTC_TIMESTAMP_TZ(:timefrom) AND TIMESTAMP <= TO_UTC_TIMESTAMP_TZ(:timeto)
This gives me output:
SUM1 |
---|
2100 |
I'd like to have similar single value but in calculation use POWERLIMIT
from previous row - in this part (250000*POWERLIMIT)
. So for the first iteration it would be (100+400)*0
since there's no previous value. Then (200+500)*0.8
and then (300+600)*0.6
.
I tried to use LAG function like LAG(POWERLIMIT, 1, 0)
but can't use it inside SUM.
Use a nested sub-query:
SELECT SUM(temp_value) AS sum2
FROM (
SELECT CASE
WHEN INVERTER1POWER+INVERTER2POWER
< 25000 * LAG(POWERLIMIT, 1, 0) OVER (ORDER BY timestamp)
THEN INVERTER1POWER+INVERTER2POWER
END AS temp_value
FROM epm1
)
Which, for the sample data:
CREATE TABLE EPM1 (TIMESTAMP, INVERTER1POWER, INVERTER2POWER, POWERLIMIT) AS
SELECT TIMESTAMP '2021-09-30 17:19:42.309 UTC', 100, 400, 0.8 FROM DUAL UNION ALL
SELECT TIMESTAMP '2021-09-30 17:20:42.309 UTC', 200, 500, 0.6 FROM DUAL UNION ALL
SELECT TIMESTAMP '2021-09-30 17:21:42.309 UTC', 300, 600, 0.7 FROM DUAL
Outputs:
SUM2 1600
db<>fiddle here