通過(guò)SQL計(jì)算同時(shí)在線問(wèn)題

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

0 需求分析

數(shù)據(jù)為主播ID，stt表示開(kāi)播時(shí)間，edt表示下播時(shí)間。

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

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

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ù)分析

問(wèn)題1分析：

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

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

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

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

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

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

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

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

（1）將數(shù)據(jù)切分：實(shí)際上就是將開(kāi)播時(shí)間和下播時(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ù)本來(lái)就是分開(kāi)的則用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ù)按照時(shí)間排序，進(jìn)入累加器進(jìn)行累加（按時(shí)間排序是累加的關(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í)刻累加的最大值，即為同時(shí)開(kāi)播最多的人數(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）

問(wèn)題2分析：

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

借鑒第一問(wèn)的結(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

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

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

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

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

不謀萬(wàn)世者，不足以謀一時(shí)。

作者： 石榴公子YYDS

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

責(zé)任編輯：haq