hive高频面试题1.5关联查询

需求：
有如下访客访问次数统计表 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
A 2015-03 16
A 2015-03 22
B 2015-03 23
B 2015-03 10
B 2015-03 1

需要输出报表：t_access_times_accumulate
访客 月份 月访问总计 月最大访问次数 累计访问总计
A 2015-01 33 33 33
A 2015-02 10 33 43
A 2015-03 38 38 81
B 2015-01 30 30 30
B 2015-02 15 30 45
B 2015-03 34 34 79

1.求出每个游客每月的月季总访问量

select
orders,dates,sum(s) sum_1
from 
t_access_times
group by orders,dates

2.进行自连接

select
t1.orders,
t1.dates,
sum_1,
sum_2
from
(select
orders,dates,sum(s) sum_1
from 
t_access_times
group by orders,dates) t1
join 
(select
orders,dates,sum(s) sum_2
from 
t_access_times
group by orders,dates) t2
on t1.orders=t2.orders
where t2.dates<=t1.dates

 

 3.观察可得求max（sum_1）,sum(sum_2)就可以得到月访问总计，累计访问总计，月最大访问次数可以通过max(sum_1),max(sum_2)中取一个得到

select
t1.orders,
t1.dates,
max(sum_1) sum_1,
max(t2.sum_2)sum_2,
sum(sum_2) sum_3
from
(select
orders,dates,sum(s) sum_1
from 
t_access_times
group by orders,dates) t1
join 
(select
orders,dates,sum(s) sum_2
from 
t_access_times
group by orders,dates) t2
on t1.orders=t2.orders
where t2.dates<=t1.dates
group by t1.orders,t1.dates