Chinaunix首页 | 论坛 | 博客
  • 博客访问: 215794
  • 博文数量: 13
  • 博客积分: 2193
  • 博客等级: 大尉
  • 技术积分: 295
  • 用 户 组: 普通用户
  • 注册时间: 2008-01-26 11:51
文章分类

全部博文(13)

文章存档

2011年(7)

2010年(2)

2008年(4)

我的朋友

分类: Oracle

2010-07-29 18:14:31

这次讲一下分组查询的高级运用,当然了,初级运用我好像还没有写,以后用空了补上。
先来复习一下分组查询的几个常见函数(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
 
分组查询基本上就讲的差不多了。好运!
阅读(1839) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~