前言：
- 本篇进阶练习题均来自真实的业务需求场景，SQL难度不高，但要求较为清晰的业务逻辑和对游戏项目的深入了解。
- 如果对于SQL不熟悉可以看我的这两篇文章：
- 数据本身已作脱敏处理，不可尽信。
例1:
数值策划想验证线上版本新玩家的升级和破产情况是否符合设计预期，故需查找2020.1.1-2020.2.5期间玩家首次破产时的等级的平均值，并区分应用版本$app_version、iOS和GooglePlay渠道channel。
由于存在一些小bug会导致部分玩家经验曲线有问题，所以只筛选首次破产在100级以下的玩家，以避免极端值影响平均值的计算。
数据表及字段如下：
/*events表*/
user_id event lv date channel $app_version
-----------------------------------------------------------------------------
玩家id 事件名 等级 日期 iOS或GooglePlay 应用版本
NUMBER STRING NUMBER DATE STRING STRING
-----------------------------------------------------------------------------
111 Loading 1 2020-01-01 iOS 5.3.1
222 EnterGame 2 2020-01-01 iOS 5.3.11
333 Click_1 2 2020-01-01 GooglePlay 5.2.10
333 Bankruptcy 28 2020-02-01 GooglePlay 5.3.1
... ... ... ... ... ...
/*users表*/
id channel lv user_group_3 user_group_10
------------------------------------------------------------------------------
玩家id iOS或GooglePlay 等级 用户分群1 用户分群2
NUMBER STRING NUMBER BOOL BOOL
------------------------------------------------------------------------------
111 iOS 46 0 1
222 iOS 149 0 1
333 GooglePlay 87 1 1
444 GooglePlay 34 0 0
... ... ... ... ...
注：
- users表中的user_group_10是事先已定义好分群的研究对象，取值为真时生效。user_group_3是测试机分群，取值为假时生效。
参考语句：
select channel,$app_version,avg(minlv) from
(select user_id,min(events.lv) as minlv
from users,events
where events.user_id = users.id
and event = 'Bankruptcy'
and events.channel in ('iOS','GooglePlay')
and date between '2020-01-01' and '2020-02-05'
and user_group_10 = 1 and user_group_3 = 0
group by user_id
having minlv <= 100)a
left join
(select user_id,events.channel,$app_version,events.lv
from users,events
where events.user_id = users.id
and event = 'Bankruptcy'
and events.channel in ('iOS','GooglePlay')
and date between '2020-01-01' and '2020-02-05'
and user_group_10 = 1 and user_group_3 = 0)b
on a.minlv = b.lv and a.user_id = b.user_id
group by channel,$app_version
例2：
礼包促销期间，玩家反馈自己购买的礼包并没有如宣传弹窗上所说得到双倍金币。经查证打点信息，该名玩家的确花了$29.99购买了原价礼包，且版本更新到了最新版本，即能下载到活动热更。
现在想统计购买同类型礼包的玩家中，有多少玩家遇到与该玩家相同的情形及占比，以便确认补偿方式，是由运营极个别手动补充，还是需要服务端程序写脚本批量补充，或者干脆对全体付费玩家补偿。
同时为了帮助开发人员定位问题所在，所以按版本查看，看是哪个版本开始出现问题。
数据表及字段如下：
/*events表*/
user_id event str2 str9 date channel $app_version
------------------------------------------------------------------------------------------------
玩家id 事件名 商品id 是否促销 日期 iOS或GooglePlay 应用版本
NUMBER STRING STRING STRING DATE STRING STRING
------------------------------------------------------------------------------------------------
111 Loading 2020-01-01 iOS 3.8.0
222 Buy 6 False 2020-01-01 iOS 3.7.0
333 Click_1 2020-01-01 GooglePlay 3.7.0
333 Buy 49 True 2020-02-01 GooglePlay 3.6.0
... ... ... ... ... ...
注：
- 由于埋点时没注意，促销礼包商品id定义成了STRING格式，id在0~6之间，以及48以上的属于和该玩家同类型的礼包。
参考语句：
select $app_version,str9,sum(num) from(
select $app_version,str9,str2,count(event) as num
from events
where event = 'Buy'
and channel in ('iOS','GooglePlay')
and $app_version in (‘3.6.0’, '3.7.0','3.8.0','3.9.0')
and date >= '2020-02-25' and date <= '2020-02-29'
group by $app_version,str9,str2
order by str9)a
where cast(str2 as integer) > 48 or cast(str2 as integer) <= 6
group by $app_version,str9
order by $app_version,str9
例3：
某款游戏做了关于前期关卡难度的AB测试，现需评估AB版本的关卡难度是否符合我们的预期。
关卡难度用过关率和通关率评估，其中
通关率 = 该关卡通关人数 / 该关卡挑战总人数
过关率 = 该关卡过关次数（不含c道具） / 该关卡挑战总次数
在此需要说明的几点：
- 过关率原则上需使用
该关卡过关次数（不含任何道具） / 该关卡挑战总次数 （不含任何道具）来进行计算。但由于后期关卡难度较大，若玩家不使用道具则难以通关，从而造成样本量过小而过关率数据失去意义。在这个栗子中，道具c的使用对过关率数据影响最大，且严格来讲玩家只有在通关失败时才会触发道具c的使用，因此使用道具c通关也视为失败，这里需排除使用道具c打通关的情形； - 通关率能从侧面反映卡级和关卡流失人数；
涉及的事件、字段及其含义如下：
/*events表*/
user_id event date level version int1
-----------------------------------------------------------------
玩家id 事件名 日期 关卡id 游戏版本 是否使用道具c
NUMBER STRING DATE NUMBER STRING NUMBER
-----------------------------------------------------------------
7863 LevelStart 2019-08-28 12 2.1.10
7937 LevelStart 2019-08-28 34 2.1.10
7389 LevelSuccess 2019-08-28 14 2.1.10 0
7863 LevelSuccess 2019-08-28 12 2.1.10 1
7364 LevelFail 2019-08-28 56 2.1.10 1
... ... ... ... ... ...
/*users表*/
id user_group_50 abtest_id
----------------------------------------------
玩家id 用户分群 ab版本号
NUMBER BOOL STRING
----------------------------------------------
7863 1 a
7937 0 a
7389 1 b
7364 1 a
... ... ...
注：
Ⅰ. events表中只有LevelSuccess/LevelFail有int1字段信息，LevelStart的int1字段为空；int1 = 1表示使用了道具c，int1 = 0表示未使用道具c；
Ⅱ. users表中的user_group_50为选定时间段内的新玩家分群，这个分群已排除了测试机、老用户换新设备等数据干扰，写SQL时选择user_group_50的值为真即可；
Ⅲ. users表中的abtest_id仅有两种取值，a和b，分别代表a版本和b版本；
参考语句：
-
a版本的过关率统计
select T1.level,T1.LevelTry,T2.LevelPass,levelpass/leveltry as SuccessRate,T1.PlayerNum from (select level, count(event)as LevelTry,count(distinct user_id)as PlayerNum from events where event ='LevelStart' and version = '2.1.10' and date >= '2019-08-28' and user_id in (select id from users where user_group_50 = 1 and abtest_id = 'a') group by level) as T1 left join (select level, count(event)as LevelPass from events where event ='LevelSuccess' and version = '2.1.10' and int1 = 0 and date >= '2019-08-28' and user_id in (select id from users where user_group_50 = 1 and abtest_id = 'a') group by level) as T2 on T1.level = T2.level order by level -
a版本的通关率统计
select T1.level,T1.LevelEnter,T2.LevelPass, levelpass/levelenter as Passrate from (select level, count(distinct user_id)as LevelEnter from events where event = 'LevelStart' and version = '2.1.10' and date>= '2019-08-28' and user_id in (select id from users where user_group_50 = 1 and abtest_id = 'a') group by level) as T1 left join (select level, count(distinct user_id)as LevelPass from events where event ='LevelSuccess' and version = '2.1.10' and date>= '2019-08-28' and user_id in (select id from users where user_group_50 = 1 and abtest_id = 'a') group by level) as T2 on T1.level = T2.level order by T1.level -
b版本的过关率&通关率统计同上，只需将users表中abtest_id字段的值改为b，其SQL语句略
例4：
通过每日数据监测，发现某款游戏收入异常下跌。对照异常数据始发时间和多维度分析，定位为4.3.1版本发布后出现的问题。按照业务经验推测，收入异常下跌往往和游戏内资产失衡挂钩，check玩家资产监测数据，果真发现4.3.1版本发布后玩家资产上涨异常。
现需找出这部分资产上涨异常的玩家行为事件，请数值策划确认是否有BUG。
（资产上涨异常视具体游戏而定，这里的资产上涨异常定义为前3~2d每日首次登录资产数不足1kw的玩家，在昨日资产上涨2个数量级，即超过10亿。这在数值设计看来是不合理的。）
数据表形式如下：
/*events表*/
user_id event time payamount coins lv viplevel str0 str1 str2 str3 int0 int1 int2 int3 double0 version
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
玩家id 事件名 时间 累计付费额 虚拟资产 等级 vip等级 str0 str1 str2 str3 int0 int1 int2 int3 double0 游戏版本
NUMBER STRING DATETIME NUMBER NUMBER NUMBER NUMBER STRING STRING STRING STRING NUMBER NUMBER NUMBER NUMBER NUMBER STRING
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8977 loading 2020-04-03 0 93038272 132 0 1 4.3.1
7383 Loading 2020-04-03 0 73839282233 68 0 0 4.2.5
7223 Pay 2020-04-04 99.99 73822 456 3 0 4.99 4.3.1
7362 Click_1 2020-04-04 0 32202746 7 0 0 4.2.4
8383 Click_2 2020-04-04 19.89 8472822 31 1 0 4.2.4
7364 Loading 2020-04-05 0 17374 98 0 1 4.3.1
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
注：
- str0 ~ str3，int0 ~ int3在不同的event中表示不同的含义；在玩家登录，即Loading事件中， 仅int1字段有值，表示玩家当日第几次登录游戏，int1 = 1表示玩家当日首次登录游戏；
参考语句：
select user_id, event, time, payamount, coins, lv, viplevel,
str0, str1, str2, str3, int0, int1, int2, int3, double0, version
from events
where user_id in
(select user_id from events
where event = 'Loading' and int1 = 1
and coins > power(10, 9)
and date = CURRENT_DATE() - INTERVAL '1' DAY
and user_id in
(select user_id from events
where event = 'Loading' and int1 = 1
and credit < power(10, 7)
and date between CURRENT_DATE() - INTERVAL '3' DAY and CURRENT_DATE() - INTERVAL '2' DAY
)
)
and date >= CURRENT_DATE() - INTERVAL '3' DAY
ORDER BY user_id, time
例5：
待更新。。。
577
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
