电影sql问题

 

看的次数多 或者 评分高

每个用户最喜欢哪个类型的电影

row_number()

ratings表中一个电影出现几次？

一个电影有几种类型？

类型在movies   评分或者观看次数ratings

movies join ratings

//将类型炸开

select mid,mname,lx from movies lateral view explode(split(type,"\\|")) types as lx;//这是炸开的表

//观看的次数  类型  id

select count(*) cnt,r.uid,t.lx

(select mid,mname,lx from movies lateral view explode(split(type,"\\|")) types as lx ) t,ratings r

where r.mid=t.mid  

group by r.uid,t.lx

//开窗函数

select t2.uid,t2.lx

from

(select row_number() over (partition by t1.uid order by t1.cnt desc) rn,t1.uid,t1.lx

from

(select count(*) cnt,r.uid,t.lx

(select mid,mname,lx from movies lateral view explode(split(type,"\\|")) types as lx ) t,ratings r

where r.mid=t.mid  

group by r.uid,t.lx) t1)t2

where t2.rn=1;

//uid lx  这个用户最喜欢的类型

======================================================================================

lx m1 m2 m3 

//电影表

select mid,mname,lx from movies lateral view explode(split(type,"\\|")) types as lx

 

 

select count(*) cnt,t.lx,r.mid

(select mid,mname,lx from movies lateral view explode(split(type,"\\|")) types as lx) t,ratings r

where r.mid=t.mid

group t.lx=r.mid; 

 

 

select row_number() overs (partition by t1.lx order by t1.cnt desc),t1.lx,t1.mid

(select count(*) cnt,t.lx,r.mid

(select mid,mname,lx from movies lateral view explode(split(type,"\\|")) types as lx) t,ratings r

where r.mid=t.mid

group t.lx=r.mid) t1

 

//

select t2.lx,t2.mid

from

(select row_number() overs (partition by t1.lx order by t1.cnt desc) rn,t1.lx,t1.mid

(select count(*) cnt,t.lx,r.mid

(select mid,mname,lx from movies lateral view explode(split(type,"\\|")) types as lx) t,ratings r

where r.mid=t.mid

group t.lx=r.mid) t1) t2

 

 

每个类型最受欢迎的前三个电影

类型==类型