数据仓库ＳＱＬ分析之一

一、基础数据准备

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) -- 当月总收入

)

然后插入实验数据：

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','大凯',10,25,10*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','小凡',14,16.67,14*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);

 

insert into earnings values('201002','北平','511601','大魁',0,30,0);

insert into earnings values('201002','北平','511602','大凯',14,25,14*25);

insert into earnings values('201002','北平','511603','小东',19,6.25,19*6.25);

insert into earnings values('201002','北平','511604','大亮',9,8.25,9*8.25);

insert into earnings values('201002','北平','511605','贱敬',21,11,21*11);

 

insert into earnings values('201002','金陵','511301','小玉',6,12.25,6*12.25);

insert into earnings values('201002','金陵','511302','小凡',17,16.67,17*16.67);

insert into earnings values('201002','金陵','511303','小妮',27,33.33,27*33.33);

insert into earnings values('201002','金陵','511304','小俐',16,18,16*18);

insert into earnings values('201002','金陵','511305','雪儿',19,9.88,19*9.88);

 

insert into earnings values('201003','北平','511601','大魁',0,30,0);

insert into earnings values('201003','北平','511602','大凯',14,25,14*25);

insert into earnings values('201003','北平','511603','小东',19,6.25,19*6.25);

insert into earnings values('201003','北平','511604','大亮',22,8.25,22*8.25);

insert into earnings values('201003','北平','511605','贱敬',21,11,21*11);

 

insert into earnings values('201003','金陵','511301','小玉',6,12.25,6*12.25);

insert into earnings values('201003','金陵','511302','小凡',17,16.67,17*16.67);

insert into earnings values('201003','金陵','511303','小妮',27,33.33,27*33.33);

insert into earnings values('201003','金陵','511304','小俐',16,18,16*18);

insert into earnings values('201003','金陵','511305','雪儿',11,9.88,11*9.88);

 

insert into earnings values('201004','北平','511601','大魁',0,30,0);

insert into earnings values('201004','北平','511602','大凯',14,25,14*25);

insert into earnings values('201004','北平','511603','小东',19,6.25,19*6.25);

insert into earnings values('201004','北平','511604','大亮',7,8.25,7*8.25);

insert into earnings values('201004','北平','511605','贱敬',21,11,21*11);

 

insert into earnings values('201004','金陵','511301','小玉',6,12.25,6*12.25);

insert into earnings values('201004','金陵','511302','小凡',17,16.67,17*16.67);

insert into earnings values('201004','金陵','511303','小妮',23,33.33,23*33.33);

insert into earnings values('201004','金陵','511304','小俐',16,18,16*18);

insert into earnings values('201004','金陵','511305','雪儿',12,9.88,12*9.88);

 

然后看看刚刚建好的库：

select * from earnings;  

(1)sum函数，统计总合
按照月份，统计每個地区的总收入

select earnmonth, area, sum(personincome)

from earnings

group by earnmonth,area

order 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;  

 结果如下：

小结：sum是统计求和的函数。
group by 是分组函数，按照earnmonth和area先后次序分组。
以上三例都是先按照earnmonth分组，在earnmonth内部再按area分组，并在area组内统计personincome总合。
group by 后面什么也不接就是直接分组。
group by 后面接 rollup 是在纯粹的 group by 分组上再加上对earnmonth的汇总统计。
group by 后面接 cube 是对earnmonth汇总统计基础上对area再统计。
另外那个 nulls last 是把空值放在最后。

rollup和cube区别：
如果是ROLLUP(A, B, C)的话，GROUP BY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序
(A、B、C)
(A、B)
(A、C)
(A)，
(B、C)
(B)
(C)，
最后对全表进行GROUP BY操作。

 

(4)grouping函数
在以上例子中，是用rollup和cube函数都会对结果集产生null，这时候可用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;  

 查看结果如下：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-1972982/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-1972982/