牛客SQL大厂面试真题之用户增长场景

好久没写sql语句了，今天给大家分析下牛客网的SQL大厂真题。想要了解更多关于mysql的函数等知识，可以光临我的SQL专栏，含有窗口函数、日期函数及频繁使用的知识点等：

💞博客主页： 博客主页💖

💞SQL集合专栏：SQL集合专栏💖

0、场景及数据字段说明

• 现有一张表tb_user_log，字段及说明如下表所示：

artical_id-文章ID代表用户浏览的文章的ID，ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

字段	数据类型	解释
id	INT	自增ID
uid	INT	用户ID
artical_id	INT	文章ID
in_time	datetime	进入时间
out_time	datetime	离开时间
sign_in	TINYINT	是否签到

具体题目详情可以参考牛客官网

1、统计2021年11月每天的人均浏览文章时长（秒数）

• 结果保留1位小数，并按时长由短到长排序。

思路：
首先where筛选出符合时间的数据，根据日期(天)分组；
人均浏览时长 = 所有人浏览时长和 / 人数(去重) ;
时长单位为秒数，利用时间戳函数timestampdiff(日期单元, start_time, end_time)

select date_format(out_time,'%Y-%m-%d') as dt
	 ,round(sum(TIMESTAMPDIFF(second,in_time,out_time)) / count(distinct uid),1) as avg_view
from tb_user_log
where date_format(out_time,'%Y-%m') = '2021-11'
group by dt 
order by avg_view asc

2、每篇文章同一时刻最大在看人数

• 统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。
• 这种题，做过一道就会了所有同时，例如之前我的滴滴打车面试题目的详解博客中就有一道各城市同时等车的最大人数，思路都是一样的。

思路：
- 题目中也说了同一时刻有进有出，因此我们首先可以以1代表进入，-1代表离开，方便加减法运算。
- 首先将原表分成两张表，一张进入，一张离开，并加入新字段in_out（进入1离开-1），进入表格的in_out=1，离开表格的in_out=-1；
- 继而将两张表纵向合并；合并后字段包含uid ,artical_id ,dt(进入或离开的时间), in_out；
- 求的是每篇文章，因此我先根据文章分区(注意：不是分组，分组的话就无法计算各时刻的同时在看人数了) 
，按照时间升序排序，对in_out进行sum求和(因为order by了，所以这里的和是指截至当前时刻的在看人数) 
- 计算完每篇文章各时刻的在线人数后，可以对文章id进行分组了(因为只要人数最多的那个时刻)，取同时在读人数最大的那个person_online就ok了。

select artical_id, max(person_online) as max_person_online
from (
	select artical_id 
		,dt 
		,in_out 
		,sum(in_out) over(partition by artical_id order by dt asc ,in_out desc) as person_online    
		# 这里的in_out降序别忘了,因为题目中 “先记录用户数增加再记录减少”
	from (
		(select uid ,artical_id ,in_time as dt, '1' as in_out from tb_user_log)
		union all
		(select uid ,artical_id ,out_time as dt, '-1' as in_out