查询最近几天每日的登陆数量,没有数量的补零
期望
查询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 协议》,转载必须注明作者和本文链接
推荐文章: