Oracle公司内部数据库培训资料17对GROUPBY子句的扩展.ppt

,对GROUPBY子句的扩展,目标,通过本章学习，您将可以:使用ROLLUP操作分组使用CUBE操作分组使用GROUPING函数处理ROLLUP或CUBE操作所产生的空值使用GROUPINGSETS操作进行单独分组,组函数,组函数处理多行返回一个行,SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUPBYgroup_by_expressionORDERBYcolumn;,SELECTAVG(salary),STDDEV(salary),COUNT(commission_pct),MAX(hire_date)FROMemployeesWHEREjob_idLIKESA%;,例子:,GROUPBY子句,SELECTdepartment_id,job_id,SUM(salary),COUNT(employee_id)FROMemployeesGROUPBYdepartment_id,job_id;,SELECTcolumn,FROMtableWHEREconditionGROUPBYgroup_by_expressionORDERBYcolumn;,例子:,语法:,group_function(column).,HAVING子句,使用HAVING对组函数进行限制对查询进行第二次限制,SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGhaving_expressionORDERBYcolumn;,带有ROLLUP和CUBE操作的GROUPBY子句,使用带有ROLLUP和CUBE操作的GROUPBY子句产生多种分组结果ROLLUP产生n+1种分组结果CUBE产生2的n次方种分组结果,ROLLUP操作符,ROLLUP是对GROUPBY子句的扩展ROLLUP产生n+1种分组结果，顺序是从右向左,SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUPBYROLLUPgroup_by_expressionHAVINGhaving_expression;ORDERBYcolumn;,ROLLUP应用举例,1,2,3,CUBE操作符,CUBE是对GROUPBY子句的扩展CUBE会产生类似于笛卡尔集的分组结果,SELECTcolumn,group_function(column).FROMtableWHEREconditionGROUPBYCUBEgroup_by_expressionHAVINGhaving_expressionORDERBYcolumn;,CUBE应用举例,SELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id60GROUPBYCUBE(department_id,job_id);,1,2,3,4,GROUPING函数,GROUPING函数可以和CUBE或ROLLUP结合使用使用GROUPING函数，可以找到哪些列在该行中参加了分组使用GROUPING函数,可以区分空值产生的原因GROUPING函数返回0或1,SELECTcolumn,group_function(column).,GROUPING(expr)FROMtableWHEREconditionGROUPBYROLLUPCUBEgroup_by_expressionHAVINGhaving_expressionORDERBYcolumn;,GROUPING函数举例,SELECTdepartment_idDEPTID,job_idJOB,SUM(salary),GROUPING(department_id)GRP_DEPT,GROUPING(job_id)GRP_JOBFROMemployeesWHEREdepartment_id50GROUPBYROLLUP(department_id,job_id);,2,1,3,GROUPINGSETS,GROUPINGSETS是对GROUPBY子句的进一步扩充使用GROUPINGSETS在同一个查询中定义多个分组集Oracle对GROUPINGSETS子句指定的分组集进行分组后用UNIONALL操作将各分组结果结合起来Groupingset的优点:只进行一次分组即可不必书写复杂的UNION语句GROUPINGSETS中包含的分组项越多,性能越好。,GROUPINGSETS应用举例,SELECTdepartment_id,job_id,manager_id,avg(salary)FROMemployeesGROUPBYGROUPINGSETS(department_id,job_id),(job_id,manager_id);,1,2,复合列,复合列是被作为整体处理的一组列的集合ROLLUP(a,d)使用括号将若干列组成复合列在ROLLUP或CUBE中作为整体进行操作在ROLLUP或CUBE中,复合列可以避免产生不必要的分组结果,(b,c),复合列应用举例,SELECTdepartment_id,job_id,manager_id,SUM(salary)FROMemployeesGROUPBYROLLUP(department_id,(job_id,manager_id);,2,1,3,连接分组集,连接分组集可以产生有用的对分组项的结合将各分组集,ROLLUP和CUBE用逗号连接Oracle自动在GROUPBY子句中将各分组集进行连接连接的结果是对各分组生成笛卡尔集,GROUPBYGROUPINGSETS(a,b),GROUPINGSETS(c,d),连接分组集应用举例,SELECTdepartment_id,job_id,manager_id,SUM(salary)FROMemployeesGROUPBYdepartment_id,ROLLUP(job_id),CUBE(manager_id);,1,2,3,4,总结,通过本章学习,您已经可以:使用ROLLUP操作符使用CUBE操作符使用GROUPING