amazon-redshift - Amazon Redshift 中的加权移动平均线

Question

有没有办法在 Amazon Redshift 中计算具有固定窗口大小的加权移动平均值？更详细地说，给定一个带有日期列和值列的表，对于每个日期计算指定大小窗口的加权平均值，并在辅助表中指定权重。

到目前为止，我的搜索尝试产生了大量使用窗口函数进行简单平均(无权重)的示例，例如 here . postgres也有一些相关的建议，例如this SO question ，但是与 postgres 相比，Redshift 的功能集非常稀少，并且它不支持许多建议的高级功能。

Answer 1

假设我们有以下表格:

create temporary table _data (ref_date date, value int);
insert into _data values
    ('2016-01-01', 34)
  , ('2016-01-02', 12)
  , ('2016-01-03', 25)
  , ('2016-01-04', 17)
  , ('2016-01-05', 22)
;

create temporary table _weight (days_in_past int, weight int);
insert into _weight values
    (0, 4)
  , (1, 2)
  , (2, 1)
;

然后，如果我们想计算 窗口上的移动平均线三天 (包括当前日期)在更接近当前日期的值被分配比更远的过去更高的权重时，我们期望 2016-01-05 的加权平均值。 (基于来自 2016-01-05 、 2016-01-04 和 2016-01-03 的值):

(22*4 + 17*2 + 25*1) / (4+2+1) = 147 / 7 = 21

查询可能如下所示:

with _prepare_window as (
    select
        t1.ref_date
      , datediff(day, t2.ref_date, t1.ref_date) as days_in_past
      , t2.value * weight as weighted_value
      , weight
      , count(t2.ref_date) over(partition by t1.ref_date rows between unbounded preceding and unbounded following) as num_values_in_window
    from
        _data t1
    left join
        _data t2 on datediff(day, t2.ref_date, t1.ref_date) between 0 and 2
    left join
        _weight on datediff(day, t2.ref_date, t1.ref_date) = days_in_past
    order by
        t1.ref_date
      , datediff(day, t2.ref_date, t1.ref_date)
)
select
    ref_date
  , round(sum(weighted_value)::float/sum(weight), 0) as weighted_average
from
    _prepare_window
where
    num_values_in_window = 3
group by
    ref_date
order by
    ref_date
;

给出结果:

  ref_date  | weighted_average
------------+------------------
 2016-01-03 |               23
 2016-01-04 |               19
 2016-01-05 |               21
(3 rows)