分组查询进阶-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   分组查询基本上就讲的差不多了。好运！