效果展示：
以上数据为测试数据
实现思路：
按照查询天数往前推规定天 比如周、月
登录表为按月分表（数据量大约一张表1000W+数据）
关键sql:
laravel5.1 框架
日活实现方式一：
不使用文件缓存
关键sql : date_format()
[php]view plaincopyprint? /***日活（登录）*@paramRequest$request*@return$this*/publicfunctiondayKeepView(Request$request){$data['start_time']=$request->input('start_time',date('Y-m-d',strtotime('-7days')));$data['end_time']=$request->input('end_time',date('Y-m-d',strtotime('-1days')));if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time())))$data['end_time']=date('Y-m-d',strtotime('-1days'));$start_time=strtotime($data['start_time']);$end_time=strtotime($data['end_time']);$table_prefix='d_user_login';$table_exist=$this->getTableRange($start_time,$end_time,$table_prefix);$select_table=current($table_exist);$res=DB::connection('log')->table($table_prefix.$select_table)->select(DB::raw("count(DISTINCT(uid))astotal,date_format(time,'%Y%m%d')asday"))->where('type','=',0)->where('time','>=',$data['start_time'].'00:00:00')->where('time','<=',$data['end_time'].'23:59:59')->groupBy('day');if(count($table_exist)>0){foreach($table_existas$key=>$val){if($key>0){$res->union(DB::connection('log')->table($table_prefix.$val)->select(DB::raw("count(DISTINCT(uid))astotal,date_format(time,'%Y%m%d')asday"))->where('type','=',0)->where('time','>=',$data['start_time'].'00:00:00')->where('time','<=',$data['end_time'].'23:59:59')->groupBy('day'));}}}$res=$res->get();krsort($res);returnview('chart/keep/dayKeepView')->with('day_keep_data',$res)->with('data',$data);}
日活实现方式二：
文件缓存方式（循环sql）:
[php]view plaincopyprint? /***日活（登录）*@paramRequest$request*@return$this*/publicfunctiondayKeepView(Request$request){$data['start_time']=$request->input('start_time',date('Y-m-d',strtotime('-14days')));$data['end_time']=$request->input('end_time',date('Y-m-d',strtotime('-1days')));if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time())))$data['end_time']=date('Y-m-d',strtotime('-1days'));$start_time=strtotime($data['start_time']);$end_time=strtotime($data['end_time']);//读取文件$file_name=DIRECTORY_SEPARATOR.'total'.DIRECTORY_SEPARATOR.'dayData.json';$day_data=Storage::disk('local')->exists($file_name)?json_decode(Storage::get($file_name),true):[];$day_data_day=count($day_data)>0?array_column($day_data,'day'):[];//循环查询时间区间for($i=$start_time,$select_day=[];$i<=$end_time;$i+=86400){$select_day[]=intval(date('Ymd',$i));}//求出差集$need_select_day=array_diff($select_day,$day_data_day);//循环执行语句查询if(count($need_select_day)>0){$res=[];foreach($need_select_dayas$key=>$val){$self_time=strtotime($val);$self_table=date('Ym',$self_time);$self_start_time=date('Y-m-d00:00:00',$self_time);$self_end_time=date('Y-m-d23:59:59',$self_time);$res[$key]=DB::connection('log')->table('d_user_login'.$self_table)->select(DB::raw("count(distinct(uid))astotal"))->where('type','=',0)->where('time','>=',$self_start_time)->where('time','<=',$self_end_time)->lists('total');$res[$key]['total']=$res[$key][0];unset($res[$key][0]);$res[$key]['day']=$val;};$list=array_merge($res,$day_data);$list=FunctionController::arr_sort($list,'SORT_DESC','day');if(count($day_data_day)>0)Storage::delete($file_name);//修改写入文件Storage::disk('local')->put($file_name,json_encode($list));$day_data=$list;}//读取文件缓存//依据时间区间读取$start_time=date('Ymd',$start_time);$end_time=date('Ymd',$end_time);foreach($day_dataas$key=>$val){if($val['day']==$start_time)$start=$key;if($val['day']==$end_time)$end=$key;if(isset($start)&&isset($end))break;}$list=array_slice($day_data,$end,$start-$end+1);returnview('chart/keep/dayKeepView')->with('day_keep_data',$list)->with('data',$data);}
周活：
关键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_login05` where type=0 and time between '-05-28'and '-06-04' group by day,uid union all SELECT count(distinct(uid)),date_format(time,'%Y%m%d') as day,uid from `d_user_login06` where type=0 and time between'-05-28'and '-06-04' group by day,uid) as t1 group by uid having rtotal>3 order by null) as t2 where uid>20000 ; (周跨月)
[php]view plaincopyprint? /***周活（登录表）*@paramRequest$request*@return$this*/publicfunctionweekKeepView(Request$request){set_time_limit(0);$data['start_time']=$request->input('start_time',date('Y-m-d',strtotime('-14days')));$data['end_time']=$request->input('end_time',date('Y-m-d',strtotime('-1days')));if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time())))$data['end_time']=date('Y-m-d',strtotime('-1days'));$start_time=strtotime($data['start_time']);$end_time=strtotime($data['end_time']);//读取文件$file_name=DIRECTORY_SEPARATOR.'total'.DIRECTORY_SEPARATOR.'weekData.json';$week_data=Storage::disk('local')->exists($file_name)?json_decode(Storage::get($file_name),true):[];$week_data_day=count($week_data)>0?array_column($week_data,'day'):[];//循环查询时间区间for($i=$start_time,$select_day=[];$i<=$end_time;$i+=86400){$select_day[]=intval(date('Ymd',$i));}//求出差集$need_select_day=array_diff($select_day,$week_data_day);//循环执行语句查询if(count($need_select_day)>0){$res=[];foreach($need_select_dayas$key=>$val){$self_time=strtotime($val);$self_start_time=date('Ymd',$self_time-86400*6);$self_end_time=date('Ymd',$self_time+86400);if(substr($self_start_time,0,6)==substr($self_end_time,0,6)){//周活$res[$key]['week']=DB::connection('log')->table('d_user_login'.substr($val,0,6))->select(DB::raw("count(distinct(uid))astotal"))->where('type','=',0)->whereBetween('time',[$self_start_time,$self_end_time])->lists('total');//周重活一天有4次登录及以上$res[$key]['week_more']=DB::connection('log')->select(DB::raw("selectcount(uid)astotalfrom(selectcount(uid)asrtotal,uidfrom(SELECTcount(distinct(uid)),date_format(time,'%Y%m%d')asday,uidfromd_user_login".substr($val,0,6)."wheretype=0andtime>='".$self_start_time."'andtime<'".$self_end_time."'groupbyday,uid)ast1groupbyuidhavingrtotal>3)asttwhereuid>20000"));}else{//周活$sql=DB::connection('log')->table('d_user_login'.substr($self_start_time,0,6))->select('uid')->where('type','=',0)->whereBetween('time',[$self_start_time,$self_end_time])->union(DB::connection('log')->table('d_user_login'.substr($self_end_time,0,6))->select('uid')->where('type','=',0)->whereBetween('time',[$self_start_time,$self_end_time]));$list_sql=$sql->tosql();$list_val=$sql->getBindings();$sql_res=self::getStringReplace($list_val,$list_sql);$res[$key]['week']=DB::connection('log')->table(DB::raw('('.$sql_res.')astem'))->select(DB::raw('count(uid)astotal'))->lists('total');//周重活$res[$key]['week_more']=DB::connection('log')->select(DB::raw("selectcount(uid)astotalfrom(selectcount(uid)asrtotal,uidfrom(selectcount(distinct(uid)),date_format(time,'%Y%m%d')asday,uidfromd_user_login".substr($self_start_time,0,6)."wheretype=0andtime>='".$self_start_time."'andtime<'".$self_end_time."'groupbyday,uidunionallSELECTcount(distinct(uid)),date_format(time,'%Y%m%d')asday,uidfromd_user_login".substr($self_end_time,0,6)."wheretype=0andtime>='".$self_start_time."'andtime<'".$self_end_time."'groupbyday,uidorderbynull)ast1groupbyuidhavingrtotal>3orderbynull)ast2whereuid>20000;"));}$res[$key]['total']=$res[$key]['week'][0];$res[$key]['more_total']=$res[$key]['week_more'][0]->total;unset($res[$key]['week']);unset($res[$key]['week_more']);$res[$key]['day']=$val;};$list=array_merge($res,$week_data);$list=FunctionController::arr_sort($list,'SORT_DESC','day');if(count($week_data_day)>0)Storage::delete($file_name);//修改写入文件Storage::disk('local')->put($file_name,json_encode($list));$week_data=$list;}//读取文件缓存//依据时间区间读取end$start_time=date('Ymd',$start_time);$end_time=date('Ymd',$end_time);foreach($week_dataas$key=>$val){if($val['day']==$start_time)$start=$key;if($val['day']==$end_time)$end=$key;if(isset($start)&&isset($end))break;}$list=array_slice($week_data,$end,$start-$end+1);returnview('chart/keep/weekKeepView')->with('week_keep_data',$list)->with('data',$data);}
月活：[php]view plaincopyprint? /***月活（登录表）*@paramRequest$request*@return$this*/publicfunctionmonthKeepView(Request$request){set_time_limit(0);$data['start_time']=$request->input('start_time',date('Y-m-d',strtotime('-14days')));$data['end_time']=$request->input('end_time',date('Y-m-d',strtotime('-1days')));if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time())))$data['end_time']=date('Y-m-d',strtotime('-1days'));$start_time=strtotime($data['start_time']);$end_time=strtotime($data['end_time']);//读取文件$file_name=DIRECTORY_SEPARATOR.'total'.DIRECTORY_SEPARATOR.'monthData.json';$month_data=Storage::disk('local')->exists($file_name)?json_decode(Storage::get($file_name),true):[];$month_data_day=count($month_data)>0?array_column($month_data,'day'):[];//循环查询时间区间for($i=$start_time,$select_day=[];$i<=$end_time;$i+=86400){$select_day[]=intval(date('Ymd',$i));}//求出差集$need_select_day=array_diff($select_day,$month_data_day);//循环执行语句查询if(count($need_select_day)>0){$res=[];foreach($need_select_dayas$key=>$val){$self_time=strtotime($val);$self_start_time=date('Ymd',strtotime($val.'-1month'));$self_end_table=date('Ymd',$self_time);$self_end_time=date('Y-m-d23:59:59',$self_time);if(substr($self_start_time,0,6)==substr($self_end_time,0,6)){$res[$key]=DB::connection('log')->table('d_user_login'.substr($val,0,6))->select(DB::raw("count(distinct(uid))astotal"))->where('type','=',0)->where('time','>=',$self_start_time)->where('time','<=',$self_end_time)->lists('total');}else{$sql=DB::connection('log')->table('d_user_login'.substr($self_start_time,0,6))->select('uid')->where('type','=',0)->whereBetween('time',[$self_start_time,$self_end_time])->union(DB::connection('log')->table('d_user_login'.substr($self_end_table,0,6))->select('uid')->where('type','=',0)->where('time','>=',$self_start_time)->where('time','<=',$self_end_time));$list_sql=$sql->tosql();$list_val=$sql->getBindings();$sql_res=self::getStringReplace($list_val,$list_sql);$res[$key]=DB::connection('log')->table(DB::raw('('.$sql_res.')astem'))->select(DB::raw('count(uid)astotal'))->lists('total');$res[$key]['total']=$res[$key][0];unset($res[$key][0]);$res[$key]['day']=$val;}};$list=array_merge($res,$month_data);$list=FunctionController::arr_sort($list,'SORT_DESC','day');if(count($month_data_day)>0)Storage::delete($file_name);//修改写入文件Storage::disk('local')->put($file_name,json_encode($list));$month_data=$list;}//读取文件缓存//依据时间区间读取$start_time=date('Ymd',$start_time);$end_time=date('Ymd',$end_time);foreach($month_dataas$key=>$val){if($val['day']==$start_time)$start=$key;if($val['day']==$end_time)$end=$key;if(isset($start)&&isset($end))break;}$list=array_slice($month_data,$end,$start-$end+1);returnview('chart/keep/monthKeepView')->with('month_keep_data',$list)->with('data',$data);}
生成json 文件的时候会比较慢 可以自己写PHP或者shell脚本去执行sql 操作！！
json 数据格式：
[html]view plaincopyprint? [{"total":80221,"more_total":22477,"day":0619},{"total":74828,"more_total":22482,"day":0618},{"total":74407,"more_total":22768,"day":0617},{"total":73924,"more_total":23069,"day":0616},{"total":74853,"more_total":23034,"day":0615},{"total":77244,"more_total":23483,"day":0614},{"total":77324,"more_total":23356,"day":0613},{"total":75257,"more_total":23141,"day":0612},{"total":75664,"more_total":22491,"day":0611},{"total":75657,"more_total":21653,"day":0610},{"total":75506,"more_total":21681,"day":0609},{"total":73061,"more_total":21518,"day":0608},{"total":69133,"more_total":21737,"day":0607},{"total":66667,"more_total":21718,"day":0606},{"total":63960,"more_total":21589,"day":0605},{"total":65249,"more_total":21486,"day":0604},{"total":66902,"more_total":21803,"day":0603},{"total":66706,"more_total":22323,"day":0602},{"total":67731,"more_total":22794,"day":0601},{"total":69316,"more_total":23015,"day":0531},{"total":73032,"more_total":23450,"day":0530},{"total":74772,"more_total":23475,"day":0529},{"total":74670,"more_total":23395,"day":0528},{"total":73963,"more_total":22998,"day":0527},{"total":72902,"more_total":22391,"day":0526},{"total":72686,"more_total":21849,"day":0525},{"total":71946,"more_total":21602,"day":0524},{"total":68636,"more_total":21285,"day":0523},{"total":67429,"more_total":21124,"day":0522},{"total":67317,"more_total":20651,"day":0521},{"total":70259,"more_total":20624,"day":0520},{"total":70546,"more_total":9,"day":0519},{"total":67623,"more_total":19286,"day":0518},{"total":64296,"more_total":18898,"day":0517},{"total":63790,"more_total":18192,"day":0516},{"total":61005,"more_total":17769,"day":0515},{"total":60373,"more_total":17582,"day":0514},{"total":54112,"more_total":17090,"day":0513},{"total":51506,"more_total":16905,"day":0512},{"total":52524,"more_total":16856,"day":0511},{"total":53048,"more_total":17212,"day":0510},{"total":49350,"more_total":17376,"day":0509},{"total":46927,"more_total":17392,"day":0508},{"total":47772,"more_total":17263,"day":0507},{"total":48827,"more_total":16877,"day":0506},{"total":50523,"more_total":17037,"day":0505},{"total":53555,"more_total":17193,"day":0504},{"total":54155,"more_total":17056,"day":0503},{"total":54164,"more_total":16608,"day":0502},{"total":53586,"more_total":15943,"day":0501},{"total":52972,"more_total":14970,"day":0430},{"total":52275,"more_total":14105,"day":0429},{"total":50818,"more_total":14033,"day":0428},{"total":45664,"more_total":13803,"day":0427},{"total":38535,"more_total":13120,"day":0426},{"total":37448,"more_total":12413,"day":0425},{"total":36108,"more_total":11613,"day":0424},{"total":34692,"more_total":10969,"day":0423},{"total":32843,"more_total":10836,"day":0422},{"total":30241,"more_total":10804,"day":0421},{"total":25541,"more_total":10754,"day":0420},{"total":24747,"more_total":10683,"day":0419},{"total":24218,"more_total":10522,"day":0418},{"total":24059,"more_total":10455,"day":0417},{"total":23732,"more_total":10335,"day":0416},{"total":23676,"more_total":10197,"day":0415},{"total":23558,"more_total":10073,"day":0414},{"total":23379,"more_total":9933,"day":0413},{"total":23177,"more_total":9811,"day":0412},{"total":23075,"more_total":9792,"day":0411},{"total":23050,"more_total":9894,"day":0410},{"total":22748,"more_total":9960,"day":0409},{"total":21951,"more_total":9964,"day":0408},{"total":21318,"more_total":9856,"day":0407},{"total":21160,"more_total":9587,"day":0406},{"total":20842,"more_total":9219,"day":0405},{"total":20352,"more_total":8823,"day":0404},{"total":19768,"more_total":8354,"day":0403},{"total":19130,"more_total":7960,"day":0402},{"total":18546,"more_total":7626,"day":0401},{"total":18159,"more_total":7223,"day":0331},{"total":17687,"more_total":6816,"day":0330},{"total":17292,"more_total":6309,"day":0329},{"total":16639,"more_total":5697,"day":0328},{"total":16022,"more_total":5238,"day":0327},{"total":15101,"more_total":4661,"day":0326},{"total":13870,"more_total":4057,"day":0325},{"total":12591,"more_total":3330,"day":0324},{"total":11004,"more_total":2540,"day":0323},{"total":9113,"more_total":1774,"day":0322},{"total":7434,"more_total":1140,"day":0321},{"total":5521,"more_total":725,"day":0320},{"total":3949,"more_total":535,"day":0319},{"total":2464,"more_total":390,"day":0318},{"total":1569,"more_total":318,"day":0317},{"total":1163,"more_total":256,"day":0316},{"total":808,"more_total":204,"day":0315},{"total":628,"more_total":150,"day":0314},{"total":512,"more_total":102,"day":0313},{"total":426,"more_total":60,"day":0312},{"total":324,"more_total":52,"day":0311},{"total":246,"more_total":39,"day":0310},{"total":124,"more_total":29,"day":0309},{"total":103,"more_total":13,"day":0308},{"total":74,"more_total":8,"day":0307},{"total":65,"more_total":0,"day":0306},{"total":27,"more_total":0,"day":0305},{"total":20,"more_total":0,"day":0304},{"total":2,"more_total":0,"day":0303},{"total":2,"more_total":0,"day":0302},{"total":2,"more_total":0,"day":0301}]
如果觉得《日活 周活（周重活） 月活 统计》对你有帮助，请点赞、收藏，并留下你的观点哦！
