title: SQL Server: Analytic window function with varying lengths
body: <p>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:</p>
  <pre><code>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
  </code></pre>
  <p>And now I want to get the result like this (the number of returned rows is the same as the original table):</p>
  <pre><code>Ticker | TradeDate | ClosePrice | Past3DaysAverage | Past1MonthAverage | Past1YearAverage
  </code></pre>
  <p>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 <code>partition by</code> clause). So far I can write the query like :</p>
  <pre><code>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
  </code></pre>
  <p>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 <code>ROWS</code> (or <code>RANGE</code>). Can anyone help me with that?</p>
  <p>Thank you very much!</p>