依据注册日期查询出对应渠道留存

实现效果（数据为测试数据）

难点：sql 编写  (分组以及inner join  union all 的运用)

            登录表按月表之后的查询（30天  需要考虑到月份31日  二月份 28日）

实现sql:

查询日期在同一月份（eg: 6.1 -6.30）

select day,count(uid) as total,channel from (select date_format(l.time,'%Y%m%d') as day,r.uid,r.`channel` from `d_user_register201705` as r  INNER JOIN  `d_user_login201706` as l on r.uid=l.uid where r.time>='2017-05-31 00:00:00' and r.time<='2017-05-31 23:59:59' and l.type=0 and l.time>='2017-06-01 00:00:00' and l.time<='2017-06-30 23:59:59' group by day,r.uid ) as tmp group by day,channel

查询日期跨一个自然月（eg:06.6-07.6）

select day,count(uid) as total,channel from (select date_format(l1.time,'%Y%m%d') as day,r.uid,r.`channel` from `d_user_register201706` as r  INNER JOIN  `d_user_login201706` as l1  on r.uid=l1.uid where r.time>='2017-06-05 00:00:00'  and r.time<='2017-06-05 23:59:59' and l1.type=0 and l1.time>='2017-06-06 00:00:00'  and l1.time<='2017-07-05 23:59:59' group by day,uid union all  select date_format(l2.time,'%Y%m%d') as day,r.uid,r.`channel` from `d_user_register201706` as r  INNER JOIN  `d_user_login201707` as l2  on r.uid=l2.uid where r.time>='2017-06-05 00:00:00'  and r.time<='2017-06-05 23:59:59'  and l2.type=0 and l2.time>='2017-06-06 00:00:00'  and l2.time<='2017-07-05 23:59:59' group by day,uid) as tt group by day,channel order by day desc

查询日期在跨越2月份（eg:1.31-3.1） 按月分表容易出现错误

select day,count(uid) as total,channel from (select date_format(l1.time,'%Y%m%d') as day,r.uid,r.`channel` from `d_user_register201701` as r INNER JOIN `d_user_login201701` as l1 on r.uid=l1.uid where r.time>='2017-01-30 00:00:00' and r.time<='2017-01-30 23:59:59' and l1.type=0 and l1.time>='2017-01-31 00:00:00' and l1.time<='2017-03-01 23:59:59' group by day,uid union all select date_format(l2.time,'%Y%m%d') as day,r.uid,r.`channel` from `d_user_register201701` as r INNER JOIN `d_user_login201702` as l2 on r.uid=l2.uid where r.time>='2017-01-30 00:00:00' and r.time<='2017-01-30 23:59:59' and l2.type=0 and l2.time>='2017-01-31 00:00:00' and l2.time<='2017-03-01 23:59:59' group by day,uid  union all select date_format(l3.time,'%Y%m%d') as day,r.uid,r.`channel` from `d_user_register201701` as r INNER JOIN `d_user_login201703` as l3 on r.uid=l3.uid where r.time>='2017-01-30 00:00:00' and r.time<='2017-01-30 23:59:59' and l3.type=0 and l3.time>='2017-01-31 00:00:00' and l3.time<='2017-03-01 23:59:59' group by day,uid 
) as tt group by day,channel

实现代码 ：  （laravel 框架）

/**
     * 渠道留存对比
     * @param Request $request
     * @return $this
     */
    public function  channelDayKeep(Request $request){
        $data['time'] = $request->input('time',date('Y-m-d',strtotime('-1 day')));
        $time = strtotime($data['time']);
        for($i=$time+86400,$select_day=[];$i<=$time+30*86400;$i+=86400) $select_day[] = date('Ymd',$i);
        list($self_month,$select_start_month,$select_end_month,$select_start_day,$select_end_day) = [
            date('Ym',$time),
            substr(reset($select_day),0,6),
            substr(end($select_day),0,6),
            date('Y-m-d',strtotime(reset($select_day))),
            date('Y-m-d',strtotime(end($select_day)))
        ];
        if($time>=strtotime(date('Y-m-d'))) $data['time']=date('Y-m-d',strtotime('-1 days'));
        // 查询出当前日期成功渠道对应的渠道注册人数
        $channel_list = Channel::where('status','=',1)->lists('channel_name')->toArray();
        $channel = Channel::where('status','=',1)->lists('channel_describe','channel_name')->toArray();
        $register_channel_total = DB::connection('log')->table('d_user_register'.$self_month)
            ->select(DB::raw('count(uid) as total,channel'))
            ->where('time','>=',$data['time'].' 00:00:00')
            ->where('time','<=',$data['time'].' 23:59:59')
            ->whereRaw('binary trim(channel) in (\''.implode($channel_list,"','").'\')')
            ->groupBy('channel')->get();
        $register_channel_total = json_encode($register_channel_total);
        $register_channel_total = json_decode($register_channel_total,true);;
        //获取各个渠道30天内渠道登录人数
        if($select_end_month - $select_start_month==0){
            //当月
            $result = DB::connection('log')->select(DB::raw("select day,count(uid) as total,channel from (select date_format(l.time,'%Y%m%d') as day,
           r.uid,r.`channel` from `d_user_register{$self_month}` as r  INNER JOIN  `d_user_login{$select_start_month}` as l 
           on r.uid=l.uid where r.time>='{$data['time']} 00:00:00' and r.time<='{$data['time']} 23:59:59' and l.type=0 
           and l.time>='{$select_start_day} 00:00:00' and l.time<='{$select_end_day} 23:59:59' group by day,r.uid ) as tmp group by day,channel"));
        }elseif($select_end_month - $select_start_month==2){
            //2月28天
            $select_middle_month = $select_start_month+1;
            $result = DB::connection('log')->select(DB::raw(" select day,count(uid) as total,channel from (select date_format(l1.time,'%Y%m%d') as day,
             r.uid,r.`channel` from `d_user_register{$self_month}` as r  INNER JOIN  `d_user_login{$select_start_month}` as l1  
             on r.uid=l1.uid where r.time>='{$data['time']} 00:00:00'  and r.time<='{$data['time']} 23:59:59' and l1.type=0
           and l1.time>='{$select_start_day} 00:00:00'  and l1.time<='{$select_end_day} 23:59:59' group by day,uid union all 
            select date_format(l2.time,'%Y%m%d') as day,r.uid,r.`channel` from `d_user_register{$self_month}` as r  INNER JOIN  
          `d_user_login{$select_middle_month}` as l2  on r.uid=l2.uid where r.time>='{$data['time']} 00:00:00'  and r.time<='{$data['time']} 23:59:59' 
           and l2.type=0 and l2.time>='{$select_start_day} 00:00:00'  and l2.time<='{$select_end_day} 23:59:59' group by day,uid
          union all select date_format(l3.time,'%Y%m%d') as day,r.uid,r.`channel` from `d_user_register{$self_month}` as r  INNER JOIN 
          `d_user_login{$select_end_month}` as l3  on r.uid=l3.uid where r.time>='{$data['time']} 00:00:00'  and r.time<='{$data['time']} 23:59:59' 
          and l3.type=0 and l3.time>='{$select_start_day} 00:00:00'  and l3.time<='{$select_end_day} 23:59:59' group by day,uid) as tt group by day,channel"));
        }else{
            //相邻月
            $result = DB::connection('log')->select(DB::raw(" select day,count(uid) as total,channel from (select date_format(l1.time,'%Y%m%d') as day,
        r.uid,r.`channel` from `d_user_register{$self_month}` as r  INNER JOIN  `d_user_login{$select_start_month}` as l1  on r.uid=l1.uid 
         where r.time>='{$data['time']} 00:00:00'  and r.time<='{$data['time']} 23:59:59' and l1.type=0 and l1.time>='{$select_start_day} 00:00:00' 
          and l1.time<='{$select_end_day} 23:59:59' group by day,uid  union all select date_format(l3.time,'%Y%m%d') as day,r.uid,r.`channel` 
         from `d_user_register{$self_month}` as r  INNER JOIN  `d_user_login{$select_end_month}` as l3  
         on r.uid=l3.uid where r.time>='{$data['time']} 00:00:00'  and r.time<='{$data['time']} 23:59:59'
       and l3.type=0 and l3.time>='{$select_start_day} 00:00:00'  and l3.time<='{$select_end_day} 23:59:59' group by day,uid) 
       as tt group by day,channel"));
        }
        //对象转换数组
        $result = json_encode($result);
        $result = json_decode($result,true);
        foreach($result as $key=>$val){
            foreach($register_channel_total as $k=>$v){
                if($v['channel']==$val['channel']){
                    foreach($select_day as $j=>$g) {
                        if ($val['day'] == $g) {
                            $register_channel_total[$k]['son'][$j] = $val;continue;
                        }
                    }
                }
            }
        }
        return view('chart/channel/channelDayKeep')->with('channel_keep_day',$register_channel_total)->with('data',$data)->with('channel',$channel);
    }

视图：

<!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@channelDayKeep')}}" method="post" role="form">
                <div class="row">
                    <div class="col-md-3">
                        <div class="form-group form-inline">
                            <label for="">选择时间</label>
                            <input type="text" name="time" class="form-control form_date" value="{{$data['time']}}" readonly placeholder="请输入id"/>
                        </div>
                    </div>
                    <div class="col-md-3">
                        <div class="col-md-3 form-inline" >
                            <div class="form-group">
                                <input type="submit" class="btn btn-sm btn-primary" value="查询"/>
                            </div>
                        </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($channel_keep_day)
            @foreach($channel_keep_day as $key=>$value)
                <tr>
                    <td>{{$channel[$value['channel']]}}</td>
                    <td>{{$value['total']}}</td>
                    @for($i=0;$i<=29;$i++)
                      @if($i<7||$i==13||$i==29)
                        @if(!isset($value['son'][$i]))
                            <td>0</td>
                        @else
                            <td>{{number_format($value['son'][$i]['total']/$value['total']*100,2,'.','')}}%</td>
                        @endif
                      @endif
                    @endfor
                </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>