牛客网SQL大厂真题二刷小白白话总结（六）某牛客直播课分析（在线教育行业）

目录

SQL30 牛客直播转换率（简单）

SQL31 牛客直播开始时各直播间在线人数（中等）

SQL32 牛客直播各科目平均观看时长（中等）

SQL33 牛客直播各科目出勤率（较难）

SQL34 牛客直播各科目同时在线人数（较难）

---

SQL30 牛客直播转换率（简单）

select course_id,course_name,
round(sum(if_sign)/sum(if_vw)*100,2) as 'sign_rate(%)'
from course_tb
join behavior_tb using(course_id)
group by course_id,course_name

这道题比较简单，因为它直接对sign,view的人数做了（0，1）的赋值，所以加总起来就是总人数。

SQL31 牛客直播开始时各直播间在线人数（中等）

select course_id,course_name,
sum(if(time(in_datetime)<="19:00:00",1,0))
as online_num
from
course_tb join attend_tb using (course_id)
group by course_id,course_name
order by course_id

这道题主要难点在于时间的转化形式，因为给的登陆时间的格式和课程时间的格式不一样，因此使用time()将登陆时间的时刻剥离出来，再和“19:00:00”比较即可。

SQL32 牛客直播各科目平均观看时长（中等）

select course_name,
round(avg(vw_len),2) as avg_Len
from 
(select course_name,timestampdiff(second,in_datetime,out_datetime)/60 as vw_len
from 
course_tb join attend_tb using(course_id)
) t1
group by course_name
order by avg_Len desc

这道题就是要把登陆和退出时间的差值表现出来，还要转换成分钟。 

当然我是习惯用second，也可以直接用分钟

TIMESTAMPDIFF(MINUTE,in_datetime,out_datetime)

SQL33 牛客直播各科目出勤率（较难）

select t1.course_id,t1.course_name,
round(attend_u/sign_u*100,2) as "attend_rate(%)"
from
(select course_id,course_name,count(distinct user_id) as attend_u
from attend_tb left join course_tb using(course_id)
where timestampdiff(minute,in_datetime,out_datetime)>=10
group by course_id,course_name) as t1
join
(select course_id,sum(if_sign) as sign_u
from behavior_tb
group by course_id) as t2
on t1.course_id=t2.course_id
order by course_id

一开始我尝试把三张表连接在一起，但更简单的方法是1，3表先连在一起，形成的整体再和表2连在一起，感觉这一题难就难在怎么处理这三张表。

SQL34 牛客直播各科目同时在线人数（较难）

select course_id,course_name,max(uv_cnt) as max_num
from
(select course_id,course_name,sum(state) over(partition by course_id order by dt,state) as uv_cnt
from
(select course_id,in_datetime as dt,1 as state 
from attend_tb
 union all
 select  course_id,out_datetime as dt,-1 as state
 from attend_tb)t1 join course_tb using(course_id))t2
 group by course_id,course_name

这道题不看参考答案，给我一下子想不出来，但是看了答案之后还是比较好懂的。

主要思路就是按照时间顺序，把每个用户登陆和退出的时间分别赋值为1和-1，这样根据时间顺序进行累加的时候就可以得出每个时间点累积用户的数量，也就是当时同时在线的人数，再求最大值就是最大的同时在线人数。

落实到表格中，最外层是求最大值，第二层是对按照时间顺序建立的新表累加求和，第三层（最内层）是根据时间顺序把登陆时间和退出时间分别建立有1和-1作为单独一列的两个表，再把这两个表union all，不要忘了这个整体还要和 course_tb连接，因为最后信息需要课程名称。

所以基本上就是三层：求最大值、窗口函数累积求和、用1和-1代替用户的登陆和退出状态变化。