oracle技术交流群,分析函数总结(oracle技术交流群).docx

分析函数总结(oracle技术交流群).docx

分析函数的基础语法ORACLE分析函数的语法FUNCTION_NAMEARG1,ARG2,OVER说明1PARTITIONCLAUSE数据记录集分组2ORDERBYCLAUSE数据记录集排序3WINDOWINGCLAUSE功能非常强大、比较复杂，定义分析函数在操作行的集合。有三种开窗方式RANGE、ROW、SPECIFYING。分析函数的四大要点ANALYTICFUNCTIONPARTITIONBYORDERBYROWSVSRANGEUNBOUNDEDPRECEDINGFOLLOWINGCURRENTROW1有的分析函数可以带WINDOW,有的不能带WINDOW子句,不能带WINDOW子句的比如RANK,DENSE_RANK,ROW_NUMBER,FIRST,LAST,LEAD,LAG可以带WINDOWS的比如COUNT,SUM,AVG,MIN,MAX,FIRST_VALUE,LAST_VALUE。FIRST,LAST里的分析函数部分ORDERBY都是不允许的有的分析函数，比如ROW_NUMBER,DENSE_RANK,RANK是必须要有ORDERBY的。2PARTITIONBY是按字段值将对应的行分组不能带括号，带括号的是MODEL和PARTITIONEDOUTERJOIN使用的，ORDERBY是组内行的顺序，WINDOW子句决定每行对应的窗口范围3PARTITIONBY,ORDERBY,WINDOW子句共同决定了当前行对应的窗口范围，当前行分析函数值就是基于这个窗口计算的4注意PARTITIONBY,ORDERBY,WINDOW子句的关系WINDOW子句是在PARTITIONBY和ORDERBY前提下设定当前行对应的窗口范围的,因此必须有ORDERBY才能写WINDOW子句。可以没有这三个子句，那么相当于当前行对应于所有行的窗口中。BETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING有PARTITIONBY可以没有有ORDERBY，注意有的分析函数必须要有ORDERBY5ORDERBY与ROWS,RANGE的区别RANGE保证结果的稳定性，RANGE的行都是逻辑行，按ORDERBY值计算，包括CURRENTROW也是逻辑行。默认的有ORDERBY没有WINDOW就是逻辑上限到当前逻辑行，排序重复稳定ROWS是物理行，按排序的行标计算，CURRENTROW是物理行。排序重复不稳定ORDERBY如果有多个排序键值，那么RANGE则必须对应的窗口是(因为逻辑窗口不知道按什么键来计算)ABETWEENUNBOUNDEDPRECEDINGANDCURRENTROW相当于没有写WINDOW，因为ORDERBY默认就是组的首行到当前行BBETWEENCURRENTROWANDUNBOUNDEDFOLLOWINGCBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING–相当于没有写ORDERBY，表示是组的首行到组的末行在排名函数，FIRST/LAST6WINDOW子句定义的范围必须从上到下。比如ROWS1FOLLOWING，RANGE1FOLLOWING,ROWSBETWEEN1PRECEDINGAND2PRECEDING都是错误的默认的窗口是RANGE到CURRENTROW70FOLLOWING和0PRECEDING都相同于CURRENTROW。分析函数里使用DISTINCT有限制,不能带ORDERBY8分析子句顺序是PARTITION子句ORDERBY子句WINDOW子句。其中有的分析函数必须有ORDERBY子句，另外有WINDOW子句必须要有ORDERBY子句。分析函数是在FROM，WHERE，GROUPBY，HAVING之后才开始工作的。出现在最后ORDERBY和SELECT之前，分析函数也可以用于子查询，用于过滤父查询的查询结果。分析函数只允许出现在ORDERBY和SELECT中，只是针对同一级查询。不可嵌套如果最后查询结果需要一定的顺序，则在最后显示指定ORDERBY，因为分区子句的ORDERBY只保证组内有序，特别是有多个分析函数的时候，会覆盖的排序结果函数优先级，顺序等，所以最后要显示ORDERBY8分析函数的缺点经常需要有排序操作，很多就算无ORDERBY也需要内部排序，如果写多个分析函数，会产生很多排序，依赖于内存。当然也可以优化排序，比如通过索引消除排序。分析函数的优点代替复杂的子查询，JOIN等，减少表的扫描次数，提高效率。分析函数的分类主要列表分为四类聚集分析函数SUM该函数计算组中表达式的累积和MIN在一个组中的数据窗口中查找表达式的最小值MAX在一个组中的数据窗口中查找表达式的最大值AVG用于计算一个组和数据窗口内表达式的平均值。COUNT对一组内发生的事情进行累积计数排名分析函数RANK根据ORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置DENSE_RANK根据ORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置FIRST从DENSE_RANK返回的集合中取出排在最前面的一个值的行LAST从DENSE_RANK返回的集合中取出排在最后面的一个值的行FIRST_VALUE返回组中数据窗口的第一个值LAST_VALUE返回组中数据窗口的最后一个值。ROW_NUMBER返回有序组中一行的偏移量，从而可用于按特定标准排序的行号行比较分析函数LAG可以访问结果集中的其它行而不用进行自连接LEADLEAD与LAG相反，LEAD可以访问组中当前行之后的行统计分析函数STDDEV计算当前行关于组的标准偏离STDDEV_POP该函数计算总体标准偏离，并返回总体变量的平方根STDDEV_SAMP该函数计算累积样本标准偏离，并返回总体变量的平方根VAR_POP该函数返回非空集合的总体变量(忽略NULL)VAR_SAMP该函数返回非空集合的样本变量(忽略NULL)VARIANCE如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMPCOVAR_POP返回一对表达式的总体协方差COVAR_SAMP返回一对表达式的样本协方差CORR返回一对表达式的相关系数CUME_DIST计算一行在组中的相对位置NTILE将一个组分为“表达式“的散列表示PERCENT_RANK和CUME_DIST(累积分配)函数类似PERCENTILE_DISC返回一个与输入的分布百分比值相对应的数据值PERCENTILE_CONT返回一个与输入的分布百分比值相对应的数据值RATIO_TO_REPORT该函数计算EXPRESSION/SUMEXPRESSION的值，它给出相对于总数的百分比REGR_LINEARREGRESSIONFUNCTIONS这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用分析函数示例4类分析函数用法示例DOCX(如打不开请双击右边的图片)分析函数专题连续值连续数区间临界值连续值区间案例1要求查出连续数据，并且要写出最小值和最大值及连续的个数DROPTABLETPURGECREATETABLETID1INT,ID2INT,ID3