oracle分组排序汇总笔记

一、相关函数：

Group by、Rollup、Cube、Grouping sets、Over、Grouping_id、Grouping、Decode、lag、lead、rank、dense_rank、row_number、count

二、初始化数据：

-- Create table

create table EMPLOYEE

(

EID NUMBER not null,

ENAME V ARCHAR2(20) not null,

EADDRESS V ARCHAR2(200) not null,

E_DID NUMBER not null,

HIRE_DA TE DA TE not null,

SALARY NUMBER(8,2) not null,

BONUS NUMBER(8,2),

BOSS NUMBER

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64

minextents 1

maxextents unlimited

);

-- Create/Recreate primary, unique and foreign key constraints

alter table EMPLOYEE

add primary key (EID)

using index;

--insert content

insert into EMPLOYEE (EID, ENAME, EADDRESS, E_DID, HIRE_DATE, SALARY, BONUS, BOSS)

values (1, '郭芙', '广东', 1, to_date('02-01-2006', 'dd-mm-yyyy'), 2000.5, 100.5, 10);

insert into EMPLOYEE (EID, ENAME, EADDRESS, E_DID, HIRE_DATE, SALARY, BONUS, BOSS)

values (3, '杨康', '成都', 3, to_date('14-07-2004', 'dd-mm-yyyy'), 3000, null, 10);

insert into EMPLOYEE (EID, ENAME, EADDRESS, E_DID, HIRE_DATE, SALARY, BONUS, BOSS)

values (9, '杨过', '广东', 1, to_date('02-03-2005', 'dd-mm-yyyy'), 2000, 1000, 10);

insert into EMPLOYEE (EID, ENAME, EADDRESS, E_DID, HIRE_DATE, SALARY, BONUS, BOSS)

values (10, '小龙女', '广东', 2, to_date('05-04-2000', 'dd-mm-yyyy'), 8000, null, 10);

insert into EMPLOYEE (EID, ENAME, EADDRESS, E_DID, HIRE_DATE, SALARY, BONUS, BOSS)

values (11, '郭襄', '广东', 3, to_date('01-12-2010', 'dd-mm-yyyy'), 5000, null, 10);

insert into EMPLOYEE (EID, ENAME, EADDRESS, E_DID, HIRE_DATE, SALARY, BONUS, BOSS)

values (14, '郭靖', '成都', 2, to_date('08-07-2008', 'dd-mm-yyyy'), 4300, null, 14);

insert into EMPLOYEE (EID, ENAME, EADDRESS, E_DID, HIRE_DATE, SALARY, BONUS, BOSS)

values (15, '黄蓉', '成都', 3, to_date('13-05-2009', 'dd-mm-yyyy'), 1600, 200, 14);

commit;

三、具体应用：

1、group by与rollup:

select eaddress a ,ename b,sum(salary) c from employee group by rollup(eaddress, ename)

图1

分析：如图，这里不光只对第一个字段做了累计，先按(eaddress,ename)分组累计,再按(eaddress)分组累计，最后累计全部

类似于：

select * from(

select eaddress,ename,sum(salary) a from employee group by eaddress,ename

union all

select eaddress,null,sum(salary) from employee group by eaddress

union all

select null,null,sum(salary) from employee

)

2、group by 与cube;

select eaddress a ,ename b,sum(salary) c from employee group by cube(eaddress, ename) order by a,b

图2

分析：CUBE这里的使用与ROLLUP基本相同，但CUBE的合计更加详细，它能够显示次分组字段的合计信息

类似于：

select eaddress a,ename b,sum(salary) c from employee group by grouping sets((eaddress,ename),(eaddress),(ename),()) order by a,b

3、group by 与grouping sets

select eaddress a ,ename b,sum(salary) c from employee group by grouping sets((eaddress, ename),())

图3-1

select eaddress a ,ename b,sum(salary) c from employee group by grouping sets((eaddress, ename),(eaddress),())

图3-2

分析：

Group by grouping sets可以应用来指定自己感兴趣的总数组合。因为它不必计算它不需要集合。形式为：group by grouping sets((list),(list)…)，这里list是圆括号中的一个列序列，这个组合生成一个总数，所以要增加一个总和，必须增加一个(null)分组集。

4、grouping结合decode的使用

GROUPING（）可接受一个列值，当列值为空时，函数返回1；如果列值非空，则返回0。（注意：GROUPING只能在ROLLUP、CUBE查询中使用。还可以结合DECODE （）函数来注释使用更好）

Decode()：相当于一条if语句，如：decode(A,1,’su’,2,’xiao’,3,’dong’,A)表示A如果为1则返回su,如果2就返回xiao,如果3就返回dong,否则返回A

以图1为例：

Select decode(grouping(eaddress),1,’合计’,eaddress) a,ename b,sum(salary) c from employee group by rollup(eaddress,ename)

图4

5、Grouping_id()

GROUPING_ID（）函数可接受一列或多列，它返回GROUPING位向量的十进制值。GROUPING 位向量的计算方法是按照顺序对每一列调用GROUPING（）函数的结果组合起来。它的作用是借助HAVING子句对记录进行过滤，将不包含小计或总计的记录除去。

GROUPING位向量的十进制值，我们由前面的介绍已知道当GROUPING（）的列值为空时它返回1，当非空时返回0；

比如：eaddress,ename两列都为非空，GROUPING（）都返回0。将这两列的值组合起来，形成一个位向量00，十进制为0。即，当eaddress,ename都非空时，GROUPING_ID（）返回0。（这里要特别注意eaddress与ename两列的顺序）下面以图2为例子：

select eaddress a ,ename b,sum(salary) c,grouping_id(eaddress,ename) g1g2,grouping_id(ename,eaddress) g2g1,grouping_id(eaddress) g1,grouping_id(ename) g2 from employee group by cube(eaddress, ename) order by a,b

图5

利用grouping_id有时可以作为判断根据

6、over

a.分析函数over

分析函数用于计算基于组的某种聚合值，它和聚合函数不同之处是对于每个组返回

多行，而聚合函数对于每个组返回一行

1、rank():排名

select eaddress,ename,salary,rank() over(partition by eaddress order by salary

desc) a from employee

图6-1

Rank是跳跃排名，如果有2个第一就直接跳过第二，下一位排第三

Dense_rank则不会

2、sum():合计

select eaddress,ename,salary,sum(salary) over(partition by eaddress order by salary desc) a from employee

图6-2

b.开窗函数over

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。如：

按照salary排序进行累计，order by是个默认的开窗函数select eaddress,ename,salary,sum(salary) over( order by salary desc) a from employee

图6-3

按照地址分区

select eaddress,ename,salary,sum(salary) over( partition by eaddress) a from employee

图6-4

按照窗口值幅度

select eaddress,ename,salary,sum(salary) over( order by salary range between300 preceding and1000 following) a from employee

图6-5

以杨过为例：2000-300<=S<=2000+1000,即求在S范围内的salary的总和，所以A=2000+2000.5+3000

按照窗口行幅度

select eaddress,ename,salary,sum(salary) over( order by salary rows between2 preceding and2 following) a from employee

图6-6

https://www.360docs.net/doc/e913366845.html,g、lead结合over

LAG()和LEAD()统计函数可以在一次查询中取出同一字段的前N行的数据和后N行值

select eaddress,ename,salary,lag(salary,1) over(order by eaddress) a,lead(salary,1) over(order by eaddress) b from employee

图6-7

c.row_number结合over

可以在各个分组内从1重新排序；但是必须和开窗函数一起使用，也就是说必须分组,若有表(employee)如下

select tt.* from(select t.*,row_number()over(partition by t.ename order by t.ename) r from employee t ) tt where tt.r=1;

就把姓名相同的一行去掉了

7、综合实例(生成一个基本报表)：

select decode(t1,null,eaddress,null) eaddress,ename,salary from(

select decode(grouping_id(e.eaddress,e.ename),1,e.eaddress,3,'合计',e.eaddress) eaddress,

decode(grouping_id(e.eaddress,e.ename),1,'小计',e.ename) ename,sum(e.salary) salary,

lag(sum(e.salary)) over(partition by eaddress order by eaddress) t1 from employee e

group by rollup(e.eaddress,e.ename)

)

8、附加函数

a.Count

ORACLE的，COUNT（*）是取所有的行数，COUNT（1）取第1个字段不为空

的记录条数也可以指定字段不为空的行数count(字段名)

b.…