效果展示：
以上数据为测试数据
实现思路：
按照查询天数往前推规定天 比如周、月
登录表为按月分表（数据量大约一张表1000W+数据）
关键sql:
laravel5.1 框架
日活实现方式一：
不使用文件缓存
关键sql : date_format()
/**
* 日活（登录）
* @param Request $request
* @return $this
*/
public function dayKeepView(Request $request){
$data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-7 days')));
$data['end_time'] = $request->input('end_time',date('Y-m-d',strtotime('-1 days')));
if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time()))) $data['end_time']=date('Y-m-d',strtotime('-1 days'));
$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)) as total ,date_format(time,'%Y%m%d') as day"))
->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_exist as $key=>$val){
if($key>0){
$res->union(
DB::connection('log')->table($table_prefix.$val)
->select(DB::raw("count(DISTINCT(uid)) as total ,date_format(time,'%Y%m%d') as day"))
->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);
return view('chart/keep/dayKeepView')->with('day_keep_data',$res)->with('data',$data);
}
日活实现方式二：
文件缓存方式（循环sql）:
/**
* 日活（登录）
* @param Request $request
* @return $this
*/
public function dayKeepView(Request $request){
$data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-14 days')));
$data['end_time'] = $request->input('end_time',date('Y-m-d',strtotime('-1 days')));
if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time()))) $data['end_time']=date('Y-m-d',strtotime('-1 days'));
$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_day as $key => $val) {
$self_time = strtotime($val);
$self_table = date('Ym',$self_time);
$self_start_time = date('Y-m-d 00:00:00',$self_time);
$self_end_time = date('Y-m-d 23:59:59',$self_time);
$res[$key] = DB::connection('log')->table('d_user_login'.$self_table)
->select(DB::raw("count(distinct(uid)) as total"))
->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_data as $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);
return view('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_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 ; (周跨月)
/**
* 周活（登录表）
* @param Request $request
* @return $this
*/
public function weekKeepView(Request $request){
set_time_limit(0);
$data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-14 days')));
$data['end_time'] = $request->input('end_time',date('Y-m-d',strtotime('-1 days')));
if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time()))) $data['end_time']=date('Y-m-d',strtotime('-1 days'));
$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_day as $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)) as total"))
->where('type','=',0)
->whereBetween('time',[$self_start_time,$self_end_time])
->lists('total');
// 周重活 一天有4次登录及以上
$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"));
}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.') as tem'))
->select(DB::raw('count(uid) as total'))->lists('total');
//周重活
$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;"));
}
$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_data as $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);
return view('chart/keep/weekKeepView')->with('week_keep_data',$list)->with('data',$data);
}
月活：
/**
* 月活（登录表）
* @param Request $request
* @return $this
*/
public function monthKeepView(Request $request){
set_time_limit(0);
$data['start_time'] = $request->input('start_time',date('Y-m-d',strtotime('-14 days')));
$data['end_time'] = $request->input('end_time',date('Y-m-d',strtotime('-1 days')));
if(strtotime($data['end_time'])>=strtotime(date('Y-m-d',time()))) $data['end_time']=date('Y-m-d',strtotime('-1 days'));
$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_day as $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-d 23: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)) as total"))
->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)
->where('time','>=',$self_start_time)
->where('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.') as tem'))
->select(DB::raw('count(uid) as total'))->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_data as $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);
return view('chart/keep/monthKeepView')->with('month_keep_data',$list)->with('data',$data);
}
生成json 文件的时候会比较慢 可以自己写php或者shell脚本去执行sql 操作！！
json 数据格式：
[{"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}]