當前位置:網站首頁>遊戲行業實戰案例5:玩家在線分布

遊戲行業實戰案例5:玩家在線分布

2022-05-13 23:59:03猴子數據分析

da000404d3899afb047d7653ec095a9b.png

【面試題】某遊戲數據後臺設有“登錄日志”和“登出日志”兩張錶。

“登錄日志”記錄各玩家的登錄時間和登錄時的角色等級。 

47acf338ac78502eb755449ce946ca0a.png

“登出日志”記錄各玩家的登出時間和登出時的角色等級。

efd55a7fbd9af56dd8f0809b119aec84.png

其中,“角色id”字段唯一識別玩家。

遊戲開服前兩天(2022-08-13至2022-08-14)的角色登錄和登出日志如下

a12d746543ace612e7afa8c69fba0582.png

9e1f38990ea637e4f346de4d7e4ed185.png

一天中,玩家可以多次登錄登出遊戲,請使用SQL分析出以下業務問題:

請根據玩家登錄登出的時間,統計在開服首日各玩家在線時長分布。

(如玩家登錄後沒有對應的登出日志,可以使用當天23:59:59作為登出時間,時間之間的計算可以考慮使用時間戳函數unix_timestamp。【區分在線時間段:0-30min,30min-1h,1-2h,2-3h,3-5h,5h以上;區間為左閉右開】)

問題5:

統計在開服首日各玩家在線時長分布,其中區分在線時間段:0-30min,30min-1h,1-2h,2-3h,3-5h,5h以上,區間為左閉右開,解釋為大白話即為:統計2022-08-13,在線時間段在0-30min、30min-1h、1-2h、2-3h、3-5h、5h以上的玩家各有多少人。

統計人數使用count()函數,而玩家的在線時間段可以用case when子句進行條件判斷,即使用case when子句判斷各玩家的總在線時長在哪個在線時間段內:

case when 總在線時長_min>=0 and 總在線時長_min<30 then '0-30min'
when 總在線時長_min>=30 and 總在線時長_min<60 then '30min-1h'
when 總在線時長_min>=60 and 總在線時長_min<120 then '1-2h'
when 總在線時長_min>=120 and 總在線時長_min<180 then '2-3h'
when 總在線時長_min>=180 and 總在線時長_min<300 then '3-5h'
else '5h以上' end

將問題4中統計各玩家每天的總在線時長的查詢結果設為臨時錶d,則判斷開服首日,各玩家的總在線時長在哪個在線時間段內的SQL的書寫方法:

select 角色id,
       (case when 總在線時長_min>=0 and 總在線時長_min<30 then '0-30min'
        when 總在線時長_min>=30 and 總在線時長_min<60 then '30min-1h'
        when 總在線時長_min>=60 and 總在線時長_min<120 then '1-2h'
        when 總在線時長_min>=120 and 總在線時長_min<180 then '2-3h'
        when 總在線時長_min>=180 and 總在線時長_min<300 then '3-5h'
        else '5h以上' end) as 在線時間段
from d
where 日期 = '2022-08-13';

利用with…as語句來封裝臨時錶d的查詢語句,則SQL的書寫方法:

with d as
(with c as
(select a.角色id,a.日期,a.登錄時間,
        (case when b.登出時間 is null then concat(a.日期,'23:59:59') else b.登出時間 end) as 登出時間
from
(select 角色id,日期,登錄時間,rank() over(partition by 角色id,日期 order by 登錄時間 asc) as 登錄排名
from 登錄日志) as a
left join
(select 角色id,日期,登出時間,rank() over(partition by 角色id,日期 order by 登出時間 asc) as 登出排名
from 登出日志) as b
on a.角色id = b.角色id and a.日期 = b.日期 and a.登錄排名 = b.登出排名
)
select 角色id,日期,
sum(round((unix_timestamp(登出時間)- unix_timestamp(登錄時間))/60,2)) as 總在線時長_min
from c
group by 角色id,日期
)
select 角色id,
       (case when 總在線時長_min>=0 and 總在線時長_min<30 then '0-30min'
        when 總在線時長_min>=30 and 總在線時長_min<60 then '30min-1h'
        when 總在線時長_min>=60 and 總在線時長_min<120 then '1-2h'
        when 總在線時長_min>=120 and 總在線時長_min<180 then '2-3h'
        when 總在線時長_min>=180 and 總在線時長_min<300 then '3-5h'
        else '5h以上' end) as 在線時間段
from d
where 日期 = '2022-08-13';

查詢結果如下:

bf1c1fb8daa47d32a1d797ed77d19b3b.png

現在我們來計算各在線時間段的玩家人數,同樣,使用group by子句和count()函數即可實現。

將上述查詢結果設為臨時錶e,則SQL的書寫方法:

select 在線時間段,count(角色id) as 玩家人數
from e
group by 在線時間段;

將臨時錶e的查詢語句代入,則SQL的書寫方法:

with d as
(with c as
(select a.角色id,a.日期,a.登錄時間,
        (case when b.登出時間 is null then concat(a.日期,'23:59:59') else b.登出時間 end) as 登出時間
from
(select 角色id,日期,登錄時間,rank() over(partition by 角色id,日期 order by 登錄時間 asc) as 登錄排名
from 登錄日志) as a
left join
(select 角色id,日期,登出時間,rank() over(partition by 角色id,日期 order by 登出時間 asc) as 登出排名
from 登出日志) as b
on a.角色id = b.角色id and a.日期 = b.日期 and a.登錄排名 = b.登出排名
)
select 角色id,日期,
sum(round((unix_timestamp(登出時間)- unix_timestamp(登錄時間))/60,2)) as 總在線時長_min
from c
group by 角色id,日期
)
select 在線時間段,count(角色id) as 玩家人數
from
(select 角色id,
       (case when 總在線時長_min>=0 and 總在線時長_min<30 then '0-30min'
        when 總在線時長_min>=30 and 總在線時長_min<60 then '30min-1h'
        when 總在線時長_min>=60 and 總在線時長_min<120 then '1-2h'
        when 總在線時長_min>=120 and 總在線時長_min<180 then '2-3h'
        when 總在線時長_min>=180 and 總在線時長_min<300 then '3-5h'
        else '5h以上' end) as 在線時間段
from d
where 日期 = '2022-08-13'
) as e
group by 在線時間段;

查詢結果如下:

422baeadf4ba201650a645d1bb1d6b22.png

可以看到,雖然我們已經得到了各在線時間段的玩家人數,但是在線時間段的排列是亂序的,查看分布情况不是很方便。因此,我們需要對在線時間段進行重新排序。

“在線時間段”這一列數據類型為字符串,無法用order by子句進行簡單排序,那麼如何對在線時間段進行重新排序呢?

可以使用field()函數。field()函數是自定義排序函數,可以自定義排列順序,使用方法為:

order by field(值,str1,str2,str3,str4,……,strn) asc/desc

意思為:

將值按照str1,str2,str3,str4,……,strn的順序昇序(asc)或者降序排列(desc)。

將其應用在本問題中,則為:

order by field(在線時間段,'0-30min','30min-1h','1-2h','2-3h','3-5h','5h以上') asc

即:將在線時間段這一列的值按照'0-30min','30min-1h','1-2h','2-3h','3-5h','5h以上'的順序昇序排列。

將其代入上述SQL語句中,則統計開服首日,玩家的在線時長分布的完整SQL的書寫方法為:

with d as
(with c as
(select a.角色id,a.日期,a.登錄時間,
        (case when b.登出時間 is null then concat(a.日期,'23:59:59') else b.登出時間 end) as 登出時間
from
(select 角色id,日期,登錄時間,rank() over(partition by 角色id,日期 order by 登錄時間 asc) as 登錄排名
from 登錄日志) as a
left join
(select 角色id,日期,登出時間,rank() over(partition by 角色id,日期 order by 登出時間 asc) as 登出排名
from 登出日志) as b
on a.角色id = b.角色id and a.日期 = b.日期 and a.登錄排名 = b.登出排名
)
select 角色id,日期,
sum(round((unix_timestamp(登出時間)- unix_timestamp(登錄時間))/60,2)) as 總在線時長_min
from c
group by 角色id,日期
)
select 在線時間段,count(角色id) as 玩家人數
from
(select 角色id,
       (case when 總在線時長_min>=0 and 總在線時長_min<30 then '0-30min'
        when 總在線時長_min>=30 and 總在線時長_min<60 then '30min-1h'
        when 總在線時長_min>=60 and 總在線時長_min<120 then '1-2h'
        when 總在線時長_min>=120 and 總在線時長_min<180 then '2-3h'
        when 總在線時長_min>=180 and 總在線時長_min<300 then '3-5h'
        else '5h以上' end) as 在線時間段
from d
where 日期 = '2022-08-13'
) as e
group by 在線時間段
order by field(在線時間段,'0-30min','30min-1h','1-2h','2-3h','3-5h','5h以上') asc;

查詢結果如下:

10005b8505dc9a1228ba8341b652eeeb.png

【本題考點】

1、考察邏輯分析能力,即:如何將複雜問題拆解成容易解决的一個個子問題的能力;

2、考察排序窗口函數的靈活使用。在需要進行分組排序時,排序窗口函數往往是首選;

3、考察case when語句的靈活應用以及分組匯總時,group by子句、聚合函數的搭配使用;

4、考察縱向聯結和橫向聯結的使用。縱向聯結使用union方法(union、union all),橫向聯結使用join方法(left join、innerjoin、right join);

5、考察多重子查詢的應用以及with…as語句的應用。

案例數據下載途徑:

e1a1d43b0ec4e32e023723cb84ea8652.png

26bd787de44a21fa4e8d18710f4a0d41.png

 ️點擊「閱讀原文」

 免費報名 數據分析訓練營

版權聲明
本文為[猴子數據分析]所創,轉載請帶上原文鏈接,感謝
https://cht.chowdera.com/2022/133/202205132353158161.html

隨機推薦