ORACLE 分组和时间函数
使用下面的日期时间函数： -TZ_OFFSET-CURRENT_DATE-CURRENT_TIMESTAMP-LOCALTIMESTAMP-DBTIMEZONE-SESSIONTIMEZONE-EXTRACT-FROM_TZ-TO_TIMESTAMP-TO_TIMESTAMP_TZ-TO_YMINTERVALoracle9i日期时间支持:: 在 Oracle9i 中，你能够在你的日期和时间数据中包含时区，并且提供对小数秒的支持:: Oracle9i 中新的日期时间数据类型：-TIMESTAMP-TIMESTAMP WITH TIME ZONE (TSTZ) -TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) :: Oracle9i 服务器提供对日期时间数据类型的夏令时支持--CURRENT_DATE:: 显示在会话时区中的当前日期和时间ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';:: CURRENT_DATE 对于会话时区是敏感的Note:the ALTER SESSION command sets the date format of the session to 'DD-MON-YYYY HH24:MI:SS' that is Day of month(1-31)-Abbreviated name of month -4-digit year Hours of dat(0-23):Minute(0-59):Second(0-59)--CURRENT_TIMESTAMP:: 显示在会话时区中的当前日期和时间,包含小数秒ALTER SESSION SET TIME_ZONE='-5:0'; //'-8.0'SELECT SESSIONTIMEZONE,CURRENT_TIMESTAMP FROM DUAL;::CURRENT_TIMESTAMP 对于会话时区是敏感的::返回值是TIMESTAMP WITH TIME ZONE 数据类型--LOCALTIMESTAMP:: 以 TIMESTAMP 数据类型的值显示在会话时区中的当前日期和时间ALTER SESSION SET TIME_ZONE = '-5:0';SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;:: LOCALTIMESTAMP 返回一个 TIMESTAMP 值，而 CURRENT_TIMESTAMP 返回一个 TIMESTAMP WITH TIME ZONE 值--DBTIMEZONE 和 SESSIONTIMEZONE:: 显示数据库时区的值 SELECT DBTIMEZONE FROM DUAL;:: 显示会话时区的值 SELECT SESSIONTIMEZONE FROM DUAL;--EXTRACT:: 从SYSDATE 中显示年 SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;:: 从 HIRE_DATE 中显示 MANAGER_ID 是100的雇员的月SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE)FROM employeesWHERE manager_id = 100;LAST_NAME HIRE_DATE EXTRACT(MONTHFROMHIRE_DATE)------------------------- ---------- ---------------------------Kochhar 21-9月 -89 9De Haan 13-1月 -93 1Raphaely 07-12月-94 12Weiss 18-7月 -96 7Fripp 10-4月 -97 4Kaufling 01-5月 -95 5语法: SELECT EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND] [TIMEZONE_HOUR] [TIMEZONE_MINUTE][TIMEZONE_REGION] [TIMEZONE_ABBR]FROM [datetime_value_expression] [interval_value_expression]);TIMESTAMP WITH LOCAL TIME ZONE 存储在数据库时区中，当一个用户选择数据时，值被调整到用户会话的时区。--***************增强 GROUP BY 子句*********************目标完成本课后, 您应当能够：:: 用 ROLLUP 操作产生小计值:: 用 CUBE 操作产生交叉表值:: 用 GROUPING 函数确定由 ROLLUP 或 CUBE 创建的行值:: 用 GROUPING SETS 产生一个单个的结果集Lesson Aim In this lesson you learn how to::: Group data for obtaining the following:-Subtotal values by using the ROLLUP operator -Cross-tabulation values by using the CUBE operator:: Use the GROUPING function to identify the level of aggregation in the results set produced by a ROLLUP or CUBE operator.:: Use GROUPING SETS to produce a single result set(结果集) that is equivalent to a UNION ALL approach(方法,步骤).--**************组函数的回顾*********************组函数在行集上操作，给出分组结果SELECT[column,] group_function(column). . .FROMtable[WHEREcondition][GROUP BYgroup_by_expression][ORDER BYcolumn];例子:SELECT AVG(salary), STDDEV(salary), COUNT(commission_pct),MAX(hire_date)FROM employeesWHERE job_id LIKE 'SA%'; Group Functions You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. Group functions can appear in select lists and in ORDER BY and HAVING clauses. The Oracle Server applies the group functions to each group of rows and returns a single result row for each group. Types of Group Functions Each of the group functions AVG, SUM, MAX, MIN, COUNT, STDDEV, and VARIANCE accept one argument. The functions AVG, SUM, STDDEV, and VARIANCE operate only on numeric values(只运算数字类型的值). MAX and MIN can operate on numeric, character, or date data values. COUNT returns the number of nonnull rows for the given expression. The example in the slide calculates the average salary, standard deviation on the salary, number of employees earning a commission and the maximum hire date for those employees whose JOB_ID begins with SA. Guidelines for Using Group Functions././././.:: The data types for the arguments can be CHAR, VARCHAR2, NUMBER, or DATE.:: All group functions except COUNT(*) ignore null values. To substitute(为NULL替换) a value for null values, use the NVL function. COUNT returns either a number or zero.:: The Oracle Server implicitly sorts(隐含) the results set in ascending order of the grouping columns specified(ASC), when you use a GROUP BY clause. To override this default ordering, you can use DESC in an ORDER BY clause. Instructor Note You can skip this slide if the students are already familiar with these concepts.Group By 子句的回顾语法:SELECT[column,] group_function(column). . .FROMtable[WHEREcondition][GROUP BYgroup_by_expression][ORDER BYcolumn];Example:SELECT department_id,job_id,SUM(salary),COUNT(employee_id)FROM employeesGROUP BY department_id,job_id;Review of GROUP BY Clause (回顾) The example illustrated in the slide is evaluated by the Oracle Server as follows: :: The SELECT clause specifies that the following columns are to be retrieved(找到):-Department ID and job ID columns from the EMPLOYEES table-The sum of all the salaries and the number of employees in each group that you have specified in the GROUP BY clause ../././ :: The GROUP BY clause specifies how the rows should be grouped in the table. The total salary and the number of employees are calculated for each job ID within each department. The rows are grouped by department ID and then grouped by job within each department.HAVING 子句的回顾SELECT[column,] group_function(column)... FROMtable[WHEREcondition][GROUP BYgroup_by_expression][HAVING having_expression] [ORDER BYcolumn];:: 用HAVING 子句指定将被显示的那些组:: 在限制条件的基础上可以进一步约束分组.The HAVING Clause Groups are formed and group functions are calculated before the HAVING clause is applied to the groups. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical../././././The Oracle Server performs the following steps when you use the HAVING clause: 1.Groups rows 2.Applies the group functions to the groups and displays the groups that match the criteria in the HAVING clause SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) >9500;DEPARTMENT_IDAVG(SALARY)8010033.34349010036.410010032.34the example dispalys depatment ID and average salary for those departments whose average salary is greater than $9,500GROUP BY 带 ROLLUP 或 CUBE 操作:: 带 ROLLUP 或 CUBE 的 GROUP BY 子句用交叉引用列产生超合计行:: ROLLUP 分组产生一个包含常规分组行和subtotal的结果集:: CUBE 分组产生一个包含 ROLLUP 行和交叉表行的结果集GROUP BY with the ROLLUP and CUBE Operators You specify ROLLUP and CUBE operators in the GROUP BY clause of a query. ROLLUP grouping produces(产生) a results set containing the regular grouped rows and subtotal rows. The CUBE operation in the GROUP BY clause groups the selected rows based on the values of all possible combinations of expressions in the specification and returns a single row of summary information for each group. You can use the CUBE operator to produce cross-tabulation rows. Note: When working with ROLLUP and CUBE, make sure that the columns following the GROUP BY clause have meaningful, real-life relationships with each other; otherwise the operators return irrelevant information(不相关的信息). The ROLLUP and CUBE operators are available only in Oracle8i and later releases. ROLLUP操作SELECT[column,] group_function(column). . .FROMtable[WHEREcondition][GROUP BY[ROLLUP] group_by_expression][HAVING having_expression];[ORDER BYcolumn];:: ROLLUP 是一个 GROUP BY 子句的扩展:: 用 ROLLUP 操作产生小计和累计The ROLLUP Operator The ROLLUP operator delivers aggregates and superaggregates for expressions within a GROUP BY statement. The ROLLUP operator can be used by report writers to extract statistics and summary information from results sets.(从结果集中得到精确的计算和信息概要) The cumulative aggregates can be used in reports, charts, and graphs. The ROLLUP operator creates groupings by moving in one direction(从一个方向移动), from right to left, along the list of columns specified in the GROUP BY clause. It then applies the aggregate function to these groupings. /././././. Note: To produce subtotals in n dimensions (that is, n columns in the GROUP BY clause) without a ROLLUP operator, n+1 SELECT statements must be linked with UNION ALL. (如果没有rollup操作,而产生N元小计值,要使用N+1个select操作用NUION ALL连接)This makes the query execution inefficient, because each of the SELECT statements causes table access. The ROLLUP operator gathers its results with just one table access. The ROLLUP operator is useful if there are many columns involved(许多与列有关的) in producing the subtotals.--ROLLUP 操作的例子SELECT department_id, job_id, SUM(salary)FROM employees WHERE department_id < 60GROUP BY ROLLUP(department_id, job_id);DEPARTMENT_ID JOB_ID SUM(SALARY)------------- ---------- ----------- 10 AD_ASST 4400 10 4400 20 MK_MAN 13000 20 MK_REP 6000 20 19000 30 PU_MAN 11000 30 PU_CLERK 13900 30 24900 40 HR_REP 6500 40 6500 50 ST_MAN 36400 50 SH_CLERK 64300 50 ST_CLERK 55700 50 156400 211200Example of a ROLLUP Operator In the example in the slide: :: Total salaries for every job ID within a department for those departments whose department ID is less than 60 are displayed by the GROUP BY clause (labeled 1)1.选出条件department_id<60的,并且按GROUP BY子句显示. 10 AD_ASST 4400 20 MK_MAN 13000 20 MK_REP 6000 30 PU_MAN 11000 30 PU_CLERK 13900 40 HR_REP 6500 50 ST_MAN 36400 50 SH_CLERK 64300 50 ST_CLERK 55700 :: The ROLLUP operator displays:-Total salary for those departments whose department ID is less than 60 (labeled 2)显示每组部门ID<60的总的salary. 10 4400 20 19000 30 24900 40 6500 50 156400-Total salary for all departments whose department ID is less than 60, irrespective of the job IDs (labeled 3)(不考虑JOB_ID 号的总的salary). 211200 :: All rows indicated as 1 are regular rows and all rows indicated as 2 and 3 are superaggregate rows.所有行将被显示:第一步是一般的行,在第二,三步将显示聚集行. The ROLLUP operator creates subtotals that roll up from the most detailed level to a grand total, following the grouping list specified in the GROUP BY clause. First it calculates the standard aggregate values for the groups specified in the GROUP BY clause (in the example, the sum of salaries grouped on each job within a department). Then it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. (In the preceding example, the sum of salaries for each department is calculated, followed by the sum of salaries for all departments.) /./././././ :: Given n expressions in the ROLLUP operator of the GROUP BY clause, the operation results in n + 1 = 2 + 1 = 3 groupings. :: Rows based on the values of the first n expressions are called rows or regular rows and the others are called superaggregate rows. --CUBE操作SELECT[column,] group_function(column)...FROMtable[WHEREcondition][GROUP BY[CUBE] group_by_expression][HAVING having_expression][ORDER BYcolumn];:: CUBE 是 GROUP BY 子句的扩展:: 能够用 CUBE 操作产生带单个 SELECT 语句的交叉表值The CUBE Operator The CUBE operator is an additional switch in the GROUP BY clause in a SELECT statement. The CUBE operator can be applied to all aggregate functions(能够用于所有聚集函数), including AVG, SUM, MAX, MIN, and COUNT. It is used to produce results sets that are typically used for cross-tabular reports. While ROLLUP produces only a fraction of possible subtotal combinations, CUBE produces subtotals for all possible combinations of groupings specified in the GROUP BY clause, and a grand total. The CUBE operator is used with an aggregate function to generate additional rows in a results set. Columns included in the GROUP BY clause are cross-referenced to produce a superset of groups. The aggregate function specified in the select list is applied to these groups to produce summary values for the additional superaggregate rows. The number of extra groups in the results set is determined by the number of columns included in the GROUP BY clause. In fact, every possible combination of the columns or expressions in the GROUP BY clause is used to produce superaggregates. If you have n columns or expressions in the GROUP BY clause, there will be 2n possible superaggregate combinations. Mathematically, these combinations form an n-dimensional cube, which is how the operator got its name. By using application or programming tools, these superaggregate values can then be fed into charts and graphs that convey results and relationships visually and effectively.--CUBE 操作: 例子SQL> select department_id,job_id,sum(salary) 2 from employees 3 where department_id<60 4 group by cube(department_id,job_id) 5 order by department_id;DEPARTMENT_ID JOB_ID SUM(SALARY)------------- ---------- ----------- 10 AD_ASST 4400 10 4400 20 MK_MAN 13000 20 MK_REP 6000 20 19000 30 PU_CLERK 13900 30 PU_MAN 11000 30 24900 40 HR_REP 6500 40 6500 50 SH_CLERK 64300 50 ST_CLERK 55700 50 ST_MAN 36400 50 156400 AD_ASST 4400 HR_REP 6500 MK_MAN 13000 MK_REP 6000 PU_CLERK 13900 PU_MAN 11000 SH_CLERK 64300 ST_CLERK 55700 ST_MAN 36400 211200Example of a CUBE Operator The output of the SELECT statement in the example can be interpreted as follows: :: The total salary for every job within a department (for those departments whose department ID is less than 60) is displayed by the GROUP BY clause (labeled 1) 10 AD_ASST 4400 20 MK_MAN 13000 20 MK_REP 6000 30 PU_MAN 11000 30 PU_CLERK 13900 40 HR_REP 6500 50 ST_MAN 36400 50 SH_CLERK 64300 50 ST_CLERK 55700 :: The total salary for those departments whose department ID is less than 60 (labeled 2) 10 4400 20 19000 30 24900 40 6500 50 156400 :: The total salary for every job irrespective of the department (labeled 3)(每个job无关的department的总的salary) HR_REP 6500 MK_MAN 13000 MK_REP 6000 PU_MAN 11000 ST_MAN 36400 AD_ASST 4400 PU_CLERK 13900 SH_CLERK 64300 ST_CLERK 55700 :: Total salary for those departments whose department ID is less than 60, irrespective of the job titles (labeled 4) 211200 In the preceding example, all rows indicated as 1 are regular rows, all rows indicated as 2 and 4 are superaggregate rows, and all rows indicated as 3 are cross-tabulation values. The CUBE operator has also performed the ROLLUP operation to display the subtotals for those departments whose department ID is less than 60 and the total salary for those departments whose department ID is less than 60, irrespective of the job titles. Additionally, the CUBE operator displays the total salary for every job irrespective of the department. Note: Similar to the ROLLUP operator, producing subtotals in n dimensions (that is, n columns in the GROUP BY clause) without a CUBE operator requires 2n SELECT statements to be linked with UNION ALL. Thus, a report with three dimensions requires 2的3次方= 8 SELECT statements to be linked with UNION ALL.--GROUPING 函数SELECT [column,] group_function(column) . , GROUPING(expr)FROM table[WHERE condition][GROUP BY [ROLLUP][CUBE] group_by_expression][HAVING having_expression][ORDER BY column];:: GROUPING 函数既能够被用于 CUBE 操作，也可以被用于 ROLLUP 操作:: 用 GROUPING 函数模拟能够发现在一行中的构成小计的分组:: 用 GROUPING 函数，你能够从 ROLLUP 或 CUBE 创建的空值中区分存储的 NULL 值:: GROUPING 函数返回 0 或 1The GROUPING Function The GROUPING function can be used with either the CUBE or ROLLUP operator to help you understand how a summary value has been obtained. ././././. The GROUPING function uses a single column as its argument(使用单一列). The expr(表达式) in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 0 or 1. The values returned by the GROUPING function(被这个函数返回的值经常用于) are useful to: :: Determine the level of aggregation of a given subtotal; that is, the group or groups on which the subtotal is based//确定组的小计值:: Identify whether a NULL value in the expression column of a row of the result set indicates: ././././././识别在结果集的列的表达式列中NULL值的说明: -A NULL value from the base table (stored NULL value) -A NULL value created by ROLLUP/CUBE (as a result of a group function on that expression) ././././././ A value of 0 returned by the GROUPING function based on an expression indicates one of the following: :: The expression has been used to calculate the aggregate value. 表达式已计算出聚集值. :: The NULL value in the expression column is a stored NULL value. 表达式中的NULL值是存储的NULL值 A value of 1 returned by the GROUPING function based on an expression indicates one of the following: :: The expression has not been used to calculate the aggregate value. :: The NULL value in the expression column is created by ROLLUP or CUBE as a result of grouping.--GROUPING 函数: 例子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_id); DEPTID JOB SUM(SALARY) GRP_DEPT GRP_JOB---------- ---------- ----------- ---------- ---------- 10 AD_ASST 4400 0 0// 10 4400 0 1// 20 MK_MAN 13000 0 0 20 MK_REP 6000 0 0 20 19000 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//SQL> SELECT department_id DEPTID, job_id JOB, 2 SUM(salary), 3 GROUPING(department_id) GRP_DEPT 4 FROM employees 5 WHERE department_id < 50 6 GROUP BY ROLLUP(department_id, job_id); DEPTID JOB SUM(SALARY) GRP_DEPT---------- ---------- ----------- ---------- 10 AD_ASST 4400 0 10 4400 0 20 MK_MAN 13000 0 20 MK_REP 6000 0 20 19000 0 30 PU_MAN 11000 0 30 PU_CLERK 13900 0 30 24900 0 40 HR_REP 6500 0 40 6500 0 54800 1Example of a GROUPING Function In the example in the slide, consider the summary value 4400 in the first row (labeled 1). This summary value is the total salary for the job ID of AD_ASST within department 10. To calculate this summary value, both the columns DEPARTMENT_ID and JOB_ID have been taken into account. Thus a value of 0 is returned for both the expressions GROUPING(department_id) and GROUPING(job_id). Consider the summary value 4400 in the second row (labeled 2). This value is the total salary for department 10 and has been calculated by taking into account the column DEPARTMENT_ID; thus a value of 0 has been returned by GROUPING(department_id). Because the column JOB_ID has not been taken into account to calculate this value, a value of 1 has been returned for GROUPING(job_id). You can observe similar output in the fifth row. In the last row, consider the summary value 23400 (labeled 3). This is the total salary for those departments whose department ID is less than 50 and all job titles. To calculate this summary value, neither of the columns DEPARTMENT_ID and JOB_ID have been taken into account. Thus a value of 1 is returned for both the expressions GROUPING(department_id) and GROUPING(job_id). //插的,老师讲解: Instructor Note Explain that if the same example is run with the CUBE operator, it returns a results set that has 1 for GROUPING(department_id) and 0 for GROUPING(job_id) in the cross-tabulation rows, because the subtotal values are the result of grouping on job irrespective of department number.SQL> SELECT department_id DEPTID, job_id JOB, 2 SUM(salary), 3 GROUPING(department_id) GRP_DEPT, 4 GROUPING(job_id) GRP_JOB 5 FROM employees 6 WHERE department_id < 50 7 GROUP BY CUBE(department_id, job_id); 8 ORDER BY department_id /deptid/DEPTID/1 ; DEPTID JOB SUM(SALARY) GRP_DEPT GRP_JOB---------- ---------- ----------- ---------- ---------- 10 AD_ASST 4400 0 0 10 4400 0 1 20 MK_MAN 13000 0 0 20 MK_REP 6000 0 0 20 19000 0 1 30 PU_CLERK 13900 0 0 30 PU_MAN 11000 0 0 30 24900 0 1 40 HR_REP 6500 0 0 40 6500 0 1 AD_ASST 4400 1 0 HR_REP 6500 1 0 MK_MAN 13000 1 0 MK_REP 6000 1 0 PU_CLERK 13900 1 0 PU_MAN 11000 1 0 54800 1 1--分组集合:: GROUPING SETS 是 GROUP BY 子句更进一步的扩展:: 你能够用 GROUPING SETS 在同一查询中定义多重分组:: Oracle 服务器计算在 GROUPING SETS 子句中指定的所有分组，并且用 UNION ALL 操作组合单个的分组结果:: 分组集合的效率：-对基表仅进行一个查询-不需要写复杂的 UNION 语句-GROUPING SETS 有更多的元素，更好的执行性能GROUPING SETS GROUPING SETS are a further extension of the GROUP BY clause that let you specify multiple groupings of data(可以指定多个组的数据). Doing so facilitates efficient aggregation and hence facilitates analysis of data across multiple dimensions. A single SELECT statement can now be written using 'GROUPING SETS' to specify various groupings (that can also include ROLLUP or CUBE operators), rather than multiple SELECT statements combined by UNION ALL operators. For example, you can say: SELECT department_id, job_id, manager_id, AVG(salary) FROM employees GROUP BY GROUPING SETS ((department_id, job_id, manager_id), (department_id, manager_id),(job_id, manager_id)); DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- SA_REP 149 7000 10 AD_ASST 101 4400 20 MK_MAN 100 13000 20 MK_REP 201 6000 30 PU_MAN 100 11000 30 PU_CLERK 114 2780 40 HR_REP 101 6500 50 ST_MAN 100 7280 50 SH_CLERK 120 2900 50 ST_CLERK 120 2625 50 SH_CLERK 121 3675DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- 50 ST_CLERK 121 2675 50 SH_CLERK 122 3200 50 ST_CLERK 122 2700 50 SH_CLERK 123 3475 50 ST_CLERK 123 3000 50 SH_CLERK 124 2825 50 ST_CLERK 124 2925 60 IT_PROG 102 9000 60 IT_PROG 103 4950 70 PR_REP 101 10000 80 SA_MAN 100 12200DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- 80 SA_REP 145 8500 80 SA_REP 146 8500 80 SA_REP 147 7766.66667 80 SA_REP 148 8650 80 SA_REP 149 8600 90 AD_PRES 24000 90 AD_VP 100 17000 100 FI_MGR 101 12000 100 FI_ACCOUNT 108 7920 110 AC_MGR 101 12000 110 AC_ACCOUNT 205 8300DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- 149 7000 10 101 4400 20 100 13000 20 201 6000 30 100 11000 30 114 2780 40 101 6500 50 100 7280 50 120 2762.5 50 121 3175 50 122 2950DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- 50 123 3237.5 50 124 2875 60 102 9000 60 103 4950 70 101 10000 80 100 12200 80 145 8500 80 146 8500 80 147 7766.66667 80 148 8650 80 149 8600DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- 90 24000 90 100 17000 100 101 12000 100 108 7920 110 101 12000 110 205 8300 AD_VP 100 17000 AC_MGR 101 12000 FI_MGR 101 12000 HR_REP 101 6500 MK_MAN 100 13000DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- MK_REP 201 6000 PR_REP 101 10000 PU_MAN 100 11000 SA_MAN 100 12200 SA_REP 145 8500 SA_REP 146 8500 SA_REP 147 7766.66667 SA_REP 148 8650 SA_REP 149 8333.33333 ST_MAN 100 7280 AD_ASST 101 4400DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- AD_PRES 24000 IT_PROG 102 9000 IT_PROG 103 4950 PU_CLERK 114 2780 SH_CLERK 120 2900 SH_CLERK 121 3675 SH_CLERK 122 3200 SH_CLERK 123 3475 SH_CLERK 124 2825 ST_CLERK 120 2625 ST_CLERK 121 2675DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- ST_CLERK 122 2700 ST_CLERK 123 3000 ST_CLERK 124 2925 AC_ACCOUNT 205 8300 FI_ACCOUNT 108 7920./././././ This statement calculates aggregates over three groupings: (department_id, job_id, manager_id), (department_id, manager_id) and (job_id, manager_id) Without this enhancement in Oracle9i, multiple queries combined together with UNION ALL are required to get the output of the preceding SELECT statement. A multiquery approach is inefficient, for it requires multiple scans of the same data.GROUPING SETS (continued) Compare the preceding statement with this alternative: SELECT department_id, job_id, manager_id, AVG(salary) FROM employees GROUP BY CUBE(department_id, job_id, manager_id); The preceding statement computes all the 8 (2 *2 *2) groupings, though only the groups (department_id, job_id, manager_id), (department_id, manager_id) and (job_id, manager_id)are of interest to you. SELECT department_id, job_id, manager_id, AVG(salary) FROM employees GROUP BY //移过来. GROUPING SETS ((department_id, job_id, manager_id), (department_id, manager_id),(job_id, manager_id)); Another alternative is the following statement: //结果与上面的一样 SELECT department_id, job_id, manager_id, AVG(salary) FROM employees GROUP BY department_id, job_id, manager_id UNION ALL SELECT department_id, NULL, manager_id, AVG(salary) FROM employees GROUP BY department_id, manager_id UNION ALL SELECT NULL, job_id, manager_id, AVG(salary) FROM employees GROUP BY job_id, manager_id; This statement requires three scans of the base table, making it inefficient. CUBE and ROLLUP can be thought of as grouping sets with very specific semantics. The following equivalencies show this fact: CUBE (a,b,c) GROUPING SETS//2的3次方is equivalent to ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c)())ROLLUP(a,b,c)GROUPING SETSis equivalent to((a,b,c),(a,b),(a),())--GROUPING SETS:例子SELECT department_id, job_id, manager_id,avg(salary)FROM employeesGROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id)); DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- SA_REP 7000 10 AD_ASST 4400 20 MK_MAN 13000 20 MK_REP 6000 30 PU_MAN 11000 30 PU_CLERK 2780 40 HR_REP 6500 50 ST_MAN 7280 50 SH_CLERK 3215 50 ST_CLERK 2785 60 IT_PROG 5760 70 PR_REP 10000 80 SA_MAN 12200 80 SA_REP 8396.55172 90 AD_VP 17000 90 AD_PRES 24000 100 FI_MGR 12000 100 FI_ACCOUNT 7920 110 AC_MGR 12000 110 AC_ACCOUNT 8300 AD_VP 100 17000 AC_MGR 101 12000 FI_MGR 101 12000 HR_REP 101 6500 MK_MAN 100 13000 MK_REP 201 6000 PR_REP 101 10000 PU_MAN 100 11000 SA_MAN 100 12200 SA_REP 145 8500 SA_REP 146 8500 SA_REP 147 7766.66667 SA_REP 148 8650...GROUPING SETS: Example The query in the slide calculates aggregates over two groupings. The table is divided into the following groups: -Department ID, Job ID -Job ID, Manager ID //每个组的平均salary要被计算. The average salaries for each of these groups are calculated. The results set displays average salary for each of the two groups. In the output, the group marked as 1 can be interpreted as:(标记为1的说明) :: The average salary of all employees with the job ID AD_ASST in the department 10 is 4400.:: The average salary of all employees with the job ID MK_MAN in the department 20 is 13000.:: The average salary of all employees with the job ID MK_REP in the department 20 is 6000.:: The average salary of all employees with the job ID ST_CLERK in the department 50 is 2925 and so on.DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) 10 AD_ASST 4400 20 MK_MAN 130001 20 MK_REP 6000 50 ST_CLERK 2925......GROUPING SETS: Example (continued) The group marked as 2 in the output is interpreted as: :: The average salary of all employees with the job ID FI_MGR, who report to the manager with the manager ID 101, is 12000. :: The average salary of all employees with the job ID HR_REP, who report to the manager with the manager ID 101, is 6500, and so on. DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)------------- ---------- ---------- ----------- FI_MGR 101 120002 HR_REP 101 6500 The example in the slide can also be written as: SELECT department_id, job_id, NULL as manager_id, AVG(salary) as AVGSAL FROM employees GROUP BY department_id, job_id UNION ALL SELECT NULL, job_id, manager_id, avg(salary) as AVGSAL FROM employees GROUP BY job_id, manager_id; In the absence of an optimizer that looks across query blocks to generate the execution plan, the preceding query would need two scans of the base table, EMPLOYEES. This could be very inefficient. Hence the usage of the GROUPING SETS statement is recommended. 复合列复合列是一个作为整体被处理的列集合ROLLUP (a, (b,c), d):: 为了指定复合列，用 GROUP BY 子句来分组在圆括号内的列，因此， Oracle 服务器在进行 ROLLUP 或 CUBE 操作时将它们作为一个整体来处理::当使用 ROLLUP 或 CUBE 时，复合列将跳过在确定级别上的集合 ././././.Composite Columns A composite column is a collection of columns that are treated as a unit during the computation of groupings. You specify the columns in parentheses(括号) as in the following statement: ROLLUP (a, (b, c), d) (b,c)就是一个复合列,并且做为一个单元处理. /././././././././././. Here, (b,c) form a composite column and are treated as a unit. In general, composite columns are useful in ROLLUP, CUBE, and GROUPING SETS. For example, in CUBE or ROLLUP, composite columns would mean skipping aggregation across certain levels. That is, GROUP BY ROLLUP(a, (b, c)) is equivalent to //// GROUP BY a, b, c UNION ALL GROUP BY a UNION ALL GROUP BY () Here, (b, c) are treated as a unit and rollup will not be applied across (b, c). It is as if you have an alias, for example z, for (b, c), and the GROUP BY expression reduces to GROUP BY ROLLUP(a, z). Note: GROUP BY( ) is typically a SELECT statement with NULL values for the columns a and b and only the aggregate function. This is generally used for generating the grand totals. SELECT NULL, NULL, aggregate_col FROM <table_name> GROUP BY ( );(continued) Compare this with the normal ROLLUP as in: GROUP BY ROLLUP(a, b, c) which would be GROUP BY a, b, c UNION ALL//// GROUP BY a, b UNION ALL GROUP BY a UNION ALL GROUP BY (). Similarly, GROUP BY CUBE((a, b), c) would be equivalent to//// GROUP BY a, b, c UNION ALL GROUP BY a, b UNION ALL GROUP BY c UNION ALL GROUP By () The following table shows grouping sets specification and equivalent GROUP BY specification.GROUPING SETS SatementsEquivalent GROUP BY StatementsGROUP BY a UNION ALLGROUP BY GROUPING SETS(a,b,c)GROUP BY b UNION ALLGROUP BY cGROUP BY GROUPING SETS(a,b,(b,c))GROUP BY a UNION ALL(The GROUPING SETS expression has aGROUP BY b UNION ALLcomposite column)GROUP BY b,cGROUP BY GROUPING SETS((a,b,c))GROUP BY a,b,cGROUP BY a UNION ALLGROUP BY GROUPING SETS(a,(b),())GROUP BY b UNION ALLGROPY BY ()GROUP BY GROUPING SETS(a,ROLLUP(b,c))GROUP BY a UNION ALL(The GROUPING SETS expression has aGROUP BY ROLLUP(b,c)composite column)复合列:例子SELECT department_id, job_id, manager_id, SUM(salary)FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id));把(job_id, manager_id)看成一个单元处理.grouping by :1.(department_id,job_id,manager_id)2.(department_id)3.( )Composite Columns: ExampleConsider the example: SELECT department_id, job_id,manager_id, SUM(salary) FROM employees GROUP BY ROLLUP( department_id,job_id, manager_id);The preceding query results in the Oracle Server computing the following groupings:1.(department_id, job_id, manager_id)2.(department_id, job_id)3.(department_id)4.( )//所有的sum(salary)DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)------------- ---------- ---------- ----------- 10 AD_ASST 101 4400 10 AD_ASST 4400 10 4400 20 MK_MAN 100 13000 20 MK_MAN 13000 20 MK_REP 201 6000 20 MK_REP 6000 20 19000 30 PU_MAN 100 11000 30 PU_MAN 11000 30 PU_CLERK 114 13900 30 PU_CLERK 13900 30 24900 50 ST_MAN 100 36400 50 ST_MAN 36400 50 SH_CLERK 120 11600 50 SH_CLERK 121 14700 50 SH_CLERK 122 12800 50 SH_CLERK 123 13900 50 SH_CLERK 124 11300 50 SH_CLERK 64300 50 ST_CLERK 120 10500 50 ST_CLERK 121 10700 50 ST_CLERK 122 10800 50 ST_CLERK 123 12000 50 ST_CLERK 124 11700 50 ST_CLERK 55700 50 156400...... 691400 If you are just interested in grouping of lines (1), (3), and (4) in the preceding example, you cannot limit the calculation to those groupings without using composite columns. With composite columns, this is possible by treating JOB_ID and MANAGER_ID columns as a single unit while rolling up. Columns enclosed in parentheses are treated as a unit while computing ROLLUP and CUBE. This is illustrated in the example on the slide. By enclosing JOB_ID and MANAGER_ID columns in parenthesis, we indicate to the Oracle Server to treat JOB_ID and MANAGER_ID as a single unit, as a composite column. (continued) The example in the slide computes the following groupings:: (department_id, job_id, manager_id): (department_id):( ) The example in the slide displays the following:: Total salary for every department (labeled 1): Total salary for every department, job ID, and manager (labeled 2): Grand total (labeled 3) The example in the slide can also be written as: SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY department_id,job_id, manager_id UNION ALL SELECT department_id, TO_CHAR(NULL),TO_NUMBER(NULL), SUM(salary) FROM employees GROUP BY department_id UNION ALL SELECT TO_NUMBER(NULL), TO_CHAR(NULL),TO_NUMBER(NULL), SUM(salary) FROM employees GROUP BY (); In the absence of an optimizer that looks across query blocks to generate the execution plan, the preceding query would need three scans of the base table, EMPLOYEES. This could be very inefficient. Hence, the use of composite columns is recommended.SQL> SELECT TO_NUMBER(NULL), TO_CHAR(NULL),TO_NUMBER(NULL), SUM(salary) 2 FROM employees 3 GROUP BY ();TO_NUMBER(NULL) T TO_NUMBER(NULL) SUM(SALARY)--------------- - --------------- ----------- 691400SQL> select count(*),sum(salary) 2 from employees; COUNT(*) SUM(SALARY)---------- ----------- 107 691400SQL> select null,null,null,sum(salary) 2 from employees;N N N SUM(SALARY)- - - ----------- 691400SQL> select null,null,null,sum(salary) 2 FROM employees 3 GROUP BY ();N N N SUM(SALARY)- - - ----------- 691400连接分组:: 连接分组提供一种简明的方式来生成有用的分组组合:: 为了指定连接分组集合，用逗号分开多重分组集合， ROLLUP，和 CUBE 操作，以便 Oracle 服务器将它们组合在一个单个的 GROUP BY 子句中:: 分组是每个分组集合交叉乘积的结果 GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)Concatenated Columns Concatenated groupings offer a concise way to generate useful combinations of groupings. The concatenated groupings are specified simply by listing multiple grouping sets, cubes, and rollups, and separating them with commas. Here is an example of concatenated grouping sets: GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d) The preceding SQL defines the following groupings: (a, c), (a, d), (b, c), (b, d) Concatenation of grouping sets is very helpful for these reasons: :: Ease of query development: you need not manually enumerate all groupings//不用手动列举所有的组.:: Use by applications: SQL generated by OLAP applications often involves concatenation of grouping sets, with each grouping set defining groupings needed for a dimension --连接分组例子SELECT department_id, job_id, manager_id, SUM(salary)FROM employeesGROUP BY department_id,ROLLUP(job_id),CUBE(manager_id);DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)------------- ---------- ---------- ----------- SA_REP 149 7000 10 AD_ASST 101 4400 20 MK_MAN 100 13000 20 MK_REP 201 6000 30 PU_MAN 100 11000... 80 SA_MAN 100 61000 100 FI_MGR 101 12000 100 FI_ACCOUNT 108 39600 110 AC_MGR 101 12000 110 AC_ACCOUNT 205 8300... 149 7000 10 101 4400 20 100 13000 20 201 6000 30 100 11000 30 114 13900 50 123 25900 50 124 23000 60 102 9000 60 103 19800... 100 101 12000 100 108 39600 110 101 12000 110 205 8300 SA_REP 7000 7000 10 AD_ASST 4400 10 4400 20 MK_MAN 13000 20 MK_REP 6000 20 19000... 50 ST_MAN 36400 50 SH_CLERK 64300 50 ST_CLERK 55700 50 156400 60 IT_PROG 28800 60 28800 70 PR_REP 10000 70 10000... 100 FI_ACCOUNT 39600 100 51600 110 AC_MGR 12000 110 AC_ACCOUNT 8300 110 20300Concatenated Groupings Example The example in the slide results in the following groupings:-(department_id, manager_id, job_id )-(department_id, manager_id)-(department_id, job_id)-(department_id) The total salary for each of these groups is calculated. The example in the slide displays the following: :: Total salary for every department, job ID, manager :: Total salary for every department, manager ID :: Total salary for every department, job ID :: Total salary for every department For easier understanding, the details for the department 10 are highlighted in the output. SUMMARY 在本课中, 您应该已经学会如何：:: 用 ROLLUP 操作产生小计值:: 用 CUBE 操作产生交叉表值:: 用 GROUPING 函数指定由 ROLLUP 或 CUBE 创建的行值:: 用 GROUPING SETS 语法定义在同一查询中的多重分组:: 用 GROUP BY 子句以不同的方式组合表达式：-组合列-接分组集合Summary :: ROLLUP and CUBE are extensions of the GROUP BY clause. :: ROLLUP is used to display subtotal and grand total values.//每组值+总的值 :: CUBE is used to display cross-tabulation values.///交叉 :: The GROUPING function helps you determine whether a row is an aggregate produced by a CUBE or ROLLUP operator. :: With the GROUPING SETS syntax, you can define multiple groupings in the same query. GROUP BY computes all the groupings specified and combines them with UNION ALL. :: Within the GROUP BY clause, you can combine expressions in various ways:-To specify composite columns, you group columns within parentheses so that the Oracle Server treats them as a unit while computing ROLLUP or CUBE operations.-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.