查到了不错的方法收藏一下,很好用,记录一下需要注意点:我这里主要参考了selectRaw里面sql写法,sql的语句还是要多多学习,哈哈
按日期进行分组:
public function index()
{
//统计七天内注册用户数量按天进行分组
$user = DB::table('*****')->whereBetween('created_at', ['前端传值开始时间', '前端传值结束时间'])
->selectRaw('date(created_at) as date,count(*) as value')
->groupBy('date')->get();
#在进行图表统计的时候直接从数据库取得数据有些日期可能是没有的,就需要我们手动进行补全一些日期
#计算日期内天数
$stimestamp = strtotime("前端传值开始时间");
$etimestamp = strtotime("前端传值结束时间");
#计算日期段内有多少天
$days = ($etimestamp - $stimestamp) / 86400;
#保存每天日期
$date = array();
for ($i = 0; $i < $days; $i++) {
$date[] = date('Y-m-d', $stimestamp + (86400 * $i));
}
#循环补全日期
foreach ($date as $key => $val) {
$data[$key] = [
'date' => $val,
'value' => 0
];
foreach ($user as $item => $value) {
if ($val == $value['date']) {
$data[$key] = $value;
}
}
}
return $data;
}
按月份进行分组:
public function index()
{
$user = DB::table('users')->whereBetween('created_at',['前端传值开始时间','前端传值开始时间'])
->selectRaw('DATE_FORMAT(created_at,"%Y-%m") as date,COUNT(*) as value')
->groupBy('date')->get();
#在进行图表统计的时候直接从数据库取得的数据有的月份可能是没有的,不过月份比较少可直接写死,同样也需要补全
$year = date('Y',time());
#一年的月份
$month = [
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',
];
#循环补全月份
foreach ($month as $key => $val){
$data[$key] = [
'date' => $val,
'value' => 0
];
foreach ($user as $item => $value){
if($val == $value['date']){
$data[$key] = $value;
}
}
}
return $data;
}
来源:https://www.jb51.net/article/158347.htm
这里需要注意的就是:看你数据库的created_at的日期是日期格式的话下面的selectRaw里面的直接可以用,要是时间戳格式的需要使用FROM_UNIXTIME(created_at,"%Y-%m-%d %H:%i:%s")转换一下时间格式,在这里踩坑了,记录一下,
推荐