从非聚合数据计算3个月移动平均线

Question

我有一堆订单。每个订单都是a类或B类订单。我想要3个月的移动平均时间来运送每种类型的订单。我如何将这个订单数据聚合成我想要使用Redshift或Postgres SQL?

从这个开始:

<表类> order_id order_type ship_date time_to_ship tbody><<tr>12021-12-251002b2021-12-3111032022-01-0120042022-01-01505b2022-01-1511062022-02-0210072022-02-283008道明>2022-04-05759道明>2022-04-06210102022-04-15150

老实说，这完全是瞎猜的，所以如果不正确我也不会感到惊讶…但在我看来，你可以在自连接中使用一系列日期来实现这一点。

select
t1.order_type, t1.ship_date, avg (t2.time_to_ship) as 3mma_time_to_ship
from
tbl t1
join tbl t2 on
t1.order_type = t2.order_type and
t2.ship_date between t1.ship_date - interval '3 months' and t1.ship_date
group by
t1.order_type, t1.ship_date

结果与你的例子不符，但我也不完全确定它们是从哪里来的。

也许这将成为最终解决方案的催化剂，或者至少是一个开始的想法。

顺便说一下，这是Pg12。不确定它是否能在红移上工作。

—EDIT—

根据你的更新，我能够完全匹配你的三个结果。我使用dense_rank来查找最近的三个月:

with foo as (
select
order_type, date_trunc ('month', ship_date)::date as ship_month,
time_to_ship, dense_rank() over (partition by order_type order by date_trunc ('month', ship_date)) as dr
from tbl
)
select
f1.order_type, f1.ship_month,
avg (f2.time_to_ship),
array_agg (f2.time_to_ship)
from
foo f1
join foo f2 on
f1.order_type = f2.order_type and
f2.dr between f1.dr - 2 and f1.dr
group by
f1.order_type, f1.ship_month

结果:

b   2022-01-01  110.0000000000000000    {110,110}
a   2022-01-01  116.6666666666666667    {100,50,200,100,50,200}
b   2022-04-01  126.2500000000000000    {110,110,75,210,110,110,75,210}
b   2021-12-01  110.0000000000000000    {110}
a   2021-12-01  100.0000000000000000    {100}
a   2022-02-01  150.0000000000000000    {100,50,200,100,300,100,50,200,100,300}
a   2022-04-01  160.0000000000000000    {50,200,100,300,150}

在数组元素中有一些重复，但它似乎不影响平均值。我敢肯定那部分可以修好。