Haggan Haggan - 12 days ago 7
SQL Question

SQL 2008 Running average with group by

So i have a table containing below columns.
I want to compute an running average from positiondate and for example 3 days back, grouped on dealno.
I know how to do with "case by" but problem is that I have around 200 different DealNo so I do not want to write an own case by clause for every deal.

On dealNo 1 it desired output should be Average(149 243 440 + 149 224 446 + 149 243 451)

DealNo PositionDate MarketValue
1 | 2016-11-27 | 149 243 440
2 | 2016-11-27 | 21 496 418
3 | 2016-11-27 | 32 249 600
1 | 2016-11-26 | 149 243 446
2 | 2016-11-26 | 21 496 418
3 | 2016-11-26 | 32 249 600
1 | 2016-11-25 | 149 243 451
3 | 2016-11-25 | 32 249 600
2 | 2016-11-25 | 21 496 418
3 | 2016-11-24 | 32 249 600
1 | 2016-11-24 | 149 225 582
2 | 2016-11-24 | 21 498 120
1 | 2016-11-23 | 149 256 867
2 | 2016-11-23 | 21 504 181
3 | 2016-11-23 | 32 253 440
1 | 2016-11-22 | 149 256 873
2 | 2016-11-22 | 21 506 840
3 | 2016-11-22 | 32 253 440
1 | 2016-11-21 | 149 234 535
2 | 2016-11-21 | 21 509 179
3 | 2016-11-21 | 32 253 600


I tried below script but it was not very effective since my table contains around 300k rows and approx 200 different dealno.
Is there a more effective way to do this in SQL 2008?

with cte as (

SELECT ROW_NUMBER() over(order by dealno, positiondate desc) as Rownr,
dealno,
positiondate,
Currency,
MvCleanCcy
FROM T1
)

select
rownr, positiondate, DealNo, Currency,
mvcleanavg30d = (select avg(MvCleanCcy) from cte2 where Rownr between c.Rownr and c.Rownr+3)

from cte as c

Answer

You don't need window functions. You can do this using outer apply:

select t1.*, tt1.marketvalue_3day
from t1 outer apply
     (select avg(tt1.marketvalue) as marketvalue_3day
      from (select top 3 tt1.*
            from t1 tt1
            where tt1.deal1 = t1.deal1 and
                  tt1.positiondate <= t1.positiondate
            order by tt1.positiondate desc
           ) tt1
     ) tt1;