数据仓库与数据挖掘- 期末课程设计-游戏日志分析

大作业题目	游戏日志分析
目的： 1.运用所学的知识，通过搭建日志分析平台，让游戏公司进行科学的运营。 2.通过此实验掌握一些基本的操作
二、环境： 1. 安装VMware Workstation Pro、搭建hadoop集群、hive能正常运行、安装xsell 2.物理机上安装mysql、Navicat Premium 15、FineBI、配置jupyter lab环境
三、内容： 项目背景   某游戏上线以后运营效果没有达到预期目标，希望通过搭建日志分析平台，进行科学的运营。 需求概述  为该游戏提供实时数据统计分析服务，监控版本质量、渠道状况、用户画像属性及用户细分行为，通过数据可视 化展现，协助产品运营决策。作为项目的一期需要完成数据离线统计分析服务。 数据结构 订单表 序号 字段名 字段类型 注释 1 order_id varchar（36） 订单ID 2 user_id int 用户id 3 amount int 消费金额 4 order_time datetime 消费时间 5 is_success Char（1） 是否成功,1成功,0失败 用户表 序号 字段名 字段类型 注释 1 user_id int 用户id 2 device_id Varchar（60） 设备id 3 name Varchar（20） 姓名 4 idcard Varchar（18） 身份证号 游戏登录记录表 序号 字段名 字段类型 注释 1 device_id Varchar(60) 设备id 2 os Varchar(20) 操作系统 3 os_version Varchar(20) 系统版本 4 start_time datetime 开始时间 5 end_time datetime 结束时间 6 duration int 登录时长（秒） 基本需求 一、游戏概况 1、用户详情：完整的用户信息 （需求一） 2、游戏用户概况（需求二） (1) 用户分布情况：累积玩家分布情况 (2) 人均使用时长：累计时长 / 累计用户 (3) 次均使用时长：累计时长 / 累计启动次数 3、总收入统计和每日收入统计（需求三） 二、首日、第三日用户每日留存率（需求四） 统计首日新用户每日的用户留存率，第三日（2017-01-03）新用户接下来几天的每日用户留存率。 例：首日用户第3 日留存率 = 首日登录的用户，未来三天内任意一天登录的用户/首日总用户数 首日用户第5 日留存率 = 首日登录的用户，未来五天内任意一天登录的用户/首日总用户数 三、每日用户数据分析（需求五） 每日新用户数和老用户数 四、TopN（需求六） 1、游戏时长Top10 2、游戏玩家充值Top10 五、数据可视化 使用个人版帆软BI或其他可视化工具如Echars等进行可视化呈现 要求： 1、数仓层级明确，3张源表对应3张ods层级表、3张DWD层级表。根据业务需求及数据汇聚需求，形成DWS层级表。将最终进行可视化呈现的数据放至ADM层级表中。 2、将数据使用sqoop从MysQL导入至Hive ODS层中 3、按数仓各层级要求，结合数仓建设理念，完成对数仓各层级的建设 4、生成最后应用层数据后，将数据导出至个人电脑MysQL中，可视化呈现的数据源使用个人电脑 5、根据最后数据情况，为此游戏公司提出建议。 6、可对项目仔细分析后，开发除基础需求外的额外需求的功能实现。
四、步骤（包含运行结果）： 第一、把game.sql从自己的盘中导入mysql应用中。 1.打开命令提示符窗口 2.用mysql -uroot -p123456命令进入mysql   3.show databases;查看数据库   4.use game;使用数据库   5.使用source D:/sql/game.sql命令把game.sql解压，此过程时间较长需要耐心等待，如图所示。（建议下载Navicat Premium 15应用导入数据）   传入完成后打开mysql应用可以看到如下内容即为导入成功，分别可以查看game_user（用户表）,order（订单表）,region（行政区化表）,registration_recond（登录表）表   第二、基本数据准备（ods层） 1.1创建数据库game并查看是否创建成功   1.2.在beeline中创建用户表ods_order（用户登陆表） create table ods_order(order_id varchar(36), use_id int, amount int, drder_time string, is_success char(1)) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";   1.3.使用sqoop将mysql中的order数据导入hive中的ods_order中 sqoop import \                 --connect jdbc:mysql://192.168.36.1:3306/game \                 --username root \                 --password 123456 \                 --table order\                 --fields-terminated-by '\t' \                 --hive-database game\                 --hive-table ods_order\                 -m 1 \                 --hive-import \                 --target-dir /temp   导入成功的标志如图所示   1.4.查看数据是否导入成功结果展示如图所示 查看表代码 select * from odrer_table     2.1创建用户表 2、创建用户表，代码如下： create table ods_game_user(user_id int, device_id Varchar(60), name Varchar(20), idcard Varchar(18)) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";   2.2使用sqoop将mysql中的game_user数据导入hive中的ods_game_user中 sqoop import \                 --connect jdbc:mysql://192.168.36.1:3306/game \                 --username root \                 --password 123456 \                 --table game_user\                 --fields-terminated-by '\t' \                 --hive-database game\                 --hive-table ods_game_user\                 -m 1 \                 --hive-import \                 --target-dir /temp   2.3查看数据是否导入成功结果展示如图所示     3.1创建登陆表 create table ods_registration_record( device_id Varchar(60), os Varchar(20), os_version Varchar(20), start_time string, end_time string, duration string) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";   3.2使用sqoop将mysql中的registration_recordr数据导入hive中的ods_registration_record中 sqoop import \                 --connect jdbc:mysql://192.168.36.1:3306/game \                 --username root \                 --password 123456 \                 --table registration_record\                 --fields-terminated-by '\t' \                 --hive-database game\                 --hive-table ods_registration_record\                 -m 1 \                 --hive-import \                 --target-dir /temp   3.3查看数据是否导入成功结果展示如图所示     4.1创建行政地区表 create table ods_region( id int,diqu varchar(40)) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";   4.2使用sqoop将mysql中的region数据导入hive中的ods_region中 sqoop import \                 --connect jdbc:mysql://192.168.36.1:3306/game \                 --username root \                 --password 123456 \                 --table region\                 --fields-terminated-by '\t' \                 --hive-database game\                 --hive-table ods_region\                 -m 1 \                 --hive-import \                 --target-dir /temp   4.3查看数据是否导入成功结果展示如图所示   5.在game数据库中所建的四张表如图所示   第三、数据清洗（DWD层） 1.用户详情：完整的用户信息 （需求一） 1.1将登录表和用户表连接起来创建dwd_game_user表获得用户基本信息 create table dwd_game_user as select ods_game_user.user_id, ods_game_user.device_id, ods_game_user.name, ods_game_user.idcard, ods_region.diqu, (case when substr(ods_game_user.idcard,17,1)%2==0 then '女' else '男' end) sex, substr(ods_game_user.idcard,7,6) year_month, year(current_date())-substr(ods_game_user.idcard,7,4) age from ods_game_user left join ods_region   on substr(ods_game_user.idcard,1,6)=ods_region.id;   1.2.查看dwd_game_user表是否有信息，并看信息是否有误（由于表比较长，这里切成两张图展示）   2.1在ods_order表中插入所需要的用户信息数据建立dwd_order表 create table dwd_order as select ods_order.order_id, ods_order.use_id, ods_game_user.user_id, ods_game_user.name, ods_game_user.idcard, ods_order.amount,   ods_order.drder_time, ods_order.is_success from ods_order left join ods_game_user   on ods_order.use_id=ods_game_user.user_id;   2.2结果展示如图所示   3.1 在ods_registration_record表中插入用户信息数据建立dwd_registration_record表 create table dwd_registration_record as select ods_registration_record.device_id, ods_registration_record.os, ods_registration_record.os_version, ods_registration_record.start_time, ods_registration_record.end_time, ods_registration_record.duration, ods_game_user.user_id, ods_game_user.name, ods_game_user.idcard from ods_registration_record left join ods_game_user   on ods_registration_record.device_id=ods_game_user.device_id;   3.2结果展示如图所示   4.建立的三张dwd表如图所示   第四、1.游戏用户概况（需求二）dwm层 1.1用户分布情况：累积玩家分布情况 create table dwm_users_fenbu as select  diqu,count(diqu) as num_people from dwd_game_user group by diqu; select *from dwm_users_fenbu limit 6;     1.2人均使用时长：累计时长 / 累计用户 select sum(duration)/count(user_id) as dwm_avgtime from  dwd_registration_record;   1.3次均使用时长：累计时长 / 累计启动次数 select sum(duration)/count(device_id) as dwm_tow_time from dwd_registration_record;   2.总收入统计和每日收入统计（需求三） 2.1总收入 select sum(amount) from dwd_order where dwd_order.is_success=='1';     2.2每日收入 create table dws_day_amount1 as select to_date(drder_time),sum(amount) from ods_order group by to_date(drder_time); select *from dws_day_amount1 limit 6;       第五、首日、第三日用户每日留存率（需求四） 统计首日新用户每日的用户留存率，第三日（2017-01-03）新用户接下来几天的每日用户留存率。 例：首日用户第3 日留存率 = 首日登录的用户，未来三天内任意一天登录的用户/首日总用户数 首日用户第5 日留存率 = 首日登录的用户，未来五天内任意一天登录的用户/首日总用户数 1.1创建新的用户登陆表 create table dwd_login as select device_id,substr(start_time,1,10) login_time from ods_registration_record;   1.2创建首日新用户登陆表 create table dwd_new_user_login as select distinct(device_id), login_time from dwd_login where login_time='2017-01-01';     1.3创建老用户登陆表 create table dwd_old_user_login as select a.device_id, min(b.login_time) log_in_time from dwd_new_user_login a left join dwd_login b on a.device_id=b.device_id where b.login_time>'2017-01-01' group by a.device_id;   1.3.1老用户登录表如图所示   2.1创建每日留存率表 create table dwm_retention_day as select count(a.device_id) as monday, sum(if(datediff(b.log_in_time,a.login_time)=1,1,0)) / count(a.device_id) as tuesday, sum(if(datediff(b.log_in_time,a.login_time)<3,1,0)) / count(a.device_id) as wednesday, sum(if(datediff(b.log_in_time,a.login_time)<4,1,0)) / count(a.device_id) as thursday, sum(if(datediff(b.log_in_time,a.login_time)<5,1,0)) / count(a.device_id) as friday, sum(if(datediff(b.log_in_time,a.login_time)<6,1,0)) / count(a.device_id) as saturday, sum(if(datediff(b.log_in_time,a.login_time)<7,1,0)) / count(a.device_id) as sunday from dwd_new_user_login as a left join  dwd_old_user_login as b on  a.device_id==b.device_id;   2.2每日留存率结果如图所示   第六、每日用户数据分析（需求五） 每日新用户数和老用户数 每天新用户 create table dwm_new_user as select idcard, min(substr(drder_time,1,10)) as log_time from dwd_order group by idcard:   1.1查看结果如图所示   1.2统计每天新用户 create table dwm_day_new_user as select log_time, count(idcard) as day_new_user from dwm_new_user group by log_time;     1.3统计每日总的人数 create table dwm_total_user as select substr(start_time,1,10) as start_time, count(distinct device_id) as day_sumuser from ods_registration_record group by substr(start_time,1,10);     1.4把每日总的人数、新用户、老用户都统计出来 create table dws_new_old_user as select a.start_time, a.day_sumuser, b.day_new_user, a.day_sumuser-b.day_new_user day_old_user from dwm_total_user a left join dwm_day_new_user b on a.start_time==b.log_time;   1.5.结果如图所示   第七、TopN（需求六） 游戏时长Top10 create table dws_game_time as select device_id,sum(duration) top from dwd_registration_record group by device_id;     2.游戏玩家充值Top10 create table dws_game_amount as select use_id,sum(amount) amount from dwd_order where dwd_order.is_success=='1' group by use_id;     第八、数据可视化 使用个人版帆软BI或其他可视化工具如Echars等进行可视化呈现 （一）、使用个人版帆软BI进行可视化 1.安装Navicat Premium 15并连接mysql中的数据库game（安装过程省略）   2.连接成功后如图，有此数据库   3.命令提示符打开mysql（mysql – u root -p123456）并使用数据库game   4.Sqoop导出，将hive中的表导入mysql中，或Navicat Premium 15中（mysql与Navicat Premium 15数据同步） 4.1在Navicat Premium 15中建表与hive中相应的表结构字段保持一致   4.2.sqoop导出通用语句   4.3如dws_game_time表导出如图所示   4.3.1导出成功的标志如图所示   4.3.2在Navicat Premium 15中查看数据导出是否正常，正常情况如图所示   4.3.3把所需表按此方法分别导出，导出的所有表如图所示   5. 用FineBI连接mysql，连接成功如图所示   6.在MyProject中添加表，选择数据库连接，选择表   7.进入如下界面进行可视化相关操作   8.可视化展示如图所示     （二）使用jupyter lab工具实现可视化 1.1将此表导出到自己D盘中   1.2.在本地中如图所示   1.3.读取数据如图   1.4.代码如图所示   1.5.可视化展示如图   2.1读取每日累计金额表如图   2.2代码如图   2.3可视化如图所示   3.1对每日新用户和消费金额进行分栏可视化（代码如图）   3.2可视化展示如图所示   第九、建议 单从每日新用户和每日累计金额上看，新用户相对较多的集中在第5日和第7日，这两日的累计金额相对也较大，是盈利的很好机会，公司应该多次研究客流量，从而来获得最大收益，还可以根据用户的偏好，提供更多的服务，从而赚取更多的收益。而其他几日新用户及消费金额都相对较少，可能这几天大都是上班时间，大家都忙于工作，但是也有很多用户是闲暇的，那么公司应当多设法几招，多研究如何吸引这些用户来消费，从而赚取更多的金额。
总结：通过此次游戏项目分析，我掌握了hive的一些基本操作，巩固了上课所学的知识，并学会了一些可视化运用工具、也了解了这个游戏公司的一些弊端，并给出相应的建议。