SQL学习笔记项目篇——游戏案例

SQL学习笔记１——SQL基础

SQL学习笔记debug——navicat执行.sql失败及数据库连接1045错误

SQL学习笔记项目篇——游戏案例

前言

• 游戏数据分析主要从新增玩家、玩家活跃度、玩家付费情况、玩家游戏习惯进行分析
• 数据集包含８２８９３４条数据，１０个字段
• 数据可视化及行业分析报告后续完善

分析流程及结果框架

SQL代码及debug

-- 1.新增玩家
SELECT COUNT(distinct user_id) as '新增玩家数量'
FROM
tap_fun_test;

SELECT COUNT(distinct user_id) as '新增付费玩家数量'
FROM
tap_fun_test
WHERE pay_price>0;

SELECT b.新增付费玩家数量/a.新增玩家数量 as '付费玩家占比'
from(
SELECT COUNT(distinct user_id) as '新增玩家数量'
FROM
tap_fun_test)as a 
join(
SELECT COUNT(distinct user_id) as '新增付费玩家数量'
FROM
tap_fun_test
WHERE pay_price>0) as b;

SELECT date(register_time) as '日期',COUNT(DISTINCT user_id) as '每日新增玩家数'
FROM tap_fun_test 
GROUP BY date(register_time);

SELECT date(register_time) as '日期',COUNT(DISTINCT user_id) as '每日新增付费玩家数'
FROM tap_fun_test 
where pay_price>0
GROUP BY date(register_time)
;
#debug 执行顺序where>group by；


#2玩家活跃度

select avg(avg_online_minutes) as 全部玩家平均在线时长
from tap_fun_test;

select avg(avg_online_minutes) as 付费玩家平均在线时长
from tap_fun_test where pay_price>0;

#分布：min，下四分位数，中位数，上四位数，max
-- 下四位数，中位数，上四分位数位置id
SELECT 
round(COUNT(distinct user_id)/4) as id_下四分位,
round(COUNT(distinct user_id)/2) as id_中位,
round(COUNT(distinct user_id)*0.75) as id_上四分位
from tap_fun_test;

SELECT 
round(COUNT(distinct user_id)/4) as id_下四分位,
round(COUNT(distinct user_id)/2) as id_中位,
round(COUNT(distinct user_id)*0.75) as id_上四分位
from tap_fun_test
where pay_price>0;


-- min，下四分位数，中位数，上四位数，max
SELECT 
min(avg_online_minutes) as min,
(SELECT avg_online_minutes from tap_fun_test 
ORDER BY avg_online_minutes limit 207233,1) as 下四分位数,
(SELECT avg_online_minutes from tap_fun_test 
ORDER BY avg_online_minutes limit 414466,1) as 中位数,
(SELECT avg_online_minutes from tap_fun_test 
ORDER BY avg_online_minutes limit 621700,1) as 上四分位数,
max(avg_online_minutes) as max
from
tap_fun_test;



SELECT 
min(avg_online_minutes) as min,
(SELECT avg_online_minutes from tap_fun_test where pay_price>0
ORDER BY avg_online_minutes limit 4886,1) as 下四分位数,
(SELECT avg_online_minutes from tap_fun_test where pay_price>0
ORDER BY avg_online_minutes limit 9774,1) as 中位数,
(SELECT avg_online_minutes from tap_fun_test where pay_price>0
ORDER BY avg_online_minutes limit 14661,1) as 上四分位数,
max(avg_online_minutes) as max
from
tap_fun_test
where pay_price>0;
#debug：
-- 用where user_id=207234寻找4分位数是不对的，这里选的时候使用了distinct，所以行数不再等于id数
-- limit 分页查询的查询需要位数-1




#3 玩家付费情况
#AU
-- SELECT distinct user_id as '活跃用户id'FROM tap_fun_test
-- where avg_online_minutes>=15;

SELECT count(DISTINCT user_id) as AU活跃用户数 FROM tap_fun_test
where avg_online_minutes>=15;
#APA
SELECT count(DISTINCT user_id) as APA FROM tap_fun_test
where avg_online_minutes>=15 and pay_price>0 ;

#ARPU
SELECT
(SELECT sum(pay_price)from tap_fun_test)/count(distinct user_id) as ARPU 
from tap_fun_test
where avg_online_minutes>=15;
#ARPPU
SELECT
(SELECT sum(pay_price)from tap_fun_test)/count(distinct user_id) as ARPPU 
from tap_fun_test
where avg_online_minutes>=15 and pay_price>0 ;

#PUR
SELECT
(SELECT count(distinct user_id)from tap_fun_test WHERE pay_price>0)/count(distinct user_id) as PUR 
from tap_fun_test
where avg_online_minutes>=15 and pay_price>0;

#4玩家游戏习惯
#PVP,AU 
SELECT 
avg(pvp_battle_count) as 平均pvp次数,
sum(pvp_lanch_count)/sum(pvp_battle_count) as 主动PVP概率,
sum(pvp_win_count)/sum(pvp_battle_count) as pvp胜利概率
from tap_fun_test where avg_online_minutes>=15;
#PVP,APA
SELECT
avg(pvp_battle_count) as 平均pvp次数,
sum(pvp_lanch_count)/sum(pvp_battle_count) as 主动PVP概率,
sum(pvp_win_count)/sum(pvp_battle_count) as pvp胜利概率
from tap_fun_test where avg_online_minutes>=15;

#PVE,AU 
SELECT 
avg(pve_battle_count) as 平均pve次数,
sum(pve_lanch_count)/sum(pve_battle_count) as 主动PVE概率,
sum(pve_win_count)/sum(pve_battle_count) as pve胜利概率
from tap_fun_test where avg_online_minutes>=15;


#PVE,APA
SELECT 
avg(pve_battle_count) as 平均pve次数,
sum(pve_lanch_count)/sum(pve_battle_count) as 主动PVE概率,
sum(pve_win_count)/sum(pve_battle_count) as pve胜利概率
from tap_fun_test where avg_online_minutes>=15 and pay_price>0;


-- debug 
-- 所有活跃玩家需要sum一下