數(shù)據(jù)庫概論第8章窗口函數(shù)移動平均線多頭排列金叉MACD：指數(shù)平滑異同平均線KDJBOLLK線分析需求：窗口移動方式滑動窗口：最近跳動窗口：每隔窗口范圍基于時間基于行基于值自定義窗口聚集stock_id trade_dayopen_pricehigh_pricelow_priceclose_pricevolume amountsz000594 2008-08-12 3.780 4.080 3.700 4.060 12926610 50557658sz000594 2008-08-11 3.950 4.180 3.620 3.800 8941089 34240973sz000594 2008-08-08 4.110 4.180 3.910 4.020 8479283 34579216sz000594 2008-08-07 3.960 4.120 3.950 4.110 4491515 18259494sz000594 2008-08-06 3.890 4.000 3.810 4.000 5582197 21883585sz000594 2008-08-05 4.000 4.060 3.900 3.900 2712970 10820544sz000594 2008-08-04 4.070 4.170 4.010 4.020 4051105 16574212sz000594 2008-07-31 4.210 4.240 4.020 4.020 4051537 16536682sz000594 2008-07-30 4.230 4.290 4.150 4.250 3863285 16318126sz000594 2008-07-29 4.240 4.270 4.090 4.190 4757301 19864337累積聚集select

S1.stock_id,S1.trade_day,S1.close_price,

avg(S2.close_price)

as

avg_pricefrom

stock

S1,stock

S2where

S1.stock_id

=

S2.stock_id

and

S2.trade_day<=S1.trade_daygroup

by

S1.stock_id,S1.trade_day,S1.close_priceorder

by

S1.stock_id,S1.trade_day累計聚集stock_id trade_dayclose_price avg_pricesz000594 2008-06-02 6.100 6.1sz000594 2008-06-03 6.030 6.065sz000594 2008-06-04 5.920 6.01666666666667sz000594 2008-06-05 5.990 6.01sz000594 2008-06-06 6.020 6.012sz000594 2008-06-10 5.420 5.91333333333333sz000594 2008-06-11 4.950 5.77571428571429sz000594 2008-06-12 4.910 5.6675sz000594 2008-06-13 4.430 5.53sz000594 2008-06-16 4.240 5.401滑動聚集select

S1.stock_id,S1.trade_day,S1.close_price, avg(S2.close_price)

as

avg_pricefrom

stock1

S1,stock1

S2where

S1.stock_id

=

S2.stock_id

and

S2.trade_day

<=

S1.trade_day

and

day(S2.trade_day)

+2>

day(S1.trade_day)group

by

S1.stock_id,S1.trade_day,S1.close_pricestock_id trade_dayclose_price avg_pricesz000594 2008-06-02 6.100 6.1sz000594 2008-06-03 6.030 6.065sz000594 2008-06-04 5.920 5.975sz000594 2008-06-05 5.990 5.955sz000594 2008-06-06 6.020 6.005sz000594 2008-06-10 5.420 5.42sz000594 2008-06-11 4.950 5.185sz000594 2008-06-12 4.910 4.93sz000594 2008-06-13 4.430 4.67sz000594 2008-06-16 4.240 4.245,-8,5,5,-8,5,-8,5,5,-8,5窗口函數(shù)function_name(<argument>,<argument>,…)over (<Partitionby子句> <Orderby子句> <Windowing子句>)Partitionby：對表進行分區(qū)，類似groupbyOrderby：

排序Windowing：窗口函數(shù)窗口函數(shù)partitionbystock_id：按照股票號分區(qū)orderbytrade_day：按照交易日期排序rowsbetween5precedingand3following：每行對應的數(shù)據(jù)窗口是之前5行，之后3行rowsbetweenunboundedprecedingandunboundedfollowing(currentrow)每行對應的數(shù)據(jù)窗口是從第一行到最后一行（當前行）rangebetween50precedingand150following：每行對應的數(shù)據(jù)窗口包含比當前行值大50以及小于150的行stock（stock_id,trade_day,open_price,high_price,low_price,close_price)窗口函數(shù)select stock_id,

trade_day,close_price,

avg(close_price)

over

(

order

by

trade_day

rows

between1preceding

and1following)

as

avg_pricefrom

stockstock_id trade_day close_priceavg_pricesz000594 2008-06-02 6.100 4.04333333333333sz000594 2008-06-03 6.030 6.01666666666667sz000594 2008-06-04 5.920 5.98sz000594 2008-06-05 5.990 5.97666666666667sz000594 2008-06-06 6.020 5.81sz000594 2008-06-10 5.420 5.46333333333333sz000594 2008-06-11 4.950 5.09333333333333窗口函數(shù)類型傳統(tǒng)聚集函數(shù)：sum,avg,count,max,min排名函數(shù)：rank,dense_rank,row_number分布函數(shù)：percent_rank,cume_dist逆分布函數(shù)：percent_cont,percentile_disc偏移函數(shù)：lag,lead示例表Scorestestid studentid scoreTestABC StudentE 50TestABC StudentC 55TestABC StudentD 55TestABC StudentH 65TestABC StudentI 75TestABC StudentB 80TestABC StudentF 80TestABC StudentA 95TestABC StudentG 95TestXYZ StudentE 50TestXYZ StudentC 55TestXYZ StudentD 55TestXYZ StudentH 65TestXYZ StudentI 75TestXYZ StudentB 80TestXYZ StudentF 80TestXYZ StudentA 95TestXYZ StudentG 95TestXYZ StudentJ 95一般聚合函數(shù)select

testid,studentid,score,

count(*)

over(partition

by

testid

order

by

score

rows

between

unbounded

preceding

and

current

row)

as

cntfrom

Stats.Scorestestid studentid score cntTestABC StudentE 50 1TestABC StudentC 55 2TestABC StudentD 55 3TestABC StudentH 65 4TestABC StudentI 75 5TestABC StudentB 80 6TestABC StudentF 80 7TestABC StudentA 95 8TestABC StudentG 95 9TestXYZ StudentE 50 1TestXYZ StudentC 55 2排名函數(shù)rank：序號重復但不連續(xù)的排名dense_rank：序號重復并且連續(xù)的排名row_number：序號不重復但連續(xù)的排名rankdense_rankrow_number101111011293238434843584367747自定義排名函數(shù)如何對重復列生成row_number()?create

function

fn_score_rank

(

@score_val

int

)returns

intbegin declare

@rk

int set

@rk

=( select

count

(

distinct

score

)

+1 from

Scores

where

score

>

score_val) return

@rkend排名函數(shù)對比select

studentid,score,

rank()

over(order

by

score

desc)

as

rk, dense_rank()

over(

order

by

score

desc)

as

drk, row_number()

over(

order

by

score

desc)

as

rnfrom

Stats.Scoresstudentid score rk drk rnStudentA 95 1 1 1StudentG 95 1 1 2StudentA 95 1 1 3StudentG 95 1 1 4StudentJ 95 1 1 5StudentB 80 6 2 6StudentF 80 6 2 7StudentB 80 6 2 8StudentF 80 6 2 9StudentI 75 10 3 10ntileselect

studentid,score, ntile(4)

over(order

by

score

desc)

as

nt1, ntile(3)

over(order

by

score

desc)

as

nt2from

Stats.Scoresstudentid

score nt1 nt2StudentG

95 1 1StudentA

95 1 1StudentF

80 1 1StudentB

80 2 2StudentI

75 2 2StudentH

65 3 2StudentD

55 3 3StudentC

55 4 3StudentE

50 4 3percent_rank計算一個值在一組值當中的相對位置或排名rk：rank排名；nr：窗口內(nèi)總行數(shù)percent_rank=(rk-1)/(nr-1)studentid score prStudentG 95 0StudentA 95 0StudentF 80 0.25StudentB 80 0.25StudentI 75 0.5StudentH 65 0.625StudentD 55 0.75StudentC 55 0.75StudentE 50 1select

studentid,score,

percent_rank()

over(order

by

score

desc)

as

prfrom

Stats.Scorescume_dist計算某個值一組值內(nèi)的累積分布，也即計算某指定值在一組值中的相對位置對于值r，假定采用升序，r的cume_dist是值低于或等于r的值的行數(shù)除以整個行數(shù)studentid score cdStudentG 95 0.222222222222222StudentA 95 0.222222222222222StudentF 80 0.444444444444444StudentB 80 0.444444444444444StudentI 75 0.555555555555556StudentH 65 0.666666666666667StudentD 55 0.888888888888889StudentC 55 0.888888888888889StudentE 50 1select

studentid,score,

cume_dist()

over(order

by

score

desc)

as

cdfrom

Stats.Scores逆分布函數(shù)percentile_disc：離散百分位percentile_cont：連續(xù)百分位select

distinct

testid,

percentile_disc(0.5)

within

group(order

by

score

)

over(partition

by

testid)

as

d_median,

percentile_cont(0.5)

within

group(order

by

score

)

over(partition

by

testid)

as

c_median,

percentile_disc(0.25)

within

group(order

by

score)

over(partition

by

testid)

as

d_lower_quartile,

percentile_cont(0.25)

within

group(order

by

score)

over(partition

by

testid)

as

c_lower_quartile,

percentile_disc(0.75)

within

group(order

by

score)

over(partition

by

testid)

as

d_upper_quartile,

percentile_cont(0.75)

within

group(order

by

score)

over(partition

by

testid)

as

c_upper_quartilefrom

Stats.ScoresTestidd_medianc_mediand_lower_quartilec_lower_quartiled_upper_quartilec_upper_quartileTestABC 75 75 55

55

80

80TestXYZ 75 77.5 55

57.5

95

91.25中位數(shù) {3,5,7,8,37}

中位數(shù)是7，平均值是12

{50.2,25.7,32.0,17.2,18.4,19.6,44.3,22.5,1000.7}

集合中元素數(shù)目是奇數(shù)，則就是正中間的那個25.7

{50.2,25.7,32.0,17.2,18.4,19.6,44.3,1000.7}

集合中元素數(shù)目是偶數(shù)，則就是正中間的兩個數(shù)的平均(25.7+32.0)=28.85中位數(shù)withdt1as (select grade,rownumber()over(orderbygrade)asnum

from SC)dt2as (select

count(grade)+1ascnt from dt1)dt3as (select grade from dt1,dt2

where num=floor(cnt/2e0)

or num=ceiling(cnt/2e0))select

decimal(avg(grade))asmedianfrom dt3floor：返回小于或等于給定數(shù)字表達式的最大整數(shù)ceiling：返回大于或等于給定數(shù)字表達式的最小整數(shù)中位數(shù)withdt1as (select max(grade)asM1

from (select top(50)percentgrade

from SC

orderby grade)dt2as (select min(grade)asM2

from (select top(50)percentgrade

from SC

orderby gradedesc)select (M1+M2)/2.0asmedianfrom dt1,dt2中位數(shù)=(按升序前50%中的最大值+

按降序前50%中的最小值)/2.0偏移函數(shù)lag(表達式,偏移量,缺省值)over(…)將當前行與同一值集中的先前行進行比較lead(表達式,偏移量,缺省值)over(…)將當前行與同一值集中的后續(xù)行進行比較select

lag(score,1,0)

over(order

by

score

)

as

pre_score, score, lead(score,1,0)

over(order

by

score

)

as

next_scorefrom

Stats.Scorespre_score score next_score0 50 5050 50 5550 55 5555 55 5555 55 5555 55 65窗口函數(shù)實習最大并發(fā)間隔問題產(chǎn)生一個報告，比較三種方法的性能，分析其執(zhí)行計劃基于集合游標窗口函數(shù)窗口函數(shù)實習談股