Laravel统计一段时间间隔的数据方法

时间:2022-10-13 22:45:03

获取七天以前到现在的数据:

  1. $days = Input::get('days', 7);
  2.  
  3. $range = \Carbon\Carbon::now()->subDays($days);
  4.  
  5. $stats = User::where('created_at', '>=', $range)
  6. ->groupBy('date')
  7. ->orderBy('date', 'DESC')
  8. ->get([
  9. DB::raw('Date(created_at) as date'),
  10. DB::raw('COUNT(*) as value')
  11. ]);

Laravel统计一段时间间隔的数据方法

  1. SELECT
  2. sum(case when `EmailSource`='FM' then 1 else 0 end) as FM_Statistic,
  3. sum(case when `EmailSource`='UOC' then 1 else 0 end) as UOC_Statistic,
  4. sum(case when `EmailSource`='OC' then 1 else 0 end) as OC_Statistic,
  5. DATE_FORMAT(Date,'%Y-%m-%d') AS `DateTime`
  6. FROM `user_performance`
  7. WHERE Email != '' AND Email != 'TOTAL'
  8. AND (DATE_FORMAT(Date,'%Y-%m-%d') >= DATE_FORMAT('2011-02-5','%Y-%m-%d'))
  9. AND (DATE_FORMAT(Date,'%Y-%m-%d') <= DATE_FORMAT('2011-03-07','%Y-%m-%d'))
  10. GROUP BY `Date`
  1. public function getNumber()
  2. {
  3. $data = [];
  4. $customers = Customer::all(['id', 'customer_type', 'created_at']);
  5.  
  6. #今天数据
  7. $data['customer_today'] = Customer::where('customer_type', 1)->where('created_at', Carbon::today())->count();
  8. $data['teacher_today'] = Customer::where('customer_type', 2)->where('created_at', Carbon::today())->count();
  9.  
  10. #昨天数据
  11. $data['customer_yesterday'] = Customer::where('customer_type', 1)->where('created_at', Carbon::yesterday())->count();
  12. $data['teacher_yesterday'] = Customer::where('customer_type', 2)->where('created_at', Carbon::yesterday())->count();
  13.  
  14. $data['today'] = $data['customer_today'] + $data['teacher_today'];
  15. $data['yesterday'] = $data['customer_yesterday'] + $data['teacher_yesterday'];
  16.  
  17. // 本周数据
  18. $this_week = [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()];
  19. $data['customer_this_week'] = Customer::where('customer_type', 1)->whereBetween('created_at', $this_week)->count();
  20.  
  21. $data['teacher_this_week'] = Customer::where('customer_type', 2)->whereBetween('created_at', $this_week)->count();
  22.  
  23. // 上周数据
  24. $last_week = [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()];
  25. $data['customer_last_week'] = Customer::where('customer_type', 1)->whereBetween('created_at', $last_week)->count();
  26.  
  27. $data['teacher_last_week'] = Customer::where('customer_type', 2)->whereBetween('created_at', $last_week)->count();
  28.  
  29. $data['this_week'] = $data['customer_this_week'] + $data['teacher_this_week'];
  30. $data['last_week'] = $data['customer_last_week'] + $data['teacher_last_week'];
  31.  
  32. // 本月数据
  33. $data['customer_this_month'] = Customer::where('customer_type', 1)->whereMonth('created_at', Carbon::now()->month)->count();
  34. $data['teacher_this_month'] = Customer::where('customer_type', 2)->whereMonth('created_at', Carbon::now()->month)->count();
  35.  
  36. // 上月数据
  37. $data['customer_last_month'] = Customer::where('customer_type', 1)->whereMonth('created_at', Carbon::now()->subMonth()->month)->count();
  38. $data['teacher_last_month'] = Customer::where('customer_type', 2)->whereMonth('created_at', Carbon::now()->subMonth()->month)->count();
  39.  
  40. $data['this_month'] = $data['customer_this_month'] + $data['teacher_this_month'];
  41. $data['last_month'] = $data['customer_last_month'] + $data['teacher_last_month'];
  42.  
  43. // 本年数据
  44. $data['customer_this_year'] = Customer::where('customer_type', 1)->whereYear('created_at', Carbon::now()->year)->count();
  45. $data['teacher_this_year'] = Customer::where('customer_type', 2)->whereYear('created_at', Carbon::now()->year)->count();
  46.  
  47. $data['today_login_users'] = LoginLog::whereDate('created_at', '=', Carbon::today())
  48. ->groupBy('customer_id')
  49. ->orderBy('customer_id')
  50. ->count();
  51.  
  52. $data['yesterday_login_users'] = LoginLog::whereDate('created_at', '=', Carbon::yesterday())
  53. ->groupBy('customer_id')
  54. ->orderBy('customer_id')
  55. ->count();
  56.  
  57. $data['this_month_login_users'] = LoginLog::whereMonth('created_at', Carbon::now()->month)
  58. ->groupBy('customer_id')
  59. ->orderBy('customer_id')
  60. ->count();
  61.  
  62. $data['last_month_login_users'] = LoginLog::whereMonth('created_at', Carbon::now()->subMonth()->month)
  63. ->groupBy('customer_id')
  64. ->orderBy('customer_id')
  65. ->count();
  66.  
  67. return $data;
  68. }
  1. public function numberCount()
  2. {
  3. $days = request('days', 7);
  4.  
  5. $range = Carbon::today()->subDays($days);
  6.  
  7. $day_stats = Customer::where('created_at', '>=', $range)
  8. ->groupBy('date')
  9. ->orderBy('date', 'DESC')
  10. ->get([
  11. \DB::raw('DATE_FORMAT(created_at,\'%Y-%m-%d\') as date,SUM(CASE WHEN customer_type = 1 THEN 1 ELSE 0 END) AS customer,SUM(CASE WHEN customer_type = 2 THEN 1 ELSE 0 END) AS teacher'),
  12. ])
  13. ->toJSON();
  14.  
  15. $week_stats = Customer::groupBy('week')
  16. ->orderBy('week', 'DESC')
  17. ->get([
  18. \DB::raw('DATE_FORMAT(created_at,\'%Y W%u\') as week,SUM(CASE WHEN customer_type = 1 THEN 1 ELSE 0 END) AS customer, SUM(CASE WHEN customer_type = 2 THEN 1 ELSE 0 END) AS teacher'),
  19. ])
  20. ->toJSON();
  21. // dd($week_stats);
  22.  
  23. // \DB::enableQueryLog();
  24. $month_stats = Customer::groupBy('month')
  25. ->orderBy('month', 'DESC')
  26. ->get([
  27. \DB::raw('DATE_FORMAT(created_at,\'%Y-%m\') as month,SUM(CASE WHEN customer_type = 1 THEN 1 ELSE 0 END) AS customer,SUM(CASE WHEN customer_type = 2 THEN 1 ELSE 0 END) AS teacher'),
  28. ])
  29. ->toJSON();
  30. // dd(\DB::getQueryLog());
  31. // dd($week_stats, $month_stats);
  32. $data = $this->getNumber();
  33. // dd($day_stats, $week_stats, $month_stats, $data);
  34. return view('admin.numberCount', compact('day_stats', 'week_stats', 'month_stats', 'data'));
  35. }

效果图:

Laravel统计一段时间间隔的数据方法

以上这篇Laravel统计一段时间间隔的数据方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们。

原文链接:https://blog.csdn.net/zhezhebie/article/details/78622534