在预言机中计算滚动加权平均值

Question

我有一个子集的数据，看起来像这样：

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 

然后，我有另一个包含未来日期的表格，如下所示：

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

我正在尝试获取我未来的日期，将它们附加到我的历史数据中，然后计算滚动加权平均值。我目前正在使用以下查询执行此操作。

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
)
;

这将导致以下结果：

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

这适用于一周中每天的第一个未来日期。但是，对于后续日期，lag*变量等于 0，因此该值处于关闭状态。以下是我希望实现的目标：

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

我看了这个问题，似乎它可能会给我想要的东西？但是使用的窗口函数关键字对我来说是陌生的。我也看了本教程，但似乎这些滚动平均函数假设非零条目。是否有可能达到这些结果？

Answer 1

使用递归查询，它将最后一个calls作为lag1，并将所有其他lag移动到过去：

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- 基本上是您的查询，我在其中添加了行号。c是CTE，rn = 1是我们的第一步。然后我们逐行添加后续步骤， 将以前的值向右移动。我认为我们应该将结果除以 4，但你没有这样做？希望这有帮助。

结果：

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