Oracle分析函数用法

Oracle 作者：bfc99 时间：2014-03-18 13:47:06 0 删除 编辑

Sql代码

• create table earnings -- 打工赚钱表
• (
•   earnmonth varchar2(6), -- 打工月份
•   area varchar2(20), -- 打工地区
•   sno varchar2(10), -- 打工者编号
•   sname varchar2(20), -- 打工者姓名
•   times int, -- 本月打工次数
•   singleincome number(10,2), -- 每次赚多少钱
•   personincome number(10,2) -- 当月总收入
• )

Sql代码

• insert into earnings values('200912','北平','511601','大魁',11,30,11*30);
• insert into earnings values('200912','北平','511602','大凯',8,25,8*25);
• insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25);
• insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);
• insert into earnings values('200912','北平','511605','贱敬',30,11,30*11);
•
• insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);
• insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);
• insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);
• insert into earnings values('200912','金陵','511304','小俐',0,18,0);
• insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88);
•
• insert into earnings values('201001','北平','511601','大魁',0,30,0);
• insert into earnings values('201001','北平','511602','大凯',14,25,14*25);
• insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25);
• insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);
• insert into earnings values('201001','北平','511605','贱敬',21,11,21*11);
•
• insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);
• insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);
• insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);
• insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);
• insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88);

Sql代码

• select * from earnings;

(1)sum函数，统计总合

Sql代码

• select earnmonth, area, sum(personincome)
• from earnings
• group by earnmonth,area;

(2)rollup函数

Sql代码

• select earnmonth, area, sum(personincome)
• from earnings
• group by rollup(earnmonth,area);

(3)cube函数

Sql代码

• select earnmonth, area, sum(personincome)
• from earnings
• group by cube(earnmonth,area)
• order by earnmonth,area nulls last;

group by 是分组函数，按照earnmonth和area先后次序分组。

group by 后面什么也不接就是直接分组。
group by 后面接 rollup 是在纯粹的 group by 分组上再加上对earnmonth的汇总统计。
group by 后面接 cube 是对earnmonth汇总统计基础上对area再统计。

rollup和cube区别：

(A、B、C)
(A、B)
(A)

(A、B、C)
(A、B)
(A、C)
(A)，
(B、C)
(B)
(C)，

(4)grouping函数

grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0

Sql代码

• select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,
•        decode(grouping(area),1,'全部地区',area) 地区, sum(personincome) 总金额
• from earnings
• group by cube(earnmonth,area)
• order by earnmonth,area nulls last;

(5)rank() over开窗函数

Sql代码

• select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
•        rank() over (partition by earnmonth,area order by personincome desc) 排名
• from earnings;

(6)dense_rank() over开窗函数

Sql代码

• select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
•        dense_rank() over (partition by earnmonth,area order by personincome desc) 排名
• from earnings;

(7)row_number() over开窗函数

Sql代码

• select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
•        row_number() over (partition by earnmonth,area order by personincome desc) 排名
• from earnings;

row_number最牛，即使两个数据相同，排名也不一样。

(8)sum累计求和

Sql代码

• select earnmonth 月份,area 地区,sname 打工者,
•        sum(personincome) over (partition by earnmonth,area order by personincome) 总收入
• from earnings;

(9)max，min，avg和sum函数综合运用

Sql代码

• select distinct earnmonth 月份, area 地区,
•        max(personincome) over(partition by earnmonth,area) 最高值,
•        min(personincome) over(partition by earnmonth,area) 最低值,
•        avg(personincome) over(partition by earnmonth,area) 平均值,
•        sum(personincome) over(partition by earnmonth,area) 总额
• from earnings;

Sql代码

• select earnmonth 本月,sname 打工者,
•        lag(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 上月,
•        lead(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 下月
• from earnings;

lag(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
lead(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；

value_expression：可以是一个字段或一个内建函数。
offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，
default就是用于处理这样的信息，默认为空。

• 博文量
79
• 访问量
576948