通過SQL計(jì)算同時在線問題

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

0 需求分析

數(shù)據(jù)為主播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）該平臺某一天主播同時在線人數(shù)最高為多少？

（2）出現(xiàn)最高峰的時間段是哪個時間？

1 數(shù)據(jù)準(zhǔn)備

（1）數(shù)據(jù)

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 ）加載數(shù)據(jù)

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

（4） 查詢數(shù)據(jù)

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 數(shù)據(jù)分析

問題1分析：

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

上述思路總結(jié)如下：

（1）將數(shù)據(jù)切分（按起始時間和結(jié)束時間）

（2）數(shù)據(jù)進(jìn)行標(biāo)簽，開播的記錄為記為1，下播的記錄記為-1用于累加

（2）將數(shù)據(jù)按時間進(jìn)行排序

（3）數(shù)據(jù)進(jìn)入累加器進(jìn)行累加

（4）獲取累加器中當(dāng)前累加值最大的數(shù)值

有了以上思路后，我們將其轉(zhuǎn)換為SQL求解思路。

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

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） 數(shù)據(jù)標(biāo)記。在上述SQL基礎(chǔ)上直接進(jìn)行標(biāo)記即可。如果數(shù)據(jù)本來就是分開的則用case when進(jìn)行標(biāo)記。

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）數(shù)據(jù)按照時間排序，進(jìn)入累加器進(jìn)行累加（按時間排序是累加的關(guān)鍵）

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） 獲取累加器中當(dāng)前時刻累加的最大值，即為同時開播最多的人數(shù)

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分析：

第二問求的是出現(xiàn)高峰時的時間段，也就是高峰時間的起始時間及結(jié)束時間，或持續(xù)時長。

借鑒第一問的結(jié)果進(jìn)行分析：

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

通過上圖我們可以看出當(dāng)由峰值出的記錄時間到下一條記錄人數(shù)減少的時候這一段時間即為峰值持續(xù)的時間，或高峰的時間段，也就是求出峰值的下一條記錄的時間與峰值對應(yīng)記錄的時間即為高峰時間段，因此利用lead（）函數(shù)很容易求出問題的答案。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

計(jì)算結(jié)果如下：

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

OK

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

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

3 小結(jié)

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

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

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

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

作者： 石榴公子YYDS

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

責(zé)任編輯：haq