--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
*/
![[http]](/moniwiki/imgs/http.png)
Window Functions in SQL Server: Part 2-The Frame(http://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-2-the-frame/)The new Analytic functions in SQL Server 2012(http://www.sqlservercentral.com/articles/SQL+Server+2012/76704/)