日活、周活（周重活）、月活 统计

效果展示：


以上数据为测试数据


实现思路：

  按照查询天数往前推规定天 比如周、月

 登录表为按月分表（数据量大约一张表1000W+数据）

关键sql:


laravel5.1 框架

日活实现方式一：

 不使用文件缓存

关键sql : date_format()

[php] view plain copy print?
  1. /** 
  2.      * 日活（登录） 
  3.      * @param Request $request 
  4.      * @return $this 
  5.      */  
  6.     public function  dayKeepView(Request $request){  
  7.         $data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-7 days')));  
  8.         $data['end_time'] = $request->input('end_time',date('Y-m-d',strtotime('-1 days')));  
  9.         if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time()))) $data['end_time']=date('Y-m-d',strtotime('-1 days'));  
  10.         $start_time = strtotime($data['start_time']);  
  11.         $end_time  = strtotime($data['end_time']);  
  12.         $table_prefix = 'd_user_login';  
  13.         $table_exist = $this->getTableRange($start_time,$end_time,$table_prefix);  
  14.         $select_table = current($table_exist);  
  15.         $res = DB::connection('log')->table($table_prefix.$select_table)  
  16.             ->select(DB::raw("count(DISTINCT(uid)) as total ,date_format(time,'%Y%m%d') as day"))  
  17.             ->where('type','=',0)  
  18.             ->where('time','>=', $data['start_time'].' 00:00:00')  
  19.             ->where('time','<=', $data['end_time'].' 23:59:59')  
  20.             ->groupBy('day');  
  21.         if(count($table_exist)>0){  
  22.             foreach($table_exist as $key=>$val){  
  23.                 if($key>0){  
  24.                     $res->union(  
  25.                         DB::connection('log')->table($table_prefix.$val)  
  26.                         ->select(DB::raw("count(DISTINCT(uid)) as total ,date_format(time,'%Y%m%d') as day"))  
  27.                         ->where('type','=',0)  
  28.                         ->where('time','>=', $data['start_time'].' 00:00:00')  
  29.                         ->where('time','<=', $data['end_time'].' 23:59:59')  
  30.                        ->groupBy('day')  
  31.                     );  
  32.                 }  
  33.             }  
  34.         }  
  35.         $res = $res->get();  
  36.         krsort($res);  
  37.         return view('chart/keep/dayKeepView')->with('day_keep_data',$res)->with('data',$data);  
  38.     }  

日活实现方式二：

 文件缓存方式（循环sql）:

[php] view plain copy print?
  1. /** 
  2.     * 日活（登录） 
  3.     * @param Request $request 
  4.     * @return $this 
  5.     */  
  6.    public function  dayKeepView(Request $request){  
  7.        $data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-14 days')));  
  8.        $data['end_time'] = $request->input('end_time',date('Y-m-d',strtotime('-1 days')));  
  9.        if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time()))) $data['end_time']=date('Y-m-d',strtotime('-1 days'));  
  10.        $start_time = strtotime($data['start_time']);  
  11.        $end_time  = strtotime($data['end_time']);  
  12.        //读取文件  
  13.        $file_name = DIRECTORY_SEPARATOR.'total'.DIRECTORY_SEPARATOR.'dayData.json';  
  14.        $day_data = Storage::disk('local')->exists($file_name) ? json_decode(Storage::get($file_name),true) : [];  
  15.        $day_data_day = count($day_data)>0 ? array_column($day_data,'day') : [];  
  16.        //循环查询时间区间  
  17.        for($i=$start_time,$select_day=[];$i<=$end_time;$i+=86400){  
  18.            $select_day[] =  intval(date('Ymd',$i));  
  19.        }  
  20.        //求出差集  
  21.        $need_select_day = array_diff($select_day,$day_data_day);  
  22.        //循环执行语句查询  
  23.        if(count($need_select_day)>0){  
  24.            $res = [];  
  25.            foreach ($need_select_day as $key => $val) {  
  26.                $self_time = strtotime($val);  
  27.                $self_table = date('Ym',$self_time);  
  28.                $self_start_time = date('Y-m-d 00:00:00',$self_time);  
  29.                $self_end_time = date('Y-m-d 23:59:59',$self_time);  
  30.                $res[$key] = DB::connection('log')->table('d_user_login'.$self_table)  
  31.                        ->select(DB::raw("count(distinct(uid)) as total"))  
  32.                        ->where('type','=',0)  
  33.                        ->where('time','>=',$self_start_time)  
  34.                        ->where('time','<=',$self_end_time)  
  35.                        ->lists('total');  
  36.                    $res[$key]['total'] = $res[$key][0];  
  37.                    unset($res[$key][0]);  
  38.                    $res[$key]['day'] = $val;  
  39.            };  
  40.            $list = array_merge($res,$day_data);  
  41.            $list = FunctionController::arr_sort($list,'SORT_DESC','day');  
  42.            if(count($day_data_day)>0) Storage::delete($file_name);  
  43.            //修改写入文件  
  44.            Storage::disk('local')->put($file_name,json_encode($list));  
  45.            $day_data = $list;  
  46.        }  
  47.        //读取文件缓存  
  48.        //依据时间区间读取  
  49.        $start_time = date('Ymd',$start_time);  
  50.        $end_time  = date('Ymd',$end_time);  
  51.        foreach($day_data as $key=>$val){  
  52.            if($val['day']==$start_time) $start = $key;  
  53.            if($val['day']==$end_time) $end = $key;  
  54.            if(isset($start)&&isset($end)) break;  
  55.        }  
  56.        $list = array_slice($day_data,$end,$start-$end+1);  
  57.        return view('chart/keep/dayKeepView')->with('day_keep_data',$list)->with('data',$data);  
  58.    }  

周活：

关键sql:

 周重活sql：

select count(uid)  as total from (select count(uid) as rtotal,uid from  (select count(distinct(uid)),date_format(time,'%Y%m%d') as day,uid  from `d_user_login201705`  where type=0 and  time between '2017-05-28'and '2017-06-04' group by day,uid  union all SELECT  count(distinct(uid)),date_format(time,'%Y%m%d') as day,uid  from `d_user_login201706`  where type=0 and  time between'2017-05-28'and '2017-06-04' group by day,uid) as t1 group by uid  having rtotal>3 order by null) as t2 where uid>20000 ;  (周跨月)

[php] view plain copy print?
  1. /** 
  2.      * 周活（登录表） 
  3.      * @param Request $request 
  4.      * @return $this 
  5.      */  
  6.     public function weekKeepView(Request $request){  
  7.         set_time_limit(0);  
  8.         $data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-14 days')));  
  9.         $data['end_time'] = $request->input('end_time',date('Y-m-d',strtotime('-1 days')));  
  10.         if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time()))) $data['end_time']=date('Y-m-d',strtotime('-1 days'));  
  11.         $start_time = strtotime($data['start_time']);  
  12.         $end_time  = strtotime($data['end_time']);  
  13.         //读取文件  
  14.         $file_name = DIRECTORY_SEPARATOR.'total'.DIRECTORY_SEPARATOR.'weekData.json';  
  15.         $week_data = Storage::disk('local')->exists($file_name) ? json_decode(Storage::get($file_name),true) : [];  
  16.         $week_data_day = count($week_data)>0 ? array_column($week_data,'day') : [];  
  17.         //循环查询时间区间  
  18.         for($i=$start_time,$select_day=[];$i<=$end_time;$i+=86400){  
  19.             $select_day[] =  intval(date('Ymd',$i));  
  20.         }  
  21.         //求出差集  
  22.         $need_select_day = array_diff($select_day,$week_data_day);  
  23.         //循环执行语句查询  
  24.         if(count($need_select_day)>0){  
  25.             $res = [];  
  26.             foreach($need_select_day as $key=>$val){  
  27.                 $self_time = strtotime($val);  
  28.                 $self_start_time = date('Ymd',$self_time-86400*6);  
  29.                 $self_end_time = date('Ymd',$self_time+86400);  
  30.                 if(substr($self_start_time,0,6)==substr($self_end_time,0,6)){  
  31.                     //周活  
  32.                     $res[$key]['week'] = DB::connection('log')->table('d_user_login'.substr($val,0,6))  
  33.                         ->select(DB::raw("count(distinct(uid)) as total"))  
  34.                         ->where('type','=',0)  
  35.                         ->whereBetween('time',[$self_start_time,$self_end_time])  
  36.                         ->lists('total');  
  37.                    // 周重活  一天有4次登录及以上  
  38.                    $res[$key]['week_more']= DB::connection('log')->select(DB::raw("select count(uid) as total from (select  count(uid) as rtotal,uid from (SELECT  count(distinct(uid)),date_format(time,'%Y%m%d') as day,uid  from d_user_login".substr($val,0,6)."  where type=0 and  time>='".$self_start_time."'and time<'".$self_end_time."' group by day,uid) as t1 group by uid having rtotal>3) as tt where uid>20000"));  
  39.                 }else{  
  40.                     //周活  
  41.                     $sql = DB::connection('log')->table('d_user_login'.substr($self_start_time,0,6))  
  42.                            ->select('uid')->where('type','=',0)  
  43.                            ->whereBetween('time',[$self_start_time,$self_end_time])  
  44.                            ->union(  
  45.                                DB::connection('log')->table('d_user_login'.substr($self_end_time,0,6))  
  46.                                    ->select('uid')->where('type','=',0)  
  47.                                    ->whereBetween('time',[$self_start_time,$self_end_time])  
  48.                             );  
  49.                     $list_sql = $sql->tosql();  
  50.                     $list_val = $sql->getBindings();  
  51.                     $sql_res =self::getStringReplace($list_val,$list_sql) ;  
  52.                     $res[$key]['week'] = DB::connection('log')->table(DB::raw('('.$sql_res.') as tem'))  
  53.                                  ->select(DB::raw('count(uid) as total'))->lists('total');  
  54.                     //周重活  
  55.                     $res[$key]['week_more'] = DB::connection('log')->select(DB::raw("select count(uid)  as total from (select count(uid) as rtotal,uid from  (select count(distinct(uid)),date_format(time,'%Y%m%d') as day,uid  from d_user_login".substr($self_start_time,0,6)."  where type=0 and  time>='".$self_start_time."'and time<'".$self_end_time."' group by day,uid union all SELECT  count(distinct(uid)),date_format(time,'%Y%m%d') as day,uid  from d_user_login".substr($self_end_time,0,6)."  where type=0 and  time>='".$self_start_time."'and time<'".$self_end_time."' group by day,uid order by null) as t1 group by uid  having rtotal>3 order by null) as t2 where uid>20000;"));  
  56.                 }  
  57.                 $res[$key]['total'] = $res[$key]['week'][0];  
  58.                 $res[$key]['more_total'] = $res[$key]['week_more'][0]->total;  
  59.                 unset($res[$key]['week']);  
  60.                 unset($res[$key]['week_more']);  
  61.                 $res[$key]['day'] = $val;  
  62.             };  
  63.             $list = array_merge($res,$week_data);  
  64.             $list = FunctionController::arr_sort($list,'SORT_DESC','day');  
  65.             if(count($week_data_day)>0) Storage::delete($file_name);  
  66.             //修改写入文件  
  67.             Storage::disk('local')->put($file_name,json_encode($list));  
  68.             $week_data = $list;  
  69.             }  
  70.             //读取文件缓存  
  71.             //依据时间区间读取end  
  72.             $start_time = date('Ymd',$start_time);  
  73.             $end_time  = date('Ymd',$end_time);  
  74.             foreach($week_data as $key=>$val){  
  75.                 if($val['day']==$start_time) $start = $key;  
  76.                 if($val['day']==$end_time) $end = $key;  
  77.                 if(isset($start)&&isset($end)) break;  
  78.             }  
  79.             $list = array_slice($week_data,$end,$start-$end+1);  
  80.         return view('chart/keep/weekKeepView')->with('week_keep_data',$list)->with('data',$data);  
  81.     }  

月活：
[php] view plain copy print?
  1. /** 
  2.      * 月活（登录表） 
  3.      * @param Request $request 
  4.      * @return $this 
  5.      */  
  6.     public function monthKeepView(Request $request){  
  7.         set_time_limit(0);  
  8.         $data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-14 days')));  
  9.         $data['end_time'] = $request->input('end_time',date('Y-m-d',strtotime('-1 days')));  
  10.         if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time()))) $data['end_time']=date('Y-m-d',strtotime('-1 days'));  
  11.         $start_time = strtotime($data['start_time']);  
  12.         $end_time  = strtotime($data['end_time']);  
  13.         //读取文件  
  14.         $file_name = DIRECTORY_SEPARATOR.'total'.DIRECTORY_SEPARATOR.'monthData.json';  
  15.         $month_data = Storage::disk('local')->exists($file_name) ? json_decode(Storage::get($file_name),true) : [];  
  16.         $month_data_day = count($month_data)>0 ? array_column($month_data,'day') : [];  
  17.         //循环查询时间区间  
  18.         for($i=$start_time,$select_day=[];$i<=$end_time;$i+=86400){  
  19.             $select_day[] =  intval(date('Ymd',$i));  
  20.         }  
  21.         //求出差集  
  22.         $need_select_day = array_diff($select_day,$month_data_day);  
  23.         //循环执行语句查询  
  24.         if(count($need_select_day)>0) {  
  25.             $res = [];  
  26.             foreach ($need_select_day as $key => $val) {  
  27.                 $self_time = strtotime($val);  
  28.                 $self_start_time = date('Ymd',strtotime($val.'-1month'));  
  29.                 $self_end_table = date('Ymd', $self_time);  
  30.                 $self_end_time = date('Y-m-d 23:59:59',$self_time);  
  31.                 if (substr($self_start_time, 0, 6) == substr($self_end_time, 0, 6)) {  
  32.                     $res[$key] = DB::connection('log')->table('d_user_login'.substr($val,0,6))  
  33.                         ->select(DB::raw("count(distinct(uid)) as total"))  
  34.                         ->where('type','=',0)  
  35.                         ->where('time','>=',$self_start_time)  
  36.                         ->where('time','<=',$self_end_time)  
  37.                         ->lists('total');  
  38.                 }else{  
  39.                     $sql = DB::connection('log')->table('d_user_login'.substr($self_start_time,0,6))  
  40.                         ->select('uid')->where('type','=',0)  
  41.                         ->whereBetween('time',[$self_start_time,$self_end_time])  
  42.                         ->union(  
  43.                             DB::connection('log')->table('d_user_login'.substr($self_end_table,0,6))  
  44.                                 ->select('uid')->where('type','=',0)  
  45.                                 ->where('time','>=',$self_start_time)  
  46.                                 ->where('time','<=',$self_end_time)  
  47.                         );  
  48.                     $list_sql = $sql->tosql();  
  49.                     $list_val = $sql->getBindings();  
  50.                     $sql_res =self::getStringReplace($list_val,$list_sql) ;  
  51.                     $res[$key] = DB::connection('log')->table(DB::raw('('.$sql_res.') as tem'))  
  52.                         ->select(DB::raw('count(uid) as total'))->lists('total');  
  53.                     $res[$key]['total'] = $res[$key][0];  
  54.                     unset($res[$key][0]);  
  55.                     $res[$key]['day'] = $val;  
  56.                 }  
  57.   
  58.             };  
  59.             $list = array_merge($res,$month_data);  
  60.             $list = FunctionController::arr_sort($list,'SORT_DESC','day');  
  61.             if(count($month_data_day)>0) Storage::delete($file_name);  
  62.             //修改写入文件  
  63.             Storage::disk('local')->put($file_name,json_encode($list));  
  64.             $month_data = $list;  
  65.         }  
  66.         //读取文件缓存  
  67.         //依据时间区间读取  
  68.         $start_time = date('Ymd',$start_time);  
  69.         $end_time  = date('Ymd',$end_time);  
  70.         foreach($month_data as $key=>$val){  
  71.             if($val['day']==$start_time) $start = $key;  
  72.             if($val['day']==$end_time) $end = $key;  
  73.             if(isset($start)&&isset($end)) break;  
  74.        }  
  75.         $list = array_slice($month_data,$end,$start-$end+1);  
  76.         return view('chart/keep/monthKeepView')->with('month_keep_data',$list)->with('data',$data);  
  77.     }  


生成json 文件的时候会比较慢 可以自己写PHP或者shell脚本去执行sql 操作！！

json 数据格式：

[html] view plain copy print?
  1. [{"total":80221,"more_total":22477,"day":20170619},{"total":74828,"more_total":22482,"day":20170618},{"total":74407,"more_total":22768,"day":20170617},{"total":73924,"more_total":23069,"day":20170616},{"total":74853,"more_total":23034,"day":20170615},{"total":77244,"more_total":23483,"day":20170614},{"total":77324,"more_total":23356,"day":20170613},{"total":75257,"more_total":23141,"day":20170612},{"total":75664,"more_total":22491,"day":20170611},{"total":75657,"more_total":21653,"day":20170610},{"total":75506,"more_total":21681,"day":20170609},{"total":73061,"more_total":21518,"day":20170608},{"total":69133,"more_total":21737,"day":20170607},{"total":66667,"more_total":21718,"day":20170606},{"total":63960,"more_total":21589,"day":20170605},{"total":65249,"more_total":21486,"day":20170604},{"total":66902,"more_total":21803,"day":20170603},{"total":66706,"more_total":22323,"day":20170602},{"total":67731,"more_total":22794,"day":20170601},{"total":69316,"more_total":23015,"day":20170531},{"total":73032,"more_total":23450,"day":20170530},{"total":74772,"more_total":23475,"day":20170529},{"total":74670,"more_total":23395,"day":20170528},{"total":73963,"more_total":22998,"day":20170527},{"total":72902,"more_total":22391,"day":20170526},{"total":72686,"more_total":21849,"day":20170525},{"total":71946,"more_total":21602,"day":20170524},{"total":68636,"more_total":21285,"day":20170523},{"total":67429,"more_total":21124,"day":20170522},{"total":67317,"more_total":20651,"day":20170521},{"total":70259,"more_total":20624,"day":20170520},{"total":70546,"more_total":20059,"day":20170519},{"total":67623,"more_total":19286,"day":20170518},{"total":64296,"more_total":18898,"day":20170517},{"total":63790,"more_total":18192,"day":20170516},{"total":61005,"more_total":17769,"day":20170515},{"total":60373,"more_total":17582,"day":20170514},{"total":54112,"more_total":17090,"day":20170513},{"total":51506,"more_total":16905,"day":20170512},{"total":52524,"more_total":16856,"day":20170511},{"total":53048,"more_total":17212,"day":20170510},{"total":49350,"more_total":17376,"day":20170509},{"total":46927,"more_total":17392,"day":20170508},{"total":47772,"more_total":17263,"day":20170507},{"total":48827,"more_total":16877,"day":20170506},{"total":50523,"more_total":17037,"day":20170505},{"total":53555,"more_total":17193,"day":20170504},{"total":54155,"more_total":17056,"day":20170503},{"total":54164,"more_total":16608,"day":20170502},{"total":53586,"more_total":15943,"day":20170501},{"total":52972,"more_total":14970,"day":20170430},{"total":52275,"more_total":14105,"day":20170429},{"total":50818,"more_total":14033,"day":20170428},{"total":45664,"more_total":13803,"day":20170427},{"total":38535,"more_total":13120,"day":20170426},{"total":37448,"more_total":12413,"day":20170425},{"total":36108,"more_total":11613,"day":20170424},{"total":34692,"more_total":10969,"day":20170423},{"total":32843,"more_total":10836,"day":20170422},{"total":30241,"more_total":10804,"day":20170421},{"total":25541,"more_total":10754,"day":20170420},{"total":24747,"more_total":10683,"day":20170419},{"total":24218,"more_total":10522,"day":20170418},{"total":24059,"more_total":10455,"day":20170417},{"total":23732,"more_total":10335,"day":20170416},{"total":23676,"more_total":10197,"day":20170415},{"total":23558,"more_total":10073,"day":20170414},{"total":23379,"more_total":9933,"day":20170413},{"total":23177,"more_total":9811,"day":20170412},{"total":23075,"more_total":9792,"day":20170411},{"total":23050,"more_total":9894,"day":20170410},{"total":22748,"more_total":9960,"day":20170409},{"total":21951,"more_total":9964,"day":20170408},{"total":21318,"more_total":9856,"day":20170407},{"total":21160,"more_total":9587,"day":20170406},{"total":20842,"more_total":9219,"day":20170405},{"total":20352,"more_total":8823,"day":20170404},{"total":19768,"more_total":8354,"day":20170403},{"total":19130,"more_total":7960,"day":20170402},{"total":18546,"more_total":7626,"day":20170401},{"total":18159,"more_total":7223,"day":20170331},{"total":17687,"more_total":6816,"day":20170330},{"total":17292,"more_total":6309,"day":20170329},{"total":16639,"more_total":5697,"day":20170328},{"total":16022,"more_total":5238,"day":20170327},{"total":15101,"more_total":4661,"day":20170326},{"total":13870,"more_total":4057,"day":20170325},{"total":12591,"more_total":3330,"day":20170324},{"total":11004,"more_total":2540,"day":20170323},{"total":9113,"more_total":1774,"day":20170322},{"total":7434,"more_total":1140,"day":20170321},{"total":5521,"more_total":725,"day":20170320},{"total":3949,"more_total":535,"day":20170319},{"total":2464,"more_total":390,"day":20170318},{"total":1569,"more_total":318,"day":20170317},{"total":1163,"more_total":256,"day":20170316},{"total":808,"more_total":204,"day":20170315},{"total":628,"more_total":150,"day":20170314},{"total":512,"more_total":102,"day":20170313},{"total":426,"more_total":60,"day":20170312},{"total":324,"more_total":52,"day":20170311},{"total":246,"more_total":39,"day":20170310},{"total":124,"more_total":29,"day":20170309},{"total":103,"more_total":13,"day":20170308},{"total":74,"more_total":8,"day":20170307},{"total":65,"more_total":0,"day":20170306},{"total":27,"more_total":0,"day":20170305},{"total":20,"more_total":0,"day":20170304},{"total":2,"more_total":0,"day":20170303},{"total":2,"more_total":0,"day":20170302},{"total":2,"more_total":0,"day":20170301}] 
posted @ 2017-06-23 14:38  wepe  阅读(5513)  评论(0)  编辑  收藏  举报