1.数据格式
a,01,150
a,01,200
b,01,1000
b,01,800
c,01,250
c,01,220
b,01,6000
a,02,2000
a,02,3000
b,02,1000
b,02,1500
c,02,350
c,02,280
a,03,350
a,03,250
2.要求
求月销售额和总销售额
字段含义
店铺,月份,金额
3.语句
1.create table t_store(name string,months int,money int) row format delimited fields terminated by ",";
2.load data local inpath "/home/hadoop/store.txt" into table t_store;
3.create table tmp_store1 as select name,months,sum(money) as money from t_store group by name,months;
4.create table tmp_store2 as select a.name aname,a.months amonths,a.money amoney,b.name bname,b.months bmonths,b.money bmoney from tmp_store1 a join tmp_store1 b on a.name=b.name order by aname,amonths;
5.select aname,amonths,amoney,sum(bmoney) as total from tmp_store2 where amonths >= bmonths group by aname,amonths,amoney;