Calculate rolling 180 days average

Question

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.

If there are 360+ days worth of data do you expect the rolling average from 180 days ago to be based on a single row or the range of rows from 359-180 days? — MT0, Sep 05 '19 at 08:16
Do you want a rolling average of 180 weekdays or the last 180 days without the weekends (so about 180*5/7 = 128.5 weekdays)? — MT0, Sep 05 '19 at 08:21
Do you have always exactly one record for each day? What about public holidays? — Wernfried Domscheit, Sep 05 '19 at 09:07

MT0 · Accepted Answer · 2019-09-05T07:54:47.967

3

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

edited Sep 05 '19 at 07:54

answered Sep 05 '19 at 07:44

MT0

143,790
11
59
117

This will calculate wrong average in case one of the `txn_date` is missing. – Popeye Sep 05 '19 at 08:09
1

@Tejash No it won't because its using a `RANGE` window not a `ROWS` window. – MT0 Sep 05 '19 at 08:10
Can you please explain to me how `RANGE` operator will help here? I will be happy to learn it. It is quite interesting – Popeye Sep 05 '19 at 08:15
@Tejash The `ROWS` window works on a number of rows. The [`RANGE` window](https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF51203) works on a range of values; in this case the range is from 180 days preceding the current value to 0 days preceding the current value (so will include the current value and all other rows on the same date if there happen to be multiple rows per day - which doesn't appear to be the case for the OP's data but may be the case in other queries). – MT0 Sep 05 '19 at 08:18
Window `RANGE BETWEEN 180 PRECEDING AND 0 PRECEDING` would be equivalent to `RANGE BETWEEN INTERVAL '180' DAY(3) PRECEDING AND INTERVAL '0' DAY PRECEDING` in case of `TIMESTAMP` data type. If your table contains always data up to the current day then you can shortcut the window to `RANGE BETWEEN 180 PRECEDING` – Wernfried Domscheit Sep 05 '19 at 09:16
@WernfriedDomscheit Yes, however I prefer to make the queries explicit in what they are doing so it is easy to review their function. `RANGE BETWEEN 180 PRECEDING` is a shortcut for `RANGE BETWEEN 180 PRECEDING AND CURRENT ROW` and then you need to check the documentation that, for a `RANGE` window, `CURRENT ROW` is equivalent to the current value so it will not exclude rows with the same value that appear later in the ordering; its just easier to be explicit from the outset. – MT0 Sep 05 '19 at 09:28

DarkRob · Answer 2 · 2019-09-06T03:55:32.243

0

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.

edited Sep 06 '19 at 03:55

answered Sep 05 '19 at 06:46

DarkRob

3,843
1
10
27

This will get a range of "180 weekdays" not "180 days excluding weekends" (which would only be about 128.5 weekdays). – MT0 Sep 05 '19 at 08:05
no, i excluded weekend in inner query, which gives only weekdays record, `row_number` will give the count of days as expected. – DarkRob Sep 05 '19 at 08:11
And 180 rows in the outer query = 180 weekdays = 36 weeks = 252 days. – MT0 Sep 05 '19 at 08:12
as OP said `average of transactions for last 180 days (excluding weekends)`, so i think this is what required, otherwise need to edit question for further explanation. – DarkRob Sep 05 '19 at 08:19
Result of `TO_CHAR(TXN_DATE,'DY')` depends on current user session `NLS_DATE_LANGUAGE` setting. Better use `TO_CHAR(TXN_DATE,'DY', 'NLS_DATE_LANGUAGE=English')` – Wernfried Domscheit Sep 05 '19 at 09:19

score 0 · Answer 3 · answered Sep 05 '19 at 07:27

0

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!!

answered Sep 05 '19 at 07:27

Popeye

35,427
4
10
31

This will only work on a set of rows from the last 180 days. The average for 180 days ago will be calculated on a single row (since all the preceding rows are excluded by the filter) rather than being calculated on a window of 180 days if there exists more rows before. – MT0 Sep 05 '19 at 07:48
It is clearly mentioned in OP's question: `We want to calculate the rolling average of transactions for last 180 days (excluding weekends).`. Why should I include all the dates then? – Popeye Sep 05 '19 at 08:08
I've asked for clarification from the OP but the rolling 180 day average from 10 days ago would be the average of all the rows from 189 to 10 days ago; however the data from 189 - 181 days ago would have been filtered out of your query and not included in the average so you would not be reporting on the full range. – MT0 Sep 05 '19 at 08:30
Result of `TO_CHAR(TXN_DATE,'DY')` depends on current user session `NLS_DATE_LANGUAGE` setting. Better use `TO_CHAR(TXN_DATE,'DY', 'NLS_DATE_LANGUAGE=English')` – Wernfried Domscheit Sep 05 '19 at 09:20
perfect @Wernfried Domscheit – Popeye Sep 05 '19 at 09:21

Calculate rolling 180 days average

3 Answers3