聚合函数的扩展使用
聚合函数的扩展使用
1.ROLLUP子句
ROLLUP是GROUP BY子句的扩展，它是为每一个分组返回一条合计记录，并为全部分组返回总计。下面举例说明：
EG：
select * from employees2;
EMPLOYEE_ID DIVISI JOB_ID FIRST_NAME LAST_NAME SALARY
----------- ------ ------ -------------------- -------------------- ----------
1 BUS PRE James Smith 800000
2 SAL MGR Ron Johnson 350000
3 SAL WOR Fred Hobbs 140000
4 SUP MGR Susan Jones 200000
5 SAL WOR Rob Green 350000
6 SUP WOR Jane Brown 200000
7 SUP MGR John Grey 265000
8 SUP WOR Jean Blue 110000
9 SUP WOR Henry Heyson 125000
10 OPE MGR Kevin Black 225000
11 OPE MGR Keith Long 165000
12 OPE WOR Frank Howard 125000
13 OPE WOR Doreen Penn 145000
14 BUS MGR Mark Smith 155000
15 BUS MGR Jill Jones 175000
16 OPE ENG Megan Craig 245000
17 SUP TEC Matthew Brant 115000
18 OPE MGR Tony Clerke 200000
19 BUS MGR Tanya Conway 200000
20 OPE MGR Terry Cliff 215000
21 SAL MGR Steve Green 275000
22 SAL MGR Roy Red 375000
23 SAL MGR Sandra Smith 335000
24 SAL MGR Gail Silver 225000
25 SAL MGR Gerald Gold 245000
26 SAL MGR Eileen Lane 235000
27 SAL MGR Doreen Upton 235000
28 SAL MGR Jack Ewing 235000
29 SAL MGR Paul Owens 245000
30 SAL MGR Melanie York 255000
31 SAL MGR Tracy Yellow 225000
32 SAL MGR Sarah White 235000
33 SAL MGR Terry Iron 225000
34 SAL MGR Christine Brown 247000
35 SAL MGR John Brown 249000
36 SAL MGR Kelvin Trenton 255000
37 BUS WOR Damon Jones 280000
已选择37行。
首先我们来看单独的GROUP BY语句：
select division_id,SUM(salary) from employees2
group by division_id;
DIVISI SUM(SALARY)
------ -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
接下来是扩展了的ROLLUP的使用：
select division_id,SUM(salary) from employees2
group by ROLLUP(division_id);
DIVISI SUM(SALARY)
------ -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
再来看个例子：
select division_id,job_id,SUM(salary)
from employees2
group by ROLLUP(division_id,job_id);
DIVISI JOB_ID 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
小结：扩展了一个rollup子句带来了分组的一个合计。
2.CUBE子句
CUBE子句也是对GROUP BY子句进行扩展，返回CUBE中所有列组合的小计信息，同时在最后显示总计信息。
EG：
select division_id,job_id,SUM(salary)
from employees2
group by CUBE(division_id,job_id)
order by division_id;
DIVISI JOB_ID 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
结：CUBE这里的使用与ROLLUP基本相同，但CUBE的合计更加详细，它能够显示次分组字段的合计信息（job_id）
3.GROUPING()函数与ROLLUP、CUBE的结合使用
GROUPING（）可接受一个列值，当列值为空时，函数返回1；如果列值非空，则返回0。（注意：GROUPING只能在ROLLUP、CUBE查询中使用。还可以结合DECODE（）函数来注释使用更好）
现在我们把上面的一个例子改一下;
select GROUPING(division_id), division_id,SUM(salary)
from employees2
group by ROLLUP(division_id);
GROUPING(DIVISION_ID) DIVISI SUM(SALARY)
--------------------- ------ -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
下面再用DECODE（）函数来转换下更好：
select DECODE(GROUPING(division_id),1,'All divisions',division_id)AS div,
division_id,SUM(salary)
from employees2
group by ROLLUP(division_id);
DIV DIVISI SUM(SALARY)
-------------------------- ------ -----------
BUS BUS 1610000
OPE OPE 1320000
SAL SAL 4936000
SUP SUP 1015000
All divisions 8881000
哈哈，这样看下更清晰明了
再举例CUBE与GROUPING（）结合的使用
select
DECODE(GROUPING(division_id),1,'All divisions',division_id)AS div,
DECODE(GROUPING(job_id),1,'All jobs',job_id) AS job,
SUM(salary)
from employees2
group by CUBE(division_id,job_id)
order by division_id;
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行。
2）下面使用GROUPING SETS子句来限制只返回小计记录。例如下：
select division_id,job_id,SUM(salary)
from employees2
group by GROUPING SETS(division_id,job_id);
DIVISI JOB_ID SUM(SALARY)
------ ------ -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
ENG 245000
MGR 6246000
PRE 800000
TEC 115000
WOR 1475000
已选择9行。
4.GROUPING_ID（）函数的使用
GROUPING_ID（）函数可接受一列或多列，它返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是按照顺序对每一列调用GROUPING（）函数的结果组合起来。它的作用是借助HAVING子句对记录进行过滤，将不包含小计或总计的记录除去。
GROUPING位向量的十进制值，我们由前面的介绍已知道当GROUPING（）的列值为空时它返回1，当非空时返回0；
比如：division_id,job_id两列都为非空，GROUPING（）都返回0。将这两列的值组合起来，形成一个位向量00，十进制为0。即，当division_id,job_id都非空时，GROUPING_ID（）返回0。（这里要特别注意division_id与job_id两列的顺序）下面举个例子：
select division_id,job_id,
GROUPING(division_id) AS div_grp,
GROUPING(job_id) AS job_grp,
GROUPING_ID(division_id,job_id) AS grp_id,
SUM(salary)
from employees2
group by CUBE(division_id,job_id)
order by division_id;
DIVISI JOB_ID 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行。
下面是一个使用GROUPING_ID过滤不包含小计或总计的记录的例子：
select division_id,job_id,
GROUPING_ID(division_id,job_id) AS grp_id,
SUM(salary)
from employees2
group by CUBE(division_id,job_id)
having grouping_id(division_id,job_id)>0
order by division_id;
DIVISI JOB_ID 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
好了这次对聚合函数GROUP BY的扩展使用到此，下次对分析函数做下总结
导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报
TAG:
