分组查询进阶-GROUP查询的高级运用

这次讲一下分组查询的高级运用，当然了，初级运用我好像还没有写，以后用空了补上。

先来复习一下分组查询的几个常见函数（GROUP FUNCTIONS）：AVG， SUM， STDDEV， VARIANCE

 

（一）review

 

分组函数使用指南：

1.数据类型可以是：char, varchar, number or date

2.所有的分组查询函数，除了COUNT（*）忽略空值，其他都不忽略空值，如果你想替代空值可以用NVL函数。

 

下面复习一下GROUP BY 子句

例：

select department_id, job_id, sum(salary), count(employee_id)

from employees

group by department_id, job_id;

 

DEPARTMENT_ID	JOB_ID	SUM(SALARY)	COUNT(EMPLOYEE_ID)
10	AD_ASST	4400	1
20	MK_MAN	13000	1
20	MK_REP	6000	1
50	ST_CLERK	9100	3
50	ST_MAN	5800	1
60	IT_PROG	19200	3
80	SA_MAN	10500	1
80	SA_REP	19600	2

 

having 的运用

例：

select department_id, job_id, sum(salary), count(employee_id)

from employees

group by department_id, job_id

having department_id > 20;

 

DEPARTMENT_ID	JOB_ID	SUM(SALARY)	COUNT(EMPLOYEE_ID)
50	ST_CLERK	9100	3
50	ST_MAN	5800	1
60	IT_PROG	19200	3
80	SA_MAN	10500	1
80	SA_REP	19600	2

 

可以发现having就是为GROUP 查询添加了一个限制条件而已。

 

（二） rollup and cube 操作与运用

 

下面我们来看一下分组查询中的rollup操作：

rollup其实是group by的一个扩展，用来产生一个累计的合计，或者是一个小计。

例：

select department_id, job_id, sum(salary)

from employees

where department_id < 60

group by rollup (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
50	ST_CLERK	9100
50	ST_MAN	5800
50		14900
		38300

 

从查询结果我们可以清晰地看出，系统按照相同的department_id进行了分组求和。相当于一个分组统计。相信这个功能还是蛮用处的。

 

我们再来看cube操作：

cube也是一个group by的一个扩展，我们先来看看它的运用再来理解他的功能

select department_id, job_id, sum(salary)
from employees
where department_id < 60
group by cube (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
50	ST_CLERK	9100
50	ST_MAN	5800
50		14900
	AD_ASST	4400
	MK_MAN	13000
	MK_REP	6000
	ST_CLERK	9100
	ST_MAN	5800
		38300

 

我们可以看到与rollup相同的是它首先按照department_id的分组进行了分组求和，所不同的是他又按照job_id的分组规则将数据重新单独排列，并又求了一个和。

 

这就是cube操作，很有意思。

 

（三）GROUPING 函数的运用

这个函数的作用就是帮助你理解通过cube,rollup获得的统计值，并返回0，1。

在以下2种情况下返回0：

当表达式用于计算统计值得时候；

当表达式中的空值是用来存储空值的时候；

 

在以下2种情况返回1：

当表达式不用来计算统计值得时候；

当表达式中的空值是rollup,cube当成分组结果而创建的时候；

 

我们看个例子来帮助理解:

select department_id, job_id, sum(salary),

grouping(department_id),

grouping(job_id)

from employees

where department_id<50

group by rollup (department_id,job_id);

 

DEPARTMENT_ID	JOB_ID	SUM(SALARY)	GROUPING(DEPARTMENT_ID)	GROUPING(JOB_ID)
10	AD_ASST	4400	0	0
10		4400	0	1
20	MK_MAN	13000	0	0
20	MK_REP	6000	0	0
20		19000	0	1
		23400	1	1

 

很好理解，大家注意最后一行数据，为什么都是1？因为最后一行是综合统计，而不是针对department_id,或者job_id的。所以2个值都被设置为1了。

 

（四）grouping sets 的运用

grouping sets 用来在一个查询中使用不同的grouping办法，我们看一个例子帮助理解。

select department_id, job_id, manager_id, avg(salary)

from employees

group by grouping sets

((department_id,job_id),(job_id,manager_id));

 

DEPARTMENT_ID	JOB_ID	MANAGER_ID	AVG(SALARY)
10	AD_ASST		4400
20	MK_MAN		13000
20	MK_REP		6000
50	ST_CLERK		3033.33333
50	ST_MAN		5800

 

DEPARTMENT_ID	JOB_ID	MANAGER_ID	AVG(SALARY)
	AC_ACCOUNT	205	8300
	AC_MGR	101	12000
	AD_ASST	101	4400

我们可以很明显的看出查询结果被分成了2个部分，第一个部分是以department_id ，job_id来统计的，第二个则是以job_id, manager_id来统计的。统计很简单，这里不再复述。

 

（五）composite columns列的组合

 

其实就是把不同的列组合起来看成一个单元然后进行分组查询比如

rollup(a,(b,c),d)

 

看个例子理解一下：

 

select department_id, job_id, manager_id, sum(salary)

from employees

group by rollup(department_id,(job_id,manager_id));

 

DEPARTMENT_ID	JOB_ID	MANAGER_ID	SUM(SALARY)
10	AD_ASST	101	4400
10			4400
20	MK_MAN	100	13000
20	MK_REP	201	6000
20			19000
50	ST_CLERK	124	9100
50	ST_MAN	100	5800
50			14900
60	IT_PROG	102	9000
60	IT_PROG	103	10200
60			19200
80	SA_MAN	100	10500
80	SA_REP	149	19600
80	ST_CLERK	124	2600
DEPARTMENT_ID	JOB_ID	MANAGER_ID	SUM(SALARY)
80			32700
90	AD_PRES		24000
90	AD_VP	100	34000
90			58000
110	AC_ACCOUNT	205	8300
110	AC_MGR	101	12000
110			20300
	SA_REP	149	7000
			7000
			175500

 

这里其实是可以分解一下

group by grouping sets(a,(b,c))=group by a union all

                                group by b,c

 

分组查询基本上就讲的差不多了。好运！