宣城市网站集约化建设大数据营销精准营销
一般在直播或者游戏中经常会统计用户在线人数，主要分为求每个时刻的在线人数和求某个时刻的在线人数两种。
【场景】：某个时刻的在线人数、每个时刻的在线人数
【知识点】：窗口函数、时间函数、sum(tag) over (order by dt,tag desc rows between unbounded preceding and current row)、窗口函数与分组函数的区别
一、分析思路
1、在线人数如何定义？
在线人数是指在某个时间段内，某一时刻在线的用户数。
可以求每个时刻的在线人数，也可以求某个时刻的在线人数。常用的是求每个时刻的同时在线人数，所以我们以其为例进行讲解
2、求解思路
要计算每个时刻的同时在线人数，我们可以按照以下步骤进行：
- 将所有登录和退出记录按照时间排序，得到一个按照时间递增的登录和退出记录。如果该记录表示用户登录，将其标记为1，如果表示用户退出标记为-1；
- 利用窗口函数按照上述顺序对标记列进行累计求和：登录时人数+1，退出时人数-1。
3、MySQL执行步骤如下：
（1）获取标记好登录和退出的记录表
#将所有用户的进入和离开记录合并一起，统一为dt,并用tag为1和-1来标记进入还是离开 (selecta.登录日期 as dt,1 as tag from 表名1 a) union (selecta.退出日期 as dt,-1 as tag from 表名1 a)这样就得到标记好登录和退出的记录。
（2）对标记记录累计求和
#如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag desc selectdt,sum(tag) over (order by dt,tag desc rows between unbounded preceding and current row) as 同时在线人数 from 标记好登录和退出的记录表
二、实例
下面就以两个实例讲清楚某个时刻的在线人数和每个时刻的在线人数。
两种问题的区别：
| 问题描述 | 特点 | 使用方法 |
|---|---|---|
| 每个时刻的用户在线人数 | 求用户状态变动时刻的用户在线人数 | 先获取标记好登录和退出的记录，然后按照时间和标记排序的顺序对标记列累计求和 |
| 某个时刻的用户在线人数 | 限制条件（截止到某个时刻）的在线人数 | 先获取标记好登录和退出的记录，然后对某个时刻前的标记列求和 |
（1）每个时刻的在线人数
案例来自：SQL163 每篇文章同一时刻最大在看人数
问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。
示例：用户行为日志表tb_user_log（uid:用户ID, artical_id:文章ID, in_time:进入时间, out_time:离开时间, sign_in:是否签到）
| id | uid | artical_id | in_time | out_time | sign_cin |
|---|---|---|---|---|---|
| 1 | 101 | 9001 | 2021-11-01 10:00:00 | 2021-11-01 10:00:11 | 0 |
| 2 | 102 | 9001 | 2021-11-01 10:00:09 | 2021-11-01 10:00:38 | 0 |
| 3 | 103 | 9001 | 2021-11-01 10:00:28 | 2021-11-01 10:00:58 | 0 |
| 4 | 104 | 9002 | 2021-11-01 11:00:45 | 2021-11-01 11:01:11 | 0 |
| 5 | 105 | 9001 | 2021-11-01 10:00:51 | 2021-11-01 10:00:59 | 0 |
| 6 | 106 | 9002 | 2021-11-01 11:00:55 | 2021-11-01 11:01:24 | 0 |
| 7 | 107 | 9001 | 2021-11-01 10:00:01 | 2021-11-01 10:01:50 | 0 |
根据示例，你的查询应返回以下结果：
| artical_id | max_uv |
|---|---|
| 9001 | 3 |
| 9002 | 2 |
解释：10点0分10秒时，有3个用户正在浏览文章9001；11点01分0秒时，有2个用户正在浏览文章9002。
求解代码：
withmain as(#用tag标记增加还是减少(selectartical_id,uid,in_time as dt,1 as tagfrom tb_user_logwhere artical_id != 0)union(selectartical_id,uid,out_time as dt,-1 as tagfrom tb_user_logwhere artical_id != 0)),main1 as(#如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，所以在窗口函数中使用sum()需要order by dt,tag descselectartical_id,sum(tag) over (partition by artical_id order by dt,tag desc rows between unbounded preceding and current row) as uvfrom main)
#统计每篇文章同一时刻最大在看人数，结果按最大人数降序
selectartical_id,max(uv) as max_uv
from main1
group by artical_id
order by max_uv desc
- 标记好登录和退出的记录
artical_id uid dt tag
1 9001 101 2021-11-01 10:00:00 1
2 9001 102 2021-11-01 10:00:09 1
3 9001 103 2021-11-01 10:00:28 1
4 9002 104 2021-11-01 11:00:45 1
5 9001 105 2021-11-01 10:00:51 1
6 9002 106 2021-11-01 11:00:55 1
7 9001 107 2021-11-01 10:00:01 1
8 9001 101 2021-11-01 10:00:11 -1
9 9001 102 2021-11-01 10:00:38 -1
10 9001 103 2021-11-01 10:00:58 -1
11 9002 104 2021-11-01 11:01:11 -1
12 9001 105 2021-11-01 10:00:59 -1
13 9002 106 2021-11-01 11:01:24 -1
14 9001 107 2021-11-01 10:01:50 -1
- 对标记记录累计求和
artical_id dt tag
1 9001 2021-11-01 10:00:00 1
2 9001 2021-11-01 10:00:01 2
3 9001 2021-11-01 10:00:09 3
4 9001 2021-11-01 10:00:11 2
5 9001 2021-11-01 10:00:28 3
6 9001 2021-11-01 10:00:38 2
7 9001 2021-11-01 10:00:51 3
8 9001 2021-11-01 10:00:58 2
9 9001 2021-11-01 10:00:59 1
10 9001 2021-11-01 10:01:50 0
11 9002 2021-11-01 11:00:45 1
12 9002 2021-11-01 11:00:55 2
13 9002 2021-11-01 11:01:11 1
14 9002 2021-11-01 11:01:24 0
- 统计每篇文章同一时刻最大在看人数
artical_id max_uv
1 9001 3
2 9002 2
（2）某个时刻的在线人数
案例来自：SQL186 牛客直播开始时各直播间在线人数
问题：请你统计直播开始时（19：00），各科目的在线人数，以上例子的输出结果为（按照course_name升序排序）：
已知上课情况表attend_tb如下（其中user_id：用户编号、course_name：课程名称，course_datetime：上课时间、in_datetime：进入直播间的时间、out_datetime：离开直播间的时间）：
| user_id | course_name | course_datetime | in_datetime | out_datetime |
|---|---|---|---|---|
| 100 | Python | 2021-12-1 19:00-21:00 | 2021-12-01 19:00:00 | 2021-12-01 19:28:00 |
| 100 | Python | 2021-12-1 19:00-21:00 | 2021-12-01 19:30:00 | 2021-12-01 19:53:00 |
| 101 | Python | 2021-12-1 19:00-21:00 | 2021-12-01 19:00:00 | 2021-12-01 20:55:00 |
| 102 | Python | 2021-12-1 19:00-21:00 | 2021-12-01 19:00:00 | 2021-12-01 19:05:00 |
| 104 | Python | 2021-12-1 19:00-21:00 | 2021-12-01 19:00:00 | 2021-12-01 20:59:00 |
| 101 | SQL | 2021-12-2 19:00-21:00 | 2021-12-02 19:05:00 | 2021-12-02 20:58:00 |
| 102 | SQL | 2021-12-2 19:00-21:00 | 2021-12-02 18:55:00 | 2021-12-02 21:00:00 |
| 104 | SQL | 2021-12-2 19:00-21:00 | 2021-12-02 18:57:00 | 2021-12-02 20:56:00 |
| 107 | SQL | 2021-12-2 19:00-21:00 | 2021-12-02 19:10:00 | 2021-12-02 19:18:00 |
| 100 | R | 2021-12-3 19:00-21:00 | 2021-12-03 19:01:00 | 2021-12-03 21:00:00 |
| 102 | R | 2021-12-3 19:00-21:00 | 2021-12-03 18:58:00 | 2021-12-03 19:05:00 |
| 108 | R | 2021-12-3 19:00-21:00 | 2021-12-03 19:01:00 | 2021-12-03 19:56:00 |
示例数据的输出结果如下
| course_name | online_num |
|---|---|
| Python | 4 |
| R | 1 |
| SQL | 2 |
求解代码：
withmain as(#进入为增加人数，出去为减少人数selectuser_id,course_name,date_format(in_datetime,'%H:%i') as dt,1 as tagfrom attend_tbunionselectuser_id,course_name,date_format(out_datetime,'%H:%i') as dt,-1 as tagfrom attend_tb)
#统计直播开始时（19：00），各科目的在线人数
selectcourse_name,sum(tag) as online_num
from course_tb
left join main using(course_name)
where dt <= '19:00'
group by course_name
order by course_name;
- 标记好登录和退出的记录
device_id course_name dt tag
100 Python 19:00 1
100 Python 19:30 1
101 Python 19:00 1
102 Python 19:00 1
104 Python 19:00 1
101 SQL 19:05 1
102 SQL 18:55 1
104 SQL 18:57 1
107 SQL 19:10 1
100 R 19:01 1
102 R 18:58 1
108 R 19:01 1
100 Python 19:28 -1
100 Python 19:53 -1
101 Python 20:55 -1
102 Python 19:05 -1
104 Python 20:59 -1
101 SQL 20:58 -1
102 SQL 21:00 -1
104 SQL 20:56 -1
107 SQL 19:18 -1
100 R 21:00 -1
102 R 19:05 -1
108 R 19:56 -1
- 对截止时间点的标记记录求和
course_name dt online_num
Python 19:00 4
R 18:58 1
SQL 18:55 2
扩展：
前往查看：MySQL 窗口函数与分组函数的区别