sqloracle-databaselaganalytic-functions

SQL How to use LAG inside SUM


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.


Solution

  • 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