牛客SQL34：牛客直播各科目同时在线人数（最大值问题）

￡神魔★判官ぃ 2023-10-04 18:34 12阅读 0赞

### 牛客SQL34： 牛客直播各科目同时在线人数（最大值问题） ###

#### 内容目录 ####

*   *  牛客SQL34： 牛客直播各科目同时在线人数（最大值问题）
     *   *  一、描述
         *  二、思路解析
         *  三、代码
         *  四、问题思考

#### 一、描述 ####

牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。

已知课程表course\_tb如下（其中course\_id代表课程编号，course\_name表示课程名称，course\_datetime代表上课时间）：

<table> 
 <thead> 
  <tr> 
   <th>course_id</th> 
   <th>course_name</th> 
   <th>course_datetime</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Python</td> 
   <td>2021-12-1 19:00-21:00</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>SQL</td> 
   <td>2021-12-2 19:00-21:00</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>R</td> 
   <td>2021-12-3 19:00-21:00</td> 
  </tr> 
 </tbody> 
</table>

上课情况表attend\_tb如下（其中user\_id表示用户编号、course\_id代表课程编号、in\_datetime表示进入直播间的时间、out\_datetime表示离开直播间的时间）：

<table> 
 <thead> 
  <tr> 
   <th>user_id</th> 
   <th>course_id</th> 
   <th>in_datetime</th> 
   <th>out_datetime</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>100</td> 
   <td>1</td> 
   <td>2021-12-01 19:00:00</td> 
   <td>2021-12-01 19:28:00</td> 
  </tr> 
  <tr> 
   <td>100</td> 
   <td>1</td> 
   <td>2021-12-01 19:30:00</td> 
   <td>2021-12-01 19:53:00</td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>1</td> 
   <td>2021-12-01 19:00:00</td> 
   <td>2021-12-01 20:55:00</td> 
  </tr> 
  <tr> 
   <td>102</td> 
   <td>1</td> 
   <td>2021-12-01 19:00:00</td> 
   <td>2021-12-01 19:05:00</td> 
  </tr> 
  <tr> 
   <td>104</td> 
   <td>1</td> 
   <td>2021-12-01 19:00:00</td> 
   <td>2021-12-01 20:59:00</td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>2</td> 
   <td>2021-12-02 19:05:00</td> 
   <td>2021-12-02 20:58:00</td> 
  </tr> 
  <tr> 
   <td>102</td> 
   <td>2</td> 
   <td>2021-12-02 18:55:00</td> 
   <td>2021-12-02 21:00:00</td> 
  </tr> 
  <tr> 
   <td>104</td> 
   <td>2</td> 
   <td>2021-12-02 18:57:00</td> 
   <td>2021-12-02 20:56:00</td> 
  </tr> 
  <tr> 
   <td>107</td> 
   <td>2</td> 
   <td>2021-12-02 19:10:00</td> 
   <td>2021-12-02 19:18:00</td> 
  </tr> 
  <tr> 
   <td>100</td> 
   <td>3</td> 
   <td>2021-12-03 19:01:00</td> 
   <td>2021-12-03 21:00:00</td> 
  </tr> 
  <tr> 
   <td>102</td> 
   <td>3</td> 
   <td>2021-12-03 18:58:00</td> 
   <td>2021-12-03 19:05:00</td> 
  </tr> 
  <tr> 
   <td>108</td> 
   <td>3</td> 
   <td>2021-12-03 19:01:00</td> 
   <td>2021-12-03 19:56:00</td> 
  </tr> 
 </tbody> 
</table>

请你统计每个科目最大同时在线人数（按course\_id排序），以上数据的输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>course_id</th> 
   <th>course_name</th> 
   <th>max_num</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Python</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>SQL</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>R</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

#### 二、思路解析 ####

这其实是一个瞬时最大值问题，解题详情见[sql解决连续问题][sql]

这类题的解析步骤可以分为以下

1.  找到数量变化的时间点，比如进入直播间和退出直播间等
2.  对数量变化做出相应的+1和-1操作
3.  按照题目规定对某个字段分组排序开窗求和，获取最大值

#### 三、代码 ####

-- 1. 处理人数增减
    with t1 as (
       select tt.course_id
         ,course_name
         ,in_datetime event_time
         ,1 as cnt
       from attend_tb tt
       left join course_tb
       using(course_id)
    
       union all
    
       select tt.course_id
         ,course_name
         ,out_datetime event_time
         ,-1 as cnt 
       from attend_tb tt
        left join course_tb
       using(course_id)
    ),
    -- 2. 按照course_id分组
    t2 as (
      select course_id
        ,course_name
        ,event_time
        ,sum(cnt)
        over(partition by course_id order by event_time) online_cnt
      from t1
    )
    select course_id
      ,course_name
      ,max(online_cnt) max_num
    from t2
    group by course_id, course_name
    order by course_id

#### 四、问题思考 ####

sum(column3) over(partition by column1 order by column2)
    和
    sum(column3) over(partition by column1 order by column2 rows between unbounded preceding and current row)

上述两者的区别

准备数据，向表里增加一些测试数据，不用太多

-- 创建表
    create table user_tb(
      user_id string, 
      score int
    );
    -- 插入数据
    insert into user_tb 
      values('1001',1),
      ('1001',10),
      ('1001',100);

测试1：

select user_id
      ,score
      ,sum(score) 
      over(partition by user_id order by score) 
    from user_tb;

![在这里插入图片描述][59174558394d4eb68267db8e9734f2c4.png]

测试2：

select user_id
      ,score
      ,sum(score) 
      over(partition by user_id order by score rows between unbounded preceding and current row) from user_tb;

![在这里插入图片描述][f6f07bcd30b349ca9c704cf1640ae4a0.png]

这里可以看出，这两个用法的结果其实是一样的，只是中间的计算逻辑不同

[sql]: https://blog.csdn.net/weixin_46429290/article/details/124582012?spm=1001.2014.3001.5502
[59174558394d4eb68267db8e9734f2c4.png]: https://img-blog.csdnimg.cn/59174558394d4eb68267db8e9734f2c4.png
[f6f07bcd30b349ca9c704cf1640ae4a0.png]: https://img-blog.csdnimg.cn/f6f07bcd30b349ca9c704cf1640ae4a0.png