Laravel按分组和分钟统计近1小时在线用户数

核心逻辑 #

要实现分钟级的分组在线统计，需先生成近1小时的分钟时间序列，再关联用户、分组、日志数据，判断用户在对应分钟是否处于在线状态，最后按分组和分钟聚合统计数量。

在线状态判定规则：

• 用户的online_at ≤ 目标分钟的时间点
• 用户的disconnected_at ≥ 目标分钟的时间点 或 disconnected_at为null（当前仍在线）

方法一：Laravel 查询构建器实现 #

1. 生成分钟时间序列 #

先构建近1小时内所有整分钟的时间集合：

// 生成从1小时前到现在的每个整分钟时间点，格式为Y-m-d H:i:00
$minutes = collect();
$startTime = now()->subHour()->startOfMinute();
$endTime = now()->startOfMinute();

while ($startTime <= $endTime) {
    $minutes->push($startTime->format('Y-m-d H:i:00'));
    $startTime->addMinute();
}

2. 关联查询并统计 #

通过交叉连接和左关联，结合时间序列完成统计：

use Illuminate\Support\Facades\DB;

$stats = DB::table(DB::raw("('" . implode("','", $minutes->toArray()) . "') as minutes(time)"))
    ->crossJoin('ispgroups')
    ->leftJoin('subscribers', 'subscribers.group_id', '=', 'ispgroups.id')
    ->leftJoin('logs', function ($join) {
        $join->on('logs.username', '=', 'subscribers.username')
             ->whereRaw("logs.online_at <= minutes.time")
             ->whereRaw("(logs.disconnected_at >= minutes.time OR logs.disconnected_at IS NULL)");
    })
    ->select(
        'ispgroups.id as group_id',
        'ispgroups.name as group_name', // 可根据分组表实际字段调整
        'minutes.time as minute',
        DB::raw('COUNT(DISTINCT subscribers.id) as online_users')
    )
    ->groupBy('ispgroups.id', 'ispgroups.name', 'minutes.time')
    ->orderBy('minutes.time', 'asc')
    ->get();

注意事项 #

• 你原Subscriber模型的group()关联参数顺序有误，正确的belongsTo写法应为：

  public function group(){
      return $this->belongsTo('App\Models\Ispgroup', 'group_id', 'id');
  }
  

  （第二个参数是当前模型的外键group_id，第三个是关联模型的主键id）
• 若logs表数据量较大，建议给username、online_at、disconnected_at字段添加索引，提升查询效率。

方法二：原生SQL实现（性能更优） #

数据量较大时，原生SQL的执行效率更高，以下分两种数据库给出实现：

PostgreSQL版本 #

WITH minute_series AS (
    SELECT generate_series(
        date_trunc('minute', NOW() - INTERVAL '1 hour'),
        date_trunc('minute', NOW()),
        INTERVAL '1 minute'
    ) AS minute_time
)
SELECT
    ig.id AS group_id,
    ig.name AS group_name,
    to_char(ms.minute_time, 'YYYY-MM-DD HH24:MI:00') AS minute,
    COUNT(DISTINCT s.id) AS online_users
FROM minute_series ms
CROSS JOIN ispgroups ig
LEFT JOIN subscribers s ON s.group_id = ig.id
LEFT JOIN logs l 
    ON l.username = s.username
    AND l.online_at <= ms.minute_time
    AND (l.disconnected_at >= ms.minute_time OR l.disconnected_at IS NULL)
GROUP BY ig.id, ig.name, ms.minute_time
ORDER BY ms.minute_time ASC;

MySQL版本 #

WITH RECURSIVE minute_series AS (
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR), '%Y-%m-%d %H:%i:00') AS minute_time
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(minute_time, INTERVAL 1 MINUTE), '%Y-%m-%d %H:%i:00')
    FROM minute_series
    WHERE minute_time <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00')
)
SELECT
    ig.id AS group_id,
    ig.name AS group_name,
    ms.minute_time AS minute,
    COUNT(DISTINCT s.id) AS online_users
FROM minute_series ms
CROSS JOIN ispgroups ig
LEFT JOIN subscribers s ON s.group_id = ig.id
LEFT JOIN logs l 
    ON l.username = s.username
    AND l.online_at <= ms.minute_time
    AND (l.disconnected_at >= ms.minute_time OR l.disconnected_at IS NULL)
GROUP BY ig.id, ig.name, ms.minute_time
ORDER BY ms.minute_time ASC;

结果格式示例 #

返回的统计数据可直接用于生成统计图，格式如下：

[
    {
        "group_id": 1,
        "group_name": "分组A",
        "minute": "2024-05-20 10:00:00",
        "online_users": 12
    },
    {
        "group_id": 1,
        "group_name": "分组A",
        "minute": "2024-05-20 10:01:00",
        "online_users": 15
    }
]

内容的提问来源于stack exchange，提问作者Ahmed Homs