查詢最近幾天每日的登陸數量，沒有數量的補零

期望

查詢2021-03-18至2021-03-20每天的使用者登入數量。如果其中哪一天沒有使用者登陸，則此日期對應登陸數量為0。資料格式如：

[
    {
        "date": "2021-03-18",
        "count": 0
    },
    {
        "date": "2021-03-19",
        "count": 2
    },
    {
        "date": "2021-03-20",
        "count": 2
    }
]

解決方案

主要思想是透過union聯合出日期中間表，需要查詢的表再join這個中間表，然後group by即可。只進行一次資料庫查詢，無需其他處理，即可滿足預期。

表結構

CREATE TABLE `qrcode_login_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) DEFAULT '',
  `channel` varchar(255) DEFAULT '',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

現存資料庫資料內容

原始SQL語句

SELECT d.date, COUNT(qrcode_login_logs.id) AS count FROM `qrcode_login_logs` 
RIGHT JOIN (
    SELECT "2021-03-18" AS date UNION SELECT "2021-03-19" AS date UNION SELECT "2021-03-20" AS date ) AS d on `d`.`date` = DATE_FORMAT(qrcode_login_logs.created_at, "%Y-%m-%d") 
GROUP BY `d`.`date`

程式碼中ORM實現

方案一

  // 沒有資料的天數也要補全為0
  $date_start = \Carbon\Carbon::parse('2021-03-18');
  $date_end = \Carbon\Carbon::parse('2021-03-20');

  // 兩個日期之間相差的天數
  $days = $date_start->diffInDays($date_end);

  // 拼接union
  $union = 'select "' . $date_start->toDateString() . '" as date ';
  for ($i=0; $i < $days; $i++) {
    $union .= 'union select "' . $date_start->addDay()->toDateString() . '" as date ';
  }

  return \App\Models\QrcodeLoginLog::rightJoin(
    \DB::raw("({$union}) as d"),
    'd.date',
    '=',
    \DB::raw('date_format(created_at, "%Y-%m-%d")')
  )
  ->selectRaw('d.date, count(id) as count')
  ->groupBy('d.date')
  ->get()
  ->toArray();

效果圖

最佳化

方案二

感謝 @91hero 的 最佳化建議，這裡補充一下最佳化後的程式碼：

  // 這裡是一個迭代器，與普通的陣列不一樣
  $days_period = \Carbon\Carbon::parse('2021-03-18')->daysUntil('2021-03-20');

  // 拼接union
  $union = implode(' union ', iterator_to_array($days_period->map(function($day){
    return 'select "'.$day->toDateString().'" as date';
  })));

  return \App\Models\QrcodeLoginLog::rightJoin(
    \DB::raw("({$union}) as d"),
    'd.date',
    '=',
    \DB::raw('date_format(created_at, "%Y-%m-%d")')
  )
  ->selectRaw('d.date, count(id) as count')
  ->groupBy('d.date')
  ->get()
  ->toArray();

方案三

感謝 @半人間、@pan_zoe 、@cheer、@thus 的建議：

MySQL儘可能簡單，將資料處理放在PHP中

說的也對，對於時間區間跨度較大的場景，例如 1 年或更多，會導致$union的字串過長，SQL關聯表變大，影響記憶體佔用和查詢效率。所以這裡再補充一個方案：

  // 這裡是一個迭代器，與普通的陣列不一樣
  $days_period = \Carbon\Carbon::parse('2021-03-18')->daysUntil('2021-03-20');

  // 用最簡單的SQL查詢出指定日期區間內每日的登陸次數
  // 如果某天沒有人登陸，則這裡的結果日期是不連續的
  $fracture_days = \App\Models\QrcodeLoginLog::
    selectRaw('date_format(created_at, "%Y-%m-%d") as date, count(id) as count')
    ->whereBetween('created_at', [
      $days_period->first()->toDateString(),
      // 因為2021-03-20指的是2021-03-20 00:00:00
      // 如果想包含2021-03-20當天的資料，那就需要需要再加一天
      $days_period->last()->addDay()->toDateString(),
    ])
    ->groupBy('date')
    ->pluck('count', 'date')
    ->toArray();

  // 建立連續的日期區間陣列
  return iterator_to_array(
    $days_period->map( function ($day) use ($fracture_days) {
      $day_str = $day->toDateString();
      return [
        'date' => $day_str,
        'count' => $fracture_days[$day_str] ?? 0,
      ];
    })
  );

本作品採用《CC 協議》，轉載必須註明作者和本文連結