《基于SQL的数据分析--》由会员分享，可在线阅读，更多相关《基于SQL的数据分析--（90页珍藏版）》请在金锄头文库上搜索。
1、第一章基于SQL的数据分析 2 聚集函数Groupby交叉表Cube rollup直方图Top中位数Skyline统计函数分析函数Rank 提纲 基本关系模式 S S SNAME SEX AGE D C C CN PC CREDIT SC S C GRADE 聚集函数 聚集函数将一列中所有的值聚集为单个值平均值 avg最小值 min最大值 max总和 sum记数 count 火眼金睛 selectS fromSCwhereGRADE max GRADE selectS fromSCwhereGRADE selectmax GRADE fromSC 分组 分组命令groupby列名 having
2、条件表达式 groupby将表中的元组按指定列上值相等的原则分组 然后在每一分组上使用聚集函数 得到单一值having则对分组进行选择 只将聚集函数作用到满足条件的分组上 分组和聚集函数 在关系的子集上运用聚集函数 得到一个新的关系 分组 列出每个学生的平均成绩 列出每门课程的平均成绩 groupbyS groupbyC 92 85 90 92 92 90 火眼金睛 R A B C selectAfromRgroupbyBselectA BfromRgroupbyAselectA CfromRgroupbyA BselectAfromRgroupbyA CselectAfromRgroupby
3、Aselect fromRgroupbyA B 目标列属性必须是分组属性 分组和聚集函数 示例列出每个学生的最高 最低 平均成绩selectS max GRADE min GRADE avg GRADE fromSCgroupbyS 分组和聚集函数 selectS avg GRADE fromSCgroupbyS havingmin GRADE 60selectS avg GRADE fromSCwhereGRADE 60groupbyS 白马非马 分组和聚集函数 列出每一年龄组中男学生 超过50人 的人数selectAGE count S fromSwhereSEX M groupbyAGE
4、havingcount 50 分组和聚集函数 找出平均成绩最高的学生号selectS fromSCgroupbyS havingavg GRADE all selectavg GRADE fromSCgroupbyS 分组和聚集函数 找出每个系的平均成绩最高的学生号selectD X S fromSX SCwhereX S SC S groupbyD SC S havingavg GRADE all selectavg GRADE fromS SCwhereS SC S andD X D groupbySC S 交叉表 用SQL生成交叉表 SELECTModel Year Color SUM
5、Sales FROMSalesWHEREModel Chevy GROUPBYModel Year ColorUNIONSELECTModel Year ALL SUM Sales FROMSalesWHEREModel Chevy GROUPBYModel YearUNIONSELECTModel ALL ALL SUM Sales FROMSalesWHEREModel Chevy GROUPBYModel 所有可能的分析需求 每种车型 Groupbymodel每个年份 Groupbyyear每种颜色 Groupbycolor每个年份 每种车型 Groupbymodel year每个年份
6、每种颜色 Groupbycolor year每种颜色 每种车型 Groupbymodel color n个属性的所有groupby共有2n个 cube CUBE cube selectModel Year Color sum Sales fromSalesgroupbyModel Year Colorwithcube 总行数 model个数 1 theyear个数 1 color个数 1 2 1 3 1 3 1 48 CUBE SELECTSUM units sold model theyear colorFROMmy cubeGROUPBYmodel theyear colorWITHCUB
7、E SELECT UnitsSold SUM units sold model ALLModels GROUPING model year ALLYears GROUPING theyear color ALLColors GROUPING color FROMmy cubeGROUPBYmodel theyear colorWITHCUBE GROUPING是一个聚合函数 它产生一个附加的列 当用CUBE或ROLLUP运算符添加行时 附加的列输出值为1 当所添加的行不是由CUBE或ROLLUP产生时 附加列值为0 CUBE SELECTSUM units sold CASEWHEN GROU
8、PING model 1 THEN ALL ELSEISNULL model END CASEWHEN GROUPING theyear 1 THEN ALL ELSEISNULL theyear END CASEWHEN GROUPING color 1 THEN ALL ELSEISNULL color ENDFROMmy cubeGROUPBYmodel theyear colorWITHCUBE ROLLUP SELECT unit sold SUM units sold model CASEWHEN GROUPING model 1 THEN ALL ELSEISNULL model
9、 END theyear CASEWHEN GROUPING theyear 1 THEN ALL ELSEISNULL theyear END color CASEWHEN GROUPING color 1 THEN ALL ELSEISNULL color ENDFROMmy cubeGROUPBYmodel theyear colorWITHROLLUP 总行数 color个数 1 theyear个数 1 model个数 1 直方图 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
10、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个最大差的对 其中 是用户指定的桶数 直方图 select1 count fromEMPwheresalary selectmax s
11、alary fromEMP 2 3 union select2 count fromEMPwheresalary selectmax salary fromEMP 3 union select3 count fromEMPwheresalary selectmax salary fromEMP 3 直方图 CREATEFUNCTIONfn histsteps numstepsASINT RETURNSTABLEASRETURNSELECTnASstep mn n 1 stepsizeASlb mn n stepsizeAShbFROMNums SELECTMIN grade ASmn MAX
12、grade MIN grade numstepsASstepsizeFROMSC ASDWHEREn numsteps 注 Nums是个序号表 step lb hb1 50 602 60 703 70 804 80 905 90 100 直方图 SELECTstep COUNT ASnumordersFROMfn histsteps 5 ASSJOINSCASOONgrade lbANDgrade hbGROUPBYstep TOP SELECT TOPn PERCENT WITHTIES select listFROMtable listWHEREconditionsPERCENT 返回n
13、行WITHTIES 返回所有并列的第n行 TOP SELECTTOP5s c gradeFROMSCSELECTTOP5s c gradeFROMSCORDERBYgradeDESCSELECTTOP5WITHTIESs c gradeFROMSCORDERBYgradeDESCSELECTTOP30PERCENTs c gradeFROMSCORDERBYgradeDESC 中位数 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
14、32 0 17 2 18 4 19 6 44 3 1000 7 集合中元素数目是偶数 则就是正中间的两个数的平均 25 7 32 0 28 85 中位数 declare tempINT medianINTset temp selectcount fromsc 2declaremy curscursorforselectGRADEfromSCorderbyGRADEopenmy curswhile temp 0 beginfetchmy curs temp temp 1endfetchmy cursinto median 给出成绩排在最中间的学生的成绩 中位数 WITHdt1AS SELECTG
15、RADE ROWNUMBER OVER ORDERBYGRADE ASnumFROMSC dt2AS SELECTCOUNT GRADE 1AScountFROMdt1 dt3AS SELECTGRADEFROMdt1 dt2WHEREnum FLOOR count 2e0 ORnum CEILING count 2e0 SELECTDECIMAL AVG GRADE 3 2 ASmedianFROMdt3 FLOOR 返回小于或等于给定数字表达式的最大整数CEILING 返回大于或等于给定数字表达式的最小整数 Skyline 问题的引入 找一个便宜并且离海滩近的旅馆系统无法决定哪些是最好的
16、但它会提供所有的备选 interesting 旅馆 也即它们不会在两个维上都比其他任何旅馆差 称其为Skyline Skyline 问题的引入 称点x统治 dominate 点y 如果x在所有维上都不比y差 并且至少在一个维上好过y旅馆 price 50 distance 0 8 统治 price 100 distance 1 0 Skyline 更高 更靠近河流的建筑 东食西宿 更英俊 更有钱 Skyline的性质 一个集合M 一个单调计分函数R 如果p M使得R最大 那么p一定在M的Skyline中不管你如何偏好旅馆的价格和距离 你最中意的旅馆总是在Skyline中对Skyline中的任意一点p 总存在一个单调计分函数 p使得它最大 也即Skyline不会包含不是任何人偏好的旅馆统治满足传递性 也即如果p统治q q统治r 则p统治r 用嵌套子查询实现Skyline Skyline的大小 Skyline的一些研究问题 Skyline的执行算法分治算法Skyline的势估计有多少个Skyline点 维灾难 维数的指数幂Skyline点的重要性评价Top kSkyline 统计函数 方差
