[edit]
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
*/
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
*/