sql - Redshift SQL Rolling SUM OVER last 3 ids by timestamp（每个 id 有多行）

Question

所以我在redshift SQL中有一个名为orders的表，类似于

时间戳	客户ID	order_id	类别	牌	数量
2020-09-02 09:53:59.000000	约翰	4213	计算机	苹果	4
2020-09-02 09:53:59.000000	约翰	4213	移动的	苹果	2
2020-09-02 09:53:59.000000	约翰	4213	计算机	生命值	3
2020-10-24 00:15:38.000000	约翰	8321	计算机	生命值	4
2020-10-24 00:15:38.000000	约翰	8312	移动的	苹果	3
2021-05-04 02:27:18.000000	约翰	3214	移动的	苹果	3
2021-05-04 02:27:18.000000	约翰	3124	计算机	苹果	5

请注意，order_id 没有特定的基于时间的序列，并且 customer_id 实际上是唯一 ID 而不是名称

我想做的是类似于这样的窗口函数会做什么：

SELECT timestamp,
 customer_id,
 order_id,
 category,
 brand
SUM(quantity) 
OVER(PARTITION BY customer_id ORDER BY timestamp ASC ROWS 3 PRECEDING) as rolling_sum 
FROM order

但是，我不想对当前和前两行进行运行 SUM，而是对当前 id 和前 2 个 order_ids 进行求和，并对每个即将到来的 order_id 执行此操作，因此不仅仅是最后 3 个或前 3 个订单，而是每个订单的最后 3 个订单作为 customer_id 可能有 100 个订单等。

基本上得到这个输出

时间戳	顾客姓名	order_id	类别	牌	数量	滚动总和
2020-09-02 09:53:59.000000	约翰	4213	计算机	苹果	4	4
2020-09-02 09:53:59.000000	约翰	4213	移动的	苹果	2	2
2020-09-02 09:53:59.000000	约翰	4213	计算机	生命值	3	3
2020-10-24 00:15:38.000000	约翰	8321	计算机	苹果	0	4
2020-10-24 00:15:38.000000	约翰	8321	计算机	生命值	4	7
2020-10-24 00:15:38.000000	约翰	8312	移动的	苹果	3	5
2021-05-04 02:27:18.000000	约翰	3124	移动的	苹果	3	8
2021-05-04 02:27:18.000000	约翰	3124	计算机	苹果	5	9
2021-05-04 02:27:18.000000	约翰	3124	计算机	生命值	0	7

据我了解，这对于常规窗口函数是不可能的，并且需要 JOINS 和公用表表达式之间的组合，所以如果您能够看到任何类型的解决方案，我会很高兴听到您的想法。

标签： sqlfunctionwindowamazon-redshiftcommon-table-expression

Answer 1

我建议总结和使用窗口函数：

select orderid, customerid, timestamp, sum(quantity) as quantity,
       sum(sum(quantity)) over (partition by customerid order by timestamp rows between 3 preceding and current row) as running_quantity_4
from orders
group by orderid, customerid, timestamp;

然后join回到你的桌子：

select o.*, oct.running_quantity_4
from orders o join
     (select orderid, customerid, timestamp, sum(quantity) as quantity,
             sum(sum(quantity)) over (partition by customerid order by timestamp rows between 3 preceding and current row) as running_quantity_4
      from orders
      group by orderid, customerid, timestamp
     ) oct
     on oct.orderid = o.orderid;