对日期范围执行聚合函数

Question

如何在日期范围上执行聚合函数(AVG(

源数据看起来像：

AccNo   Amt     Date    
1       100     2/1/2021
1       200     2/5/2021
1       300     3/3/2021
2       400     5/20/2021
2       500     5/18/2021

目标需要通过以下方法导出：

例如：要导出avg_past_week，请计算日期范围Date到Date - 7内所有行的平均金额

类似地，对于avg_past_month，它将是Date到Date - 30

目标：

AccNo   Amt     Date        Avg_past_week   Avg_past_month  Avg_past_3 month
1       100     2/1/2021    100             100             100
1       200     2/5/2021    150             150             150
1       300     3/3/2021    300             250             200
2       400     5/20/2021   450             450             450
2       500     5/18/2021   500             500             500

Answer 1

select a.AccNo, a.Amt, a.Date,
avg(b.Amt) as Amt7,
avg(c.Amt) as Amt30,
avg(d.Amt) as Amt90
from T a 
join T b on a.AccNo = b.AccNo and b.Date > DateAdd(day, -7, a.Date) and b.date <= a.Date
join T c on a.AccNo = c.AccNo and c.Date > DateAdd(day, -30, a.Date) and c.date <= a.Date
join T d on a.AccNo = d.AccNo and d.Date > DateAdd(day, -90, a.Date) and d.date <= a.Date
group by a.AccNo, a.Amt, a.Date

结果

1   100 2021-02-01  100 100 100
1   200 2021-02-05  150 150 150
1   300 2021-03-03  300 250 200
2   400 2021-05-20  450 450 450
2   500 2021-05-18  500 500 500

Answer 2

您可以使用窗口函数：

select t.*,
avg(amt) over (partition by accNo
order by datediff('2000-01-01', date)
range between 6 preceding and current row
) as avg_7day,
avg(amt) over (partition by accNo
order by datediff('2000-01-01', date)
range between 30 preceding and current row
) as avg_30day,
avg(amt) over (partition by accNo
order by datediff('2000-01-01', date)
range between 90 preceding and current row
) as avg_91day
from t;

这会将日期转换为数字(自某个任意日期以来的天数(，然后使用时间段的窗口框架。

请注意，返回的天数比期间少一天，因为当前日期也包括在内。