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
;

Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example Try create a sample in rextester.com — Juan Carlos Oropeza
Instead of a FOR .. LOOP just use INSERT's, but give us a working example otherwise we cant test it — Juan Carlos Oropeza
I've fixed the fiddle for you: dbfiddle.uk/… but I think this fiddle is not suited to test a big demo with hudreds rows like this one, since it only shows max. 10 rows. — krokodilko
The problem with using random values is that we don't know what the result should be; if you provided fixed inserts then you could include your expected results, and have something more concrete to aim for and test against. You might just want RANGE BETWEEN 30 PRECEDING AND 1 PRECEDING but it's hard to tell at the moment. — Alex Poole
How about: range between interval '30' day preceding and interval '1' second preceding? This should work as long as you can't have duplicate date-time for the same ID_KEY_HASH. — mathguy

mathguy mathguy · Accepted Answer · 2019-06-25T17:18:00

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.

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

1 Answers