题解 | #每篇文章同一时刻最大在看人数#
每篇文章同一时刻最大在看人数
https://www.nowcoder.com/practice/fe24c93008b84e9592b35faa15755e48
一、新的知识点与思路
⭐计算某一时刻同时在线人数
对进出状态进行1，-1编码，这样使用窗口函数按时间顺序汇总一下就可以求出每个时刻的在线人数。从其他回答学习到的一个非常实用的一个思路👍👍👍
二、题目易踩的坑
⭐如果同一时刻有进入也有离开时，先记录用户数增加再记录减少
比如在某一秒钟有人进来也有人出去，那个此时的计算顺序应该是在进来时先加一，出去时再减一，如果没有特意这一点的话，那么进来跟出去两个时间的人数都应该是不加不减。
这就要求使用窗口函数时，不仅要按时间排序，还要按出入顺序进行排序。
三、解题步骤
（一）对进出状态进行编码
将表拆成两个部分分开来看，在in_time时编码为1，out_time编码为-1
WITH t1 AS (SELECT artical_id, in_time dt, 1 diff FROM tb_user_log WHERE artical_id <> 0 UNION ALL SELECT artical_id, out_time dt, -1 diff FROM tb_user_log WHERE artical_id <> 0 ORDER BY artical_id, dt)（二）使用窗口函数计算每个时刻实时的人数
SELECT artical_id, MAX(uv) max_uv FROM (SELECT artical_id, dt , SUM(diff) OVER(partition by artical_id ORDER BY dt, diff DESC) uv FROM t1) t2 GROUP BY artical_id ORDER BY max_uv DESC
四、汇总
WITH t1 AS (SELECT artical_id, in_time dt, 1 diff FROM tb_user_log WHERE artical_id <> 0 UNION ALL SELECT artical_id, out_time dt, -1 diff FROM tb_user_log WHERE artical_id <> 0 ORDER BY artical_id, dt) 7 SELECT artical_id, MAX(uv) max_uv FROM (SELECT artical_id, dt , SUM(diff) OVER(partition by artical_id ORDER BY dt, diff DESC) uv FROM t1) t2 GROUP BY artical_id ORDER BY max_uv DESC
从来就没有什么点击就送
TP-LINK开奖124人在聊
查看25道真题和解析