Chapter 7: Advanced Queries(2)
■The Extended GROUP BY Clauses
・ROLLUP, which extends the GROUP BY clause to return a row containing a subtotal for each group of rows, plus a row containing a grand total for all the groups.
eg:
①
SQL> select division_id,sum(salary)
2 from employees2
3 group by division_id
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
②Passing a Single Column to ROLLUP
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
③Passing Multiple Columns to ROLLUP
SQL> select division_id,job_id,sum(salary)
2 from employees2
3 group by rollup(division_id,job_id)
4 order by division_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE 1320000
SAL MGR 4446000
SAL WOR 490000
SAL 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP 1015000
8881000
已选择16行。
④Changing the Position of Columns Passed to ROLLUP
1 select job_id,division_id,sum(salary)
2 from employees2
3* group by rollup(job_id,division_id)
SQL> /
JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE 245000
ENG 245000
MGR BUS 530000
MGR OPE 805000
MGR SAL 4446000
MGR SUP 465000
MGR 6246000
PRE BUS 800000
PRE 800000
TEC SUP 115000
TEC 115000
WOR BUS 280000
WOR OPE 270000
WOR SAL 490000
WOR SUP 435000
WOR 1475000
8881000
已选择17行。
⑤Using Other Aggregate Functions with ROLLUP（Avg()....）
・CUBE, which extends the GROUP BY clause to return rows containing a subtotal for all
combinations of columns, plus a row containing the grand total.
SQL> l
1 select division_id,job_id,sum(salary)
2 from employees2
3 group by cube(division_id,job_id)
4 order by division_id,job_id
5*
SQL> /
DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE 1320000
SAL MGR 4446000
SAL WOR 490000
SAL 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP 1015000
ENG 245000
MGR 6246000
PRE 800000
TEC 115000
WOR 1475000
8881000
已选择21行。
SQL> select job_id,division_id,sum(salary)
2 from employees2
3 group by cube(job_id,division_id)
4 order by job_id,division_id
5 ;
JOB DIV SUM(SALARY)
--- --- -----------
ENG OPE 245000
ENG 245000
MGR BUS 530000
MGR OPE 805000
MGR SAL 4446000
MGR SUP 465000
MGR 6246000
PRE BUS 800000
PRE 800000
TEC SUP 115000
TEC 115000
WOR BUS 280000
WOR OPE 270000
WOR SAL 490000
WOR SUP 435000
WOR 1475000
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
已选择21行。
SQL>
■GROUPING() Function
The GROUPING() function accepts a column and returns 0 or 1. GROUPING() returns 1 when the column value is null and returns 0 when the column value is non-null.
eg:
①Using GROUPING() with a Single Column in a ROLLUP
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id
5 ;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
SQL> select grouping(division_id),division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
②Using CASE to Convert the Returned Value from GROUPING()
SQL> edit;
已写入 file afiedt.buf
1 select
2 case grouping(division_id)
3 when 1 then 'All divisions'
4 else division_id
5 end as div,
6 sum(salary)
7 from employees2
8 group by rollup(division_id)
9* order by division_id
SQL> /
DIV SUM(SALARY)
------------- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
All divisions 8881000
③Using CASE and GROUPING() to Convert Multiple Column Values
SQL> l
1 select
2 case grouping(division_id)
3 when 1 then 'All divisions'
4 else division_id
5 end as div,
6 case grouping(job_id)
7 when 1 then 'All jobs'
8 else job_id
9 end as job,
10 sum(salary)
11 from employees2
12 group by rollup(division_id,job_id)
13* order by division_id,job_id
SQL> /
DIV JOB SUM(SALARY)
------------- -------- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS All jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE All jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL All jobs 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP All jobs 1015000
All divisions All jobs 8881000
已选择16行。
④Using GROUPING() with CUBE
SQL> edit;
已写入 file afiedt.buf
1 select
2 case grouping(division_id)
3 when 1 then 'All divisions'
4 else division_id
5 end as div,
6 case grouping(job_id)
7 when 1 then 'All jobs'
8 else job_id
9 end as job,sum(salary)
10 from employees2
11 group by cube(division_id,job_id)
12* order by division_id,job_id
SQL> /
DIV JOB SUM(SALARY)
------------- -------- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS All jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE All jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL All jobs 4936000
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP All jobs 1015000
All divisions ENG 245000
All divisions MGR 6246000
All divisions PRE 800000
All divisions TEC 115000
All divisions WOR 1475000
All divisions All jobs 8881000
已选择21行。
SQL>
■GROUPING SETS Clause
You use the GROUPING SETS clause to get just the subtotal rows.
the totalfor all salaries is not returned.
eg:
SQL> select division_id,job_id,sum(salary)
2 from employees2
3 group by grouping sets(division_id,job_id)
4 order by division_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
ENG 245000
MGR 6246000
PRE 800000
TEC 115000
WOR 1475000
已选择9行。
TIP：The GROUPING SETS clause typically offers better performance than
CUBE. Therefore, you should use GROUPING SETS rather than CUBE wherever possible.
■GROUPING_ID() Function
You can use the GROUPING_ID() function to filter rows using a HAVING clause to exclude rows
that don’t contain a subtotal or total. The GROUPING_ID() function accepts one or more columns
and returns the decimal equivalent of the GROUPING bit vector.
non-null non-null 00 0
non-null null 01 1
null non-null 10 2
null null 11 3
non-null null 01 1
null non-null 10 2
null null 11 3
eg：
SQL> edit
已写入 file afiedt.buf
1 select
2 division_id,job_id,
3 grouping(division_id) as div_grp
4 ,grouping(job_id) as job_grp,
5 grouping_id(division_id,job_id) as grp_id,
6 sum(salary)
7 from employees2
8 group by cube(division_id,job_id)
9* order by division_id,job_id
10 ;
DIV JOB DIV_GRP JOB_GRP GRP_ID SUM(SALARY)
--- --- ---------- ---------- ---------- -----------
BUS MGR 0 0 0 530000
BUS PRE 0 0 0 800000
BUS WOR 0 0 0 280000
BUS 0 1 1 1610000
OPE ENG 0 0 0 245000
OPE MGR 0 0 0 805000
OPE WOR 0 0 0 270000
OPE 0 1 1 1320000
SAL MGR 0 0 0 4446000
SAL WOR 0 0 0 490000
SAL 0 1 1 4936000
SUP MGR 0 0 0 465000
SUP TEC 0 0 0 115000
SUP WOR 0 0 0 435000
SUP 0 1 1 1015000
ENG 1 0 2 245000
MGR 1 0 2 6246000
PRE 1 0 2 800000
TEC 1 0 2 115000
WOR 1 0 2 1475000
1 1 3 8881000
已选择21行。
SQL> select
2 division_id,job_id,
3 grouping_id(division_id,job_id) as grp_id,
4 sum(salary)
5 from employees2
6 group by cube(division_id,job_id)
7 having grouping_id(division_id,job_id)>0
8 order by division_id,job_id;
DIV JOB GRP_ID SUM(SALARY)
--- --- ---------- -----------
BUS 1 1610000
OPE 1 1320000
SAL 1 4936000
SUP 1 1015000
ENG 2 245000
MGR 2 6246000
PRE 2 800000
TEC 2 115000
WOR 2 1475000
3 8881000
已选择10行。
■GROUP_ID() Function
・You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BYclause. GROUP_ID() doesn’t accept any parameters. If n duplicates exist for a particulargrouping, GROUP_ID returns numbers in the range 0 to n – 1.
・Using a Column Multiple Times in a GROUP BY Clause
SQL> edit;
已写入 file afiedt.buf
1 select division_id,job_id,group_id(),sum(salary)
2 from employees2
3* group by division_id,rollup(division_id,job_id)
SQL> /
DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS MGR 0 530000
BUS PRE 0 800000
BUS WOR 0 280000
OPE ENG 0 245000
OPE MGR 0 805000
OPE WOR 0 270000
SAL MGR 0 4446000
SAL WOR 0 490000
SUP MGR 0 465000
SUP TEC 0 115000
SUP WOR 0 435000
BUS 0 1610000
OPE 0 1320000
SAL 0 4936000
SUP 0 1015000
BUS 1 1610000
OPE 1 1320000
SAL 1 4936000
SUP 1 1015000
已选择19行。
・
SQL> edit
已写入 file afiedt.buf
1 select division_id,job_id,group_id(),sum(salary)
2 from employees2
3 group by division_id,rollup(division_id,job_id)
4* having group_id()=0
SQL> /
DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS MGR 0 530000
BUS PRE 0 800000
BUS WOR 0 280000
OPE ENG 0 245000
OPE MGR 0 805000
OPE WOR 0 270000
SAL MGR 0 4446000
SAL WOR 0 490000
SUP MGR 0 465000
SUP TEC 0 115000
SUP WOR 0 435000
BUS 0 1610000
OPE 0 1320000
SAL 0 4936000
SUP 0 1015000
已选择15行。
※
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 Satements Equivalent GROUP BY Statements
GROUP BY a UNION ALL
GROUP BY GROUPING SETS(a,b,c) GROUP BY b UNION ALL
GROUP BY c
GROUP BY GROUPING SETS(a,b,(b,c)) GROUP BY a UNION ALL
(The GROUPING SETS expression has a GROUP BY b UNION ALL
composite column) GROUP BY b,c
GROUP BY GROUPING SETS((a,b,c)) GROUP BY a,b,c
GROUP BY a UNION ALL
GROUP BY GROUPING SETS(a,(b),()) GROUP BY b UNION ALL
GROPY BY ()
GROUP BY GROUPING SETS(a,ROLLUP(b,c)) GROUP BY a UNION ALL
(The GROUPING SETS expression has a GROUP BY ROLLUP(b,c)
composite column)
※※
※※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 SETS
is equivalent to ((a,b,c),(a,b),(a),())
■Using the Analytic Functions
246~