对比mysql和hive：分组，取不同组前几条记录-explode函数的使用

id,name,age,favors(爱好)
1,huangxiaoming,45,a-c-d-f
2,huangzitao,36,b-c-d-e
3,huanglei,41,c-d-e
4,liushishi,22,a-d-e
5,liudehua,39,e-f-d
6,liuyifei,35,a-d-e

create table mian6(
    id int,
    name string,
    age int,
    favors string
)row format delimited fields terminated by ",";

load data local inpath "/home/hadoop/apps/mian6.txt" into table mian6;

create table mian6_1 as
select 
	m.id id,
	m.name name,
	m.age age,
	m.favors favors,
	tf.hobby hobby
from 
	mian6 m
lateral view explode(split(m.favors,"-")) tf as hobby;

select 
	m.hobby,
	m.name name,
	m.age age,
	m.favors favors,
	row_number() over(partition by hobby order by age desc) index
from 
	mian6_1 m;

select 
	a.hobby,
	a.name,
	a.age,
	a.favors
from 
	(select m.name name,m.hobby hobby,m.age age,m.favors favors,
row_number() over(partition by hobby order by age desc) index
from mian6_1 m)a
where a.index<=2;