对于具有周数值的每一行，如何计算前几周的行数

Question

所以我有这个表，其中每一行都是一个订单，week值代表下订单的一年中的第n周：

35

orderId	customerId	周
1	2
2	1	35
3	2	35
4	1	36
5	1	37
6	1	38

使用标量子查询将是最容易阅读的。

select orderid, customerid, week,
(select count(*) from the_table where customerid = tx.customerid and week = tx.week - 1) "week-1 orders",
(select count(*) from the_table where customerid = tx.customerid and week < tx.week) "prev-weeks orders"
from the_table as tx;

或者使用带有子查询的横向连接

select orderid, customerid, week, l.*
from the_table as tx cross join lateral     
(
select 
count(*) filter (where customerid = tx.customerid and week = tx.week - 1) "week-1 orders",
count(*) filter (where customerid = tx.customerid and week < tx.week) "prev-weeks orders"
from the_table
) as l;

演示

使用窗口函数可能也有一个很好的解决方案。