#title Run

==== t-sql 구현사례 ====
예제 데이터
{{{
drop table #temp
create table #temp
(
    dt date
,   val int
)
go

declare 
    @i int = 1
,   @dt date = '2011-09-21'
while(@i <= 50)
begin
    insert #temp
    values(dateadd(dd, @i, @dt), convert(int, rand() * 1000))

    set @i += 1
end
go
--2011-09-21 ~ 2011-11-09
/*
select * from #temp

dt         val
---------- -----------
2011-09-22 818
2011-09-23 190
2011-09-24 365
2011-09-25 313
2011-09-26 920
2011-09-27 859
2011-09-28 859
2011-09-29 37
2011-09-30 85
2011-10-01 737
2011-10-02 291
2011-10-03 303
2011-10-04 536
2011-10-05 750
2011-10-06 507
2011-10-07 564
2011-10-08 816
2011-10-09 695
2011-10-10 381
2011-10-11 662
2011-10-12 874
2011-10-13 212
2011-10-14 9
2011-10-15 874
2011-10-16 85
2011-10-17 24
2011-10-18 471
2011-10-19 631
2011-10-20 584
2011-10-21 889
2011-10-22 908
2011-10-23 898
2011-10-24 911
2011-10-25 256
2011-10-26 10
2011-10-27 246
2011-10-28 322
2011-10-29 897
2011-10-30 138
2011-10-31 933
2011-11-01 746
2011-11-02 575
2011-11-03 962
2011-11-04 500
2011-11-05 872
2011-11-06 182
2011-11-07 522
2011-11-08 671
2011-11-09 949
2011-11-10 450
*/
}}}

관리도
{{{
select 
    dt
,   val
,   avg(val) over() [AVG]
,   avg(val) over() + stdevp(val) over()*1.5 UCL
,   avg(val) over() + stdevp(val) over()*-1.5 LCL
from #temp
where dt between '20111001' and '20111031'
--이상치는 {평균+표준편차*1.5 ~ 평균-표준편차*1.5} 범위를 벗어나면 이상치라고 설정

/*
dt         val         AVG         UCL                    LCL
---------- ----------- ----------- ---------------------- ----------------------
2011-10-01 737         529         987.995710126655       70.0042898733451
2011-10-02 291         529         987.995710126655       70.0042898733451
2011-10-03 303         529         987.995710126655       70.0042898733451
2011-10-04 536         529         987.995710126655       70.0042898733451
2011-10-05 750         529         987.995710126655       70.0042898733451
2011-10-06 507         529         987.995710126655       70.0042898733451
2011-10-07 564         529         987.995710126655       70.0042898733451
2011-10-08 816         529         987.995710126655       70.0042898733451
2011-10-09 695         529         987.995710126655       70.0042898733451
2011-10-10 381         529         987.995710126655       70.0042898733451
2011-10-11 662         529         987.995710126655       70.0042898733451
2011-10-12 874         529         987.995710126655       70.0042898733451
2011-10-13 212         529         987.995710126655       70.0042898733451
2011-10-14 9           529         987.995710126655       70.0042898733451
2011-10-15 874         529         987.995710126655       70.0042898733451
2011-10-16 85          529         987.995710126655       70.0042898733451
2011-10-17 24          529         987.995710126655       70.0042898733451
2011-10-18 471         529         987.995710126655       70.0042898733451
2011-10-19 631         529         987.995710126655       70.0042898733451
2011-10-20 584         529         987.995710126655       70.0042898733451
2011-10-21 889         529         987.995710126655       70.0042898733451
2011-10-22 908         529         987.995710126655       70.0042898733451
2011-10-23 898         529         987.995710126655       70.0042898733451
2011-10-24 911         529         987.995710126655       70.0042898733451
2011-10-25 256         529         987.995710126655       70.0042898733451
2011-10-26 10          529         987.995710126655       70.0042898733451
2011-10-27 246         529         987.995710126655       70.0042898733451
2011-10-28 322         529         987.995710126655       70.0042898733451
2011-10-29 897         529         987.995710126655       70.0042898733451
2011-10-30 138         529         987.995710126655       70.0042898733451
2011-10-31 933         529         987.995710126655       70.0042898733451
*/
}}}
attachment:Run/cchart.png

런
{{{
select
    min(dt) begin_dt
,   max(dt) end_dt
,   hi_low
,   count(*) run_count
,   power(0.5000000000, count(*)) prob --정규분포에서 발생확률
from (
    select
        dt
    ,   val
    ,   avg_val
    ,   row_no
    ,   hi_low
    ,   row_no - row_number() over(order by hi_low, dt) run
    from (
        select 
            dt
        ,   val
        ,   avg(val) over() avg_val
        ,   case when avg(val) over() <= val then '상' else '하' end hi_low
        ,   row_number() over(order by dt) row_no
        from #temp
        where dt between '20111001' and '20111031'
    ) t
) t
group by
    run, hi_low
having count(*) >= 4
order by 1

/*
begin_dt   end_dt     run_count
---------- ---------- -----------
2011-10-19 2011-10-24 6
2011-10-25 2011-10-28 4
*/
}}}