[2k] 이동평균 구하기 위한 Correlated Subquery

1. /**********************************************************************************************
   -- Title : [2k] 이동평균 구하기 위한 Correlated Subquery
   -- Reference : dbRang.com
   -- Key word : correlated subquery, 이동 평균
   **********************************************************************************************/
    
   use tempdb
   go
    
   if object_id('t_T') is not null
    drop table t_T
   go
    
   create table t_T
   (
    col1  char(8) not null
   , col2  int  not null
   )
   go
    
   insert into t_T values ('20030901', 200)
   insert into t_T values ('20030902', 200)
   insert into t_T values ('20030903', 100)
   insert into t_T values ('20030904', 100)
   go
    
   select * from t_T
   go
    
   SELECT
    col1
   , col2
   , (col2 + ( SELECT col2
       FROM t_T T2
       WHERE col1 = ( SELECT Max(col1)
           FROM t_T
           WHERE col1 < T1.col1)
        )
      )/2 AS col3
   FROM t_T T1