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 isDayofmonth(1-31)-Abbreviated nameofmonth -4-digit year Hours of dat(0-23):Minute(0-59):Second(0-59)
TIMESTAMPWITHLOCALTIME 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 valuesby using the ROLLUP operator
-Cross-tabulation valuesby using the CUBE operator
:: Use the GROUPINGfunctionto identify the levelof aggregation in the results set produced by a ROLLUPorCUBE operator.
:: Use GROUPING SETS to produce a single result set(结果集) that is equivalent to a UNIONALL approach(方法,步骤).
--**************组函数的回顾*********************
组函数在行集上操作,给出分组结果
SELECT [column,] group_function(column). . .
FROMtable
[WHERE condition]
[GROUPBY group_by_expression]
[ORDERBYcolumn];
例子:
SELECTAVG(salary), STDDEV(salary),
COUNT(commission_pct),MAX(hire_date)
FROM employees
WHERE job_id LIKE'SA%';
Group Functions
You can use the GROUPBY clause to divide the rowsin a tableinto groups. You can then use the group functions toreturn summary information for each group. Group functions can appear inselect lists andinORDERBYandHAVING clauses. The Oracle Server applies the group functions to each groupofrowsandreturns a single result row for each group.
Types ofGroup 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 onlyonnumericvalues(只运算数字类型的值). MAXandMIN can operate onnumeric, character, ordate data values. COUNTreturns the number of nonnull rowsfor 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 datefor those employees whose JOB_ID begins with SA.
Guidelines for Using Group Functions ././././.
:: The data types for the arguments can be CHAR, VARCHAR2, NUMBER, orDATE.
:: Allgroup functions exceptCOUNT(*) ignorenullvalues. To substitute(为NULL替换) a value fornullvalues, use the NVL function. COUNTreturns either a number or zero.
:: The Oracle Server implicitly sorts(隐含) the results setin ascending orderof the grouping columns specified(ASC), when you use a GROUPBY clause. To override this default ordering, you can use DESCin an ORDERBY clause.
Instructor Note
You can skip this slide if the students are already familiar with these concepts.
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 sumofall the salaries and the number of employees in each group that you have specified in the GROUPBY clause ../././
:: The GROUPBY 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 andthen grouped by job within each department.
HAVING 子句的回顾
SELECT [column,] group_function(column)...
FROMtable
[WHERE condition]
[GROUPBY group_by_expression]
[HAVING having_expression]
[ORDERBYcolumn];
:: 用HAVING 子句指定将被显示的那些组
:: 在限制条件的基础上可以进一步约束分组.
The HAVING Clause
Groups are formed andgroup functions are calculated before the HAVING clause is applied to the groups. The HAVING clause can precede the GROUPBY clause, but it is recommended that you place the GROUPBY 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
GROUPBY department_id
HAVINGAVG(salary) >9500;
DEPARTMENT_ID AVG(SALARY)
80 10033.3434
90 10036.4
100 10032.34
the example dispalys depatment ID and average salary for those departments whose average salary is greater than $9,500
GROUPBY 带 ROLLUP 或 CUBE 操作
:: 带 ROLLUP 或 CUBE 的 GROUPBY 子句用交叉引用列产生超合计行
:: ROLLUP 分组产生一个包含常规分组行和subtotal的结果集
:: CUBE 分组产生一个包含 ROLLUP 行和交叉表行的结果集
GROUPBYwith the ROLLUPandCUBE Operators
You specify ROLLUPandCUBE operators in the GROUPBY clause of a query. ROLLUPgrouping produces(产生) a results set containing the regular grouped rowsand subtotal rows. The CUBE operation in the GROUPBY clause groups the selected rows based on the valuesofall possible combinations of expressions in the specification andreturns a single row of summary information for each group. You can use the CUBE operator to produce cross-tabulation rows.
Note: When working withROLLUPandCUBE, make sure that the columns following the GROUPBY clause have meaningful, real-life relationships with each other; otherwise the operators return irrelevant information(不相关的信息).
The ROLLUPandCUBE operators are available onlyin Oracle8i and later releases.
ROLLUP操作
SELECT [column,] group_function(column). . .
FROMtable
[WHERE condition]
[GROUPBY [ROLLUP] group_by_expression]
[HAVING having_expression];
[ORDERBYcolumn];
:: ROLLUP 是一个 GROUPBY 子句的扩展
:: 用 ROLLUP 操作产生小计和累计
The ROLLUP Operator
The ROLLUP operator delivers aggregates and superaggregates for expressions within a GROUPBY statement. The ROLLUP operator can be used by report writers to extract statisticsand 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(从一个方向移动), fromrighttoleft, along the list of columns specified in the GROUPBY clause. It then applies the aggregate functionto these groupings.
/././././.
Note: To produce subtotals in n dimensions (that is, n columns in the GROUPBY clause) without a ROLLUP operator, n+1 SELECT statements must be linked withUNIONALL. (如果没有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 < 60
GROUPBYROLLUP(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
211200
Example 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 GROUPBY clause (labeled 1)
1.选出条件department_id<60的,并且按GROUPBY子句显示.
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 forall departments whose department ID is less than 60, irrespective of the job IDs (labeled 3)(不考虑JOB_ID 号的总的salary).
211200
:: Allrows indicated as 1 are regular rowsandallrows indicated as 2 and 3 are superaggregate rows.所有行将被显示:第一步是一般的行,在第二,三步将显示聚集行.
The ROLLUP operator creates subtotals that roll up from the most detailed levelto a grand total, following the grouping list specified in the GROUPBY clause. First it calculates the standard aggregate valuesfor the groups specified in the GROUPBY clause (in the example, the sumof salaries grouped on each job within a department). Then it creates progressively higher-level subtotals, moving fromrighttoleft through the list ofgrouping columns. (In the preceding example, the sumof salaries for each department is calculated, followed by the sumof salaries forall departments.)
/./././././
:: Given n expressions in the ROLLUP operator of the GROUPBY clause, the operation results in n + 1 = 2 + 1 = 3 groupings.
:: Rows based on the valuesof the first n expressions are called rowsor regular rowsand the others are called superaggregate rows.
--CUBE操作
SELECT [column,] group_function(column)...
FROMtable
[WHERE condition]
[GROUPBY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDERBYcolumn];
:: CUBE 是 GROUPBY 子句的扩展
:: 能够用 CUBE 操作产生带单个 SELECT 语句的交叉表值
The CUBE Operator
The CUBE operator is an additional switch in the GROUPBY clause in a SELECT statement. The CUBE operator can be applied toall aggregate functions(能够用于所有聚集函数), including AVG, SUM, MAX, MIN, andCOUNT. It is used to produce results sets that are typically used forcross-tabular reports. While ROLLUP produces only a fraction of possible subtotal combinations, CUBE produces subtotals forall possible combinations of groupings specified in the GROUPBY clause, and a grand total.
The CUBE operator is used with an aggregate functionto generate additional rowsin a results set. Columns included in the GROUPBY 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 valuesfor the additional superaggregate rows. The number of extra groups in the results setis determined by the number of columns included in the GROUPBY clause.
In fact, every possible combination of the columns or expressions in the GROUPBY clause is used to produce superaggregates.
If you have n columns or expressions in the GROUPBY 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 groupbycube(department_id,job_id)
5 orderby 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
211200
Example of a CUBE Operator
The outputof 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 GROUPBY 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, allrows indicated as 1 are regular rows, allrows indicated as 2 and 4 are superaggregate rows, andallrows 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 GROUPBY clause) without a CUBE operator requires 2n SELECT statements to be linked withUNIONALL. Thus, a report with three dimensions requires 2的3次方= 8 SELECT statements to be linked withUNIONALL.
The GROUPINGfunction can be used with either the CUBEorROLLUP operator to help you understand how a summary value has been obtained.
././././.
The GROUPINGfunction uses a single columnas its argument(使用单一列). The expr(表达式) in the GROUPINGfunction must match one of the expressions in the GROUPBY clause. The functionreturns a value of 0 or 1.
The values returned by the GROUPINGfunction(被这个函数返回的值经常用于) are useful to:
:: Determine the levelof aggregation of a given subtotal; that is, the groupor groups on which the subtotal is based//确定组的小计值
:: Identify whether a NULL value in the expression columnof a row of the result set indicates: ././././././识别在结果集的列的表达式列中NULL值的说明:
-A NULL value from the base table (stored NULL value)
-A NULL value created byROLLUP/CUBE (as a result of a groupfunctionon that expression)
././././././
A value of 0 returned by the GROUPINGfunction 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 columnis a stored NULL value.
表达式中的NULL值是存储的NULL值
A value of 1 returned by the GROUPINGfunction 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 columnis created byROLLUPorCUBEas a result ofgrouping.
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) andGROUPING(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 byGROUPING(department_id). Because the column JOB_ID has not been taken into account to calculate this value, a value of 1 has been returned forGROUPING(job_id). You can observe similar outputin 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 andall 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) andGROUPING(job_id).
//插的,老师讲解:
Instructor Note
Explain that if the same example is run with the CUBE operator, it returns a results set that has 1 forGROUPING(department_id) and 0 forGROUPING(job_id) in the cross-tabulation rows, because the subtotal values are the result ofgroupingon job irrespective of department number.
GROUPING SETS are a further extension of the GROUPBY 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 ROLLUPorCUBE operators), rather than multiple SELECT statements combined byUNIONALL operators. For example, you can say:
Without this enhancement in Oracle9i, multiple queries combined together withUNIONALL are required to get the outputof 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:
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.
This statement requires three scans of the base table, making it inefficient.
CUBEandROLLUP can be thought ofasgrouping 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 SETS
is equivalent to ((a,b,c),(a,b),(a),())
--GROUPING SETS:例子
SELECT department_id, job_id,
manager_id,avg(salary)
FROM employees
GROUPBYGROUPING 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 tableis 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 ofall employees with the job ID AD_ASST in the department 10 is 4400.
:: The average salary ofall employees with the job ID MK_MAN in the department 20 is 13000.
:: The average salary ofall employees with the job ID MK_REP in the department 20 is 6000.
:: The average salary ofall 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 13000 1
20 MK_REP 6000
50 ST_CLERK 2925
...
...
GROUPING SETS: Example (continued)
The group marked as 2 in the outputis interpreted as:
:: The average salary ofall employees with the job ID FI_MGR, who report to the manager with the manager ID 101, is 12000.
:: The average salary ofall 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 12000 2
HR_REP 101 6500
The example in the slide can also be written as:
SELECT department_id, job_id, NULLas manager_id,
AVG(salary) as AVGSAL
FROM employees
GROUPBY department_id, job_id
UNIONALL
SELECTNULL, job_id, manager_id, avg(salary) as AVGSAL
FROM employees
GROUPBY 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.
A composite columnis a collection of columns that are treated as a unit during the computation of groupings. You specify the columns in parentheses(括号) asin the following statement:
ROLLUP (a, (b, c), d) (b,c)就是一个复合列,并且做为一个单元处理.
/././././././././././.
Here, (b,c) form a composite columnand are treated as a unit. In general, composite columns are useful inROLLUP, CUBE, andGROUPING SETS. For example, inCUBEorROLLUP, composite columns would mean skipping aggregation across certain levels.
That is, GROUPBYROLLUP(a, (b, c))
is equivalent to
GROUPBY a, b, c UNIONALL
GROUPBY a UNIONALL
GROUPBY ()
Here, (b, c) are treated as a unit androllup will not be applied across (b, c). It isas if you have an alias, for example z, for (b, c), and the GROUPBY expression reduces to
GROUPBYROLLUP(a, z).
Note: GROUPBY( ) is typically a SELECT statement withNULLvaluesfor the columns a and b andonly the aggregate function. This is generally used for generating the grand totals.
SELECTNULL, NULL, aggregate_col
FROM <table_name>
GROUPBY ( );
(continued)
Compare this with the normal ROLLUPasin:
GROUPBYROLLUP(a, b, c)
which would be
GROUPBY a, b, c UNIONALL
GROUPBY a, b UNIONALL
GROUPBY a UNIONALL
GROUPBY ().
Similarly,
GROUPBYCUBE((a, b), c)
would be equivalent to
GROUPBY a, b, c UNIONALL
GROUPBY a, b UNIONALL
GROUPBY c UNIONALL
GROUPBy ()
The following table shows grouping sets specification and equivalent GROUPBY specification.
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 ingroupingof 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 ROLLUPandCUBE. 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)
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.
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:
GROUPBYGROUPING SETS(a, b), GROUPING SETS(c, d)
The preceding SQL defines the following groupings:
(a, c), (a, d), (b, c), (b, d)
Concatenation ofgrouping 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 ofgrouping sets, with each groupingset defining groupings needed for a dimension
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 语法定义在同一查询中的多重分组
:: 用 GROUPBY 子句以不同的方式组合表达式:
-组合列
-接分组集合
Summary
:: ROLLUPandCUBE are extensions of the GROUPBY clause.
:: ROLLUPis used to display subtotal and grand total values.//每组值+总的值
:: CUBEis used to display cross-tabulation values. ///交叉
:: The GROUPINGfunction helps you determine whether a row is an aggregate produced by a CUBEorROLLUP operator.
:: With the GROUPING SETS syntax, you can define multiple groupings in the same query. GROUPBY computes all the groupings specified and combines them withUNIONALL.
:: Within the GROUPBY 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 ROLLUPorCUBE operations.
-To specify concatenated grouping sets, you separate multiple grouping sets, ROLLUP, andCUBE operations with commas so that the Oracle Server combines them into a single GROUPBY clause. The result is a cross-product of groupings from each groupingset.