hive面试之【自连接,行转列,列转行】

hive面试之【自连接,行转列,列转行】

1.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



根据答案总结出来的规律:
1、按照用户分组
2、按照月份排序
3、求max和sum(在以上两点的基础之上)



准备数据和表：

create database exercise;
use exercise;
drop table if exists visit;
create table visit(username string, month string, visit int) row format delimited fields terminated by ",";
load data local inpath "/home/hadoop/visit.txt" into table visit;
select * from visit;



思路：

1、从数据当中发现数据规律：
	每个用户在每月的访问记录有多条
	解决方法：先求每个用户在每个月份里面的总访问次数

// 第一个HQL语句：
create table visit_month as select username, month , sum(visit) visit from visit group by username, month order by username, month;

用户	月份			当月访问次数
A	2015-01			  33
A	2015-02			  10
A	2015-03			  38
B	2015-01			  30
B	2015-02	                  15
B	2015-03		          44


2、求出截止到每月时的单月最大访问次数 和总访问次数

A 2015-01 33   ====>   A 2015-01 33 33 33


A 2015-01 33
A 2015-02 10   =====>  A 2015-02 10 33 43

......



最终的数据结果：

A	2015-01	 （33）
A	2015-02	 （33，10）
A	2015-03	 （33，10, 43）
B