hivesql(4)

有一个5000万的用户文件(user_id，name，age)，一个2亿记录的用户看电影的记录文件(user_id，url)，根据年龄段观看电影的次数进行排序？

先求出每个人看了几次电影,t1然后t1和user表join，拼接age字段 t2表
划分年龄段，0-20，20-40，40-60，60–按年龄段分组，按照次数排序

建表
create table forth_user(user_id string,name string,age int);
create table forth_log(user_id string,url string);

insert into table forth_user values(‘01’,‘UZI’,10);
insert into table forth_user values(‘02’,‘MING’,18);
insert into table forth_user values(‘03’,‘zZiTAI’,30);
insert into table forth_user values(‘04’,‘LETME’,50);

insert into table forth_log values(‘01’,‘RNG’);
insert into table forth_log values(‘01’,‘FPX’);
insert into table forth_log values(‘02’,‘RNG’);
insert into table forth_log values(‘03’,‘RNG’);
insert into table forth_log values(‘04’,‘RNG’);

数据量大的时候，尽量的先过滤，然后在合并，减少数据量

		select 
			t1.user_id,
			t1.age,
			t2.con 
		from forth_user t1 
		join 
		(
			select 
				user_id,
				count(*) con 
			from  forth_log 
			group by user_id
		) t2 
		on t1.user_id=t2.user_id

结果为：

t1.user_id	t1.age	t2.con
001	10	2
002	18	1
003	30	1
004	50	1

第二步：

select 
		t4.category,
		sum(t4.con) contimes 
	from 
	(
		select 
			t3.con con,
			case 
			when 0<=t3.age and t3.age<20 then 'a' 
			when 20<=t3.age and 40<t3.age then 'b' 
			when 40<t3.age and 60<=t3.age then 'c' 
			else 'd' 
			end as category 
		from 
		(
			select 
				t1.user_id,
				t1.age,
				t2.con 
			from forth_user t1 
			join 
			(
				select 
					user_id,
					count(*) con 
				from  forth_log 
				group by user_id
			) t2 
			on t1.user_id=t2.user_id
		) t3
	) t4 
	group by t4.category

结果为：

t4.category	contimes
a	3
b	1
d	1

最后结果：

select 
	t5.category,
	t5.contimes 
from 
(
	select 
		t4.category,
		sum(t4.con) contimes 
	from 
	(
		select 
			t3.con con,
			case 
			when 0<=t3.age and t3.age<20 then 'a' 
			when 20<=t3.age and 40<t3.age then 'b' 
			when 40<t3.age and 60<=t3.age then 'c' 
			else 'd' 
			end as category 
		from 
		(
			select 
				t1.user_id,
				t1.age,
				t2.con 
			from forth_user t1 
			join 
			(
				select 
					user_id,
					count(*) con 
				from  forth_log 
				group by user_id
			) t2 
			on t1.user_id=t2.user_id
		) t3
	) t4 
	group by t4.category
)t5 
order by t5.contimes desc;

结果为：

t4.category	contimes
a	3
b	1
d	1

---

总结一下

---

order by
　　Hive中的order by和数据库中的order by 功能一致，按照某一项或者几项排序输出，可以指定是升序或者是降序排序。它保证全局有序，但是进行order by的时候是将所有的数据全部发送到一个Reduce中，所以在大数据量的情况下可能不能接受，最后这个操作将会产生一个文件。

sort by
　　sort by只能保证在同一个reduce中的数据可以按指定字段排序。使用sort by 你可以指定执行的reduce个数 （set mapreduce.job.reduce=） 这样可以输出更多的数据。对输出的数据再执行归并排序，即可以得到全部结果。需要注意的是，N个Reduce处理的数据范围是可以重叠的，所以最后排序完的N个文件之间数据范围是有重叠的。

distribute by
　　按照指定的字段将数据划分到不同的输出reduce中，这使用在本博客的《Hive：解决Hive创建文件数过多的问题》有详细的介绍。这个可以保证每个Reduce处理的数据范围不重叠，每个分区内的数据是没有排序的。

cluster By
　　cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。 所以最终的结果是每个Reduce处理的数据范围不重叠，而且每个Reduce内的数据是排序的，而且可以打到全局有序的结果。