如何查询最近7天的用户注册数据

DB::table('users')->whereBetween('created_at',['2020-7-23','2020-7-29']) ->selectRaw('date(created_at) as date,count(*) as num') ->groupBy('date')->get();

查最近7天，然后按日期分组。

xxxxxxxxxxxxxxxxxxxxxxxxxxxx
->select(
     DB::raw("COUNT(id) AS num")
     DB::raw("DATE_FORMAT(created_at,'%m/%d') AS date")
)
->groupBy('date')
->orderBy('date')
->get();

用 FROM_UNIXTIME(create_time,%Y%M%D) ，多翻翻mysql的手册，熟悉原生的函数就会好一些。不过得注意，使用过函数的字段，是不走索引的。

尽量少用数据库函数。先查7天内的id, 在用程序去分组

        $start_date = strtotime($request->start_date);
        $end_date = strtotime($request->end_date);
        $cut = ($end_date - $start_date) / 86400;
        $data = [];
        for($tmp = 0; $tmp <= $cut; $tmp++){
            $data[date('Y-m-d',$start_date + 86400 * $tmp)] = 0;
        }
        $date = [$start_date, $end_date];
        User::query()->selectRaw('count(1) as c, from_unixtime(created_at, \'%Y-%m-%d\') as day')
            ->groupBy('day')
            ->whereBetween('created_at', $date)
            ->get()
            ->map(function ($v) use (&$data){
            $data[$v->day] = $v->c;
        });
        $this->success($data)->throwResponse();

我用的和1楼的差不多，只是纠结里面会有空缺的日期

实时查询最近七天的数据：

    $start = date('Y-m-d',strtotime('-7 day'));    // 七天前的日期

    $end = date("Y-m-d",strtotime(now()));    // 当前日期

    $arr1 = array();

    for($i=0; $i<7; $i++){

        $arr1[date('Y-m-d',strtotime("-{$i} day"))] = [
            'count' => 0,
            'date' => date('n-j',strtotime("-{$i} day"))
        ];
    }

    $arr1 = array_reverse($arr1);     // 排序

    $data = User::query()
            ->whereRaw("date_format(created_at, '%Y-%m-%d') >='{$start}' and date_format(created_at, '%Y-%m-%d') <= '{$end}'")   // 如果格式是datetime或者timestamp就需要这样
            ->selectSub("date_format(created_at, '%Y-%m-%d')", 'date')
            ->groupByRaw("date_format(created_at, '%Y-%m-%d')")
            ->get();

    foreach ($arr1 as $k=>$v){
        foreach ($data as $k1=>$v1){
            if($k == $k1){
                $arr1[$k]['count'] = $v1;
            }
        }
    }

大概就是这样了，具体按照自己的数据结构修改。

博客：sql 语言 groupBy 分组统计时间段解决方案

$days = 7;
$list = Model::query()->where('created_at', ">=", $date." 00:00:00");->get();
for($i = 0; $i<$days; $i++ ) {

    $date = Carbon::now()->subDay($i)->toDateString();

    if(intval($request->input('type', 1)) == 1) {
    $count = $list->where('action', 'view')->whereDate('created_at', $date)->count();

    } else {
    $count = $list->where('action', 'view')->whereDate('created_at', $date)->unique('openid')->count();

    }

    $data[] = compact('date', 'count');
}

补全日期：

    $start_day = date('Y-m-d',strtotime('-7 day'));

    $end_date = date('Y-m-d');

    $week = [];

    for ($i = 1 ; $i < 8 ; $i++) {
        $week[] = [
            'date' => date('m-d',strtotime("+ $i day",strtotime($start_day))),
            'num' => 0
        ];
    }
    $user = User::query()->whereBetween('created_at',[$start_day,$end_date])
        ->selectRaw('date(created_at) as date,count(*) as num')
        ->groupBy('date')
        ->orderBy('date')
        ->get();

    foreach ($user as $item) {
        foreach ($week as $k => $v) {
            if (date('m-d',strtotime($item->date)) == $v['date']) {
                $week[$k]['num'] = $item->num;
                break;
            }
        }
    }

    return response()->json(['code' => 200,'data' => $week]);

不过要是统计一个月的数据里面的双重循环 次数最多可能是 30*30 是不是量有点大了？

请问楼主有找到最完美的解决方案么？

这里还有个实现，可以参考下：查询最近几天的登陆数量，没有数量的补零