大厂SQL题5-同时在线人数、不同类别的第一

一、用户行为分析

1、被收藏次数最多的商品为？-so easy

select mch_id,count(1)
from xhs_fav_rcd
group by mch_id
1
2
3

想得到商品名称， 就再连接一下表一

2、购买人数最多的商品类目为？ -平平无奇

select a.gd_typ,count(distinct c.cust_uid) as cnt
from xhs_pchs_rcd c left join gd_inf a
on  a.gd_id=c.mch_id
group by a.gd_typ
order by 2 desc
1
2
3
4
5

3、被收藏，却未被购买的商品？-轻松拿下

select distinct mch_id
from xhs_fav_rcd b
where mch_id not in
(select distinct mch_id from xhs_pchs_rcd)
1
2
3
4

4、哪个商品，既被同一个用户购买，又被同一个用户收藏，且购买人数最多？–不难

用内连接进行筛选

select  b.mch_id,count(1)
from xhs_fav_rcd b join xhs_pchs_rcd c
on  b.mch_id=c.mch_id and b.cust_uid=c.cust_uid
group by  b.mch_id
order by 2 desc;
1
2
3
4
5

二、直播观看人数

1、进入直播间的高峰期为？(以进入用户数衡量)-easy

高峰期问题：group by hour(时间)

select hour(enter_time),count(distinct usr_id)
from ks_live_t1 t1
group by hour(enter_time)
order by 2 desc
1
2
3
4

2、晚上 11 点，哪个直播间的进入人数最多？-easy,读题：是11点进入，不是11点在直播间

select live_id,count(distinct usr_id)
from ks_live_t1 t1
where hour(enter_time) = 23 
group by live_id
1
2
3
4

3、20：00-23：00，娱乐类、搞笑类，进入人数最多直播间分别是？

不同类别的第一输出
要是类别不多，求出次数cnt后，直接排个序看
要是一定要只输出第一：那就下面这样，嵌套用dense_rank()over(…)对次数分组排序rnk，再嵌套删选rnk=1的项

select *
from (
select *,dense_rank()over(partition by live_type order by cnt desc) rnk
from (
select t2.live_type,t2.live_id,count(distinct usr_id) cnt
from ks_live_t1 t1
left join ks_live_t2 t2
on t1.live_id = t2.live_id
where hour(enter_time) between 20 and 22 
and t2.live_type in ('购物','娱乐')
group by t2.live_type,t2.live_id
) a
)
where rnk=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4、娱乐类、搞笑类，人均在线时长（退出时间-进入时间）最长的直播间分别是？

和3一样套路，选择标准‘进入人数’–‘平均时长’
改一下cnt 即可，记得用avg()平均数

select *
from (
select *,dense_rank()over(partition by live_type order by cnt desc) rnk
from (
select t2.live_type,t2.live_id,avg(timestampdiff(second,t1.enter_time,t1.leave_time) cnt
from ks_live_t1 t1
left join ks_live_t2 t2
on t1.live_id = t2.live_id
where hour(enter_time) between 20 and 22 
and t2.live_type in ('购物','娱乐')
group by t2.live_type,t2.live_id
) a
)
where rnk=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4、各个直播间的同时在线人数的峰值？------重要！！！！！！！！

同时在线人数问题：
1，（id，进去时间，打标tag为1 ）union all（id，出去时间，打标tag为-1 ）
2，sum(tag) over (partition by live_id order by tms) as num
要是想得到直播间名字，就再连接一下表二
3,按id分组，输出最大人数max(tms)

select live_id,max(num) as max_n
from (
select live_id,tm,sum(tag)over(partition by live_id order by tms) as num
from
(select live_id,enter_time tm,1 as tag from ks_live_t1
union all
select live_id,leave_time tm,-1 as tag from ks_live_t1
) t
) a
group by live_id
1
2
3
4
5
6
7
8
9
10

语法：
sum(col1) over(partition by col2 order by col3 )
按col2 进行分组(partition )，每组以col3 进行排序(order)，并进行连续加总（sum）
例子：

sum(tag)over(partition by live_id order by tms) as num
所以，按 live_id 分组后，按照时间tms累计求和