数据库概论 第八章窗口函数

数据库概论第8章窗口函数移动平均线多头排列金叉MACD：指数平滑异同平均线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窗口函数function_name(<argument>,<argument>,…)over (<Partitionby子句> <Orderby子句> <Windowing子句>)Partitionby：对表进行分区，类似groupbyOrderby：

排序Windowing：窗口函数窗口函数partitionbystock_id：按照股票号分区orderbytrade_day：按照交易日期排序rowsbetween5precedingand3following：每行对应的数据窗口是之前5行，之后3行rowsbetweenunboundedprecedingandunboundedfollowing(currentrow)每行对应的数据窗口是从第一行到最后一行（当前行）rangebetween50precedingand150following：每行对应的数据窗口包含比当前行值大50以及小于150的行stock（stock_id,trade_day,open_price,high_price,low_price,close_price)窗口函数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窗口函数类型传统聚集函数：sum,avg,count,max,min排名函数：rank,dense_rank,row_number分布函数：percent_rank,cume_dist逆分布函数：percent_cont,percentile_disc偏移函数：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一般聚合函数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排名函数rank：序号重复但不连续的排名dense_rank：序号重复并且连续的排名row_number：序号不重复但连续的排名rankdense_rankrow_number101111011293238434843584367747自定义排名函数如何对重复列生成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排名函数对比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：窗口内总行数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计算某个值一组值内的累积分布，也即计算某指定值在一组值中的相对位置对于值r，假定采用升序，r的cume_dist是值低于或等于r的值的行数除以整个行数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逆分布函数percentile_disc：离散百分位percentile_cont：连续百分位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中位数 {3,5,7,8,37}

中位数是7，平均值是12

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

集合中元素数目是奇数，则就是正中间的那个25.7

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

集合中元素数目是偶数，则就是正中间的两个数的平均(25.7+32.0)=28.85中位数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：返回小于或等于给定数字表达式的最大整数ceiling：返回大于或等于给定数字表达式的最小整数中位数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中位数=(按升序前50%中的最大值+

按降序前50%中的最小值)/2.0偏移函数lag(表达式,偏移量,缺省值)over(…)将当前行与同一值集中的先前行进行比较lead(表达式,偏移量,缺省值)over(…)将当前行与同一值集中的后续行进行比较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窗口函数实习最大并发间隔问题产生一个报告，比较三种方法的性能，分析其执行计划基于集合游标窗口函数窗口函数实习谈股