<?php declare (strict_types = 1); namespace app\command\controller; use app\command\CmdLog; use think\Exception; use think\facade\Config; use think\facade\Db; use think\facade\Log; class Retain { // 10分钟统计一次 public function runs(){ CmdLog::debug("正在执行留存统计..."); $today = date('Y-m-d'); // 0点10分,补存前一天的数据 if( in_array(date('H-i'),['00-10']) ){ $today = date("Y-m-d",time()-86400); } // 游戏留存 $this->get_retain_data($today); // 付费留存 $this->get_recharge_data($today); } /** * 游戏留存 * @param $day */ private function get_retain_data($day){ // 登录日志表有注册时间+登录时间 CmdLog::debug("正在删除当天统计的数据..."); Db::table('data_register_retain')->where(['day_time'=>$day])->delete(true); // 重新统计入库 CmdLog::debug("重新统计入库,date: {$day}..."); $model_data = Db::table('app_memberloginlog')->field("login_ver,appid,DATE_FORMAT(reg_time, '%Y-%m-%d') AS reg_date,DATE_FORMAT(login_time, '%Y-%m-%d') AS login_date,count(DISTINCT uid) AS count_login_uid_num,DATEDIFF('{$day}',DATE_FORMAT(reg_time, '%Y-%m-%d')) as diff_day ") ->where("DATE_FORMAT(login_time, '%Y-%m-%d') = '{$day}'") ->group('login_ver,appid,reg_date')->having("diff_day >0 and diff_day <= 180")->select()->toArray(); if($model_data){ foreach ($model_data as $key => $value){ $reg_count = Db::table("app_member")->where("ver = '{$value['login_ver']}' and app_id = {$value['appid']} and DATE_FORMAT(reg_time, '%Y-%m-%d') = '{$value['reg_date']}'")->count(); Db::table("data_register_retain")->strict(false)->insert([ 'day_time' => $day, 'ver_code' => $value['login_ver'], 'appid' => $value['appid'], 'reg_num' => $reg_count, 'login_num' => $value['count_login_uid_num'], 'create_date' => date('Y-m-d H:i:s',time()), 'day'.(intval($value['diff_day'])-1) => $value['count_login_uid_num'] ]); } } CmdLog::debug("end!"); } /** * 付费留存,即:用户在注册当天产生充值行为,并在N天后登录, * @param $day */ private function get_recharge_data($day){ CmdLog::debug("正在删除当天付费留存的数据..."); Db::table('data_recharge_retain')->where(['day_time'=>$day])->delete(true); // 重新统计入库 CmdLog::debug("重新统计入库,date: {$day}..."); $model_data = Db::table('app_member')->alias("mem") ->field("log.login_ver,mem.app_id,FROM_UNIXTIME(mem.reg_time, '%Y-%m-%d') AS reg_day,count(DISTINCT log.uid) AS log_uid_num,count(DISTINCT mem.uid) AS mem_uid_num,DATEDIFF('{$day}',FROM_UNIXTIME(mem.reg_time, '%Y-%m-%d')) AS diff_day") ->leftJoin("app_memberloginlog log","log.uid=mem.uid and log.login_ver = mem.ver") ->leftJoin("app_order o","o.uid=mem.uid and o.ver = mem.ver and FROM_UNIXTIME(o.dateline, '%Y-%m-%d') = FROM_UNIXTIME(mem.reg_time, '%Y-%m-%d')") ->where("0.status=1 and FROM_UNIXTIME(log.login_time, '%Y-%m-%d') = '{$day}'") ->group("log.login_ver,mem.app_id,reg_day") ->having("diff_day >0 and diff_day <=180")->select()->toArray(); if($model_data){ foreach ($model_data as $key => $value){ Db::table("data_recharge_retain")->strict(false)->insert([ 'day_time' => $day, 'ver_code' => $value['login_ver'], 'appid' => $value['app_id'], 'reg_num' => $value['mem_uid_num'], 'login_num' => $value['log_uid_num'], 'create_date' => date('Y-m-d H:i:s',time()), 'day'.(intval($value['diff_day'])-1) => $value['log_uid_num'] ]); } } CmdLog::debug("end!"); } }