重庆思庄Oracle、Redhat认证学习论坛

标题: OCP课程17：SQL之通过分组相关数据产生报告 [打印本页]

作者: stonebox 时间: 2015-12-21 18:27
标题: OCP课程17：SQL之通过分组相关数据产生报告

课程目标：

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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 108 | 4968 | 11 (19)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | | | | | |

| 3 | TABLE ACCESS FULL | EMPLOYEES | 108 | 2160 | 3 (0)| 00:00:01 |

| 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 |

| 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认证学习论坛 (http://bbs.cqsztech.com/)