Data statistics for the last 6 days / 6 weeks / 6 months, there is no need to fill in 0

Posted May 25, 20202 min read

Recently received a demand:

  1. Require some large-screen data display and data visualization. Put some user data(such as the number of comments and complaints) on the visual screen. If there is no data for the day/week/month, display 0 to form a line chart

Background:
Use Laravel6.0 framework to develop
Make a separate statistical table, and count the number of clicks, comments, and complaints in days.(When the amount of data is large, do not scan the business table.)

Development:

It is easier to find out these data by using the Sql statement, but it is difficult to add 0 to the data when it is not there. When using foreach to compare one by one, the efficiency is too low.

Code:

  1. Display the data display of the last 7 days

    $params = $request-> all();

    $array = [];
    $day = 7;
    for($i = $day-1; 0 <= $i; $i--) {

      $array []= date('Y-m-d 00:00:00', strtotime('-'. $i. 'day'));
      $nums []= 0;

    }

    $result = DB ::table('data_insert_days')

          -> select([
              DB ::raw("FROM_UNIXTIME(UNIX_TIMESTAMP(time), '%Y-%m-%d') as date"),
              DB ::raw('sum(number) AS count'),
         ])
          -> whereBetween('time', [Carbon ::yesterday()-> subDays(7), Carbon ::now()])
          -> groupBy("date")
          -> orderBy('date', 'asc')
          -> get()
          -> toArray();

    array_walk($result, function($value, $key) use($array, & $nums) {

          $index = array_search($value-> date, $array);
          $nums [$index]= $value-> count;
      });
      $data = [
          'date' => $array,
          'count' => $nums
     ];

Then the final display data structure is

{
    "data":{
        "date":[
            "2020-05-19",
            "2020-05-20",
            "2020-05-21",
            "2020-05-22",
            "2020-05-23",
            "2020-05-24",
            "2020-05-25"
       ],
        "count":[
            "36",
            "11",
            "45",
            "49",
            "38",
            "39",
            "1"
       ]
    },
    "Success":true,
    "Message":{
        "Code":0,
        "Content":"Operation succeeded"
    }
}

Data display for the last 6 weeks

    $today_week = date('W', time());
    $start_week = $today_week-7;
    for($i = $start_week + 1; $i <= $today_week; $i ++) {
        $array []= $i;
        $nums []= 0;
    }

    $result = DB ::table('data_insert_days')
            -> select(DB ::raw('weekofyear(time) as w, SUM(number) as t'))
            -> where("site_id", $params ['site_id'])
            -> whereRaw('time> DATE_SUB(now(), INTERVAL 7 WEEK)')
            -> groupBy(DB ::raw('weekofyear(time)'))
            -> get()
            -> toArray();

    array_walk($result, function($value, $key) use($array, & $nums) {
            $index = array_search($value-> w, $array);
            $nums [$index]= $value-> t;
        });

        $data = [
            'w' => $array,
            't' => $nums
       ];

By analogy, the data of the last 6 months can be calculated.