Calculate a rolling weighted average in oracle

Question

I have data with a subset that looks something like this:

create table tbl_1 as (
    select * from (
        select trunc(sysdate - (rownum - 1)) as call_dt,
               rownum as calls,
               to_char(trunc(sysdate - (rownum - 1)), 'DAY') as dow
        from dual connect by rownum <= 22
    )
    where dow like '%MONDAY%'
    order by call_dt
)
;

 call_dt  | calls | dow
-------------------------
17-SEP-18    22    MONDAY   
24-SEP-18    15    MONDAY   
01-OCT-18    8     MONDAY   
08-OCT-18    1     MONDAY

I then have another table with future dates that looks like this:

create table tbl_2 as (
    select * from (
        select  trunc(sysdate + (rownum - 1)) as call_dt, 
                0 as calls,
                to_char(trunc(sysdate + (rownum - 1)), 'DAY') as dow
        from dual
        connect by rownum <= 15
    )
    where dow like '%MONDAY%'
)
;

 call_dt  | calls | dow
-------------------------
15-OCT-18     0    MONDAY   
22-OCT-18     0    MONDAY

I am trying to take my future dates, append them to my historical data, and then calculate a rolling weighted average. I am currently doing this with the following query.

select  call_dt,
        case when calls = 0 then (
            (1 * lag1) + (0.8 * lag2) + (0.5 * lag3) + (0.3 * lag4))
             else calls 
             end as calls,
        dow
from (
    select  call_dt, calls, dow,
            lag(calls, 4) OVER (partition by dow order by call_dt) as lag4,
            lag(calls, 3) OVER (partition by dow order by call_dt) as lag3,
            lag(calls, 2) OVER (partition by dow order by call_dt) as lag2,
            lag(calls, 1) OVER (partition by dow order by call_dt) as lag1
    from (
        select * from tbl_1
        union
        select * from tbl_2
    )
    order by dow, call_dt
)
;

This results in the following:

 call_dt  | calls | dow
-------------------------
17-SEP-18    22    MONDAY   
24-SEP-18    15    MONDAY   
01-OCT-18    8     MONDAY   
08-OCT-18    1     MONDAY 
15-OCT-18    46    MONDAY   
22-OCT-18    24    MONDAY

This works great for the first future date for each day of week. However, for subsequent dates, the lag* variables are equal to 0, and so the value is off. Here is what I'm hoping to achieve:

 call_dt  | calls | dow
-------------------------
17-SEP-18    22    MONDAY   
24-SEP-18    15    MONDAY   
01-OCT-18    8     MONDAY   
08-OCT-18    1     MONDAY 
15-OCT-18    46    MONDAY   
22-OCT-18    70    MONDAY

I looked at this question, which seems like it might give me what I want? But the window function keywords used were foreign to me. I also looked at this tutorial, but it seems like these rolling average functions assume non-zero entries. Is it possible to achieve these results?

What version of Oracle are you using? The simplest method is possibly a recursive CTE. — Gordon Linoff
Are you aware of the windowing_clause? Maybe something like RANGE BETWEEN INTERVAL '4' DAY PRECEDING AND INTERVAL '4' DAY FOLLOWING — Wernfried Domscheit
@GordonLinoff I'm using this version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production — tblznbits
@WernfriedDomscheit I am not familiar with the windowing_clause. Can you elaborate a little more on how BETWEEN INTERVAL '4' DAY PRECEDING AND INTERVAL '4' DAY FOLLOWING works? Does that create a +/- 4 day window around the date of the current row Oracle is operating on? — tblznbits
Yes, that's how it works. Oracle takes in this example +/- 4 days, no matter how many rows are covered by this range. — Wernfried Domscheit

Ponder Stibbons Ponder Stibbons · Accepted Answer · 2018-10-08T16:26:45

Use recursive query, which takes last calls as lag1 and moves all other lags into the past:

with 
  s as (
    select  rn, call_dt, calls, 
            lag(calls, 4) OVER (partition by dow order by call_dt) as lag4,
            lag(calls, 3) OVER (partition by dow order by call_dt) as lag3,
            lag(calls, 2) OVER (partition by dow order by call_dt) as lag2,
            lag(calls, 1) OVER (partition by dow order by call_dt) as lag1
    from (
        select 0 rn, tbl_1.* from tbl_1 union all
        select row_number() over (order by call_dt), tbl_2.* from tbl_2)),
  c(rn, call_dt, calls, lag1, lag2, lag3, lag4) as (
    select rn, call_dt, (1 * lag1) + (0.8 * lag2) + (0.5 * lag3) + (0.3 * lag4), 
           lag1, lag2, lag3, lag4 
      from s where rn = 1
    union all
    select s.rn, s.call_dt, (1 * c.calls) + (0.8 * c.lag1) + (0.5 * c.lag2) + (0.3 * c.lag3), 
           c.calls, c.lag1, c.lag2, c.lag3
      from s join c on c.rn+1 = s.rn)
select * from c

s - is basically your query, where I added row numbering. c is CTE, where rn = 1 is our anchor, first step. Then we add next steps row by row, moving previous values to the right. I think we should divide result by 4, but you didn't do it? Hope this helps.

Result:

    RN CALL_DT          CALLS       LAG1       LAG2       LAG3       LAG4
------ ----------- ---------- ---------- ---------- ---------- ----------
     1 2018-10-15        21,5          1          8         15         22
     2 2018-10-22        30,8       21,5          1          8         15

Calculate a rolling weighted average in oracle

1 Answers