CODEWITHSUNDEEP
sqloracle-databaserolling-average
Sid
Sid

Reputation: 582

Calculate rolling 180 days average

We have a table with columns TXN_DATE and NO_OF_TXNS. Below is sample data:

TXN_DATE           NO_OF_TXNS
25-AUG-19             0
26-AUG-19             1000
27-AUG-19             1500
28-AUG-19             1800
29-AUG-19             1100
30-AUG-19             1400

We want to calculate the rolling average of transactions for last 180 days (excluding weekends). If it is 1st day, average will be equal to number of transactions on that day and if it is 2nd day it will be equal to (n1+n2)/2 and 3rd day then it will be equal to (n1+n2+n3)/3 and so on.

Any help will be highly appreciated.

Upvotes: 0

Views: 1060

Answers (3)

DarkRob
DarkRob

Reputation: 3833

You may try this. First you need to filter out the weekends from the list, since in your question you want to find the record of last 180 days excluding weekends. To get the days name of date we use TO_CHAR, and for making sure that it will read as language english we'll add 'NLS_DATE_LANGUAGE=English'.

Once you have your list then you can simply filter your 180 records for calculation. For this you may have several methods like top, row_number, limit etc. I am using row_number. So final sample code will be like this.

with record ( SLNO, TXN_DATE, NO_OF_TXNS ) AS 
( 
     SELECT ROW_NUMBER() OVER (ORDER BY TXN_DATE DESC) AS SLNO,
            TXN_DATE,
            NO_OF_TXNS
     FROM   TABLE 
     WHERE  TO_CHAR(TXN_DATE,'DY', 'NLS_DATE_LANGUAGE=English') NOT IN ('SAT', 'SUN') 
)
select TXN_DATE,
       NO_OF_TXNS, 
       (select avg(t.NO_OF_TXNS) from record t where t.TXN_DATE<=t1.TXN_DATE
and t.SLNO<180) as Sum 
from record t1

Thanks to @WernfriedDomscheit for improvement.

Upvotes: 0

MT0
MT0

Reputation: 167972

You can use an analytic function with a RANGE window. You can also use TXN_DATE - TRUNC( TXN_DATE, 'IW' ) to find the number of days of the week since the start of the ISO week (which always start on Monday) and means that your query is not tied to a specific language or session parameters (which each user can change within their session and change the language to TO_CHAR does not give the expected output for days of the week).

SELECT TXN_DATE,
       AVG( NO_OF_TXNS ) OVER (
         ORDER BY TXN_DATE
         RANGE BETWEEN 180 PRECEDING
               AND     0   PRECEDING
       ) AS avg_no_of_txns
FROM   table_name
WHERE  NOT ( TXN_DATE - TRUNC( TXN_DATE, 'IW' ) BETWEEN 5 AND 7 );

If you want to restrict it to the last 180 days worth of data then you need to find the averages and then filter afterwards:

SELECT *
FROM   (
  SELECT TXN_DATE,
         AVG( NO_OF_TXNS ) OVER (
           ORDER BY TXN_DATE
           RANGE BETWEEN 180 PRECEDING
                 AND     0   PRECEDING
         ) AS avg_no_of_txns
  FROM   table_name
  WHERE  NOT ( TXN_DATE - TRUNC( TXN_DATE, 'IW' ) BETWEEN 5 AND 7 )
)
WHERE TXN_DATE >= TRUNC( SYSDATE ) - INTERVAL '180' DAY(3);

db<>fiddle

Upvotes: 3

Popeye
Popeye

Reputation: 35900

You can use analytical function and I would avoid using row preeciding operator as it will not give exact result in case entry is missing for one or more days so it is better to use WHERE clause for fetching last 180 days.

SELECT TXN_DATE,
       AVG(NO_OF_TXNS) OVER (ORDER BY TXN_DATE) AS ROLLING_AVERAGE
  FROM YOUR_TABLE
 WHERE TO_CHAR(TXN_DATE,'DY') NOT IN ('SAT','SUN')
   AND TRUNC(TXN_DATE) >= TRUNC(SYSDATE) - 180

Cheers!!

Upvotes: 0

Related Questions