Oracle_071_lesson_p21
Generating Reports by Grouping > you should be able to use the:1、ROLLUP operation to produce subtotal values
2、CUBE operation to produce cross-tabulation values
3、GROUPING function to> 4、GROUPING SETS to produce a single result set
group
SELECT group_function(column). . .
FROM table
;
例：
SELECT AVG(salary), STDDEV(salary),
COUNT(commission_pct),MAX(hire_date)
FROM employees
WHEREjob_id LIKE 'SA%';
SELECT
FROM table
;
SELECT department_id, job_id, SUM(salary),
COUNT(employee_id)
FROM employees
GROUP BY department_id, job_id ;
SELECT group_function(column)...
FROM table
;
ROLLUP操作:从右往左减列分别运算输出小计
SELECTgroup_function(column). . .
FROM table
;
;
例：
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);
COBE操作：除了ROLLUP的操作，还要从左往右减列分别运算输出小计
SELECT group_function(column)...
FROM table
;
例：
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY CUBE (department_id, job_id) ;
!
GROUPING 函数
The GROUPING function:
1、Is used with either the CUBE or ROLLUP operator
2、Is used to find the groups forming the subtotal in a row
3、Is used to differentiate stored NULL values from NULL values created by ROLLUP or CUBE
4、Returns 0 or 1
SELECT group_function(column) .. ,
GROUPING(expr)
FROM table
group_by_expression]
;
例：
SELECT department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
GROUPING SETS
The GROUPING SETS syntax is used to define multiple groupings in the same query.
All groupings specified in the GROUPING SETS clause are computed and the results of individual groupings are combined with a UNION ALL operation.
Grouping set efficiency:
Only one pass over the base table is required.
There is no need to write complex UNION statements.
The more elements GROUPING SETS has, the greater is the performance benefit.
例：
SELECT department_id, job_id,
manager_id,AVG(salary)
FROM employees
GROUP BY GROUPING SETS((department_id,job_id), (job_id,manager_id));
Composite Columns复合列
A composite column is a collection of columns that are treated as a unit.
ROLLUP (a, (b,c), d)
Use parentheses within the GROUP BY clause to group columns, so that they are treated as a unit while computing ROLLUP or CUBE operations.
When used with ROLLUP or CUBE, composite columns would require skipping aggregation across certain levels.
例：
SELECT department_id, job_id, manager_id,
SUM(salary)
FROM employeesGROUP BY ROLLUP( department_id,(job_id, manager_id));
Concatenated Groupings连接分组
Concatenated groupings offer a concise way to generate useful combinations of groupings.
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.
GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)
例：
SELECT department_id, job_id, manager_id,
SUM(salary)
FROM employeesGROUP BY department_id,
ROLLUP(job_id),
CUBE(manager_id);
页:
[1]