查询最近几天每日的登陆数量,没有数量的补零

期望

查询2021-03-182021-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 协议》,转载必须注明作者和本文链接
再见了妈妈今晚我就要远航,别为我担心我有快乐和智慧的桨~
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 7
// 两个日期之间相差的具体时间,可以用carbon来实现
$periods = Carbon::parse($this->input('start'))->daysUntil($this->input('end'));
3周前 评论
    $counts = \App\Models\QrcodeLoginLog::selectRaw('date, count(id) as count')
        ->whereBetween('date', ['2021-03-18', '2021-03-25'])
        ->groupBy('date')
        ->get();

    $days = new CarbonPeriod('2021-03-18', '1 days', '2021-03-25');

    $result = [];
    foreach ($days as $day) {
        $count = $counts->where('date', $day->toDateString())->first();
        $result[$day->toDateString()] =  $count->count ?? 0;
    }

    dd($result);

//主要减少数据库关联查询

3周前 评论
半人间 (作者) 3周前
LiamHao (楼主) 3周前
llii 3周前
llii 3周前
半人间 (作者) 3周前
LiamHao (楼主) 3周前
半人间 (作者) 3周前
LiamHao (楼主) 3周前
LiamHao (楼主) 3周前
LiamHao (楼主) 3周前

骚操作啊,['2021-02-18' => 0,'2021-02-21' => 0] + ['2021-02-19' => 0] 会不会更好一点,生成一个2021-03-18 到2021-03-21 的数据,然后和数据库的数据合并一下就ok了吧!核心就是数据库原有的不变,合并他们的差集

3周前 评论
LiamHao (楼主) 3周前
//如果想搞合并二个结果的.

$counts = Aggregate::selectRaw('date, count(id) as count')
            ->whereBetween('date', ['2021-03-18', '2021-03-25'])
            ->groupBy('date')
            ->get()
            ->keyBy('date')
            ->map(function ($value) {
                return $value->count;
            });

        $days = new CarbonPeriod('2021-03-18', '1 days', '2021-03-25');

        $result = collect([]);
        foreach ($days as $day) {
            $result->prepend(0, $day->toDateString());
        }
        $result = $result->merge($counts);

        dd($result->all());
3周前 评论

:neutral_face: 别把压力给到mysql,mysql这一层的查询一定要简单简单再简单。

3周前 评论
LiamHao (楼主) 3周前
半人间 3周前
LuminEe 3周前

这种是不是应该先group by 日期,然后php来把不存在的补0

3周前 评论
LiamHao (楼主) 3周前
Jourdon

刚好写过这个功能,left join 子查询等等都用上了,反正一年的也能查,就是慢,
大概就是查询的时间段生成一个临时表。然后 Leftjoin上去查到的数据。基本上 SQL 干完了,不需要 PHP 去干

3周前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!