|
课程目标： - ROLLUP
- CUBE
- GROUPING
- GROUPING SETS
1、复习组函数、group by子句及having子句 - group functions 可以位于select lists，order by and having clause
- grouping column隐式升序排序
语法： 例子：使用组函数取平均薪水，薪水标准差，提成总计，最大入职日期 SQL> select avg(salary),stddev(salary),count(commission_pct),max(hire_date) 2 from employees where job_id like 'SA%'; AVG(SALARY) STDDEV(SALARY) COUNT(COMMISSION_PCT) MAX(HIRE_DAT ----------- -------------- --------------------- ------------ 8900 2030.64754 35 21-APR-08 例子：查看每个部门每个职位的薪水合计及人数 SQL> select department_id,job_id,sum(salary),count(employee_id) 2 from employees 3 group by department_id,job_id; DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID) ------------- ---------- ----------- ------------------ 110 AC_ACCOUNT 8300 1 2、ROLLUP运算符 rollup是group by子句的扩展，用于产生小计。 语法： 运算规则是从右往左把group by后面的分组字段整体分组后，再一个一个去掉，剩下的group by，有n个字段，则会有n+1组结果。 例子：查询各个部门的薪水小计及整个公司的总薪水 使用集合的方式，需要进行2次查询 SQL> select department_id,sum(salary) from employees group by department_id 2 union 3 select null,sum(salary) from employees; DEPARTMENT_ID SUM(SALARY) ------------- ----------- 10 10400 741416 13 rows selected. 使用rollup，只需要进行1次查询 SQL> select department_id,sum(salary) from employees group by rollup(department_id); DEPARTMENT_ID SUM(SALARY) ------------- ----------- 10 10400 741416 13 rows selected. 例子：查询按部门和职位分组，按部门分组的薪水小计以及公司薪水总计 使用集合的方式，需要进行3次查询 SQL> select department_id,job_id,sum(salary) from employees group by department_id,job_id 2 union 3 select department_id,null,sum(salary) from employees group by department_id 4 union 5 select null,null,sum(salary) from employees; DEPARTMENT_ID JOB_ID SUM(SALARY) ------------- ---------- ----------- 10 AD_ASST 4400 741416 33 rows selected. 使用rollup，只需要进行1次查询 SQL> select department_id,job_id,sum(salary) from employees group by rollup(department_id,job_id); DEPARTMENT_ID JOB_ID SUM(SALARY) ------------- ---------- ----------- SA_REP 7000 741416 33 rows selected. 查看执行计划，只进行了1次全部扫描，减少物理I/O SQL> explain plan for 2 select department_id,job_id,sum(salary) from employees group by rollup(department_id,job_id); Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1088000809 -------------------------------------------------------------------------------- -- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- -- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 108 | 1728 | 4 (25)| 00:00:01 | | 1 | SORT GROUP BY ROLLUP| | 108 | 1728 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMPLOYEES | 108 | 1728 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- -- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 9 rows selected. 3、CUBE运算符 cube是group by子句的扩展，用于产生交叉汇总。 语法： 运算规则是对group by后面的分组字段所有组合进行聚合，有n个字段，则会有2^n组结果。 例子：查询按部门及职位分组，按部门分组及职位分组的薪水小计以及公司薪水总计 使用集合的方式，需要进行4次查询 SQL> select department_id,job_id,sum(salary) from employees group by department_id,job_id 2 union 3 select department_id,null,sum(salary) from employees group by department_id 4 union 5 select null,job_id,sum(salary) from employees group by job_id 6 union 7 select null,null,sum(salary) from employees; DEPARTMENT_ID JOB_ID SUM(SALARY) ------------- ---------- ----------- 10 AD_ASST 4400 741416 52 rows selected. 使用cube，只需要进行1次查询 SQL> select department_id,job_id,sum(salary) from employees group by cube(department_id,job_id); DEPARTMENT_ID JOB_ID SUM(SALARY) ------------- ---------- ----------- 741416 110 AC_ACCOUNT 8300 52 rows selected. 4、grouping函数 主要用于区分查询结果里面的null是使用ROLLUP或者CUBE产生的还是本来就是null，如果是由ROLLUP或者CUBE产生的，返回1，否则返回0。 语法： 例子：使用grouping函数，确认查询结果中的null是否是由rollup产生 SQL> select department_id deptid,job_id job,sum(salary),grouping(department_id) grp_detp,grouping(job_id) grp_job 2 from employees where department_id<50 3 group by rollup(department_id,job_id); DEPTID JOB SUM(SALARY) GRP_DETP GRP_JOB ---------- ---------- ----------- ---------- ---------- 10 MK_REP 6000 0 0 10 AD_ASST 4400 0 0 10 10400 0 1 20 MK_MAN 13000 0 0 20 13000 0 1 30 PU_MAN 11000 0 0 30 PU_CLERK 13900 0 0 30 24900 0 1 40 HR_REP 6500 0 0 40 6500 0 1 54800 1 1 11 rows selected. 例子：使用说明文字替换rollup产生的null SQL> select 2 decode(grouping(department_id),1,(decode(grouping(job_id),1,'total:',department_id)),department_id) as deptid, 3 decode(grouping(job_id),1,(decode(grouping(department_id),1,null,'subtotal:')),job_id), 4 sum(salary) 5 from employees where department_id<60 6 group by rollup(department_id,job_id); DEPTID DECODE(GRO SUM(SALARY) ---------------------------------------- ---------- ----------- 10 MK_REP 6000 10 AD_ASST 4400 10 subtotal: 10400 20 MK_MAN 13000 20 subtotal: 13000 30 PU_MAN 11000 30 PU_CLERK 13900 30 subtotal: 24900 40 HR_REP 6500 40 subtotal: 6500 50 ST_MAN 36400 DEPTID DECODE(GRO SUM(SALARY) ---------------------------------------- ---------- ----------- 50 SH_CLERK 64300 50 ST_CLERK 105700 50 subtotal: 206400 total: 261200 15 rows selected. 5、grouping sets - 在同一个查询中定义多个分组
- 相当于使用union all拼接起来
- 只需对源表扫描一次，提升性能
例子：计算相同部门相同职务人员的平均薪水以及相同职务相同领导的平均薪水 SQL> select department_id,job_id,manager_id,avg(salary) from employees 2 group by grouping sets((department_id,job_id),(job_id,manager_id)); DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- AC_MGR 101 12008 查看执行计划，对源表扫描1次后生成比源表小的2个临时表，后续就对临时表进行操作。 SQL> explain plan for 2 select department_id,job_id,manager_id,avg(salary) from employees 3 group by grouping sets((department_id,job_id),(job_id,manager_id)); Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 52239849 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 108 | 4968 | 11 (19)| 00:00:01 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6605_242506 | | | | | | 3 | TABLE ACCESS FULL | EMPLOYEES | 108 | 2160 | 3 (0)| 00:00:01 | | 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6606_242506 | | | | | | 5 | HASH GROUP BY | | 108 | 1836 | 3 (34)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ | 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_242506 | 108 | 1836 | 2 (0)| 00:00:01 | | 7 | LOAD AS SELECT | SYS_TEMP_0FD9D6606_242506 | | | | | | 8 | HASH GROUP BY | | 108 | 1728 | 3 (34)| 00:00:01 | | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_242506 | 108 | 1728 | 2 (0)| 00:00:01 | | 10 | VIEW | | 108 | 4968 | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_242506 | 108 | 1836 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- 18 rows selected. 如果不使用grouping sets，使用union all就需要扫描employees2遍 SQL> select department_id,job_id,null,avg(salary) from employees group by department_id,job_id 2 union all 3 select null,job_id,manager_id,avg(salary) from employees group by job_id,manager_id; DEPARTMENT_ID JOB_ID NULL AVG(SALARY) ------------- ---------- ---------- ----------- 110 AC_ACCOUNT 8300 前面讲的rollup和cube都可以使用grouping sets来进行组合。 6、复合列 就是将多列使用括号括起来作为1个整体。 例子：查询按部门、领导和职位分组，按部门分组的薪水小计以及公司薪水总计 SQL> select department_id,job_id,manager_id,sum(salary) from employees 2 group by rollup(department_id,(job_id,manager_id)); DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY) ------------- ---------- ---------- ----------- SA_REP 149 7000 741416 使用grouping sets及复合列与单独使用group by与union all的对应关系 7、相关习题 （1）View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables. You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT_ID for the last six months. Which SQL statement would you execute to get the desired output? A.SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id,oi.product_id) WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6 . B.SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id,oi.product_id) HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6 . C.SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi JOIN orders o ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) WHERE MONTHS_BETWEEN(order_date, SYSDATE) >= 6 . D.SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" FROM order_items oi JOIN orders o ON oi.order_id=o.order_id WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6 GROUP BY ROLLUP (o.customer_id, oi.product_id) . 答案：D （2）In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause? A.to find the groups forming the subtotal in a row B.to create group-wise grand totals for the groups specified within a GROUP BY clause C.to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals D.to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals 答案：C （3）Which statement best describes the GROUPING function? A.It is used to set the order for the groups to be used for calculating the grand totals and subtotals. B.It is used to form various groups to calculate total and subtotals created using ROLLUP and CUBE operators. C.It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP or CUBE. D.It is used to specify the concatenated group expressions to be used for calculating the grand totals andsubtotals. 答案：C （4）Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement? A.It produces only the subtotals for the groups specified in the GROUP BY clause. B.It produces only the grand totals for the groups specified in the GROUP BY clause. C.It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause. D.It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause. 答案：C （5）Which two statements are true about the GROUPING function? (Choose two.) A.It is used to find the groups forming the subtotal in a row. B.It is used to identify the NULL value in the aggregate functions. C.It is used to form the group sets involved in generating the totals and subtotals. D.It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause. 答案：AD （6）View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables. Evaluate the following SQL statement: SELECTo.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Order Amount" FROM order_items oi JOIN orders o ON oi.order_id = o.order_id GROUP BY CUBE (o.customer_id, oi.product_id);Which three statements are true regarding the output of this SQL statement? (Choose three.) A.It would return the subtotals for the Order Amount of every CUSTOMER_ID. B.It would return the subtotals for the Order Amount for every PRODUCT_ID. C.It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group. D.It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. E.It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. 答案：ABD （7）View the Exhibit1 and examine the descriptions of the EMPLOYEES and DEPARTMENTS tables. The following SQL statement was executed: SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total, GROUPING(e.department_id) GRP_DEPT, GROUPING(e.job_id) GRP_JOB, GROUPING(d.location_id) GRP_LOC FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY ROLLUP (e.department_id, e.job_id, d.location_id)； View the Exhibit2 and examine the output of the command. Which two statements are true regarding the output? (Choose two.) A.The value 1 in GRP_LOC means that the LOCATION_ID column is taken into account to generate the subtotal.
B.The value 1 in GRP_JOB and GRP_LOC means that JOB_ID and LOCATION_ID columns are not taken into account to generate the subtotal.
C.The value 1 in GRP_JOB and GRP_LOC means that the NULL value in JOB_ID and LOCATION_ID columns are taken into account to generate the subtotal.
D.The value 0 in GRP_DEPT, GRP_JOB, and GRP_LOC means that DEPARTMENT_ID, JOB_ID, and LOCATION_ID columns are taken into account to generate the subtotal. 答案：BD （8）View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables. Evaluate the following SQL statement: SELECT e.department_id, e.job_id, d.location_id, sum(e.salary) total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY CUBE (e.department_id, e.job_id, d.location_id)； Which two statements are true regarding the output of this command? (Choose two.) A.The output would display the total salary for all the departments.
B.The output would display the total salary for all the JOB_IDs in a department.
C.The output would display only the grand total of the salary for all JOB_IDs in a LOCATION_ID.
D.The output would display the grand total of the salary for only the groups specified in the GROUP BY clause. 答案：AB （9）Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement ?
A.It produces only aggregates for the groups specified in the GROUP BY clause.
B.It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause.
C.It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.
D.It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. 答案：C
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2015-12-21 18:27:22
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对