按日期進行分組

#統(tǒng)計七天內(nèi)注冊用戶數(shù)量按天進行分組
$start_date = '2018-01-01';
$end_date = '2018-01-07';
$users = DB::table('users')->whereBetween('created_at',[$start_date ,$end_date])
        ->selectRaw('DATE(created_at) as date,COUNT(*) as num')
        ->groupBy('date')
        ->get();

#獲取的用戶分組數(shù)據(jù)
{
    "date": "2018-01-01",    #日期
    "num": 199             #數(shù)量
},
{
    "date": "2018-01-02",
    "num": 298
},
{
    "date": "2018-01-03",
    "num": 399
}，
...
  
#在進行圖表統(tǒng)計的時候直接從數(shù)據(jù)庫取得數(shù)據(jù)有些日期可能是沒有的,就需要我們手動進行補全一些日期
#計算日期內(nèi)天數(shù)
$stimestamp = strtotime($start_time);
$etimestamp = strtotime($end_time);
#計算日期段內(nèi)有多少天
$days = ($etimestamp - $stimestamp) / 86400;
#保存每天日期
$date = array();
for($i = 0;$i < $days;$i++){
    $date[] = date('Y-m-d', $stimestamp + (86400 * $i));
}
#循環(huán)補全日期
foreach ($date as $key => $val){
    $data[$key] = [
        'date' => $val,
        'num' => 0
    ];
    foreach ($user as $item => $value){
        if($val == $value['date']){
            $data[$key] = $value;
        }
    }
}
return $data;

按月份進行分組

#統(tǒng)計一年內(nèi)注冊用戶數(shù)量按月份進行分組
$user = DB::table('users')->whereBetween('created_at',['2018-01-01','2018-12-31'])
        ->selectRaw('DATE_FORMAT(created_at,"%Y-%m") as date,COUNT(*) as num')
        ->groupBy('date')
        ->get();
#獲取的用戶分組數(shù)據(jù)
{
    "date": "2018-01",    #月份
    "num": 1497         #數(shù)量
},
{
    "date": "2018-02",
    "num": 2354
},
{
    "date": "2018-03",
    "num": 4560
}  
#在進行圖表統(tǒng)計的時候直接從數(shù)據(jù)庫取得的數(shù)據(jù)有的月份可能是沒有的,不過月份比較少可直接寫死,同樣也需要補全
$year = date('Y',time());
#一年的月份
$months = [
    0 => $year.'-01',
    1 => $year.'-02',
    2 => $year.'-03',
    3 => $year.'-04',
    4 => $year.'-05',
    5 => $year.'-06',
    6 => $year.'-07',
    7 => $year.'-08',
    8 => $year.'-09',
    9 => $year.'-10',
    10 => $year.'-11',
    11 => $year.'-12'

];
#循環(huán)補全月份
foreach ($months as $key => $month){
    $data[$key] = [
        'date' => $month,
        'num' => 0
    ];
    foreach ($user as $k => $v){
        if($month == $v['date']){
            $data[$k] = $v;
        }
    }
}
return $data;