用户留存（app）统计

一个产品的用户留存关系到该产品是否健康的发展

实现效果：

表详情：

注册表：d_user_register201704  以月份分表  （uid 唯一）

CREATE TABLE `d_user_register201704` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `uid` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',  `inviteUid` bigint(20) NOT NULL,  `name` varchar(32) NOT NULL DEFAULT '',  `account` varchar(32) NOT NULL DEFAULT '' COMMENT '用户名',  `systemType` smallint(3) NOT NULL DEFAULT '0' COMMENT '用户注册系统标识(ios-0 android-1...)',  `authorizationType` smallint(3) NOT NULL DEFAULT '0' COMMENT '用户注册第三方授权标识',  `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  UNIQUE KEY `time` (`time`) USING BTREE,  KEY `uid` (`uid`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

登录表：d_user_register201704 同样以月份分表（0 代表登录  用户每登录一次就会插入一条数据  很大的数据冗余  数据大约300W条左右）

实现思路：

首先将要查时间区间的每一天所对应的用户留存数据计算出来(考虑到分表我们使用union 进行联查)

实现sql

    select count(uid) as onl,date_format(time,'%H') as hour  from d_user_login201704 where id in (select * from ((select min(id) from d_user_login201704 where type=0 and  time>= '2017-04-09 00:00:00' and time<='2017-04-09 23:59:59' group by(uid)) as tmptable)) group by hour;  

笔者考虑了好长时间也没有想到除了循环查询 能更好的查询办法

下面依次循环 要查询的日期 考虑到分表 我们使用union  日期唯一所有使用union　想要去重就使用union all

select count(DISTINCT(uid)) as liu,date_format(time,'%Y%m%d') as day from d_user_login201704 where uid in (select  uid  from `d_user_register201704`  where time BETWEEN '2017-04-08 00:00:00' and '2017-04-08 23:59:59')  and time BETWEEN '2017-04-09'  and  '2017-05-09' group by day  union  select count(DISTINCT(uid)) as liu,date_format(time,'%Y%m%d') as day from d_user_login201705 where uid in (select  uid  from `d_user_register201704`  where time BETWEEN '2017-04-08 00:00:00' and '2017-04-08 23:59:59')  and time BETWEEN '2017-04-09'  and  '2017-05-09' group by day

下面贴出代码：

php   控制器

 /**     * 用户留存     * @param Request $request     * @return $this     */    public function userKeepView(Request $request){        $data['start_time'] = $request->input('start_time', date('Y-m-d',strtotime("-15 day") ));        $data['end_time'] = $request->input('end_time', date('Y-m-d'));        $start_month = date('Ym',strtotime($data['start_time']));        $end_month = date('Ym',strtotime($data['end_time']));        $table_prefix = 'd_user_register';        //获得查询表集合        $select_set = [];        for($i=$start_month;$i<=$end_month;$i++){            $select_set[] = intval($i);        }        //获得已有表集合        $table_prefix_length = strlen($table_prefix);        $tables = DB::connection('log')->select("show tables like 'd_user_register%'");        $register_tables_set = [];        foreach($tables as $key=>$value){            $value = (array)$value;            $table = $value['Tables_in_dingdlog (d_user_register%)'];            $res = (int)substr($table,$table_prefix_length);            array_push($register_tables_set,$res);        }        $register_tables_set = array_filter($register_tables_set);        sort($register_tables_set);        //获取最终查询表交集        $register_tables = array_values(array_intersect($register_tables_set,$select_set));        //循环查询        $user_keep_data = [];        $first_table = current($register_tables);        $end_table = end($register_tables);        //锁定存在表时间区间        $first = date('Y-m-d 00:00:00',strtotime($first_table.'01'));        $end = date('Y-m-d 23:59:59',strtotime($end_table."01 +1month -1day"));        if(strtotime($data['start_time'])<strtotime($first)){            $data['start_time'] = $first;        }        if(strtotime($data['end_time'])>strtotime($end)){            $data['end_time'] = $end;        }        //循环时间区间以天查询        for($i=strtotime($data['start_time']);$i<=strtotime($data['end_time']);$i+=24*3600){           //当前时间            $self_time = date('Y-m-d',$i);            $self_next_time = date('Y-m-d',$i+24*3600);            $self_month = date('Ym',$i);            //30天查询            $self_next_month = date('Y-m-d',strtotime($self_time.'+ 31 day'));            $select_next_table = date('Ym',strtotime('+1month -1day'));            //先将本天的用户留存率算出来            $uid_set = DB::connection('log')->table($table_prefix.$self_month)->where('time','>=',$self_time.' 00:00:00')                      ->where('time','<=',$self_time.' 23:59:59')->lists('uid');            $self_total = count($uid_set);            $date[] = date('Y.m.d',$i);            $total[] =  $self_total;            if(in_array($select_next_table,$register_tables_set)){                //使用union连接上查询                $result = DB::connection('log')->table('d_user_login'.$select_next_table)                ->select(DB::raw("count(DISTINCT(uid)) as liu,date_format(time,'%Y%m%d') as day"))                ->whereBetween('time',[$self_next_time,$self_next_month])->where('type','=','0')->whereIn('uid',$uid_set)->groupby('day');                $user_keep_data [] =  DB::connection('log')->table('d_user_login'.$self_month)                 ->select(DB::raw("count(DISTINCT(uid)) as liu,date_format(time,'%Y%m%d') as day"))                 ->whereBetween('time',[$self_next_time,$self_next_month])->where('type','=','0')->whereIn('uid',$uid_set)->groupby('day')                 ->union($result)->get();            }else{                $user_keep_data [] =  DB::connection('log')->table('d_user_login'.$self_month)                 ->select(DB::raw("count(DISTINCT(uid)) as liu,date_format(time,'%Y%m%d') as day"))                ->whereBetween('time',[$self_next_time,$self_next_month])->where('type','=','0')->whereIn('uid',$uid_set)->groupby('day')->get();            }        }        rsort($date);        $total = array_reverse($total,false);        $user_keep_data = array_reverse($user_keep_data,false);        return view('chart/userKeepView')->with('user_keep_data', $user_keep_data)            ->with('total', $total)            ->with('date',$date)            ->with('data',$data);    }

html

<!DOCTYPE html><html lang="zh-cn">@include('layouts.head')<body><div class="container-fluid">    <div class="panel panel-default">        <div class="panel-heading">           用户留存        </div>        <div class="panel-body">            <form action="{{action('Home\ChartController@userKeepView')}}" method="post" role="form">                <div class="row">                    <div class="col-md-6 form-inline">                        <div class="form-group form-inline">                            <label for="">开始时间</label>                            <input type="text" name="start_time" class="form-control form_date" value="{{$data['start_time']}}" readonly placeholder="请输入id"/>                        </div>                        <div class="form-group form-inline">                            <label for="">结束时间</label>                            <input type="text" name="end_time" class="form-control form_date" value="{{$data['end_time']}}" readonly placeholder="请输入id"/>                        </div>                    </div>                    @if(session('msg'))                        <p style="color:red">{{session('msg')}}</p>                    @endif                    <div class="col-md-3">                        <label for=""> </label>                        <div class="form-group">                            <input type="submit" class="btn btn-sm btn-primary" value="查询"/>                        </div>                    </div>                </div>            </form>        </div>    </div>    <table class="table table-responsive table-bordered">        <thead>        <th>日期</th>        <th>当天注册人数</th>        <th>次日留存</th>        <th>2日留存</th>        <th>3日留存</th>        <th>4日留存</th>        <th>5日留存</th>        <th>6日留存</th>        <th>7日留存</th>        <th>14日留存</th>        <th>30日留存</th>        </thead>        <tbody>        @if($user_keep_data)            @foreach($user_keep_data as $key=>$value)                <tr>                    <td>{{$date[$key]}}</td>                    <td>{{$total[$key]}}</td>                    @foreach($value as $k=>$v)                            @if($k<7||$k==13||$k==29)                            <td>{{number_format($v->liu/$total[$key]*100,2,'.','')}}%</td>                            @endif                    @endforeach                </tr>            @endforeach        @else            <tr>                <td colspan="4">暂无数据</td>            </tr>        @endif        </tbody>    </table></div></body><script type="text/javascript">    $('.form_date').datetimepicker({        language: 'zh-CN', /*加载日历语言包，可自定义*/        weekStart: 1, /*星期*/        todayBtn: 0, /*当天*/        autoclose: true,//选中之后自动隐藏日期选择框        todayHighlight: 1, /*今天高亮显示*/        startView: 2, /*4年3月2日1小时*/        minView: 2, /*0分1小时2天*/        format: 'yyyy-mm-dd',        forceParse: 0,        showMeridian:true    });</script></html>

explain 测试sql :

explain select count(DISTINCT(uid)) as liu,date_format(time,'%Y%m%d') as day from d_user_login201704 where uid in (select  uid  from `d_user_register201704`  where time BETWEEN '2017-04-08 00:00:00' and '2017-04-08 23:59:59')and time BETWEEN '2017-04-09'  and  '2017-05-09' group by day  union  select count(DISTINCT(uid)) as liu,date_format(time,'%Y%m%d') as day from d_user_login201705 where uid in (select  uid  from `d_user_register201704`  where time BETWEEN '2017-04-08 00:00:00' and '2017-04-08 23:59:59') and time BETWEEN '2017-04-09'  and  '2017-05-09' group by day

由于 login5 表没有建立索引 所以没有使用到      （笔者在 register 表 uid 以及 login  表的 uid  和 time 上建立了索引）

查询197ms   循环得出结果需要2S 左右   需要优化的地方还有很多！！