MYSQL經(jīng)典SQL之時(shí)長(zhǎng)統(tǒng)計(jì)

概述

假設(shè)有一張表呐矾,記錄了用戶的登入妹笆、登出信息,我們需要根據(jù)這些信息統(tǒng)計(jì)用戶的在線時(shí)長(zhǎng)奠支,相應(yīng)的設(shè)置用戶等級(jí),就像掛QQ升級(jí)

數(shù)據(jù)如下:

mysql> select * from user_sessions;
+----+----------+-------+----------+----------+
| id | platform | usr   | start    | end      |
+----+----------+-------+----------+----------+
|  1 | web      | user1 | 08:30:00 | 10:30:00 |
|  2 | web      | user2 | 08:30:00 | 08:45:00 |
|  3 | web      | user1 | 09:00:00 | 09:30:00 |
|  4 | web      | user2 | 09:15:00 | 10:30:00 |
|  5 | web      | user1 | 09:15:00 | 09:30:00 |
|  6 | web      | user2 | 10:30:00 | 14:30:00 |
|  7 | web      | user1 | 10:45:00 | 11:30:00 |
|  8 | web      | user2 | 11:00:00 | 12:30:00 |
|  9 | app      | user1 | 08:30:00 | 08:45:00 |
| 10 | app      | user2 | 09:00:00 | 09:30:00 |
| 11 | app      | user1 | 11:45:00 | 12:00:00 |
| 12 | app      | user2 | 12:30:00 | 14:00:00 |
| 13 | app      | user1 | 12:45:00 | 13:30:00 |
| 14 | app      | user2 | 13:00:00 | 14:00:00 |
| 15 | app      | user1 | 14:00:00 | 16:30:00 |
| 16 | app      | user2 | 15:30:00 | 17:00:00 |
+----+----------+-------+----------+----------+

我們的站點(diǎn)有兩個(gè)入口:web端和app端

很明顯抚芦,這些數(shù)據(jù)時(shí)間上是有重疊的倍谜,例如user1在08:30~10:30這個(gè)時(shí)間段內(nèi)用三個(gè)web端和一個(gè)app端登錄,但他的時(shí)長(zhǎng)應(yīng)該只能算作2個(gè)小時(shí)

要正確統(tǒng)計(jì)叉抡,我們需要三步:

  1. 獲取用戶的重疊時(shí)間段中最早的起始時(shí)間
  2. 獲取用戶的重疊時(shí)間段中最晚的結(jié)束時(shí)間
  3. 前兩步的起始時(shí)間與結(jié)束時(shí)間合并尔崔,得到綜合的時(shí)間段,再進(jìn)行統(tǒng)計(jì)

第一步:

查詢用戶名褥民、起始時(shí)間
其中起始時(shí)間需要滿足的條件是:不包含于其它時(shí)間段季春,即不存在一條記錄來滿足條件“s>start且s<=end”
得到的結(jié)果可能存在重復(fù),所以用distinct去重
這樣找到的s就是用戶重疊時(shí)間段的最早時(shí)間消返。如下:

mysql> select distinct usr,start from user_sessions as a where not exists(select * from user_sessions as b where a.usr=b.usr and a.start>b.start and a.start<=b.end);
+-------+----------+
| usr   | start    |
+-------+----------+
| user1 | 08:30:00 |
| user2 | 08:30:00 |
| user1 | 10:45:00 |
| user2 | 09:00:00 |
| user1 | 11:45:00 |
| user1 | 12:45:00 |
| user1 | 14:00:00 |
| user2 | 15:30:00 |
+-------+----------+

第二步:

查詢用戶名鹤盒、結(jié)束時(shí)間
這步于第一步一樣道理,結(jié)束時(shí)間滿足的條件是:不包含于其它時(shí)間段侦副,即不存在一條記錄滿足“e>=start且e<end”
結(jié)果如下:

mysql> select distinct usr,end from user_sessions as a where not exists(select * from user_sessions as b where a.usr=b.usr and a.end>=b.start and a.end<b.end);
+-------+----------+
| usr   | end      |
+-------+----------+
| user1 | 10:30:00 |
| user2 | 08:45:00 |
| user2 | 14:30:00 |
| user1 | 11:30:00 |
| user1 | 12:00:00 |
| user1 | 13:30:00 |
| user1 | 16:30:00 |
| user2 | 17:00:00 |
+-------+----------+

第三步:

合并前兩步的結(jié)果
為了方便查詢和敘述侦锯,我們將前兩步建立對(duì)應(yīng)的視圖:

mysql> create view v_s as select distinct usr,start from user_sessions as a where not exists(select * from user_sessions as b where a.usr=b.usr and a.start>b.start and a.start<=b.end);

mysql> create view v_e as select distinct usr,end from user_sessions as a where not exists(select * from user_sessions as b where a.usr=b.usr and a.end>=b.start and a.end<b.end);

查看v_s和v_e兩個(gè)視圖:

mysql> select * from v_s;
+-------+----------+
| usr   | start    |
+-------+----------+
| user1 | 08:30:00 |
| user2 | 08:30:00 |
| user1 | 10:45:00 |
| user2 | 09:00:00 |
| user1 | 11:45:00 |
| user1 | 12:45:00 |
| user1 | 14:00:00 |
| user2 | 15:30:00 |
+-------+----------+

mysql> select * from v_e;
+-------+----------+
| usr   | end      |
+-------+----------+
| user1 | 10:30:00 |
| user2 | 08:45:00 |
| user2 | 14:30:00 |
| user1 | 11:30:00 |
| user1 | 12:00:00 |
| user1 | 13:30:00 |
| user1 | 16:30:00 |
| user2 | 17:00:00 |
+-------+----------+

可以看到,兩個(gè)視圖總行數(shù)是相等的秦驯,但并不是按行對(duì)應(yīng)的尺碰,所以并不是單純的將兩個(gè)結(jié)果橫向合并!

方案一:

對(duì)于v_s中的起始時(shí)間译隘,我們應(yīng)該在v_e中找“usr相同且end>start”的最小值
例如亲桥,對(duì)于v_s中user1起始時(shí)間為08:30:00,我們應(yīng)該找到v_e中end>08:30:00且usr=user1的記錄固耘,然后取min得到結(jié)果為10:30:00作為與之對(duì)應(yīng)的結(jié)束時(shí)間
SQL及結(jié)果如下:

mysql> select distinct v_s.usr,v_s.start,(select min(end) from v_e where v_e.end>v_s.start and v_e.usr=v_s.usr) as end from v_s,v_e where v_s.usr=v_e.usr;
+-------+----------+----------+
| usr   | start    | end      |
+-------+----------+----------+
| user1 | 08:30:00 | 10:30:00 |
| user2 | 08:30:00 | 08:45:00 |
| user1 | 10:45:00 | 11:30:00 |
| user2 | 09:00:00 | 14:30:00 |
| user1 | 11:45:00 | 12:00:00 |
| user1 | 12:45:00 | 13:30:00 |
| user1 | 14:00:00 | 16:30:00 |
| user2 | 15:30:00 | 17:00:00 |
+-------+----------+----------+

在此基礎(chǔ)上题篷,按usr進(jìn)行分組,統(tǒng)計(jì)用戶的總時(shí)長(zhǎng)厅目,得到最終結(jié)果:

mysql> select usr,sec_to_time(sum(timestampdiff(second,start,end))) as time from (select distinct v_s.usr,v_s.start,(select min(end) from v_e where v_e.end>v_s.start and v_e.usr=v_s.usr) as end from v_s,v_e where v_s.usr=v_e.usr) as c group by usr;
+-------+----------+
| usr   | time     |
+-------+----------+
| user1 | 06:15:00 |
| user2 | 07:15:00 |
+-------+----------+

其中番枚,timestampdiff函數(shù)是計(jì)算兩個(gè)時(shí)間的差值,第一個(gè)參數(shù)用來指定其單位损敷,這里指定為秒葫笼;
sec_to_time函數(shù)是將求和后的秒數(shù)再轉(zhuǎn)為時(shí)間格式來顯示

方案二

既然v_s與v_e的數(shù)據(jù)不是按行對(duì)應(yīng)的,那么我們可以對(duì)其排序拗馒,使其按行對(duì)應(yīng)后路星,就可以直接按行合并
為了按行合并,我們同時(shí)加上行號(hào)诱桂,讓兩個(gè)子查詢行號(hào)匹配即可:

mysql> set @sno=0;
mysql> set @eno=0;
mysql> select s.usr,s.start,e.end from (select @sno:=@sno+1 as sno,usr,start from v_s order by usr,start) as s, (select @eno:=@eno+1 as eno,usr,end from v_e order by usr,end) as e where s.sno=e.eno;
+-------+----------+----------+
| usr   | start    | end      |
+-------+----------+----------+
| user1 | 08:30:00 | 10:30:00 |
| user1 | 10:45:00 | 11:30:00 |
| user1 | 11:45:00 | 12:00:00 |
| user1 | 12:45:00 | 13:30:00 |
| user1 | 14:00:00 | 16:30:00 |
| user2 | 08:30:00 | 08:45:00 |
| user2 | 09:00:00 | 14:30:00 |
| user2 | 15:30:00 | 17:00:00 |
+-------+----------+----------+

之后的分組統(tǒng)計(jì)與方案一相同

擴(kuò)展

實(shí)際場(chǎng)景中洋丐,我們經(jīng)常將不同的方式的在線時(shí)長(zhǎng)加上獎(jiǎng)勵(lì)倍數(shù)呈昔,來鼓勵(lì)用戶更多的使用某種入口。
例如友绝,我們?yōu)榱俗層脩舾嗟氖褂胊pp登錄韩肝,規(guī)定app的在線時(shí)長(zhǎng)獎(jiǎng)勵(lì)2倍,即app在線一小時(shí)相當(dāng)于web在線兩小時(shí)九榔。
這時(shí),我們就需要將不同的登錄方式區(qū)分開來統(tǒng)計(jì)涡相。要區(qū)分開也很簡(jiǎn)單哲泊,只需要添加SQL條件讓platform相同即可,例如第一步修改為:

mysql> select distinct usr,platform,start from user_sessions as a where not exists(select * from user_sessions as b where a.usr=b.usr and a.platform=b.platform and a.start>b.start and a.start<=b.end);
+-------+----------+----------+
| usr   | platform | start    |
+-------+----------+----------+
| user1 | web      | 08:30:00 |
| user2 | web      | 08:30:00 |
| user2 | web      | 09:15:00 |
| user1 | web      | 10:45:00 |
| user1 | app      | 08:30:00 |
| user2 | app      | 09:00:00 |
| user1 | app      | 11:45:00 |
| user2 | app      | 12:30:00 |
| user1 | app      | 12:45:00 |
| user1 | app      | 14:00:00 |
| user2 | app      | 15:30:00 |
+-------+----------+----------+

之后的步驟類似催蝗,在最終統(tǒng)計(jì)時(shí)分別統(tǒng)計(jì)web和app的時(shí)長(zhǎng)切威,并將app的時(shí)長(zhǎng)乘以2再與web時(shí)長(zhǎng)相加即可,不再贅述丙号。

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末先朦,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子犬缨,更是在濱河造成了極大的恐慌喳魏,老刑警劉巖,帶你破解...
    沈念sama閱讀 211,817評(píng)論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件怀薛,死亡現(xiàn)場(chǎng)離奇詭異刺彩,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)枝恋,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,329評(píng)論 3 385
  • 文/潘曉璐 我一進(jìn)店門创倔,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人焚碌,你說我怎么就攤上這事畦攘。” “怎么了十电?”我有些...
    開封第一講書人閱讀 157,354評(píng)論 0 348
  • 文/不壞的土叔 我叫張陵知押,是天一觀的道長(zhǎng)。 經(jīng)常有香客問我鹃骂,道長(zhǎng)朗徊,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,498評(píng)論 1 284
  • 正文 為了忘掉前任偎漫,我火速辦了婚禮爷恳,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘象踊。我一直安慰自己温亲,他們只是感情好棚壁,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,600評(píng)論 6 386
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著栈虚,像睡著了一般袖外。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上魂务,一...
    開封第一講書人閱讀 49,829評(píng)論 1 290
  • 那天曼验,我揣著相機(jī)與錄音,去河邊找鬼粘姜。 笑死鬓照,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的孤紧。 我是一名探鬼主播豺裆,決...
    沈念sama閱讀 38,979評(píng)論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼号显!你這毒婦竟也來了臭猜?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 37,722評(píng)論 0 266
  • 序言:老撾萬榮一對(duì)情侶失蹤押蚤,失蹤者是張志新(化名)和其女友劉穎蔑歌,沒想到半個(gè)月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體揽碘,經(jīng)...
    沈念sama閱讀 44,189評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡丐膝,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,519評(píng)論 2 327
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了钾菊。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片帅矗。...
    茶點(diǎn)故事閱讀 38,654評(píng)論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖煞烫,靈堂內(nèi)的尸體忽然破棺而出浑此,到底是詐尸還是另有隱情,我是刑警寧澤滞详,帶...
    沈念sama閱讀 34,329評(píng)論 4 330
  • 正文 年R本政府宣布凛俱,位于F島的核電站,受9級(jí)特大地震影響料饥,放射性物質(zhì)發(fā)生泄漏蒲犬。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,940評(píng)論 3 313
  • 文/蒙蒙 一岸啡、第九天 我趴在偏房一處隱蔽的房頂上張望原叮。 院中可真熱鬧,春花似錦、人聲如沸奋隶。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,762評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)唯欣。三九已至嘹吨,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間境氢,已是汗流浹背蟀拷。 一陣腳步聲響...
    開封第一講書人閱讀 31,993評(píng)論 1 266
  • 我被黑心中介騙來泰國(guó)打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留萍聊,地道東北人问芬。 一個(gè)月前我還...
    沈念sama閱讀 46,382評(píng)論 2 360
  • 正文 我出身青樓,卻偏偏與公主長(zhǎng)得像脐区,于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子她按,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,543評(píng)論 2 349

推薦閱讀更多精彩內(nèi)容