聚合函数的扩展使用

聚合函数的扩展使用

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的扩展使用到此，下次对分析函数做下总结

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8030006/viewspace-368902/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8030006/viewspace-368902/