SQL Server:具有不同长度的分析窗口函数

Question

我想写一个分析函数，在几个不同长度的窗口上使用聚合函数。假设我有一张股票收盘价格表，看起来像这样：

Ticker | TradeDate | ClosePrice | 
----------------------------------------
A1      20201209       1.1       
A1      20201208       1.2
A1      20201207       1.6
.......
A1      20191209       1.1       
A1      20191208       1.2
A1      20191207       1.6
A2      20201209       2.1       
A2      20201208       2.2
A2      20201207       2.6
.......
A2      20191209       2.1       
A2      20191208       2.2
A2      20191207       2.6

现在我想得到这样的结果(返回的行数与原始表相同(：

Ticker | TradeDate | ClosePrice | Past3DaysAverage | Past1MonthAverage | Past1YearAverage

因此，前三列与原始表相同。第四列，我把它作为参考，表示过去3个交易日的平均价格，直到同一股票的交易日期(因此是partition by条款(。到目前为止，我可以写这样的查询：

select 
t.Ticker, t.TradeDate, t.ClosePrice, 
avg(t.ClosePrice) over (partition by t.Ticker order by TradeDate rows between 2 preceding and current row) as Past3DaysAverage
from PriceTable t

最后两列计算相同Ticker在过去1个月和1年的平均价格，直到交易日期。现在它很麻烦，因为我不知道如何指定不同的窗口长度(如果可能的话(，因为一年(或一个月(到不同日期的交易天数不同，所以我不能使用ROWS(或RANGE(的类似例程。有人能帮我吗？

非常感谢！

Answer 1

遗憾的是，SQL Server不支持range框架到窗口函数。最简单的方法可能是横向连接：

select t.*, t1.*, t2.*, t3.*
from pricetable t
cross apply (
select avg(t1.closeprice) as past_3days_average
from pricetable t1
where 
t1.ticker = t.ticker 
and t1.tradedate >= dateadd(day, -3, t.tradedate)
and t1.tradedate <= t.tradedate
) as t1
cross apply (
select avg(t1.closeprice) as past_1month_average
from pricetable t1
where 
t1.ticker = t.ticker 
and t1.tradedate >= dateadd(month, -1, t.tradedate)
and t1.tradedate <= t.tradedate
) t2
cross apply (
select avg(t1.closeprice) as past_1year_average
from pricetable t1
where 
t1.ticker = t.ticker 
and t1.tradedate >= dateadd(year, -1, t.tradedate)
and t1.tradedate <= t.tradedate
) t3

另一种选择只使用单个横向连接和条件聚合：

select t.*, t1.*
from pricetable t
cross apply (
select 
avg(case when t1.trade_date >= dateadd(day,   -3, t.tradedate) then closeprice end) as past_3days_average,
avg(case when t1.trade_date >= dateadd(month, -1, t.tradedate) then closeprice end) as past_1month_average,
avg(t1.closeprice) as past_1year_average
from pricetable t1
where 
t1.ticker = t.ticker 
and t1.tradedate >= dateadd(year, -1, t.tradedate)
and t1.tradedate <= t.tradedate
) t1