Retain.php

<?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！");
    }



}