# 数据仓库ＳＱＬ分析之一

## 一、基础数据准备

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;

结果如下：

group by

group by

group by

group by

rollup
cube区别：

(A
BC)
(A
B)
(A)

(A
BC)
(A
B)
(A
C)
(A)

(B
C)
(B)
(C)

(4)grouping函数

grouping

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;

查看结果如下：

• 博文量
126
• 访问量
985475