Oracle特殊特性

Oracle高级SQL特性谢建斌2011年5月1课程期望达到效果23.Oracle分区了解分区表的用途及作用，学会使用表分区的操作与维护管理；2.Oracle物化视图了解物化视图创建方法，学会使用物化视图简化开发过程与提高开发效率1.Oracle分析函数:了解分析函数概念、分析函数运用、分析函数范例争取通过对分析函数的了解，加强在开发过程中的应用4.Oracle自治事务了解自治事务的工作条件，灵活运用自治事务简化开发工作。5.动态sql了解动态SQL使用，灵活运用动态SQL简化开发工作。Oracle分析函数3•概述–分析函数是SQL的扩展，是Oracle分析函数专门针对类似于经营总额、找出一组中的百分之多少或计算排名前几位等问题设计的。–分析函数运行效率高，使用方便。–分析函数是基于一组行来计算的。这不同于聚集函数且广泛应用于OLAP环境中。–Oracle从8.1.6开始提供，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数只返回一行。Oracle分析函数•简介OLAP的系统(即OnlineAanalyseProcess)，这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者根本不关注这方面的要求，以查询、统计操作为主。我们来看看下面的几个典型例子：①查找上一年度各个销售区域排名前10的员工②按区域查找上一年度订单总额占区域订单总额20%以上的客户③查找上一年度销售最差的部门所在的区域④查找上一年度销售最好和最差的产品我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同，具体有：①需要对同样的数据进行不同级别的聚合操作②需要在表内将多条数据和同一条数据进行多次的比较③需要在排序完的结果集上进行额外的过滤操作4Oracle分析函数•分析函数语法•FUNCTION_NAME(argument,argument...)OVER(Partition-ClauseOrder-by-ClauseWindowingClause)•例:sum(sal)over(partitionbydeptnoorderbyename)new_aliassum就是函数名(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)over是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数partitionbydeptno是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区orderbyename是可选的orderby子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的.5Oracle分析函数•1)FUNCTION子句•ORACLE提供了26个分析函数,按功能分5类分析函数分类等级(ranking)函数:用于寻找前N种查询开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上例:sum(t.sal)over(orderbyt.deptno,t.ename)running_total,sum(t.sal)over(partitionbyt.deptnoorderbyt.ename)department_total6Oracle分析函数•1)FUNCTION子句•制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列例:sum(t.sal)over()running_total2,sum(t.sal)over(partitionbyt.deptno)department_total2制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDERBY子句!LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常有用的.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值7Oracle分析函数•2)PARTITION子句•按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组•3)ORDERBY子句分析函数中ORDERBY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDERBY时,默认的窗口是全部的分区在Orderby子句后可以添加nullslast,如:orderbycommdescnullslast表示排序时忽略comm列为空的行.8Oracle分析函数•4)WINDOWING子句用于定义分析函数将在其上操作的行的集合Windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行操作默认的窗口是一个固定的窗口,仅仅在一组的第一行开始,一直继续到当前行,要使用窗口,必须使用ORDERBY子句根据2个标准可以建立窗口:数据值的范围(RANGES)或与当前行的行偏移量.9Oracle分析函数•5)Rang窗口Range5preceding:将产生一个滑动窗口,他在组中拥有当前行以前5行的集合ANGE窗口仅对NUMBERS和DATES起作用,因为不可能从VARCHAR2中增加或减去N个单元另外的限制是ORDERBY中只能有一列,因而范围实际上是一维的,不能在N维空间中例:avg(t.sal)over(orderbyt.hiredateascrange100preceding)统计前100天平均工资10Oracle分析函数•6)Row窗口利用ROW分区,就没有RANGE分区那样的限制了,数据可以是任何类型,且ORDERBY可以包括很多列•7)Specifying窗口UNBOUNDEDPRECEDING:这个窗口从当前分区的每一行开始,并结束于正在处理的当前行CURRENTROW:该窗口从当前行开始(并结束)NumericExpressionPRECEDING:对该窗口从当前行之前的数字表达式(NumericExpression)的行开始,对RANGE来说,从从行序值小于数字表达式的当前行的值开始.NumericExpressionFOLLOWING:该窗口在当前行NumericExpression行之后的行终止(或开始),且从行序值大于当前行NumericExpression行的范围开始(或终止)rangebetween100precedingand100following:当前行100前,当前后100后11Oracle分析函数•26个分析函数•SUM：该函数计算组中表达式的累积和•MIN：在一个组中的数据窗口中查找表达式的最小值•MAX：在一个组中的数据窗口中查找表达式的最大值•AVG：用于计算一个组和数据窗口内表达式的平均值。•COUNT：对一组内发生的事情进行累积计数•RANK：根据ORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置•DENSE_RANK：根据ORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置•FIRST：从DENSE_RANK返回的集合中取出排在最前面的一个值的行•LAST：从DENSE_RANK返回的集合中取出排在最后面的一个值的行•FIRST_VALUE：返回组中数据窗口的第一个值•LAST_VALUE：返回组中数据窗口的最后一个值。•LAG：可以访问结果集中的其它行而不用进行自连接•LEAD：LEAD与LAG相反，LEAD可以访问组中当前行之后的行•ROW_NUMBER：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号12Oracle分析函数•26个分析函数•LAST_VALUE：返回组中数据窗口的最后一个值。•LAG：可以访问结果集中的其它行而不用进行自连接•LEAD：LEAD与LAG相反，LEAD可以访问组中当前行之后的行•ROW_NUMBER：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号•STDDEV：计算当前行关于组的标准偏离•STDDEV_POP：该函数计算总体标准偏离，并返回总体变量的平方根•STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根•VAR_POP：该函数返回非空集合的总体变量（忽略null）13Oracle分析函数•26个分析函数VAR_SAMP：该函数返回非空集合的样本变量（忽略null）•VARIANCE：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP•COVAR_POP：返回一对表达式的总体协方差•COVAR_SAMP：返回一对表达式的样本协方差•CORR：返回一对表达式的相关系数•CUME_DIST：计算一行在组中的相对位置•NTILE：将一个组分为表达式的散列表示•PERCENT_RANK：和CUME_DIST（累积分配）函数类似•PERCENTILE_DISC：返回一个与输入的分布百分比值相对应的数据值14Oracle分析函数•26个分析函数•PERCENTILE_CONT：返回一个与输入的分布百分比值相对应的数据值•RATIO_TO_REPORT：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比•REGR_(LinearRegression)Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用•CUBE:按照OLAP的CUBE方式进行数据统计,即各个维度均需统计•ROLLUP:15Oracle分析函数•Oracle分析函数简单实例(1)通过一个实际的例子：按区域查找上一年度订单总额占区域订单总额20%以上的客户，来看看分析函数的应用见Oracle特殊特性_分析函数.sql•分析函数OVER解析(2)见Oracle特殊特性_分析函数.sql16Oracle分析函数•分析函数(3)(Top/BottomN、First/Last、NTile)1.带空值的排列2.Top/BottomN查询3.First/Last排名查询4.按层次查询见Oracle特殊特性_分析函数.sql17Oracle分析函数•分析函数(4)窗口函数列出每月的订单总额以及全年的订单总额为例，来看看窗口函数的应用见Oracle特殊特性_分析函数.sql18Oracle分析函数•分析函数总结一、统计方面：•Sum()Over([Partitionby][Orderby])Sum()Over([Partitionby][Orderby]RowsBetweenPrecedingAndFollowing)Sum()Over([Partitionby][Orderby]RowsBetweenPrecedingAndCurrentRow)Sum()Over([Partitionby][Orderby]RangeBetweenInterval'''Day'PrecedingAndInterval'''Day'Following)19Oracle分析函数•分析函数总结•二、排列方面：Rank()Over([Partitionby][Orderby][NullsFirst/Last])Dense_rank()Over([Patitionby][Orderby][NullsFirst/Last])Row_number()Over([Partitionby][Orderby][NullsFirst/Last])Ntile()Over([Partitionby][Orderby])20Oracle分析函数•分析函数总结•三、最大值/最小值查找方面：Min()/Max()Keep(Dense_rank