大数据第41天—Mysql练习题12-游戏玩法分析-杨大伟
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了大数据第41天—Mysql练习题12-游戏玩法分析-杨大伟,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3915字,纯文字阅读大概需要6分钟。
内容图文
需求一:写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。
Activity表:显示了某些游戏的玩家的活动情况。
player_id | device_id | event_date | games_played |
---|---|---|---|
1 | 2 | 2016-03-01 | 5 |
1 | 2 | 2016-05-02 | 6 |
2 | 3 | 2017-06-25 | 1 |
3 | 1 | 2016-03-02 | 0 |
3 | 4 | 2018-07-03 | 5 |
展示效果:
player_id | first_login |
---|---|
1 | 2016-03-01 |
2 | 2017-06-25 |
3 | 2016-03-02 |
1 Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int); 23insertinto Activity (player_id, device_id, event_date, games_played) values (1, 2, ‘2016-03-01‘, 5); 4insertinto Activity (player_id, device_id, event_date, games_played) values (1, 2, ‘2016-05-02‘, 6); 5insertinto Activity (player_id, device_id, event_date, games_played) values (2, 3, ‘2017-06-25‘, 1); 6insertinto Activity (player_id, device_id, event_date, games_played) values (3, 1, ‘2016-03-02‘, 0); 7insertinto Activity (player_id, device_id, event_date, games_played) values (3, 4, ‘2018-07-03‘, 5);
最终SQL:
1 select 2 player_id, 3 min(event_date) as first_login 4from5 Activity 6groupby7 player_id;
需求二 :描述每一个玩家首次登陆的设备名称
player_id | device_id |
---|---|
1 | 2 |
2 | 3 |
3 | 1 |
最终SQL:
1 select 2 player_id, 3 device_id 4 from 5 (select* 6from 7 Activity 8where 9 (player_id,event_date) in (select10 player_id, 11 device_id 12min(event_date) 13from14 Activity 15groupby16 player_id 17 ) 18 ) as t;
需求三 :编写一个 SQL 查询,同时报告每组玩家和日期,以及玩家到目前为止玩了多少游戏。也就是说,在此日期之前玩家所玩的游戏总数。详细情况请查看示例。
player_id | event_date | games_played_so_far |
---|---|---|
1 | 2016-03-01 | 5 |
1 | 2016-05-02 | 11 |
2 | 2017-06-25 | 1 |
3 | 2016-03-02 | 0 |
3 | 2018-07-03 | 5 |
提示:对于 ID 为 3 的玩家,2018-07-03 共玩了 0+5=5 个游戏。
最终SQL:
1 // 方法一 2 SELECT C.player_id,C.event_date,C.games_played_so_far 3 FROM ( 4 SELECT 5 A.player_id, 6 A.event_date, 7 @sum_cnt:= 8if(A.player_id =@pre_idAND A.event_date !=@pre_date, 9@sum_cnt+ A.games_played, 10 A.games_played 11 ) 12AS `games_played_so_far`, 13@pre_id:=A.player_id AS `player_ids`, 14@pre_date:=A.event_date AS `event_dates` 15FROM16 activity AS A, 17 (SELECT@pre_id:=NULL,@pre_date:=NULL,@sum_cnt:=0) AS B 18orderBY19 A.player_id,A.event_date 20 ) AS C 2122//方法二 23SELECT24 B.player_id, 25 B.event_date, 26SUM(A.games_played) AS `games_played_so_far` 27FROM28 Activity AS A 29JOIN30 Activity AS B 31ON32 A.player_id = B.player_id 33AND A.event_date <= B.event_date 34GROUPBY35 B.player_id,B.event_date;
需求四 :编写一个 SQL 查询,报告在首次登录的第二天再次登录的玩家的百分比,四舍五入到小数点后两位。换句话说,您需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。
fraction |
---|
0.00 |
提示:对于 ID 为 1 的玩家,2016-05-02 共玩了 5+6=11 个游戏. 对于 ID 为 3 的玩家,2018-07-03 共玩了 0+5=5 个游戏。
最终SQL:
1 select 2 round ( 3 sum(casewhendatediff(a.event_date,b.first_date)=1then1else0end) 4/ 5 (selectcount(distinct(player_id)) from activity) 6 ,2 ) as fraction 7from 8 activity a, 9 (select10 player_id, 11min(event_date) first_date 12from13 activity 14groupby15 player_id 16 ) b 17where18 a.player_id=b.player_id;
需求五 :编写一个 SQL 查询,报告每个安装日期、当天安装游戏的玩家数量和第一天的保留时间。
install_dt | installs | Day1_retention |
---|---|---|
2016-03-01 | 2 | 0.50 |
2017-06-25 | 1 | 0.00 |
提示:玩家 1 和 3 在 2016-03-01 安装了游戏,但只有玩家 1 在 2016-03-02 重新登录,所以 2016-03-01 的第一天保留时间是 1/2=0.50玩家 2 在 2017-06-25 安装了游戏,但在 2017-06-26 没有重新登录,因此 2017-06-25 的第一天保留为 0/1=0.00
最终SQL:
1 #方法一 2 SELECT 3 A.install_date, 4 COUNT(A.player_id) AS `installs`, 5COUNT(AA.player_id) AS `return_cnt` 6FROM 7 (SELECT 8 player_id, 9MIN(event_date) AS `install_date` 10FROM11 Activity 12GROUPBY13 player_id 14 ) AS A 15leftJOIN16 Activity AS AA 17ON18 AA.event_date = DATE_ADD(A.install_date,INTERVAL 1DAY) AND AA.player_id = A.player_id 19GROUPBY20 A.install_date; 2122#方法二 23SELECT24 A.event_date AS `install_dt`, 25COUNT(A.player_id) AS `installs`, 26round(COUNT(C.player_id)/COUNT(A.player_id),2) AS `Day1_retention` 27FROM28 Activity AS A 29leftJOIN30 Activity AS B 31ON32 A.player_id = B.player_id AND A.event_date > B.event_date 33leftJOIN34 Activity AS C 35ON36 A.player_id = C.player_id AND C.event_date = DATE_ADD(A.event_date,INTERVAL 1DAY) 37WHERE38 B.event_date ISNULL39GROUPBY40 A.event_date;
原文:https://www.cnblogs.com/shui68home/p/13525961.html
内容总结
以上是互联网集市为您收集整理的大数据第41天—Mysql练习题12-游戏玩法分析-杨大伟全部内容,希望文章能够帮你解决大数据第41天—Mysql练习题12-游戏玩法分析-杨大伟所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。