Hive实战之求月销售额和累计销售额

---

数据（基表：store）：

店铺，月份，销售额
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

需求：求出每个店铺的当月销售额和累计到当月的总销售额。

实现需求步骤：

　　1，做出店铺，月份，营业额的表。

create table store_sum as select name as name, mon as mon ,sum(money) as money
from store group by name,mon;

　　2，做出内链接的视图。

create view store_view 
as select a.name as aname, a.mon as amon, a.sum as asum,
b.name as bname, b.mon as bmon, b.sum as bsum
from store_sum a join store_sum b on a.name=b.name 
group by a.name,a.mon;

　　3，通过视图来进行月份的比较，来对销售额进行积累。

select aname,amon,amoney,sum(bmoney) as summoney 
from store_view where amon<=bmon  group by aname,amon,amoney;

 

---

 

转载于:https://www.cnblogs.com/songweideboke/p/9850646.html