Блог

Я хотел бы написать аналитическую функцию с агрегированными функциями для нескольких окон с разной длиной. Допустим, у меня есть таблица цен закрытия акций, которая выглядит следующим образом:

 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
 

В последних двух столбцах вычисляются средние цены одного и того же тикера за последние 1 месяц и 1 год до даты торгов. Теперь это вызывает проблемы, потому что я не знаю, как указать различную длину окна (если это возможно), потому что количество торговых дней в течение одного года (или одного месяца) до разных дат отличается, поэтому я не могу использовать аналогичную процедуру ROWS (или RANGE ). Кто-нибудь может мне помочь с этим?

Большое вам спасибо!

К сожалению, 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