效果展示:
以上数据为测试数据
实现思路:
按照查询天数往前推规定天 比如周、月
登录表为按月分表(数据量大约一张表1000W+数据)
关键sql:
laravel5.1 框架
日活实现方式一:
不使用文件缓存
关键sql : date_format()
/**
* 日活(登录)
* @param Request $request
* @return $this
*/
publicfunctiondayKeepView(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_existas$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);
returnview('chart/keep/dayKeepView')->with('day_keep_data',$res)->with('data',$data);
}
日活实现方式二:
文件缓存方式(循环sql):
/**
* 日活(登录)
* @param Request $request
* @return $this
*/
publicfunctiondayKeepView(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_dayas$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][&