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

LiamHao發表於2021-03-21

期望

查詢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();
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章