11.4.7 统计上传视频最多的用户 Top10 以及他们上传的观看次数在前 20 的视频

1. 找到上传视频最多的 top10 用户, 直接在表gulivideo_user_orc中查找即可. 结果记为 t1
select 
    *
from gulivideo_user_orc
order by videos desc
limit 10;    // t1

2. 找到每个用户上传的视频id和观看次数. t1和gulivideo_orc连接

select
   t1.uploader uploader, 
   gulivideo_orc.videoId videoId,
   gulivideo_orc.views views
from t1 join gulivideo_orc
on t1.uploader = gulivideo_orc.uploader ;   // t2

3. 对每个用户的视频的观看次数降序排列   // 记为 t3
select
    *,
    rank() over(partition by uploader order by views desc) rank
from t2;


4. 每个用户的只取前 20 的视频
select
    *
from t3
where rank <= 20

5. 合并:
select
    *
from(
    select
        *,
        rank() over(partition by uploader order by views desc) rank
    from (
        select
           t1.uploader uploader, 
           gulivideo_orc.videoId videoId,
           gulivideo_orc.views views
        from (
            select 
                *
            from gulivideo_user_orc
            order by videos desc
            limit 10
        )t1 join gulivideo_orc
        on t1.uploader = gulivideo_orc.uploader        
    )t2
) t3
where rank <= 20;