当前位置 主页 > 网站技术 > 代码类 > 最大化 缩小

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

    栏目:代码类 时间:2019-10-09 18:05

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

    $days = Input::get('days', 7);
    
    $range = \Carbon\Carbon::now()->subDays($days);
    
    $stats = User::where('created_at', '>=', $range)
     ->groupBy('date')
     ->orderBy('date', 'DESC')
     ->get([
      DB::raw('Date(created_at) as date'),
      DB::raw('COUNT(*) as value')
     ]);
    

    SELECT 
    sum(case when `EmailSource`='FM' then 1 else 0 end) as FM_Statistic,
    sum(case when `EmailSource`='UOC' then 1 else 0 end) as UOC_Statistic,
    sum(case when `EmailSource`='OC' then 1 else 0 end) as OC_Statistic,
    DATE_FORMAT(Date,'%Y-%m-%d') AS `DateTime` 
    FROM `user_performance` 
    WHERE Email != '' AND Email != 'TOTAL'
    AND (DATE_FORMAT(Date,'%Y-%m-%d') >= DATE_FORMAT('2011-02-5','%Y-%m-%d')) 
    AND (DATE_FORMAT(Date,'%Y-%m-%d') <= DATE_FORMAT('2011-03-07','%Y-%m-%d')) 
    GROUP BY `Date`
    
     public function getNumber()
     {
      $data = [];
      $customers = Customer::all(['id', 'customer_type', 'created_at']);
    
      #今天数据
      $data['customer_today'] = Customer::where('customer_type', 1)->where('created_at', Carbon::today())->count();
      $data['teacher_today'] = Customer::where('customer_type', 2)->where('created_at', Carbon::today())->count();
    
      #昨天数据
      $data['customer_yesterday'] = Customer::where('customer_type', 1)->where('created_at', Carbon::yesterday())->count();
      $data['teacher_yesterday'] = Customer::where('customer_type', 2)->where('created_at', Carbon::yesterday())->count();
    
      $data['today'] = $data['customer_today'] + $data['teacher_today'];
      $data['yesterday'] = $data['customer_yesterday'] + $data['teacher_yesterday'];
    
      // 本周数据
      $this_week = [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()];
      $data['customer_this_week'] = Customer::where('customer_type', 1)->whereBetween('created_at', $this_week)->count();
    
      $data['teacher_this_week'] = Customer::where('customer_type', 2)->whereBetween('created_at', $this_week)->count();
    
      // 上周数据
      $last_week = [Carbon::now()->startOfWeek()->subWeek(), Carbon::now()->endOfWeek()->subWeek()];
      $data['customer_last_week'] = Customer::where('customer_type', 1)->whereBetween('created_at', $last_week)->count();
    
      $data['teacher_last_week'] = Customer::where('customer_type', 2)->whereBetween('created_at', $last_week)->count();
    
      $data['this_week'] = $data['customer_this_week'] + $data['teacher_this_week'];
      $data['last_week'] = $data['customer_last_week'] + $data['teacher_last_week'];
    
      // 本月数据
      $data['customer_this_month'] = Customer::where('customer_type', 1)->whereMonth('created_at', Carbon::now()->month)->count();
      $data['teacher_this_month'] = Customer::where('customer_type', 2)->whereMonth('created_at', Carbon::now()->month)->count();
    
      // 上月数据
      $data['customer_last_month'] = Customer::where('customer_type', 1)->whereMonth('created_at', Carbon::now()->subMonth()->month)->count();
      $data['teacher_last_month'] = Customer::where('customer_type', 2)->whereMonth('created_at', Carbon::now()->subMonth()->month)->count();
    
      $data['this_month'] = $data['customer_this_month'] + $data['teacher_this_month'];
      $data['last_month'] = $data['customer_last_month'] + $data['teacher_last_month'];
    
      // 本年数据
      $data['customer_this_year'] = Customer::where('customer_type', 1)->whereYear('created_at', Carbon::now()->year)->count();
      $data['teacher_this_year'] = Customer::where('customer_type', 2)->whereYear('created_at', Carbon::now()->year)->count();
    
      $data['today_login_users'] = LoginLog::whereDate('created_at', '=', Carbon::today())
       ->groupBy('customer_id')
       ->orderBy('customer_id')
       ->count();
    
      $data['yesterday_login_users'] = LoginLog::whereDate('created_at', '=', Carbon::yesterday())
       ->groupBy('customer_id')
       ->orderBy('customer_id')
       ->count();
    
      $data['this_month_login_users'] = LoginLog::whereMonth('created_at', Carbon::now()->month)
       ->groupBy('customer_id')
       ->orderBy('customer_id')
       ->count();
    
      $data['last_month_login_users'] = LoginLog::whereMonth('created_at', Carbon::now()->subMonth()->month)
       ->groupBy('customer_id')
       ->orderBy('customer_id')
       ->count();
    
      return $data;
     }