Oracle常用分析函数说明

Oracle分析函数Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。除本文内容外，你还可参考：ROLLUP与CUBE分析函数使用例子介绍：本文如果未指明，缺省是在HR用户下运行例子。开窗函数的的理解：开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：over（orderbysalary）按照salary排序进行累计，orderby是个默认的开窗函数over（partitionbydeptno）按照部门分区over（orderbysalaryrangebetween50precedingand150following）每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150over（orderbysalaryrowsbetween50precedingand150following）每行对应的数据窗口是之前50行，之后150行over（orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing）每行对应的数据窗口是从第一行到最后一行，等效：over（orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing）主要参考资料：《expertone-on-one》TomKyte《Oracle9iSQLReference》第6章1).AVG功能描述：用于计算一个组和数据窗口内表达式的平均值。SAMPLE：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；SELECTmanager_id,last_name,hire_date,salary,AVG(salary)OVER(PARTITIONBYmanager_idORDERBYhire_dateROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASc_mavgFROMemployees;MANAGER_IDLAST_NAMEHIRE_DATESALARYC_MAVG----------------------------------------------------------------100Kochhar21-SEP-891700017000100DeHaan13-JAN-931700015000100Raphaely07-DEC-941100011966.6667100Kaufling01-MAY-95790010633.3333100Hartstein17-FEB-96130009633.33333100Weiss18-JUL-96800011666.6667100Russell01-OCT-961400011833.33332).CORR功能描述：返回一对表达式的相关系数，它是如下的缩写：COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))从统计上讲，相关性是变量之间关联的强度，变量之间的关联意味着在某种程度上一个变量的值可由其它的值进行预测。通过返回一个-1~1之间的一个数,相关系数给出了关联的强度，0表示不相关。SAMPLE：下例返回1998年月销售收入和月单位销售的关系的累积系数（本例在SH用户下运行）SELECTt.calendar_month_number,CORR(SUM(s.amount_sold),SUM(s.quantity_sold))OVER(ORDERBYt.calendar_month_number)asCUM_CORRFROMsaless,timestWHEREs.time_id=t.time_idANDcalendar_year=1998GROUPBYt.calendar_month_numberORDERBYt.calendar_month_number;CALENDAR_MONTH_NUMBERCUM_CORR-------------------------------1213.9943093824.8520408755.8466522046.8712506287.9100298038.9175563999.92015435610.8672025111.84486476512.9035426623).COVAR_POP功能描述：返回一对表达式的总体协方差。SAMPLE：下例CUM_COVP返回定价和最小产品价格的累积总体协方差SELECTproduct_id,supplier_id,COVAR_POP(list_price,min_price)OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVP,COVAR_SAMP(list_price,min_price)OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVSFROMproduct_informationpWHEREcategory_id=29ORDERBYproduct_id,supplier_id;PRODUCT_IDSUPPLIER_IDCUM_COVPCUM_COVS-----------------------------------------1774103088017751030871473.252946.517941030961702.777782554.1666718251030931926.252568.3333320041030861591.41989.2520051030861512.5181524161030881475.979591721.97619..4).COVAR_SAMP功能描述：返回一对表达式的样本协方差SAMPLE：下例CUM_COVS返回定价和最小产品价格的累积样本协方差SELECTproduct_id,supplier_id,COVAR_POP(list_price,min_price)OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVP,COVAR_SAMP(list_price,min_price)OVER(ORDERBYproduct_id,supplier_id)ASCUM_COVSFROMproduct_informationpWHEREcategory_id=29ORDERBYproduct_id,supplier_id;PRODUCT_IDSUPPLIER_IDCUM_COVPCUM_COVS-----------------------------------------1774103088017751030871473.252946.517941030961702.777782554.1666718251030931926.252568.3333320041030861591.41989.2520051030861512.5181524161030881475.979591721.97619..5).COUNT功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数SELECTlast_name,salary,COUNT(*)OVER()AScnt1,COUNT(*)OVER(ORDERBYsalary)AScnt2,COUNT(*)OVER(ORDERBYsalaryRANGEBETWEEN50PRECEDINGAND150FOLLOWING)AScnt3FROMemployees;LAST_NAMESALARYCNT1CNT2CNT3-----------------------------------------------------------------Olson210010713Markle220010732Philtanker220010732Landry240010758Gee240010758Colmenares25001071110Patel25001071110..6).CUME_DIST功能描述：计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置。例如，在一个3行的组中，返回的累计分布值为1/3、2/3、3/3SAMPLE：下例中计算每个工种的员工按薪水排序依次累积出现的分布百分比SELECTjob_id,last_name,salary,CUME_DIST()OVER(PARTITIONBYjob_idORDERBYsalary)AScume_distFROMemployeesWHEREjob_idLIKE'PU%';JOB_IDLAST_NAMESALARYCUME_DIST-------------------------------------------------------PU_CLERKColmenares2500.2PU_CLERKHimuro2600.4PU_CLERKTobias2800.6PU_CLERKBaida2900.8PU_CLERKKhoo31001PU_MANRaphaely1100017).DENSE_RANK功能描述：根据ORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDERBY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDERBY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。密集的序列返回的时没有间隔的数SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与RANK函数的区别）SELECTd.department_id,e.last_name,e.salary,DENSE_RANK()OVER(PARTITIONBYe.department_idORDERBYe.salary)asdrankFROMemployeese,departmentsdWHEREe.department_id=d.department_idANDd.department_idIN('60','90');DEPARTMENT_IDLAST_NAMESALARYDRANK----------------------------------------------------------60Lorentz4200160Austin4800260Pataballa4800260Ernst6000360Hunold9000490Kochhar17000190DeHaan17