在固定时间窗口中为每个记录获取列的分区和

Question

TelNo   | OrderDate     | Orders  
A       | 2017-01-01    | 5  
A       | 2017-02-02    | 4  
A       | 2017-07-05    | 6  
A       | 2017-09-10    | 10  
B       | 2017-03-01    | 3  
B       | 2017-04-12    | 6  
B       | 2017-11-10    | 4  

上面是order表，要求在最后6个月内为每个“TelNo”找到“OrderDate”与表中每条记录的“订单”之和。以下是预期产出；

TelNo   | OrderDate     | Sum_of_orders  
A       | 2017-01-01    | 5  
A       | 2017-02-02    | 9  
A       | 2017-07-05    | 10  
A       | 2017-09-10    | 16  
B       | 2017-03-01    | 3  
B       | 2017-04-12    | 9  
B       | 2017-11-10    | 4  

我尝试过CTE和sum(case -)的不同组合(按-)方法(通过-)进行分区(SQL-server-2016)，但没有找到解决方案。感谢有人能在这方面提供帮助。

Answer 1

我会使用apply：

select t.TelNo, t.OrderDate, tt.Sum_of_orders  
from table t cross apply (
     select sum(t1.orders) Sum_of_orders
     from table t1
     where t1.TelNo = t.TelNo and
           t1.OrderDate >= dateadd(mm, -6, t.orderdate) and 
           t1.OrderDate <= t.OrderDate
) tt;

Answer 2

您可以使用下面的查询获得所需的结果：

select mt.TelNo, mt.OrderDate, sum(mt1.orders) from MyTbl mt
left join MyTbl mt1 on mt.TelNo = mt1.TelNo and datediff(day,mt1.OrderDate, mt.OrderDate) <= 180
and mt1.orderdate <= mt.OrderDate
Group by mt.TelNo, mt.OrderDate
order by 1,2