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

期望

查询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 协议》,转载必须注明作者和本文链接
再见了妈妈今晚我就要远航,别为我担心我有快乐和智慧的桨~
本帖由系统于 1年前 自动加精
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 8
// 两个日期之间相差的具体时间,可以用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年前
zion_xayts_com 3年前
zion_xayts_com 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年前 评论
LuminEe 3年前
LiamHao (楼主) 3年前
半人间 3年前

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

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

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

3年前 评论

方案3更好吧,反正我一直这么干。

2年前 评论

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