MYSQL练习题：每篇文章同时刻最大在看人数

最新推荐文章于 2023-09-29 15:27:57 发布

Avasla

最新推荐文章于 2023-09-29 15:27:57 发布

阅读量1.6k

点赞数 3

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/WHYbeHERE/article/details/127961980

版权

Mysql 专栏收录该内容

51 篇文章 10 订阅

订阅专栏

题目

在这里插入图片描述
场景逻辑说明： artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

题目分析：

难点：如何求同时刻在线人数。

很多人可能第一想到用Join自连接的方法，但是在实际操作中，非常复杂，不容易理解。而且一些条件容易遗漏，导致最后结果出错。所以在这一题中用连结的方法反而是把简单的问题复杂化了。

下面就总结了最简单易懂的三步走方法，通过转换原始的数据表格，利用窗口函数求累计值，一步步完成题目的要求：

第一步：要分别求出每个时刻的登入、登出人数

只有一张每个用户的登录登出记录表，我们需要记录每个秒用户的在线人数。再看题目的提示：“如果同一时刻有进入也有离开时，先记录用户数增加再记录减少”，所以****。

第一步我们可以通过通过Union ALL 将数据重新排序，将增加和减少的人数放在同一列。因为每一行是一个用户的行为记录，所以可以直接将增加人数赋值为1，离开人数为-1

# 按登入时间 in_time，汇总每篇文章每秒登录的人数
SELECT
	artical_id,
	in_time,
	1 AS num 
FROM tb_user_log 
WHERE artical_id != 0  # 不要漏掉了这个条件：要排除掉非内容页！
Union all  #将两类结果合并在一起
#按登出时间out_time，汇总每篇文章每秒的离开的人数
select  
	artical_id, 
	out_time,
	-1 as number  
from tb_user_log
where artical_id!=0

这样我们这里就得到了下面这张表格，正数表示此刻登录的人数，负数表示离开的人数
在这里插入图片描述

第二步：求出每时刻在线的人数

注意：

这里需要的是同时在线的人数，需要进行累加的操作。
这里容易错误地想到直接根据时间group by 然后sum，这样只能求出每时刻的人数变化量，而不是同时在线人数。正确的解法是使用窗口函数根据artical_id分别求出累计人数。
并且“如果同一时刻有进入也有离开时，先记录用户数增加再记录减少” 。
因此在窗口函数层面，还需要对num进行倒序排序！！这点容易被忽略掉！！要先按照时间排序、再按照计数排序。

SELECT
		t1.artical_id,
		t1.in_time,
		sum(t1.num) over ( PARTITION BY artical_id ORDER BY in_time,num desc) AS num
        from
		(
		SELECT
			artical_id,
			in_time,
			1 AS num 
		FROM tb_user_log 
		WHERE artical_id != 0 
		UNION ALL
		SELECT
			artical_id,
			out_time,
			- 1 AS num 
		FROM tb_user_log 
		WHERE artical_id != 0 )t1

这里结果第三列number就是每时刻的人数累计值，即同时在线的人数。
在这里插入图片描述

第三步：每篇文章同一时刻最大在看人数

最后一步简单明了，通过max 对每篇文章分组聚合，注意最后排序。

SELECT 
	artical_id,
	max( number ) AS max_uv 
from (第二步代码) t2
GROUP BY
	artical_id,
ORDER BY
	max_uv DESC

最终完整答案：

SELECT 
	t2.artical_id,
	max(t2.num) AS max_uv 
from (
SELECT
		t1.artical_id,
		t1.in_time,
		sum(t1.num) over ( PARTITION BY artical_id ORDER BY in_time,num) AS num
        from
		(
		SELECT
			artical_id,
			in_time,
			1 AS num 
		FROM tb_user_log 
		WHERE artical_id != 0 
		UNION ALL
		SELECT
			artical_id,
			out_time,
			- 1 AS num 
		FROM tb_user_log 
		WHERE artical_id != 0 
		) t1  
	) t2
GROUP BY artical_id
ORDER BY max_uv DESC

小结

这题难点就在于求同时在线的人数，果没接触过同类型的题目就真的一头雾水，知道了解题的思路后就很简单了。代码本身不难，使用的也是窗口函数和聚合方法。

首先是对每个用户行为记录的转化，通过对in_time和out_time分别赋值，每一行为一个记录，所以直接赋值为1和-1, 然后通过Union ALL 函数将两部分合并起来；

接着通过窗口函数计算累计值：这里有个细节是——对计数也进行排序，先算增加的再减去离开的人数。

题目链接：https://www.nowcoder.com/practice/fe24c93008b84e9592b35faa15755e48?tpId=268&tqId=2285343&ru=%2Fpractice%2Fd337c95650f640cca29c85201aecff84&qru=%2Fta%2Fsql-factory-interview%2Fquestion-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D268