--drop table #temp create table #temp ( seq int identity(1,1) , std_dt date , val float ) go insert #temp values ('20110801', 600) , ('20110801', 200) , ('20110801', 800) , ('20110802', 700) , ('20110802', 500) , ('20110802', 100) , ('20110803', 700) , ('20110803', 300) , ('20110803', 900) go
select std_dt , val , sum(val) over(partition by std_dt) 讌覲豌危 , sum(val) over(partition by std_dt order by seq) 讌覲 , sum(val) over() 豌危 , sum(val) over(order by seq) SEQ襦_ from #temp /* std_dt val 讌覲豌危 讌覲 豌危 SEQ襦_ ---------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 2011-08-01 600 1600 600 4800 600 2011-08-01 200 1600 800 4800 800 2011-08-01 800 1600 1600 4800 1600 2011-08-02 700 1300 700 4800 2300 2011-08-02 500 1300 1200 4800 2800 2011-08-02 100 1300 1300 4800 2900 2011-08-03 700 1900 700 4800 3600 2011-08-03 300 1900 1000 4800 3900 2011-08-03 900 1900 1900 4800 4800 */ --讌覲 螳 螳 select * from ( select std_dt , val , max(val) over(partition by std_dt) max_val from #temp ) t where val = max_val /* std_dt val max_val ---------- ---------------------- ---------------------- 2011-08-01 800 800 2011-08-02 700 700 2011-08-03 900 900 */ --螳 蠏 螳 蠏殊 螳? select * from ( select *, min(abs_val) over() min_val from ( select *, abs(val - avg(val) over()) abs_val from #temp ) t ) t where abs_val = min_val /* seq std_dt val abs_val min_val ----------- ---------- ---------------------- ---------------------- ---------------------- 5 2011-08-02 500 33.3333333333334 33.3333333333334 */
-- select std_dt , val , rank() over(partition by std_dt order by val) [std_dt_rank] , rank() over(order by val) [rank] , dense_rank() over(order by val) [dense_rank] , row_number() over(order by val) [row_number] from #temp /* std_dt val std_dt_rank rank dense_rank row_number ---------- ---------------------- -------------------- -------------------- -------------------- -------------------- 2011-08-02 100 1 1 1 1 2011-08-01 200 1 2 2 2 2011-08-03 300 1 3 3 3 2011-08-02 500 2 4 4 4 2011-08-01 600 2 5 5 5 2011-08-02 700 3 6 6 6 2011-08-03 700 2 6 6 7 2011-08-01 800 3 8 7 8 2011-08-03 900 3 9 8 9 */
-- ろ 螳語り鍵 select seq , std_dt , val , lead(seq, 1) over(order by seq) 1 , lag(seq, 1) over(order by seq) 1 , lead(seq, 2, 0) over(order by seq) 2 --朱 null 螳 0 , lag(seq, 2, 0) over(order by seq) 2 --朱 null 螳 0 from #temp /* seq std_dt val 1 1 2 2 ----------- ---------- ---------------------- ----------- ----------- ----------- ----------- 1 2011-08-01 600 2 NULL 3 0 2 2011-08-01 200 3 1 4 0 3 2011-08-01 800 4 2 5 1 4 2011-08-02 700 5 3 6 2 5 2011-08-02 500 6 4 7 3 6 2011-08-02 100 7 5 8 4 7 2011-08-03 700 8 6 9 5 8 2011-08-03 300 9 7 0 6 9 2011-08-03 900 NULL 8 0 7 */
--range select seq , std_dt , val , min(val) over(order by seq range between 100 preceding and 200 following) from #temp --覃讌 4194, 譴 16, 1, 譴 1 --RANGE UNBOUNDED 覦 CURRENT ROW 谿 蟲覿 蠍壱語襷 讌. select seq , std_dt , val , min(val) over(order by seq range between current row and unbounded following) 螳_螳ル讌襷螳譴_螳レ螳 , sum(val) over(order by seq rows between 2 preceding and current row) 螳_2蟇伎伎螳_ , sum(val) over(order by seq rows between unbounded preceding and current row) 螳_豌螳_ from #temp order by 1 /* seq std_dt val 螳_螳ル讌襷螳譴_螳レ螳 ----------- ---------- ---------------------- ---------------------- 1 2011-08-01 600 100 2 2011-08-01 200 100 3 2011-08-01 800 100 4 2011-08-02 700 100 5 2011-08-02 500 100 6 2011-08-02 100 100 7 2011-08-03 700 300 8 2011-08-03 300 300 9 2011-08-03 900 900 */ --rows select seq , std_dt , val , min(val) over(order by seq rows between 1 preceding and 1 following) る1蟇伎_螳レ螳 , min(val) over(order by seq rows between current row and 2 following) 朱2蟇伎_螳レ螳 from #temp order by 1 /* seq std_dt val る1蟇伎_螳レ螳 朱2蟇伎_螳レ螳 ----------- ---------- ---------------------- ---------------------- ---------------------- 1 2011-08-01 600 200 200 2 2011-08-01 200 200 200 3 2011-08-01 800 200 500 4 2011-08-02 700 500 100 5 2011-08-02 500 100 100 6 2011-08-02 100 100 100 7 2011-08-03 700 100 300 8 2011-08-03 300 300 300 9 2011-08-03 900 300 900 */ --row 蟲覓語 "current row" "0 preceding" select val , sum(val) over(order by val rows between 0 preceding and 0 following) , sum(val) over(order by val rows between 1 preceding and 0 following) 伎1 , sum(val) over(order by val rows between 0 preceding and 1 following) 危1 , sum(val) over(order by val rows between 1 preceding and 1 following) 1 , sum(val) over(order by val range between current row and unbounded following) [~襷讌襷] from (values (1),(2),(3),(4),(5),(6),(7)) t(val) order by 1 /* val 伎1 危1 1 ~襷讌襷 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 3 3 28 2 2 3 5 6 27 3 3 5 7 9 25 4 4 7 9 12 22 5 5 9 11 15 18 6 6 11 13 18 13 7 7 13 7 13 7 */
select seq , std_dt , val , cume_dist() over(order by std_dt) --覿(豌伎 覈% ?) , rank() over(order by std_dt) [rank] , percent_rank() over(order by std_dt) [percent_rank] , convert(float, rank() over(order by std_dt)-1) / (count(*) over()-1) calc_percent_rank--percent_rank()螻 from #temp /* seq std_dt val rank percent_rank calc_percent_rank ----------- ---------- ---------------------- ---------------------- -------------------- ---------------------- ---------------------- 1 2011-08-01 600 0.333333333333333 1 0 0 2 2011-08-01 200 0.333333333333333 1 0 0 3 2011-08-01 800 0.333333333333333 1 0 0 4 2011-08-02 700 0.666666666666667 4 0.375 0.375 5 2011-08-02 500 0.666666666666667 4 0.375 0.375 6 2011-08-02 100 0.666666666666667 4 0.375 0.375 7 2011-08-03 700 1 7 0.75 0.75 8 2011-08-03 300 1 7 0.75 0.75 9 2011-08-03 900 1 7 0.75 0.75 */
select seq , std_dt , val , first_value(val) over(partition by std_dt order by val) first_val , last_value(val) over(partition by std_dt order by val) last_val-- . --rows between unbounded preceding and unbounded following螻 螳 伎 . from #temp /* seq std_dt val first_val last_val ----------- ---------- ---------------------- ---------------------- ---------------------- 2 2011-08-01 200 200 200 1 2011-08-01 600 200 600 3 2011-08-01 800 200 800 6 2011-08-02 100 100 100 5 2011-08-02 500 100 500 4 2011-08-02 700 100 700 8 2011-08-03 300 300 300 7 2011-08-03 700 300 700 9 2011-08-03 900 300 900 */
--覦焔 select seq , std_dt , val , cume_dist() over(order by val) cume_dist , percentile_cont(0.5) within group(order by val) over() median_cont--0.5企 覃伎.., 一 , percentile_disc(0.5) within group(order by val) over() median_disc --伎壱 , percentile_disc(0.5) within group(order by std_dt) over() median_dt from #temp /* seq std_dt val cume_dist median_cont median_disc median_dt ----------- ---------- ---------------------- ---------------------- ---------------------- ---------------------- ---------- 2 2011-08-01 200 0.222222222222222 600 600 2011-08-02 1 2011-08-01 600 0.555555555555556 600 600 2011-08-02 3 2011-08-01 800 0.888888888888889 600 600 2011-08-02 4 2011-08-02 700 0.777777777777778 600 600 2011-08-02 6 2011-08-02 100 0.111111111111111 600 600 2011-08-02 5 2011-08-02 500 0.444444444444444 600 600 2011-08-02 8 2011-08-03 300 0.333333333333333 600 600 2011-08-02 7 2011-08-03 700 0.777777777777778 600 600 2011-08-02 9 2011-08-03 900 1 600 600 2011-08-02 */