SQL Server: Analytic window function with varying lengths

Question

I would like to write an analytic function with aggregated functions on several windows with varying lengths. Let's say I have a table of close prices of stocks, which looks like this:

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

And now I want to get the result like this (the number of returned rows is the same as the original table):

Ticker | TradeDate | ClosePrice | Past3DaysAverage | Past1MonthAverage | Past1YearAverage

So the first three columns are the same as the original table. The fourth column, which I put as a reference, indicates the average price of past 3 trading days until TradeDate of the same ticker (hence a partition by clause). So far I can write the query like :

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

The last two columns calculates the average prices of the same Ticker for the past 1 month and 1 year until the TradeDate. Now it troubles because I don't know how to specify the varying window lengths (if that's possible) because the numbers of trading days of one year (or one month) until different dates are different, so I can't use the similar routine of ROWS (or RANGE). Can anyone help me with that?

Thank you very much!

score 0 · Answer 1 · answered Dec 16 '20 at 12:21

Unfortunately SQL Server does not support range frames to window functions. The simplest approach might be lateral joins:

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

An alternative uses just a single lateral join and conditinal aggregation:

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

Thank you for your kindly reply. I tried your first solution and it works (in the second one there was some error which I couldn't figure out). But then I realized I mistakenly put some simplificaiton in my original question: not only I want the average price (actualy aso the max and min values) , for the past 1 month and 1 year, but also I need to get the `percentile` value of the close price on TradeDate, for the past 1 month and 1 year. I thought with window function `AVG` and 'PERCENT_RANK' can be applied similarly. But it doesn't seem so with `CROSS APPLY` ? — tete, Dec 17 '20 at 01:34

SQL Server: Analytic window function with varying lengths

1 Answers1