【题目】
某短视频公司数据库有三张表，用户视频信息明细表、主播开播明细表、直播间用户信息明细表。
用户视频信息明细表包含用户id，发布视频id，视频点赞数，视频发布日期，视频类别和用户的粉丝数。
主播开播明细表包含主播id，主播开直播房间的id号，和开播的时间。
直播间用户信息明细表包含进入直播间观看的观众id，进入的直播间id和观众进入的时间。
业务需求：
1.找出每个用户点赞数最高的视频，点赞数相同时按照视频id最大的记录。
2.找出粉丝数在6月2号提升最多的20个用户id (对比6月1号)。
3.找出开播三分钟内无人进入的直播房间号。
【解题思路】
1.找出每个用户点赞数最高的视频，点赞数相同时按照视频id最大的记录。
我们先来把这个业务需求翻译成大白话：
1）查询结果需要的字段是用户id、视频id、点赞数
2）按照用户id分组再根据每个用户视频的点赞数排序，若点赞数相同时按照视频id排序
3）选择每个用户点赞数最高的视频
要求每个用户上传的每一条视频都要显示出来，我们知道 group by分组汇总后改变了表的行数，一行只有一个类别。而使用窗口函数的话不会减少原表中的行数。
按用户id分组(partiotion by 用户id)、并按最点赞数、视频id降序排列(order by点赞数,视频id )，降序排列desc用套入窗口函数的语法，得出下面的SQL语句：
代码语言：javascript复制select
用户id ,视频id ,点赞数 ,
row_number()over(partition by 用户id order by 点赞数 desc, 视频id desc) as 排名
from 用户视频信息明细表;查询结果：
按照每个用户的视频点赞数排名后，我们筛选排名第一，即点赞数最多的视频 。SQL写法如下：
代码语言：javascript复制select 用户id ,视频id ,点赞数
from
(select 用户id ,视频id ,点赞数 ,row_number()over(partition by 用户id
order by 点赞数 desc, 视频id desc) as 排名
from 用户视频信息明细表 )t
where 排名=1;查询结果：
2.找出粉丝数在6月2号提升最多的3个用户id (对比6月1号)。
我们先来把这个业务需求拆分：
1）得出每个用户在6月2号的涨粉数
2）找出前3个粉丝提升最多的用户id
1）得出每个用户在6月2号的涨粉数
观察一下用户视频信息明细表这张表，要找出在6月2号粉丝提升最快的用户，就要知道6月1号用户的粉丝数是多少，把6月2号的粉丝数减去6月1号的粉丝数即可得出每个用户的涨粉数。
我们将发布时间限定在6月1号-2号之间，使用if函数和sum函数来计算涨粉数，如果发布日期是6月2号，显示字段”用户累计粉丝数”,如果不是就显示”（负）-用户累计粉丝数”，最后求和得出涨粉数。
SQL写法如下：
代码语言：javascript复制select 用户id ,sum(if (发布日期 ="2022/6/2",用户累计粉丝数,-用户累计粉丝数)) as "涨粉数"
from 用户视频信息明细表
where 发布日期 in ("2022/6/2","2022/6/1")
group by 用户id;查询结果：
2）找出前3个粉丝提升最多的用户id
得出每个用户在6月2号的涨粉数作为临时表t，用order by对用户的涨粉数进行降序排序（desc）后，用limit 3 获取前三个粉丝提升最多的用户id。
SQL写法如下：
代码语言：javascript复制select 用户id,涨粉数
from
(select 用户id ,sum(if (发布日期 ="2022/6/2",用户累计粉丝数,-用户累计粉丝数)) as "涨粉数"
from 用户视频信息明细表
where 发布日期 in ("2022/6/2","2022/6/1")
group by 用户id )t
order by 涨粉数 desc
limit 3;查询结果：
3.找出开播三分钟内无人进入的直播房间号。
观察主播开播明细表和直播间用户信息明细表，我们可以得知每位主播的开播时间和观众什么时间进入哪个直播间。用直播间id将两表联结来得出直播间进入的用户信息。
SQL写法如下：
代码语言：javascript复制select a.主播id, a.直播间id,观众id,a.开播时间,b.进入时间
from 主播开播明细表 a
left join 直播间用户信息明细表 b
on a.直播间id =b.进入的直播间id;查询结果：
从查询结果可以很明显看到R004这个直播间是没有观众进入的，我们可以用观众id是否为空来判断直播间有无观众的情况 （观众id is null），R005这个直播间观众是三分钟后再进入直播间的。
业务需求我们找出主播开播后三分钟内无观众进入的直播id，用date_add函数来计算开播时间和观众的进入时间来算时间差。date_add函数用法如下：
SQL写法如下：
代码语言：javascript复制b.进入时间 > date_add(a.开播时间,interval 3 minute)代入整个SQL中
代码语言：javascript复制select a.主播id, a.直播间id,观众id,a.开播时间,b.进入时间
from 主播开播明细表 a
left join 直播间用户信息明细表 b
on a.直播间id =b.进入的直播间id
and b.进入时间 > date_add(a.开播时间,interval 3 minute)
where b.观众id is null;查询结果：
【本题考点】
1.熟悉窗口函数的用法，大部分应用于到每个用户下每种类别的排序类似的业务需求。
2.遇到复杂的业务需求，试着将用多维度拆解分析方法拆解成为多个简单的问题。
3.运用到多表信息，首先先想到多表联结，再根据具体业务场景得出联结类型。
