Average over last 30 days not including current record (mixed date and row-based condition)

Question

I'm working on a column called CRD_TRX_SYAMT_AVG_30d which fills these criteria:

• Computes average of SYSTEMAMOUNT column over last 30 days according to TRXHOSTDATETIME
• Excluding rows where RESPONSECODE is not 00
• Excluding the current record

There's complete sample data below. And I've pasted this into DB Fiddle, but there's an issue preventing the code from running there. For me, it runs fine in SQL Developer but gives unexpected results.

I've tried substituting CURRENT ROW with 1 PRECEDING, but it's not possible to mix an interval and a range to my understanding. The column CRD_TRX_SYAMT_AVG_30_trx_exc uses a row-based condition (ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) and this works fine. So the issue is only with CRD_TRX_SYAMT_AVG_30d.

How can I change the condition to something like the following?

RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND 1 PRECEDING

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=471da47ef5df960b67e427053e7642d4

-- DATE formatting
alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

-- DROP and CREATE TABLE
DROP TABLE TMP_EXA_RSS_ISS_AUT_S_100CRD_T;

CREATE TABLE "RAI"."TMP_EXA_RSS_ISS_AUT_S_100CRD_T" 
 (  "ID_KEY_HASH" VARCHAR2(10), 
"TRXHOSTDATETIME" DATE, 
"SYSTEMAMOUNT" NUMBER(10,1), 
"RESPONSECODE" CHAR(2)
 );

 -- fill entire table
DECLARE
  nTRX NUMBER(10) := 100;
BEGIN
FOR i IN 1 .. nTRX LOOP
  INSERT INTO tmp_exa_RSS_ISS_AUT_S_100CRD_T VALUES
  (
    FLOOR(DBMS_RANDOM.VALUE (1, 4)), -- ID_KEY_HASH
    TO_DATE('01.01.2018', 'DD.MM.YYYY') + dbms_random.value(0, 60), -- TRXHOSTDATETIME
    FLOOR(DBMS_RANDOM.VALUE (1, 10)) * 100, -- SYSTEMAMOUNT
    CASE round(dbms_random.value(1,11)) 
            WHEN 1 THEN '55' 
            WHEN 2 THEN '88'
            ELSE '00'
       END -- RESPONSECODE 
  );
END LOOP;
END;
/

-- review all data created
SELECT * FROM tmp_exa_RSS_ISS_AUT_S_100CRD_T ORDER BY ID_KEY_HASH, TRXHOSTDATETIME;


-- example features:

SELECT ID_KEY_HASH, TRXHOSTDATETIME, SYSTEMAMOUNT, RESPONSECODE,
ROUND(AVG(
  CASE WHEN ((SYSTEMAMOUNT > 0) AND (RESPONSECODE = '00')  ) THEN SYSTEMAMOUNT ELSE NULL END
) OVER (PARTITION BY ID_KEY_HASH ORDER BY TRXHOSTDATETIME 
                        RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW), 2)  AS CRD_TRX_SYAMT_AVG_30d,
NVL( ROUND( 
  AVG(
      CASE WHEN ((SYSTEMAMOUNT > 0) AND (RESPONSECODE = '00')  ) THEN SYSTEMAMOUNT ELSE NULL END
      ) 
      OVER (PARTITION BY ID_KEY_HASH ORDER BY TRXHOSTDATETIME NULLS LAST 
      ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)
  , 2), 0 ) AS CRD_TRX_SYAMT_AVG_30_trx_exc
FROM tmp_exa_RSS_ISS_AUT_S_100CRD_T
ORDER BY ID_KEY_HASH, TRXHOSTDATETIME
;

Answer 1

If the composite (ID_KEY_HASH, TRXHOSTDATETIME) is unique (no duplicate date-times for the same ID_KEY_HASH), then the following expression will do what you require:

 avg(case when systemamount > 0 and responsecode = '00' then systemamount end)
   over (partition by id_key_hash order by TRXHOSTDATETIME 
         range between interval '30' day preceding 
                   and interval '1' second preceding)

You can wrap it within ROUND( ... , 2) if needed.