hql

select t2.videoid,t2.views,t2.tmp 
from(
      select t1.videoid,t1.views,t1.tmp,rank() over(partition by tmp order by t1.views desc)rc
          from (
                select videoid,views,tmp
                from gulivideo_orc lateral view explode(category) kk as tmp
               )t1
     )t2
       where t2.rc<=10; 

 select  t2.tmp,count(t2.videoid)
 from
 (select t1.videoid,t1.views,tmp
 from(
 select  videoid,views,category from gulivideo_orc  order by views desc limit 20 )t1
  lateral view  explode(t1.category)kk as tmp )t2  group by t2.tmp;

1、50
 (select videoid,relatedid from gulivideo_orc order by views limit 50)t1;
2、解析关联
  (select tmp from (select videoid,relatedid from gulivideo_orc order by views limit 50)t1 later view explode(relatedid) k1 as tmp) 
3、解析元组
 select t3.videoid,tmp2 from 
 (select videoid,category from 
 (select tmp
 from(select videoid,relatedid,views from 
 gulivideo_orc order by views desc limit 50) t1 
 lateral view explode(t1.relatedid)k1 as tmp)t2 
 join gulivideo_orc o on t2.tmp=o.videoid)t3 
 lateral view explode(category) k3 as tmp2;