課程目標：

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

OCP課程17：SQL之透過分組相關資料產生報告

相關文章