Contents

1 ろ 一危
2 sum, accumulate
3 rank, dense_rank, row_number
4 lead, lag
5 range, rows
6 cume_dist, percent_rank
7 first_value, last_value
8 percentile_cont, percentile_disc
9 谿瑚襭


譴..

sql server 2011 ctp3 version

1 ろ 一危 #

--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

2 sum, accumulate #

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
*/

3 rank, dense_rank, row_number #

--
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
*/

4 lead, lag #

-- ろ 螳語り鍵
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
*/

5 range, rows #

--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
*/

6 cume_dist, percent_rank #

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
*/

7 first_value, last_value #

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
*/

8 percentile_cont, percentile_disc #

--覦焔 
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

*/

9 谿瑚襭 #