ITPub博客

首页 > 大数据 > 数据挖掘 > 数据仓库SQL分析之一

数据仓库SQL分析之一

原创 数据挖掘 作者:bestpaydata 时间:2016-01-07 09:28:14 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) -- 当月总收入

)

然后插入实验数据:

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
是分组函数,按照earnmontharea先后次序分组。
以上三例都是先按照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
BC)
(A
B)
(A)
最后对全表进行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)GROUP BY顺序
(A
BC)
(A
B)
(A
C)
(A)

(B
C)
(B)
(C)

最后对全表进行GROUP BY操作。

 

(4)grouping函数
在以上例子中,是用rollupcube函数都会对结果集产生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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-19

  • 博文量
    126
  • 访问量
    985475