一、数据表结构
视频表 t_video
字段 | 注释 | 描述 |
---|---|---|
videoId | 视频唯一 id(String) | 11 位字符串 |
uploader | 视频上传者(String) | 上传视频的用户名 String |
age | 视频年龄(int) | 视频在平台上的整数天 |
category | 视频类别(Array<String> ) | 上传视频指定的视频分类 |
length | 视频长度(Int) | 整形数字标识的视频长度 |
views | 观看次数(Int) | 视频被浏览的次数 |
rate | 视频评分(Double) | 满分 5 分 |
Ratings | 流量(Int) | 视频的流量,整型数字 |
conments | 评论数(Int) | 一个视频的整数评论数 |
relatedId | 相关视频 id(Array<String> ) | 相关视频的 id,最多 20 个 |
用户表 t_user
字段 | 注释 | 类型 |
---|---|---|
uploader | 上传者用户名 | string |
videos | 上传视频数 | int |
friends | 朋友数量 | int |
二、具体实现
-- 统计上传视频最多的用户 Top10 以及他们上传的视频观看次数在前 20 的视频
/**
思路:
1.处理结果集 1:在用户表中按照 uploader 分组聚合统计每个用户的总上传视频数,再使用 order by 降序和 limit 获取前 10 的用户
2.处理结果集 2:在视频表中按照 videoid 和 uploader 分组聚合统计观看次数
3.使用结果集 1 左连接结果集 2,关联字段为 uploader,然后使用排序窗口函数按左表的 uploader 分组,右表的观看次数降序进行组内编号
4.使用 where 子句选取前 20 的数据
*/
SELECT
uploader,
videoid,
num,
rn
FROM
(
SELECT
a.uploader,
b.videoid,
b.num,
DENSE_RANK() over(PARTITION by a.uploader ORDER by b.num DESC) as rn
FROM
(
SELECT
uploader,
SUM(videos) as videos
FROM t_user
group by uploader
order by videos DESC
limit 10
) a
left join
(
SELECT
videoid,
uploader,
SUM(`views`) as num
FROM t_video
group by videoid, uploader
) b
on a.uploader=b.uploader
) t
WHERE rn <= 20;
标签:视频,20,videoid,num,uploader,上传
From: https://blog.csdn.net/weixin_44480009/article/details/140084530