数仓级联求和

今天给大家分享下数仓的小案例------级联求和
有如下访客访问次数统计表 t_access_times
访客 月份 访问次数
A 2015-01 5
A 2015-01 15
B 2015-01 5
A 2015-01 8
B 2015-01 25
A 2015-01 5
A 2015-02 4
A 2015-02 6
B 2015-02 10
B 2015-02 5
…… …… ……
需要输出报表：t_access_times_accumulate
访客 月份 月访问总计 累计访问总计
A 2015-01 33 33
A 2015-02 10 43
……. ……. ……. …….
B 2015-01 30 30
B 2015-02 15 45
……. ……. ……. …….
1、建表语句：
create table if not exists t_access_times(
user string,
dt string,
count int
)
row format delimited
fields terminated by ‘\t’
;
create table if not exists t_access_times_accumulate(
user string,
dt string,
m_count,
a_count
)
row format delimited
fields terminated by ‘\t’
;
2、导入数据：
load data local inpath ‘/root/jilian/t_access_times.txt’ into table t_access_times;
load data local inpath ‘/root/jilian/t_access_times_accumulate.txt’ into table t_access_times;
3、查询
（1）
select user,dt,sum(count)
from t_access_times
group by user,dt；
（2）
select A.* ,B.* from
(select user,dt,sum(count) as salary
from t_access_times group by dt,user) A
inner join
(select user,dt,sum(count) as salary
from t_access_times group by dt,user) B
on A.user=B.user
;
（3）整合起来：
select A.user,A.dt,max(A.salary),sum(B.salary)
from (select user,dt,sum(count) as salary from t_access_times group by dt,user) A
inner join
(select user,dt,sum(count) as salary from t_access_times group by dt,user) B
on A.user=B.user
where B.dt<=A.dt
group by A.user,A.month
order by A.user,A.month
得到最终结果。