本文是通過 SQL 計算同時在線問題，即求最高在線人數以及最高峰時間段。

0 需求分析

數據為主播ID，stt表示開播時間，edt表示下播時間。

idsttedt

10012021-06-14 12122021-06-14 1812

10032021-06-14 13122021-06-14 1612

10042021-06-14 13122021-06-14 2012

10022021-06-14 15122021-06-14 1612

10052021-06-14 15122021-06-14 2012

10012021-06-14 20122021-06-14 2312

10062021-06-14 21122021-06-14 2312

10072021-06-14 22122021-06-14 2312

求：

（1）該平臺某一天主播同時在線人數最高為多少？

（2）出現最高峰的時間段是哪個時間？

1 數據準備

（1）數據

vim play.txt

idstt edt

10012021-06-14 1212 2021-06-14 1812

10032021-06-14 13122021-06-14 1612

10042021-06-14 13122021-06-14 2012

10022021-06-14 15122021-06-14 1612

10052021-06-14 15122021-06-14 2012

10012021-06-14 20122021-06-14 2312

10062021-06-14 21122021-06-14 2312

10072021-06-14 22122021-06-14 2312

（2） 建表

create table if not exists play（

id string，

stt string，

edt string

）

row format delimitedfields terminated by ‘ ’

;

（3 ）加載數據

load data local inpath “/home/centos/dan_test/play.txt” into table play;

（4） 查詢數據

hive》 select * from play;

OK

1001 2021-06-14 12:12:12 2021-06-14 18:12:121003 2021-06-14 13:12:12 2021-06-14 16:12:121004 2021-06-14 13:15:12 2021-06-14 20:12:121002 2021-06-14 15:12:12 2021-06-14 16:12:121005 2021-06-14 15:18:12 2021-06-14 20:12:121001 2021-06-14 20:12:12 2021-06-14 23:12:121006 2021-06-14 21:12:12 2021-06-14 23:15:121007 2021-06-14 22:12:12 2021-06-14 23:10:12

Time taken： 0.087 seconds， Fetched： 8 row（s）

2 數據分析

問題1分析：

本題如果直接從SQL本身很難下手，無從做起，不妨我們換個思路，假定我們拿到的是一條數據，現在用java程序怎么做？其實就是一個累加器的思想（如SPARK的累加器）。首先我們需要將這樣一條記錄進行拆分，分成不同的記錄或數據流進入累加器，然后給每條記錄進行標記，如果開播的話該條記錄記為1，下播的話記為-1，此時的數據流按照時間順序依次進入累加器，然后在累加器中進行疊加，其中累計的結果最大時候就是所求的結果。其實本質是利用累加器思想，但進入累加器的數據是按時間排好序的時序流數據（數據進入按時間先后順序進入）。

上述思路總結如下：

（1）將數據切分（按起始時間和結束時間）

（2）數據進行標簽，開播的記錄為記為1，下播的記錄記為-1用于累加

（2）將數據按時間進行排序

（3）數據進入累加器進行累加

（4）獲取累加器中當前累加值最大的數值

有了以上思路后，我們將其轉換為SQL求解思路。

（1）將數據切分：實際上就是將開播時間和下播時間轉換成一條條記錄。也就是列轉行，我們用熟悉的UNION操作，進行轉換。

select id，stt dt from play

unionselect id，edt dt from play

--------------------------------------------------------------------------------

OK

1001 2021-06-14 12:12:121001 2021-06-14 18:12:121001 2021-06-14 20:12:121001 2021-06-14 23:12:121002 2021-06-14 15:12:121002 2021-06-14 16:12:121003 2021-06-14 13:12:121003 2021-06-14 16:12:121004 2021-06-14 13:15:121004 2021-06-14 20:12:121005 2021-06-14 15:18:121005 2021-06-14 20:12:121006 2021-06-14 21:12:121006 2021-06-14 23:15:121007 2021-06-14 22:12:121007 2021-06-14 23:10:12

Time taken： 20.502 seconds， Fetched： 16 row（s）

（2） 數據標記。在上述SQL基礎上直接進行標記即可。如果數據本來就是分開的則用case when進行標記。

select id，stt dt ， 1 flag from play

unionselect id，edt dt ，-1 flag from play

--------------------------------------------------------------------------------

OK

1001 2021-06-14 12:12:12 11001 2021-06-14 18:12:12 -11001 2021-06-14 20:12:12 11001 2021-06-14 23:12:12 -11002 2021-06-14 15:12:12 11002 2021-06-14 16:12:12 -11003 2021-06-14 13:12:12 11003 2021-06-14 16:12:12 -11004 2021-06-14 13:15:12 11004 2021-06-14 20:12:12 -11005 2021-06-14 15:18:12 11005 2021-06-14 20:12:12 -11006 2021-06-14 21:12:12 11006 2021-06-14 23:15:12 -11007 2021-06-14 22:12:12 11007 2021-06-14 23:10:12 -1

Time taken： 7.408 seconds， Fetched： 16 row（s）

（3）數據按照時間排序，進入累加器進行累加（按時間排序是累加的關鍵）

select id

，dt

，sum（flag） over（order by dt） as cur_cnt

from（

select id，stt dt ， 1 flag from play

union

select id，edt dt ，-1 flag from play

） t

--------------------------------------------------------------------------------

OK

1001 2021-06-14 12:12:12 11003 2021-06-14 13:12:12 21004 2021-06-14 13:15:12 31002 2021-06-14 15:12:12 41005 2021-06-14 15:18:12 51002 2021-06-14 16:12:12 31003 2021-06-14 16:12:12 31001 2021-06-14 18:12:12 21001 2021-06-14 20:12:12 11004 2021-06-14 20:12:12 11005 2021-06-14 20:12:12 11006 2021-06-14 21:12:12 21007 2021-06-14 22:12:12 31007 2021-06-14 23:10:12 21001 2021-06-14 23:12:12 11006 2021-06-14 23:15:12 0

Time taken： 8.133 seconds， Fetched： 16 row（s）

（4） 獲取累加器中當前時刻累加的最大值，即為同時開播最多的人數

select max（cur_cnt）

from（

select id

，dt

，sum（flag） over（order by dt） as cur_cnt

from（

select id，stt dt ， 1 flag from play

union

select id，edt dt ，-1 flag from play

） t

） m

--------------------------------------------------------------------------------

OK

5

Time taken： 13.087 seconds， Fetched： 1 row（s）

問題2分析：

第二問求的是出現高峰時的時間段，也就是高峰時間的起始時間及結束時間，或持續時長。

借鑒第一問的結果進行分析：

select *，max（cur_cnt） over（）

from（

select id

，dt

，sum（flag） over（order by dt） as cur_cnt

from（

select id，stt dt ， 1 flag from play

union

select id，edt dt ，-1 flag from play

） t

） m

通過上圖我們可以看出當由峰值出的記錄時間到下一條記錄人數減少的時候這一段時間即為峰值持續的時間，或高峰的時間段，也就是求出峰值的下一條記錄的時間與峰值對應記錄的時間即為高峰時間段，因此利用lead（）函數很容易求出問題的答案。SQL如下：

select max_cur_cnt

，dt as start_time

，lead_dt as end_time

from（

select *

，lead（dt，1，dt） over（order by dt） lead_dt

from（

select *，max（cur_cnt） over（） as max_cur_cnt

from（

select id

，dt

，flag

，sum（flag） over（order by dt） as cur_cnt

from（

select id，stt dt ， 1 flag from play

union

select id，edt dt ，-1 flag from play

） t

） m

） n

） p

where cur_cnt=max_cur_cnt

計算結果如下：

--------------------------------------------------------------------------------

OK

5 2021-06-14 15:18:12 2021-06-14 16:12:12

Time taken： 17.513 seconds， Fetched： 1 row（s）

3 小結

本文針對SQL統計同時在線人數問題進行了分析，利用累加器思想對該問題進行求解，最終劃歸為時序數據，進行時序數據分析（常用技巧：打標簽，形成序列，多序列進行分析），最后利用sum（） over（）對標簽進行累加求出當前在線人數本題最關鍵的點在于轉換為時序數據及累加器的思想，望讀者能夠掌握。

事實上該問題的分析在業務上具有重要的意義，我們能夠實時跟蹤隨著時間變化的在線人數，了解服務器的負載變化情況，服務器的實時并發數等。該問題在不同業務場景下，有不同意義，比如某個游戲的同時在線人數，比如某個服務器的實時并發數，比如某個倉庫的貨物積壓數量，某一段時間內的同時處于服務過程中的最大訂單量等。實際上求最大在線人數和求實時在線人數是一回事，最大人數依賴于當前在線人數表，只有先求出當前在線人數表，才能求出最大同時在線人數。

不謀全局者，不足以謀一域。

不謀萬世者，不足以謀一時。

作者： 石榴公子YYDS

https://blog.csdn.net/godlovedaniel/article/details/118651811

責任編輯：haq