一般在直播或者游戲中經(jīng)常會統(tǒng)計用戶在線人數(shù)，主要分為求每個時刻的在線人數(shù)和求某個時刻的在線人數(shù)兩種。

【場景】：某個時刻的在線人數(shù)、每個時刻的在線人數(shù)

【知識點】：窗口函數(shù)、時間函數(shù)、sum(tag) over (order by dt,tag desc rows between unbounded preceding and current row)、窗口函數(shù)與分組函數(shù)的區(qū)別

一、分析思路

1、在線人數(shù)如何定義？

在線人數(shù)是指在某個時間段內(nèi)，某一時刻在線的用戶數(shù)。

可以求每個時刻的在線人數(shù)，也可以求某個時刻的在線人數(shù)。常用的是求每個時刻的同時在線人數(shù)，所以我們以其為例進行講解

2、求解思路

要計算每個時刻的同時在線人數(shù)，我們可以按照以下步驟進行：

1. 將所有登錄和退出記錄按照時間排序，得到一個按照時間遞增的登錄和退出記錄。如果該記錄表示用戶登錄，將其標記為1，如果表示用戶退出標記為-1；
2. 利用窗口函數(shù)按照上述順序?qū)擞浟羞M行累計求和：登錄時人數(shù)+1，退出時人數(shù)-1。

---

3、MySQL執(zhí)行步驟如下：

（1）獲取標記好登錄和退出的記錄表

#將所有用戶的進入和離開記錄合并一起，統(tǒng)一為dt,并用tag為1和-1來標記進入還是離開
(selecta.登錄日期 as dt,1 as tag
from 表名1 a)
union
(selecta.退出日期 as dt,-1 as tag
from 表名1 a)

這樣就得到標記好登錄和退出的記錄。

（2）對標記記錄累計求和

#如果同一時刻有進入也有離開時，先記錄用戶數(shù)增加再記錄減少，所以在窗口函數(shù)中使用sum()需要order by dt,tag desc
selectdt,sum(tag) over (order by dt,tag desc rows between unbounded preceding and current row) as 同時在線人數(shù)
from 標記好登錄和退出的記錄表

二、實例

下面就以兩個實例講清楚某個時刻的在線人數(shù)和每個時刻的在線人數(shù)。

---

兩種問題的區(qū)別：

問題描述	特點	使用方法
每個時刻的用戶在線人數(shù)	求用戶狀態(tài)變動時刻的用戶在線人數(shù)	先獲取標記好登錄和退出的記錄，然后按照時間和標記排序的順序?qū)擞浟?strong>累計求和
某個時刻的用戶在線人數(shù)	限制條件（截止到某個時刻）的在線人數(shù)	先獲取標記好登錄和退出的記錄，然后對某個時刻前的標記列求和

---

（1）每個時刻的在線人數(shù)

案例來自：SQL163 每篇文章同一時刻最大在看人數(shù)
問題：統(tǒng)計每篇文章同一時刻最大在看人數(shù)，如果同一時刻有進入也有離開時，先記錄用戶數(shù)增加再記錄減少，結果按最大人數(shù)降序。
示例：用戶行為日志表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

根據(jù)示例，你的查詢應返回以下結果：

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(#如果同一時刻有進入也有離開時，先記錄用戶數(shù)增加再記錄減少，所以在窗口函數(shù)中使用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)
#統(tǒng)計每篇文章同一時刻最大在看人數(shù)，結果按最大人數(shù)降序 
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

• 統(tǒng)計每篇文章同一時刻最大在看人數(shù)

artical_id	max_uv
1	9001				3
2	9002				2

（2）某個時刻的在線人數(shù)

案例來自：SQL186 ?？椭辈ラ_始時各直播間在線人數(shù)
問題：請你統(tǒng)計直播開始時（19：00），各科目的在線人數(shù)，以上例子的輸出結果為（按照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

示例數(shù)據(jù)的輸出結果如下

course_name	online_num
Python	4
R	1
SQL	2

求解代碼：

withmain as(#進入為增加人數(shù)，出去為減少人數(shù)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)
#統(tǒng)計直播開始時（19：00），各科目的在線人數(shù)
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 窗口函數(shù)與分組函數(shù)的區(qū)別