数据库概论-基于SQL的数据分析

第8章基于SQL的数据分析2目录抽样直方图TopRank中位数Skyline统计函数抽样抽样应用近似查询，在线聚集，统计直方图，terasort抽样方法行级Bernoulli抽样系统页级抽样抽样评价Efficient、effective数据倾斜和抽样率决定了分析效果单表和多表连接抽样的不同抽样select*from salestablesample

system(3rows)Page1：100行Page2：100行Page3：10行每个页被选中的概率＝[返回行数/总行数]不返回行的概率是(69/70)^3返回行时，2/3的概率返回100行抽样declare@sample_ratefloatset@sample_rate=0.10select *from saleswhere rand(1)<sample_rate问题：rand对整个查询只调用一次抽样createviewmy_randas(selectrand()asrnd)createfunctionfn_rand()returnfloatasbeginreturn(selectrndfrommy_rand)endselect *from saleswhere fn_rand()<sample_rate直方图1,1,5,5,5,5,5,8,8,10,10,10,10,12,14,14,14,15,15,15,15,15,15,18,18,18,18,18,18,18,18,20,20,20,20,20,20,20,21,21,21,21,25,25,25,25,25,28,28,30,30,30值/频数对直方图等宽直方图：每个桶的宽度区间是一致的等频(等深)直方图：每个桶的频率粗略地为常数（每个桶包含大致相同个数的邻近样本)V最优直方图：给定桶的个数，V最优直方图是具有最小方差的直方图。直方图的方差是每个桶代表的原来值的加权和，其中权等于桶中值的个数MaxDiff直方图：考虑每对相邻值之间的差。桶的边界是具有β－1个最大差的对，其中β是用户指定的桶数等宽直方图( select 1,count(*)

from SC

where grade<(select

max(grade)fromSC)*1/3)union( select 2,count(*)

from SC

where grade>=(select

max(grade)fromSC)*1/3)

and grade<(select

max(grade)fromSC)*2/3)union( select 3,count(*)

from SC

where grade>=(select

max(grade)fromSC)*1/3)等宽直方图createfunctionfn_HistBuckets(@buck_numasint)returntableasreturnselect s_numberasbucket_id, mn+(s_number-1)*stepsizeaslb, mn+s_number*stepsizeashbfrom SeqNums, (select

min(grade)asmn,(max(grade)-min(grade))/@buck_numasstepsize from SC)asbuck_Twhere s_number<=@buck_num;等宽直方图select bucket_id,count(*)asnumordersfrom fn_Hist_buckets(5)asS joinSCasO ongrade>=lbandgrade<hbgroupby bucket_id序号表SeqNumsdeclare@nint;set@n=10000;withNumsas(select 1asiunionallselect i+1from Numswhere i<@n)select iinto SeqNumsfrom Numsoption(maxrecursion0)insertintodigits(digit) values(0),(1),…,(9)select D3.digit*100+ D2.digit*10+ D1.digit+1into SeqNumsfrom digitsasD1 crossjoin digitsasD2crossjoin digitsasD3等深直方图Ntile数据库提供了一种类似等深直方图的数据划分的功能，ntile，它将有序分区中的行分发到指定数目的组中，使得每组内包含的行数尽可能地相同对于数1,2,2,3,3,3,4,4,4,4,4

如果对其执行ntile(3)，则划分为{1,2,2,3},{3,3,4,4},{4,4,4}三个组， ntile(6)的结果是{1,2},{2,3},{3,3},{4,4},{4,4},{4}。ntileselect s#, case

ntile(3)over(orderbygrade) when1then'low' when2then'meduim' when3then'high' endasgrade_levelfrom SCorderby gradentileselect s#, casemod(row_number()over(orderbygrade),3)+1 when1then'low' when2then'meduim' when3then'high' endasgrade_levelfrom SCorderbygradetoptop-k语法格式如下：select [topk[percent][withties]] select_listpercent

：返回n%行withties

：返回所有并列的第k行topselect

top5s#,snamefrom Sselect

top5percents#,snamefrom Sselect

top5withtiess#,snamefrom Sorderby agedesctop使用top拆分大事务while1=1begin delete top(1000) from bigtable if@@rowcount<1000break;end分区top列出每个学生成绩最高的3门课select*from SCC1wherec#in (select top(3)c# from SCC2 where C1.s#=C2.s# orderby grade)OFFSETFETCHselect s#,snamefrom Sorderby agedescoffset0rowsfetchnext10rowsonlyselect s#,c#,gradefrom SCorderby gradedescoffset100rowsrankrank：序号重复但不连续的排名dense_rank：序号重复并且连续的排名row_number：序号不重复但连续的排名rankdense_rankrow_number101111011293238434843584367747rankcreatefunctionfn_grade_rank(@grade_valint)returnsintbegin select

count(distinctgrade)+1 from SC where grade>grade_valend如何对重复列生成row_number()?分区top列出每个学生成绩最高的3门课withgradeCTEas(select row_number()over( partitionbys#orderbygrade)asrn,

s#,c#,gradefrom SC)select

*from gradeCTEwhere rn<=3落差select G1.grade,G2.grade, DIFF=(G1.grade–G2.grade)from SCG1leftouterjoinSCG2

on(fn_grade_rank(G1.grade)= fn_grade_rank(G2.grade)+1)中位数 {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.0Skyline:问题的引入找一个便宜并且离海滩近的旅馆系统无法决定哪些是最好的，但它会提供所有的备选(interesting)旅馆，也即它们不会在两个维上都比其他任何旅馆差，称其为SkylineSkyline:问题的引入称点x统治(dominate)点y，如果x在所有维上都不比y差，并且至少在一个维上好过y旅馆(price=50,distance=0.8)统治(price=100,distance=1.0)Skyline：更高、更靠近河流的建筑东食西宿：更英俊、更有钱Skyline的性质一个集合M，一个单调计分函数R,如果pM使得R最大，那么p一定在M的Skyline中不管你如何偏好旅馆的价格和距离，你最中意的旅馆总是在Skyline中对Skyline中的任意一点p，总存在一个单调计分函数，p使得它最大，也即Skyline不会包含不是任何人偏好的旅馆统治满足传递性，也即如果p统治q，q统治r，则p统治r用嵌套子查询实现Skylineselect *from hotelshwhere

notexists( select *

from hotelsh1

where h1.distance<=h.distance

and h1.price<=h.price

and (h1.distance<h.distanceorh1.price<h.price))Skyline的大小Skyline的一些研究问题Skyline的执行算法分治算法Skyline的势估计有多少个Skyline点？维灾难，维数的指数幂Skyline点的重要性评价Top-kSkylineK-Skyband统计函数汇览AVG：返回集合的平均值COUNT：返回集合中的行数COUNT_BIG返回集合中的行数MAX：返回集合的最大值MIN：返回集合的最小值SUM：返回集合的总和RAND：返回0到1之间的随机浮点数VARIANCE或VAR：返回方差STDDEV：返回标准差COVARIANCEorCOVAR：返回协方差CORRELATION或CORR：返回相关系数统计数据集统计函数示例select avg(salary)asaverage,

variance(salary)asVariance, stddev(salary)asStandDevfrom employeewhere workdept='D11’平均工资是24677.78方差是1.885506172839506E7标准差是4342.24方差：样本中各数据与样本平均数的差的平方和的平均数统计函数示例：DB2select covariance(salary,bonus)asCOV, correlation(salary,bonus)asCORfrom employeewhere workdept=‘D11’COV结果是23650.86正的结果说明salary和bonus之间是正相关的，也即员工的工资越高，获得的奖励就越高.但是covariance只是告诉是正相关还是负相关，它并不能告诉相关性的强度COR结果是0.739说明salary和bonus之间存在强线性关系COV(X，Y)=E(XY)-E(X)E(Y)ρXY=COV(X，Y)/√D(X)√D(Y)统计函数示例：DB2sales(cust_id,prod_id,amount)select C1.cust-id,C2.cust_id,coor(C1.amount,C2.amount)ascorrfrom salesC1,salesC2where C1.prod_id=C2.prod_idand C1.cust_id<C2.cust_idgroupby C1.cust_id,C2.cust_idhaving coor(C1.amount,C2.amount)>=0.5andcount(*)>100