2022 毕业设计，基于 Hadoop 的游戏数据分析系统.zip

package dbgame; import java.sql.*; import java.util.ArrayList; public class connDb { private static Connection con = null; private static Statement stmt = null; private static ResultSet rs = null; //连接数据库方法 public static void startConn(){ try{ Class.forName("com.mysql.jdbc.Driver"); //连接数据库中间件 try{ con = DriverManager.getConnection("jdbc:MySQL://localhost:3306/dbgame","root","root"); }catch(SQLException e){ e.printStackTrace(); } }catch(ClassNotFoundException e){ e.printStackTrace(); } } //关闭连接数据库方法 public static void endConn() throws SQLException{ if(con != null){ con.close(); con = null; } if(rs != null){ rs.close(); rs = null; } if(stmt != null){ stmt.close(); stmt = null; } } // public static ArrayList index() throws SQLException{ // ArrayList<DataAnalysis> list = new ArrayList(); // startConn(); // stmt = con.createStatement(); // rs = stmt.executeQuery("select * from 表名"); // while(rs.next()){ // DataAnalysis data = new DataAnalysis(); // //比如userId; // data.setUserId(rs.getString("user_id")); // list.add(data); // } // endConn(); // return list; // } // test方法 public static ArrayList test() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT COUNT(DISTINCT user_id)FROM user_log"); while(rs.next()){ String[] temp={rs.getString(1)}; // System.out.print(rs.next()); // String[] temp={rs.getString("action"),rs.getString("num")}; list.add(temp); } endConn(); return list; } // 3.1新增用户分析 // 3.1.1新增用户人数 public static ArrayList f1_1() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT COUNT(DISTINCT user_id)FROM user_log"); while(rs.next()){ String[] temp={rs.getString(1)}; list.add(temp); } endConn(); return list; } // 3.1.2新增付费玩家数 public static ArrayList f1_2() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT COUNT( DISTINCT user_id ) FROM user_log WHERE pay_price >0"); while(rs.next()){ String[] temp={rs.getString(1)}; list.add(temp); } endConn(); return list; } // 3.1.3注册玩家付费转化率 public static ArrayList f1_3() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT ( SELECT COUNT( DISTINCT user_id ) FROM user_log WHERE pay_price > 0 )/( SELECT COUNT( DISTINCT user_id ) FROM user_log )"); while(rs.next()){ String[] temp={rs.getString(1)}; list.add(temp); } endConn(); return list; } // 3.1.4每日新增玩家DNU public static ArrayList f1_4() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT DATE( register_time ) AS '时间',COUNT( DISTINCT user_id ) AS '新增玩家数' FROM user_log GROUP BY DATE(register_time)"); while(rs.next()){ String[] temp={rs.getString(1),rs.getString(2)}; list.add(temp); } endConn(); return list; } // 3.1.5每日新增付费玩家数 public static ArrayList f1_5() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT DATE( register_time ) AS '时间',COUNT( DISTINCT user_id ) AS '新增玩家数' FROM user_log WHERE pay_price>0 GROUP BY DATE(register_time)"); while(rs.next()){ String[] temp={rs.getString(1),rs.getString(2)}; list.add(temp); } endConn(); return list; } // 玩家活跃度分析 //玩家平均在线时长 public static ArrayList f2_1() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery(" SELECT ROUND(AVG( avg_online_minutes ),2) FROM user_log"); while(rs.next()){ String[] temp={rs.getString(1)}; list.add(temp); } endConn(); return list; } // #付费玩家平均在线时长 public static ArrayList f2_2() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT AVG( avg_online_minutes ) '付费玩家平均在线时长' FROM user_log WHERE pay_price>0"); while(rs.next()){ String[] temp={rs.getString(1)}; list.add(temp); } endConn(); return list; } //假定用户平均在线时长在30分钟及以上为活跃用户，对比分析每日新增用户数、日活跃用户数以及日付费用户数 public static ArrayList f2_3() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery(" SELECT DATE( register_time ) AS '日期', COUNT(DISTINCT user_id) AS '每日新增用户', SUM(CASE WHEN avg_online_minutes>=30 THEN 1 ELSE 0 END) AS '每日活跃用户', CONCAT((SUM(CASE WHEN avg_online_minutes>=30 THEN 1 ELSE 0 END)/COUNT(register_time))) AS '每日活跃用户占比',SUM(CASE WHEN pay_price>0 THEN 1 ELSE 0 END) AS '每日付费用户',CONCAT((sum(CASE WHEN pay_price>0 THEN 1 ELSE 0 END)/COUNT(register_time))) AS '每日付费用户占比' FROM user_log GROUP BY DATE(register_time)"); while(rs.next()){ String[] temp={rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)}; list.add(temp); } endConn(); return list; } // 玩家付费行为分析 // 活跃用户付费比例 // 返回值为付费【活跃玩家，活跃玩家，活跃用户付费比例】 public static ArrayList f3_1() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT a.APA,a.AU,a.APA/a.AU as 'PUR' FROM(SELECT COUNT(CASE WHEN avg_online_minutes>30 THEN user_id ELSE NULL END) 'AU',COUNT(CASE WHEN pay_price>0 AND avg_online_minutes>30 THEN user_id ELSE NULL END) 'APA' FROM user_log ) a"); while(rs.next()){ String[] temp={ rs.getString(1), rs.getString(2), rs.getString(3) }; list.add(temp); } endConn(); return list; } // 平均每用户收入 public static ArrayList f3_2() throws SQLException{ ArrayList<String[]> list = new ArrayList(); startConn(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT ROUND(a.income/a.AU,2) as 'ARPU' FROM (SELECT COUNT(CASE WHEN avg_online_minutes>30 THEN user_id ELSE NULL END) 'AU', sum(pay_price) 'income' FROM user_log) a"); while(rs.next()){ String[] temp={ rs.getString(1) }; list.add(temp); } endConn();