日活、周活（周重活）、月活 统计
来源：互联网 发布：网络基础知识说课ppt 编辑：程序博客网 时间：2024/04/30 15:37
效果展示：
以上数据为测试数据
实现思路：
按照查询天数往前推规定天 比如周、月
登录表为按月分表（数据量大约一张表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}]阅读全文
0 0
- 日活、周活（周重活）、月活 统计
- 日活、周活（周重活）、月活 统计
- 年、日、月，周访问量统计
- 实现日、周、月排行统计 sql
- 实现日、周、月排行统计 sql
- JSP中年、日、月，周访问量统计方法
- Java实现：月，日，年，周，访问量统计
- Java实现：月，日，年，周，访问量统计
- 投稿统计 (7月23日)
- 实现统计的年，月，日
- Tableau--使用参数为报表增加一个统计周期筛选条件（日，周，月，季度，年）
- 周 月 日 互转
- 文章统计日点击、周点击、月点击制作思路(存储过程实现)
- 9月11日作业 （泛型）和统计字母个数
- 全国高级项目经理人数知多少？（数据统计截止2013年6月22日）
- 这周的工作总结（11月10日 - 11月14日）
- 截止2010年3月25日的流量统计
- Mysql 根据时间戳按年月日分组统计
- 及事务四个特性
- 横着显示各工序数据
- 【WPF】关闭非装窗体以外所有窗体
- Qt 5.6.1 + vs2013 加载自己编写的静态链接库
- 指针笔记
- 日活、周活（周重活）、月活 统计
- 切换标题栏文字颜色
- 在64位windows7下CMD命令安装python与Flask
- CRC算法
- cv::Mat类型转为CImage，MFC传入窗口句柄显示CImage图片
- 连接池技术
- 01背包及完全背包问题（51Nod
- 首席架构师眼里的架构本质
- postman下载
