![26.oracleocp题库dba经典辅助学习les_第1页](http://file4.renrendoc.com/view/e9df88ed1f4c1674513f5fa3b0a0991b/e9df88ed1f4c1674513f5fa3b0a0991b1.gif)
![26.oracleocp题库dba经典辅助学习les_第2页](http://file4.renrendoc.com/view/e9df88ed1f4c1674513f5fa3b0a0991b/e9df88ed1f4c1674513f5fa3b0a0991b2.gif)
![26.oracleocp题库dba经典辅助学习les_第3页](http://file4.renrendoc.com/view/e9df88ed1f4c1674513f5fa3b0a0991b/e9df88ed1f4c1674513f5fa3b0a0991b3.gif)
![26.oracleocp题库dba经典辅助学习les_第4页](http://file4.renrendoc.com/view/e9df88ed1f4c1674513f5fa3b0a0991b/e9df88ed1f4c1674513f5fa3b0a0991b4.gif)
![26.oracleocp题库dba经典辅助学习les_第5页](http://file4.renrendoc.com/view/e9df88ed1f4c1674513f5fa3b0a0991b/e9df88ed1f4c1674513f5fa3b0a0991b5.gif)
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、Generating Reports by GroupingRelated Data ObjectivesAfter completing this lesson, you should be able to do the following:Use the ROLLUP operation to producesubtotal valuesUse the CUBE operation to produce cross-tabulation valuesUse the GROUPING function to identify the row values created by ROLLUP
2、or CUBEUse GROUPING SETS to produce a single result setReview of Group FunctionsGroup functions operate on sets of rows to give one result per group.Example:SELECTcolumn, group_function(column). . .FROMtableWHEREconditionGROUP BYgroup_by_expressionORDER BYcolumn;SELECT AVG(salary), STDDEV(salary), C
3、OUNT(commission_pct),MAX(hire_date)FROM employeesWHERE job_id LIKE SA%;Review of the GROUP BY ClauseSyntax:Example:SELECT department_id, job_id, SUM(salary), COUNT(employee_id)FROM employeesGROUP BY department_id, job_id ;SELECTcolumn,FROMtableWHEREconditionGROUP BYgroup_by_expressionORDER BYcolumn;
4、group_function(column). . .Review of the HAVING ClauseUse the HAVING clause to specify which groups are to be displayed.You further restrict the groups on the basis of a limiting condition.SELECTcolumn, group_function(column). FROMtableWHEREconditionGROUP BYgroup_by_expressionHAVING having_expressio
5、n ORDER BYcolumn;GROUP BY with ROLLUP andCUBE OperatorsUse ROLLUP or CUBE with GROUP BY to produce superaggregate rows by cross-referencing columns.ROLLUP grouping produces a result set containing the regular grouped rows and the subtotal values.CUBE grouping produces a result set containing the row
6、s from ROLLUP and cross-tabulation rows.ROLLUP OperatorROLLUP is an extension to the GROUP BY clause.Use the ROLLUP operation to produce cumulative aggregates, such as subtotals.SELECTcolumn, group_function(column). . .FROMtableWHEREconditionGROUP BYROLLUP group_by_expressionHAVING having_expression
7、;ORDER BYcolumn;ROLLUP Operator: ExampleSELECT department_id, job_id, SUM(salary)FROM employees WHERE department_id 60GROUP BY ROLLUP(department_id, job_id);312CUBE OperatorCUBE is an extension to the GROUP BY clause.You can use the CUBE operator to produce cross-tabulation values with a single SELE
8、CT statement.SELECTcolumn, group_function(column).FROMtableWHEREconditionGROUP BYCUBE group_by_expressionHAVING having_expressionORDER BYcolumn;CUBE Operator: ExampleSELECT department_id, job_id, SUM(salary)FROM employees WHERE department_id 60GROUP BY CUBE (department_id, job_id) ;1234GROUPING Func
9、tionThe GROUPING function: Is used with either the CUBE or ROLLUP operatorIs used to find the groups forming the subtotal in a rowIs used to differentiate stored NULL values from NULL values created by ROLLUP or CUBEReturns 0 or 1SELECT column, group_function(column) . , GROUPING(expr)FROM tableWHER
10、E conditionGROUP BY ROLLUPCUBE group_by_expressionHAVING having_expressionORDER BY column;GROUPING Function: Example SELECT department_id DEPTID, job_id JOB, SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOBFROM employeesWHERE department_id 50GROUP BY ROLLUP(department_id, job_
11、id);123GROUPING SETSGROUPING SETS syntax is used to define multiple groupings in the same query.All groupings specified in the GROUPING SETS clause are computed and the results of individual groupings are combined with a UNION ALL operation.Grouping set efficiency:Only one pass over the base table i
12、s required.There is no need to write complex UNION statements.The more elements GROUPING SETS has, the greater the performance benefit.Notes OnlyGROUPING SETS: ExampleSELECT department_id, job_id, manager_id,avg(salary)FROM employeesGROUP BY GROUPING SETS (department_id,job_id), (job_id,manager_id);
13、12Notes OnlyComposite ColumnsA composite column is a collection of columns that are treated as a unit.ROLLUP (a, , d)Use parentheses within the GROUP BY clause to group columns, so that they are treated as a unit while computing ROLLUP or CUBE operations.When used with ROLLUP or CUBE, composite colu
14、mns would require skipping aggregation across certain levels.(b,c)Notes OnlyComposite Columns: ExampleSELECT department_id, job_id, manager_id, SUM(salary)FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id);2341Concatenated GroupingsConcatenated groupings offer a concise way to genera
15、te useful combinations of groupings.To specify concatenated grouping sets, you separate multiple grouping sets, ROLLUP, and CUBE operations with commas so that the Oracle server combines them into a single GROUP BY clause.The result is a cross-product of groupings from each grouping set.GROUP BY GRO
16、UPING SETS(a, b), GROUPING SETS(c, d)Concatenated Groupings: ExampleSELECT department_id, job_id, manager_id, SUM(salary)FROM employeesGROUP BY department_id, ROLLUP(job_id), CUBE(manager_id);12345SummaryIn this lesson, you should have learned how to use the:ROLLUP operation to produce subtotal valuesCUBE operation to produce cross-tabulation valuesGROUPING function to identify the row values created by ROLLUP or CUBEGROUPING SETS
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 影视广告制作基础06影视广告的拍摄方式及技巧
- 农业商业计划书
- 水库水生态修复工程施工组织设计方案
- 初中二年级下学期物理《压强》教学设计
- 工业以太网课件
- 贵州省遵义凤冈二中2025年高三下学期第一次联考数学试题理试卷含解析
- 纳米漆产品商业计划书
- 高性能传输线缆产品相关项目建议书
- 金融数据加密机产品相关项目建议书
- 诊断用单克隆抗体产品项目运营指导方案
- 2024年社区工作者考试必背1000题题库【含答案】
- 北师大版数学三年级上册全册核心素养目标教学设计
- 用A4纸做会议《姓名牌》模版
- 大美青海主题班会教学课件
- 商务谈判说课.PPT
- 百家姓全文带拼音完美版
- 【省级公开课】高中英语非限制性定语从句(36张PPT)
- 融资租赁债权转让协议
- 输变电工程规划选址研究报告
- LNG加气站危险源与风险分析(完整版)
- 纺织工业企业设计防火规范
评论
0/150
提交评论