Hive面试题一

现有这么一批数据，现要求出：

每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数

三个字段的意思：用户名，月份，访问次数
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,11

最后结果展示:

用户	    月份	最大访问次数	    总访问次数	   当月访问次数
 A	   2015-01         33		        33	        33
 A	   2015-02	   33			43		10
 A	   2015-03	   38			81		38
 B	   2015-01	   30			30		30
 B	   2015-02	   30			45        	15
 B	   2015-03	   44			89		44

分析：
由给出的结果可以知道总访问次数和最大访问次数都依赖于当月访问次数，所以先求出当月访问次数：

由给出的数据可以看出用户在同一个月份有多条记录，首先对每个用户进行分组求和，算出每个用户在某个月所有的访问次数。

create table exercise1_step1  as  select name, month, sum(visit) as visit from exercise1 group by name, month;      
select * from exercise1_step1;

A       2015-01     33
A       2015-02     10
A       2015-03     38
B       2015-01     30
B       2015-02     15
B       2015-03     44

接下来我们考虑统计总访问次数，不难看出，我们要求的是截止到当月（包含当月）的总访问次数就是我们要求的。

那么我们是不是可以看成下面这种样式：

name	 montha	 visita	 monthb	 visitb
 A       2015-01   33	2015-01	  33

 A       2015-01   33	2015-02	  10
 A       2015-02   10	2015-02	  10

 A       2015-01   33	2015-03	  38
 A       2015-02   10	2015-03	  38
 A       2015-03   38	2015-03	  38

这样我们根据name和visitb进行分组，求visita的sum和就可以了。所以使用第一步产生的中间表进行自连接操作，并作为第二步的结果表：

create table exercise1_step2 as 
select  a.name as namea, a.month as montha, a.visit as visita,
b.name as nameb, b.month as monthb, b.visit as visitb 
from exercise1_step1 a join exercise1_step1 b on a.name = b.name;

exercise1_step2:
A       2015-01 33      A       2015-01 33
A       2015-02 10      A       2015-01 33
A       2015-03 38      A       2015-01 33

A       2015-01 33      A       2015-02 10
A       2015-02 10      A       2015-02 10
A       2015-03 38      A       2015-02 10

A       2015-01 33      A       2015-03 38
A       2015-02 10      A       2015-03 38
A       2015-03 38      A       2015-03 38

添加这个条件：monthb >= montha是不是就得到了上面所需要的结果：
最后一步添加这个调价统计总访问次数和最大访问次数：

create table exercise1_step3 as 
select nameb, monthb, visitb, max(visita) as max_visit, sum(visita) as sum_visit 
from exercise1_step2 
where montha <= monthb 
group by nameb, monthb, visitb ;

最后查询第三步的结果表就可以了。

select * from exercise1_step3;

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 44      44      89

关键是使用自连接。