『SQL实战』图解经典考题-最大在线人数

数据攻略 2022-08-04

1106

点击上方蓝色【数据攻略】关注+星标~

第一时间获取最新内容

哈喽大家好，我是六哥~

SQL作为数据分析师必考点

说简单也简单但说难也挺难

有时也会一道题难倒做题家

所以这次，打算以图解方式唠唠经典考题

—— 最大在线人数

往期SQL类专题文章可点击

本篇，将结合真实业务需求

图解『最大在线人数』类问题

采用图文方式进行思路逐步拆解

确保做一题会一类

（含数据集+思路解析+考点）

------正文手动分割线------

本文结构速览：

一、业务背景

二、数据说明

三、解题思路

四、SQL实战

ps.文末有备考Tips

业务背景

最大在线人数的定义举例：

① 文章同时阅读的最大人数

② 视频同时在看的最大人数

③ 商品同时在看的最大人数

场景举例①：

比如，近期是求职旺季，我想看看大家诉求

《大厂SQL精讲课》同时在看的最大人数
数据攻略里每篇文章的最大在线阅读人数

场景举例②：

再比如，我想统计罗翔老师的bilibili视频同时在看的最大人数

类似的场景可以列举很多

几乎所有大厂，只要有平台/产品/网站都可考查此类题目

毕竟，最大在线人数此指标可反应产品的热度和质量

数据说明

由于业务不一样，数据类型会有差异

但这类题型都可以归纳为以下数据结构：

用户信息，可以有以下类型

用户ID：一般为注册的UID
设备ID：用来唯一标识用户使用的设备
cookie：即便用户不登录的情况下，也可以识别用户身份

产品ID，唯一标识产品信息

文章ID，不同的文章，ID不一样
视频ID，不同的视频，ID不一样
商品ID，不同的商品，ID不一样
其他，比如评论ID

进入时间：标记用户访问产品的时间
离开时间：标记用户访问产品离开的时间

数据样例展示：

▼ 思考：

① 如此刻的你刚好也叫六哥，如何区分呢？

② 如文章名相同，该如何区分呢？

▼ 答案：

这个时候就是用户ID，文章ID的用处了。

① 都叫六哥，但系统分配的用户ID不一样

② 都是SQL实战，但系统分配的文章ID不一样

为了贴近实际业务，我将样例数据进行转换

为了查找uid=10001对应的是六哥
articleid=90001对应的是《数据分析岗 | AB实验框架+高频考点（一）》

所以会有两张信息表

方便大家理解，对实战数据集进行了简化：

表1：文章访问信息（artical_log）

表2：用户信息表（user_info）

表3：文章信息表（artical_info）

『SQL实战问题』

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

为便于大家实战，以下是本题数据集：

DROP TABLE IF EXISTS artical_log;
CREATE TABLE artical_log (
    uid INT  COMMENT '用户ID',
    artical_id INT COMMENT '文章ID',
    start_time  datetime COMMENT '进入时间',
    end_time  datetime COMMENT '离开时间'
);


INSERT INTO artical_log(uid, artical_id, start_time, end_time) VALUES
  (10001, 9001, '2022-08-01 10:00:00', '2022-08-01 10:00:11'),
  (10002, 9001, '2022-08-01 10:00:09', '2022-08-01 10:00:38'),
  (10003, 9001, '2022-08-01 10:00:28', '2022-08-01 10:00:58'),
  (10004, 9002, '2022-08-01 11:00:45', '2022-08-01 11:01:11'),
  (10005, 9001, '2022-08-01 10:00:51', '2022-08-01 10:00:59'),
  (10006, 9002, '2022-08-01 11:00:55', '2022-08-01 11:01:24'),
  (10007, 9001, '2022-08-01 10:00:01', '2022-08-01 10:01:50')
;


DROP TABLE IF EXISTS artical_info;
CREATE TABLE artical_info (
    artical_id INT  COMMENT '文章ID',
    artical_name  varchar(60) COMMENT '文章名称'
);
INSERT INTO artical_info(artical_id, artical_name) VALUES
(9001,'数据分析岗 | AB实验框架+高频考点（一）'),
(9002,'【数据分析岗】| AB实验之方案设计（二）'),
(9003,'数据分析岗 | AB实验之实验分流（三）'),
(9004,'AB高频考点！大白话讲懂『多重检验』'),
(9005,'【数据分析岗】SQL类高频考点归纳'),
(9006,'SQL出题技巧及大厂母题（附答案）'),
(9007,'【SQL实战】淘宝营销活动分析')
;




DROP TABLE IF EXISTS user_info;
CREATE TABLE user_info (
    uid INT  COMMENT '用户id',
    uid_name  varchar(60) COMMENT '文章名称'
);


INSERT INTO user_info(uid, uid_name) VALUES
(10001,'六哥'),
(10002,'罗卡'),
(10003,'张三'),
(10004,'李四'),
(10005,'赵五'),
(10006,'钱六'),
(10007,'高八')
;

解题思路

方便理解，将每篇文章记为一个图书馆的阅读区。

本题的文章访问信息的样例中

总共有两个阅读区，分别是9001,9002

假设每个阅读区仅有一条门出入

且门口有一位场馆管理员

只要进入一个读者，管理员将手动计入+1

只要出去一个读者管理员将计入-1

所以上面的样例数据，可以拆分成如下结果

接下来，按照每个场馆发生的顺序进行排序

每次进入进出都会统计一下当前场馆人数

统计解读（以9002场馆为例）：

10004第一个进入，当前场馆人数为1
10006第二个进入，当前场馆人数为2
10004离开场馆，当前场馆人数为1
10006离开场馆，当前场馆人数为0

以此类推，可得9001场馆每次进入进出的人数

下面通过Excel的计算加深理解

统计解读：

10005进入场馆9001，当前场馆人数为从第一个进入场馆进行求和。

SQL实战

▼ 解题思路

以下将文章阅读类比为场馆

① 将样例数据拆分为进入和进出

② 分场馆按照时间进行排序

③ 统计每次进入进出场馆在线人数

④ 求场馆最大在线人数

步骤 ①

① 将样例数据拆分为进入和进出

select 
    artical_id
    -- 进入时间
    ,start_time as log_time 
    ,1 as num 
from artical_log


union all 


select 
    artical_id
    -- 离开时间
    ,end_time as log_time
    ,-1 as num 
from artical_log

步骤 ②

以下两步可以借助窗口函数进行实现

② 分场馆按照时间进行排序

③ 统计每次进入进出场馆在线人数

▼ Tips:

在计算在线人数时，题目有补充部分

如果同一时刻有进入也有离开时，先记录用户数增加再记录减少

该问题的处理方法为窗口函数中order by的使用

① 优先针对发生时间进行排序

② 发生时间相同时，优先针对进入排序

select 
    artical_id
    ,sum(num) over (partition by artical_id order by log_time asc,num desc) as online_uv
from 
( 
    select 
        artical_id
        -- 进入时间
        ,start_time as log_time 
        ,1 as num 
    from artical_log


    union all 


    select 
        artical_id
        -- 离开时间
        ,end_time as log_time
        ,-1 as num 
    from artical_log
)a

步骤 ③

④ 求场馆最大在线人数

select 
    artical_id
    ,max(online_uv) AS max_online_uv
from 
( 
    select 
        artical_id
        ,sum(num) over (partition by artical_id order by log_time asc,num desc) as online_uv
    from 
    ( 
        select 
            artical_id
            -- 进入时间
            ,start_time as log_time 
            ,1 as num 
        from artical_log


        union all 


        select 
            artical_id
            -- 离开时间
            ,end_time as log_time
            ,-1 as num 
        from artical_log
    )a 
)a 
group by 
    artical_id

运行结果如下：

步骤 ④

细心的你会发现，此处9001是文章id

文章ID在结果查看不够直观

所以还需要对文章ID进行匹配转义

select 
    b.artical_name 
    ,a.artical_id
    ,a.max_online_uv
from 
( 
    select 
        artical_id
        ,max(online_uv) AS max_online_uv
    from 
    ( 
        select 
            artical_id
            ,sum(num) over (partition by artical_id order by log_time asc,num desc) as online_uv
        from 
        ( 
            select 
                artical_id
                -- 进入时间
                ,start_time as log_time 
                ,1 as num 
            from artical_log


            union all 


            select 
                artical_id
                -- 离开时间
                ,end_time as log_time
                ,-1 as num 
            from artical_log
        )a 
    )a 
    group by 
        artical_id
)a 
inner join artical_info b 
    on a.artical_id = b.artical_id