SQL How to use LAG inside SUM

Question

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.

score 2 · Accepted Answer · answered Sep 30 '21 at 17:46

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

Thanks a lot, I spend whole day trying to do this. Just started playing with SQL — r3vo91, Sep 30 '21 at 17:54

SQL How to use LAG inside SUM

1 Answers1